All Forums Database
rrenn001 7 posts Joined 09/08
30 Sep 2009
Extracting Object Permissions

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 21 posts Joined 09/09
30 Sep 2009

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

rrenn001 7 posts Joined 09/08
30 Sep 2009

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 clauseselect '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?)Cheersrrenn001

UpaMishra 35 posts Joined 01/08
05 Feb 2010

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 PermissionFROM dbc.AllRightsWHERE 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

You must sign in to leave a comment.