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 6 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 RevokeAllscript,sql. Follow instructions in the comments to tailor the script as needed:
/* Uncomment appropriate line to include ALL tables or only Schema tables in revoke script. Comment out the one that isn’t used */
&GLOBAL-DEFINE AllorSchema «Schema»
/*&GLOBAL-DEFINE AllorSchema «All»*/
/* Uncomment the ‘Pre101C «yes»‘ line to exclude SQL features introduced in 10.1C from the revoke script. Comment out the one that isn’t used */
/*&SCOPED-DEFINE Pre101C «yes»*/
&SCOPED-DEFINE Pre101C «no»
OUTPUT TO «RevokeAllScript.sql».
FOR EACH _File NO-LOCK &IF {&AllorSchema}=»Schema» &THEN WHERE _Tbl-Type NE «T» &ENDIF BY _Tbl-Type:
PUT UNFORMATTED «REVOKE ALL ON » QUOTER(_Owner) «.» QUOTER(_FILE-NAME) &IF {&Pre101C}=»yes» &THEN » FROM PUBLIC;» &ELSE » FROM PUBLIC GRANTED BY ANY_USER;» &ENDIF 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.
VERIFY:
SELECT * FROM SYSPROGRESS.SYSDBAUTH;