Salesforce

The Sqlschema utility does not handle update permissions when using the -g parameter.

« Go Back

Information

 
TitleThe Sqlschema utility does not handle update permissions when using the -g parameter.
URL NameP71617
Article Number000171086
EnvironmentProduct: Progress
Version: 9.1D, 9.1E
Product: OpenEdge
Version: 10.0x
OS: All supported platforms
Question/Problem Description
The SQLSCHEMA utility does not handle update permissions when using the -g parameter.
The SQLSCHEMA utility does not produce any GRANT UPDATE statements when using the -g parameter.
Steps to Reproduce
Clarifying Information
Error Message
Defect NumberDefect PSC00149953 / OE00101858 / 20040311-001
Enhancement Number
Cause
The sqlschema utility does not handle update permissions due to missing functionality when generating output to the .dsql file.
Resolution
Upgrade to Progress 9.1E02, OpenEdge 10.0B03 Service Pack, where SQLSCHEMA writes UPDATE schema permissions to the output .dsql file.
Workaround
The following Stored Procedure will list all UPDATE privileges for all users for the database, which can then be manually applied with GRANT statements.

 
CREATE PROCEDURE grantupdate ()
RESULT ( cSqlCmd CHARACTER(132) )
BEGIN
    SQLCursor scTbl = new SQLCursor ("SELECT tblowner, tbl, grantee "
                                     + "FROM sysprogress.systabauth "
                                     + "WHERE upd = 'y'");
    scTbl.open ();
    scTbl.fetch ();
    while (scTbl.found()) {
        String sSchemaName = (String) scTbl.getValue(1, CHARACTER);
        String sTblName = (String) scTbl.getValue(2, CHARACTER);
        String sUser = (String) scTbl.getValue(3, CHARACTER);
        sUser = "'" + sUser + "'";
        String sGrantCmd = new String("GRANT UPDATE ON "
                                      + sSchemaName.trim() + "."
                                      + sTblName.trim()
                                      + " TO " + sUser.trim() + ";");
        SQLResultSet.set (1, sGrantCmd);
        SQLResultSet.insert();
        scTbl.fetch();
    }
    scTbl.close();
    SQLResultSet.set (1, "COMMIT WORK;");
    SQLResultSet.insert();
END;
Notes
Keyword Phrase
Last Modified Date12/5/2017 8:37 AM

Powered by