Kbase P161411: SQL: How to revoke SQL privileges of the Default DBA and hide Progress Schema tables from public in
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
23/11/2010 |
|
Status: Unverified
GOAL:
SQL: How to revoke SQL privileges of the Default DBA and hide sysdbauth and systabauth from public in 10.1C and up?
GOAL:
Who is the Default DBA of an OpenEdge / Progress database?
GOAL:
How to revoke SQL privileges ON PUB (data) tables from the user who created the database?
GOAL:
How to revoke SQL privileges ON system or virtual tables from the Default DBA?
GOAL:
How to hide who the SQL Default DBA is from the public?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.1C
OpenEdge 10.2x
FIX:
To create a SQL DBA and hide all Progress Schema tables and views from the public do the following:
PRECAUTIONS: A. Take a backup of the database prior to making any changes.
B. Test on a copy or test database prior to making changes to the Production database.
1. Enable the SYSPROGRESS sql DBA by registering/adding the user SYSPROGRESS via the 4GL Data Administration/security/Edit User List.
Select the "Add" button to add the user.
Note: When adding users to the _user table, this will break most 4GL applications because the application will not know how to handle the Requests that are generated at connect time requesting User Name and Password.
Therefore, time should be scheduled to do this when other users will not be using the database. The problem created by adding a user to the _User table will be corrected in step 5 below.
2. Log in with a SQL application as the SYSPROGRESS DBA user activated in step 1.
Revoke the default DBA privileges from the SQL interface by running the following SQL command:
REVOKE RESOURCE, DBA FROM <default-DBA-user>;
3. Run the following code from a 4GL session to create a file called RevokeAllOnSchemaTablesFromPUBLIC.sql:
OUTPUT TO "RevokeAllOnSchemaTablesFromPUBLIC.sql".
FOR EACH _File NO-LOCK WHERE _Tbl-Type NE "T" BY _Tbl-Type:
PUT UNFORMATTED "REVOKE ALL ON " QUOTER(_Owner) "." QUOTER(_FILE-NAME) " FROM PUBLIC GRANTED BY ANY_USER;" SKIP.
PUT UNFORMATTED "commit;" SKIP.
END.
OUTPUT CLOSE.
4. Execute the file RevokeAllOnSchemaTablesFromPUBLIC.sql file from SQL Explorer in batch mode as follows:
sqlexp -char -infile RevokeAllOnSchemaTablesFromPUBLIC.sql -outfile report.txt -db -S -H -user -password < password>
5. Create a new SQL DBA by running the following command:
GRANT DBA, RESOURCE to '<new-dba-user>';
6. Using the 4GL Data Administration tool remove the SYSPROGRESS User added in step 1 above:
Select 4GL Data Administration/security/Edit User List/Delete and select the User SYSPROGRESS.
Note: This process removes the User SYSPROGESS from the _User table which will allow the 4GL applications to function normally.