To create a SQL DBA and hide all Progress Schema tables and views from the public do the following:
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 6 below.
2. Log in with a SQL application as the SYSPROGRESS DBA user activated in step 1.
Follow the instructions in the following article to revoke Default DBA privileges:
SQL: How to revoke SQL privileges of the Default DBA?
3. Run the following code from a 4GL session to create a file called RevokeAllscript,sql. Follow instructions in the comments to tailor the script as needed:
ABL/4GL Code to revoke rights to SCHEMA tables.
The GRANTED BY ANY_USER option of the REVOKE statement was introduced in 10.1C and is first documented in the OpenEdge 10.2A release.
If the revoke script should be run against an earlier release of the database, make sure the '&SCOPED-DEFINE Pre101C "no"' version of the script is used in step 3.
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: STEP 6 removes the User SYSPROGESS from the _User table which will allow the 4GL applications to function normally.