Feedback
Did this article resolve your question/issue?

   

Article

SQL: How to revoke SQL privileges of the Default DBA and hide Progress Schema tables from public ?

« Go Back

Information

 
TitleSQL: How to revoke SQL privileges of the Default DBA and hide Progress Schema tables from public ?
URL NameP161411
Article Number000140581
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: All Supported Operating Systems
Other: RDBMS, SQL92
Question/Problem Description
How to revoke SQL privileges of the Default DBA and hide sysdbauth and systabauth from public?
How to revoke SQL privileges of the Default DBA and hide all tables from public?
How to revoke SQL privileges ON PUB (data) tables from the user who created the database?
How to revoke SQL privileges ON system or virtual tables from the Default DBA?
How to revoke SQL privileges ON system or all tables from the Default DBA?
Who is the Default DBA of an OpenEdge / Progress database?
How to hide who the SQL Default DBA is from the public?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution

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.
     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.

Workaround
Notes

 
Last Modified Date2/18/2021 1:54 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.