Kbase P17829: How to dump SQL-92 privileges
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
30/03/2010 |
|
Status: Verified
GOAL:
How to dump SQL-92 privileges.
GOAL:
How to dump SQL-92 table permissions.
GOAL:
How to obtain the SQL-92 permissions set within a database.
GOAL:
How can SQL security information be dumped
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
Progress 9.x
Progress 9.1x
FIX:
Use the SQLSCHEMA command line utility with the -g parameter to generate SQL-92 GRANT statements of one or or more tables. Example:
sqlschema -u user_name [-a password] -g [owner_name].table_name -o outputFile database_name
database_name = progress:T:host:portnum:dbname Pass the -g parameter %.% to obtain a list for all the tables within the database. The output file will contain all the permissions granted. Such as: grant select on "SYSPROGRESS"."SYS_TBL_CONSTRS" to "PUBLIC";
The output file name created will always be created with a file extension of .dfsql.
To load the file output from the sqlschema command into a new database, use SQLExplorer in character mode with the -infile option. sqlexp -char -db dbname -H localhost -S portnumber -infile c:\temp\input.sqlSyntax to dump all table rights for all tables into a file which can later be loaded with SQL Explorer:
sqlschema -u <username> -a <password> -g %.% -o <outputfile> progress:T:<hostname or IP>:<port name or number>:<database name>
Syntax to dump all table rights for one table into a file which can later be loaded with SQL Explorer:
sqlschema -u <username> -a <password> -g <owner name>.<table name> -o <outputfile> progress:T:<hostname or IP>:<port name or number>:<database name>
Example: To dump the rights defined for customer table of sports2000 database running on sample port 2500 on sample machine mymachine, use the following command:
sqlschema -u myusername -a mypassword -g PUB.customer -o myoutputfile progress:T:mymachine:2500:sports2000