Feedback
Did this article resolve your question/issue?

   

Article

How to find SQL permissions on a table in an OpenEdge database?

Information

 
TitleHow to find SQL permissions on a table in an OpenEdge database?
URL NameP124758
Article Number000155560
EnvironmentProduct: Progress
Version: 9.1D, 9.1E
Product: OpenEdge
Version: 10.x, 11.x, 12.x
OS: All Supported Operating Systems
Question/Problem Description
How to check user permissions on a table in SQL92?
How to find SQL permissions on a table in an OpenEdge database?
What user permissions are currently defined for a table?

How to find which Tables can be modified by SQL only?

When SQL users try to change a table they have no access to they receive message:

You can only alter security on SQL tables with GRANT and REVOKE.

Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
After connecting to the SQL side of the Progress / OpenEdge database the following query can be used to check what user(s) have DBA rights:
 
select * from sysprogress.sysdbauth;

Logging in with a user accounts listed as having DBA privileges will have sufficient rights / privileges to redefine rights on tables to other user(s).

The following command can be used to view what rights are currently defined for a particular table:

 
select * from sysprogress.systabauth where TBL='<insert table name here>';

Example: Verify which users have rights to the sports2000 pub.benefits table:
 
select * from sysprogress.systabauth where TBL='BENEFITS';

The output from the select statement:  
  • In this example TESTUSER was the DBA who granted select, SEL, rights to user foo on the Benefits table.
GRANTOR                          GRANTEE                          TBLOWNER              TBL      
INS DEL UPD SEL EXE NDX ALT REF     GRANTOR_DOMAINID     GRANTEE_DOMAINID                        
-------------------------------- -------------------------------- --------------------- ---------
--- --- --- --- --- --- --- --- -------------------- --------------------                        
PSCBLD                           PSCBLD                           PUB                   BENEFITS 
  g   g   g   g       g   g   g                      0                    0                      
TESTUSER                         foo                              PUB                   BENEFITS 
              y

There will typically be one line of output for each user that specific permissions have been defined for:
  • The first field of this table is the GRANTOR (user who granted permissions ).
  • The second field is the GRANTEE (user to whom permissions are granted).
  • The third field is the Table Owner (who owns the schema of the table).
  • The fourth field is the table name (_field._field-name).
  • The remaining fields are the individual rights which can be toggled to allow particular rights, revoke rights or additionally allow the user to grant those rights as well.
Workaround
Notes
Last Modified Date8/17/2021 12:36 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.