To dump SQL-92 table permissions:Generate SQL-92 GRANT statements on one or more tables with the SQLSCHEMA -g parameter:
- The output file name created will always be created with a file extension of .dfsql.
- Pass the -g parameter %.% to obtain a list for all the tables in the database.
- In Progress versions prior 9.1E02, OpenEdge 10.0B03, UPDATE permissions need to be manually added. Refer to Article The Sqlschema utility does not handle update permissions when using the -g parameter.
sqlschema -u user_name [-a password] -g [owner_name].table_name -o outputFile <database_name>
Where: database_name = progress:T:host:portnum:dbname
Examples:
1. Dump all table rights for all tables into a file which can loaded later.
sqlschema -u <username> -a <password> -g %.% -o <outputfile> progress:T:<hostname or IP>:<port name or number>:<database name>
2. Dump all table rights for one table into a file which can loaded later:
sqlschema -u <username> -a <password> -g <owner name>.<table name> -o <outputfile> progress:T:<hostname or IP>:<port name or number>:<database name>
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
The output file will contain all the permissions granted. Example:
grant select on "SYSPROGRESS"."SYS_TBL_CONSTRS" to "PUBLIC";
To load the SQL-92 table permissions:Load the file output from the SQLSCHEMA command into a new database:
Example:
sqlexp -char -db dbname -H localhost -S portnumber -infile c:\temp\input.dfsql
