Consultor Eletrônico

Status: Verified

GOAL:

How to grant privileges on all the tables of a particular schema to a list of users or to PUBLIC.

FACT(s) (Environment):

Progress 9.1A
Progress 9.1B
Progress 9.1C
Progress 9.1E

FIX:

The code presented in this solution applies to Progress 9.1A, 9.1B, 9.1C and 9.1E only. It does not apply to 9.1D. If DBAs would like to grant privileges on all the tables of a particular schema to a list of users or to PUBLIC, multiple GRANT statements must be performed, one per each table. For example:

GRANT SELECT ON PUB.CUSTOMER TO PUBLIC;
GRANT SELECT,UPDATE ON PUB.STATE TO USER1,USER2,USER3;
Note that multiple privileges can be listed and multiple user names can be used. However, in each statement only one table can be specified.

The SQL-92 Java Stored Procedure listed below provides a possible approach to grant privileges to all tables in a specific schema. The stored procedure generates a SQL script with the corresponding SQL statements necessary to grant the privileges. Execute the GRANT commands generated by the procedure to accomplish the task. You may modify this stored procedure to fit your particular needs or create a procedure revokeschema to revoke the privileges using a similar logic:

Stored procedure name: grantschema
Parameters:

- cPrivileges = Character string listing the privileges to grant.

- cSchemaName = Character string with the name of the schema.

- cUsers = Character string listing the grantee users or PUBLIC.
Sample usage:
- CALL grantschema ('SELECT','PUB','PUBLIC');
- CALL grantschema ('SELECT,UPDATE', 'PUB', 'USER1,USER2,USER3');

When you execute this CALL statement from the SQL Explorer, disable the option "Table Format" in the View menu so the output can be copied and pasted into the SQL Explorer editor to effectively run the SQL script.

To verify the granted privileges, you can use the following SQL statement:

select grantee, tblowner, tbl from sysprogress.systabauth

where grantor = ''

grantschema - SQL-92 Java Stored Procedure
-----------------------------------------

DROP PROCEDURE grantschema;
CREATE PROCEDURE grantschema ( IN cPrivileges CHARACTER(60),
IN cSchemaName CHARACTER(15),
IN cUsers CHARACTER(60))
RESULT ( cSqlCmd CHARACTER(132) )
BEGIN
SQLCursor scTbl = new SQLCursor ("select tbl from "
+ "sysprogress.systables where owner = ? and "
+ "tbltype = 'T'");
scTbl.setParam (1, cSchemaName);
scTbl.open ();
scTbl.fetch ();
while (scTbl.found()) {
String sTblName = (String) scTbl.getValue(1, CHARACTER);
String sGrantCmd = new String( "GRANT " + cPrivileges.trim()
+ " ON " + cSchemaName.trim() + ".\""
+ sTblName.trim()
&nb.sp; + "\" TO " + cUsers.trim() + ";" );
SQLResultSet.set (1, sGrantCmd);
SQLResultSet.insert();
scTbl.fetch();
}
scTbl.close();
SQLResultSet.set (1, "COMMIT WORK;");
SQLResultSet.insert();
END;
COMMIT WORK;
.