Kbase P95289: How to generate SQL script to GRANT all privileges on all the database tables using 4GL?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
6/22/2010 |
|
Status: Verified
GOAL:
How to generate SQL script to GRANT all privileges on all the database tables using 4GL?
GOAL:
Batch file to grant SQL92 permissions to all tables
GOAL:
Code to generate SQL-92 script to Grant Privileges on a Schema tables.
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
The following procedure generates an SQL script file to to GRANT ALL privileges on all the connected database tables to PUBLIC. To generate the SQL script, simply connect to the database and run the following 4GL/ABL code.
To GRANT ALL privileges on all the connected database tables to PUBLIC, simply execute the SQL script generated for it by this 4GL/ABL procedure. The resulting SQL script files may be executed using any JDBC client like the Progress SQL Explorer Tool.
To generate SQL script syntax to run using an ODBC client like WinSQL, run the GenerateScriptForWinSQL.p version of this code in the note below:
/***************GenerateScriptForJDBC.p************/
OUTPUT TO VALUE("GrantAll.sql").
FOR EACH _File WHERE _Tbl-Type = "T":
PUT UNFORMATTED "GRANT ALL ON PUB." + QUOTER(_File-Name) + " TO PUBLIC;" SKIP.
END.
PUT UNFORMATTED "COMMIT WORK;" SKIP.
OUTPUT CLOSE.
The SQL-92 script file, "GrantAll.sql", generated by running the above 4GL code will look like the following sample:
GRANT ALL ON PUB."Benefits" TO PUBLIC;
GRANT ALL ON PUB."BillTo" TO PUBLIC;
GRANT ALL ON PUB."Bin" TO PUBLIC;
GRANT ALL ON PUB."Customer" TO PUBLIC;
GRANT ALL ON PUB."Department" TO PUBLIC;
GRANT ALL ON PUB."Employee" TO PUBLIC;
GRANT ALL ON PUB."Family" TO PUBLIC;
GRANT ALL ON PUB."Feedback" TO PUBLIC;
GRANT ALL ON PUB."InventoryTrans" TO PUBLIC;
GRANT ALL ON PUB."Invoice" TO PUBLIC;
GRANT ALL ON PUB."Item" TO PUBLIC;
GRANT ALL ON PUB."LocalDefault" TO PUBLIC;
GRANT ALL ON PUB."Order" TO PUBLIC;
GRANT ALL ON PUB."OrderLine" TO PUBLIC;
GRANT ALL ON PUB."POLine" TO PUBLIC;
GRANT ALL ON PUB."PurchaseOrder" TO PUBLIC;
GRANT ALL ON PUB."RefCall" TO PUBLIC;
GRANT ALL ON PUB."Salesrep" TO PUBLIC;
GRANT ALL ON PUB."ShipTo" TO PUBLIC;
GRANT ALL ON PUB."State" TO PUBLIC;
GRANT ALL ON PUB."Supplier" TO PUBLIC;
GRANT ALL ON PUB."SupplierItemXref" TO PUBLIC;
GRANT ALL ON PUB."TimeSheet" TO PUBLIC;
GRANT ALL ON PUB."Vacation" TO PUBLIC;
GRANT ALL ON PUB."Warehouse" TO PUBLIC;
COMMIT WORK;
The SQL-92 statements in the above "GrantAll.sql" file may be executed using any ODBC or JDBC client. For example, the following command executes the above script using the Progress SQL Explorer Tool Character JDBC Client:
sqlexp -char -db sports2000 -S 23456 -H hostname -infile GrantAll.sql -outfile GrantAll.out -user YourUserId -password YourPassword