Extracting Object Permissions

[rrenn001]

Hello,

Is there a way to find and extract the permissions of objects using bteq?

I want to create an output file that scripts out the permissions for a specific object (table, stproc, macro etc...)

ex.
grant select on db.tableA to userA;

Thank you for your help.

[ravi_videla]

You should be able to query dbc.allrolerights and find list of accesses granted and who granted it.

[rrenn001]

Thanks for the information. So what your saying is the easiest way to create a script of object permissions would be to create a dynamic query such as:

Substituing the dbc.allrolerights.accessright column with some kind of convert clause

select 'grant', (INSERT CONVERSION FUNCTION for accessright column here), on ', databasename,'.',tablename,'to ',RoleName,';' from dbc.allrolerights where tablename = 'whatever_table'

to form something like this...

grant select on database.tablename to rolename;

It seems like overkill. Is there something else like DDL extract functionality or something simpler like: SHOW TABLE tablex (which includes permissions on that table?)

Cheers
rrenn001

[UpaMishra]

Try the below query......

SEL

TRIM(username)

,TRIM(databasename)

,TRIM(tablename)

,'GRANT '|| CASE

WHEN AccessRight = 'AF ' THEN 'ALTER FUNCTION'

WHEN AccessRight = 'AP ' THEN 'ALTER PROCEDURE'

WHEN AccessRight = 'AS ' THEN 'ABORT SESSION'

WHEN AccessRight = 'CD ' THEN 'CREATE DATABASE'

WHEN AccessRight = 'CF ' THEN 'CREATE FUNCTION'

WHEN AccessRight = 'CG ' THEN 'CREATE TRIGGER'

WHEN AccessRight = 'CM ' THEN 'CREATE MACRO'

WHEN AccessRight = 'CO ' THEN 'CREATE PROFILE'

WHEN AccessRight = 'CP ' THEN 'CHECKPOINT'

WHEN AccessRight = 'CR ' THEN 'CREATE ROLE'

WHEN AccessRight = 'CT ' THEN 'CREATE TABLE'

WHEN AccessRight = 'CU ' THEN 'CREATE USER'

WHEN AccessRight = 'CV ' THEN 'CREATE VIEW'

WHEN AccessRight = 'D ' THEN 'DELETE'

WHEN AccessRight = 'DD ' THEN 'DROP DATABASE'

WHEN AccessRight = 'DF ' THEN 'DROP FUNCTION'

WHEN AccessRight = 'DG ' THEN 'DROP TRIGGER'

WHEN AccessRight = 'DM ' THEN 'DROP MACRO'

WHEN AccessRight = 'DO ' THEN 'DROP PROFILE'

WHEN AccessRight = 'DP ' THEN 'DUMP'

WHEN AccessRight = 'DR ' THEN 'DROP ROLE'

WHEN AccessRight = 'DT ' THEN 'DROP TABLE'

WHEN AccessRight = 'DU ' THEN 'DROP USER'

WHEN AccessRight = 'DV ' THEN 'DROP VIEW'

WHEN AccessRight = 'E ' THEN 'EXECUTE'

WHEN AccessRight = 'EF ' THEN 'EXECUTE FUNCTION'

WHEN AccessRight = 'I ' THEN 'INSERT'

WHEN AccessRight = 'IX ' THEN 'INDEX'

WHEN AccessRight = 'MR ' THEN 'MONITOR RESOURCE'

WHEN AccessRight = 'MS ' THEN 'MONITOR SESSION'

WHEN AccessRight = 'PC ' THEN 'CREATE PROCEDURE'

WHEN AccessRight = 'PD ' THEN 'DROP PROCEDURE'

WHEN AccessRight = 'PE ' THEN 'EXECUTE PROCEDURE'

WHEN AccessRight = 'RO ' THEN 'REPLICATION OVERRIDE'

WHEN AccessRight = 'R ' THEN 'RETRIEVE/SELECT'

WHEN AccessRight = 'RF ' THEN 'REFERENCE'

WHEN AccessRight = 'RS ' THEN 'RESTORE'

WHEN AccessRight = 'SS ' THEN 'SET SESSION RATE'

WHEN AccessRight = 'SR ' THEN 'SET RESOURCE RATE'

WHEN AccessRight = 'U ' THEN 'UPDATE'

END || ' ON '||TRIM(databasename)||'.'||TRIM(tablename)||' to '||TRIM(username)||';' AS Permission

FROM dbc.AllRights

WHERE DatabaseName = 'DBNAME' and USERNAME <> 'LOGGEDINUSERNAME' AND TABLENAME <> 'All';

Substitude the DBNAME with the database name in which the objects are there.
and the LOGGEDINUSERNAME with the database user through which you have logged into BTEQ