Consultor Eletrônico

Status: Verified

GOAL:

How to check permissions on a table in SQL92?

GOAL:

How to find SQL permissions on a table within Progress 9 and OpenEdge 10 databases?

GOAL:

What permissions are currently defined for a SQL92 table?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.x
OpenEdge 10.x

FIX:

After connecting to the SQL92 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 any user accounts listed as have DBA will give sufficient rights / privileges to redefine rights on tables to another 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>';

The following example checks on what users have rights to the pub.benefits table:
select * from sysprogress.systabauth where TBL='BENEFITS';

Here is the output from the select statement:
GRANTOR GRANTEE TBLOWNER
TBL INS DEL UPD SEL EXE NDX ALT
REF
-------------------------------- -------------------------------- --------------
------------------ -------------------------------- --- --- --- --- --- --- ---
---
PSCBLD PSCBLD PUB
BENEFITS g g g g g g
g
TESTUSER foo PUB
BENEFITS y

In this example TESTUSER was the DBA who granted select, SEL, rights to user foo on the Benefits table.

The first field of this table is the grantor.
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.
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.


There will typically be one line of output for each user that specific permissions have been defined for.