How do you get a list of all explicitly granted user rights

[justin_willey]

We've had customers' auditors asking for a list of which database users have been granted rights to the various database objects - easy to see in Sybase Central user by user - but a full exportable list?

[justin_willey · Accepted solution]

This works for v10 and is based on the captured queries from Sybase Central. It doesn't look for inherited rights or consider implicit ownership or DBA derived rights

    SELECT if T.table_type NOT IN ( 2, 21 ) then 'Table' else 'View' endif as "Object", e.User_Name as UserName, U.user_name as Owner, T.table_name as "Object Name", 
if MAX( IF P.selectauth = 'G' THEN 2 ELSE ( IF P.selectauth = 'Y' THEN 1 ELSE 0 ENDIF ) ENDIF )= 1 then 'YES' else 'NO' endif AS "SELECT",
if MAX( IF P.insertauth = 'G' THEN 2 ELSE ( IF P.insertauth = 'Y' THEN 1 ELSE 0 ENDIF ) ENDIF )= 1 then 'YES' else 'NO' endif  AS "INSERT", 
if MAX( IF P.deleteauth = 'G' THEN 2 ELSE ( IF P.deleteauth = 'Y' THEN 1 ELSE 0 ENDIF ) ENDIF )= 1 then 'YES' else 'NO' endif  AS "DELETE", 
if MAX( IF P.updateauth = 'G' THEN 2 ELSE ( IF P.updateauth = 'Y' THEN 1 ELSE 0 ENDIF ) ENDIF )= 1 then 'YES' else 'NO' endif  AS "UPDATE", 
if MAX( IF P.alterauth = 'G' THEN 2 ELSE ( IF P.alterauth = 'Y' THEN 1 ELSE 0 ENDIF ) ENDIF )= 1 then 'YES' else 'NO' endif  AS "ALTER", 
if MAX( IF P.referenceauth = 'G' THEN 2 ELSE ( IF P.referenceauth = 'Y' THEN 1 ELSE 0 ENDIF ) ENDIF )= 1 then 'YES' else 'NO' endif  AS "REFERENCE" ,
'N/A' as "EXECUTE"
FROM SYS.SYSTAB T JOIN SYS.SYSUSER U ON U.user_id = T.creator JOIN ( SYS.SYSUSER E JOIN SYS.SYSTABLEPERM P ON P.grantee = E.user_id ) ON P.stable_id = T.table_id 
GROUP BY Object, UserName, T.table_name, U.user_name

UNION ALL

SELECT if IFNULL( R.parm_id, 'N', 'Y' )='Y' then 'Function' else 'Procedure' endif as "Object",e.User_Name as UserName,  U.user_name as "Owner", F.proc_name as "Object Name",  
'N/A' AS "SELECT",
'N/A'  AS "INSERT", 
'N/A'  AS "DELETE", 
'N/A'  AS "UPDATE", 
'N/A'  AS "ALTER", 
'N/A' AS "REFERENCE",
if IFNULL( P.grantee, 'N', 'Y' ) = 'Y' then 'YES' else 'NO' endif AS "EXECUTE"
FROM SYS.SYSPROCEDURE F JOIN SYS.SYSUSER U ON U.user_id = F.creator LEFT OUTER JOIN SYS.SYSPROCPARM R ON R.proc_id = F.proc_id 
AND R.parm_type = 4 LEFT OUTER JOIN ( SYS.SYSUSER E JOIN SYS.SYSPROCPERM P ON P.grantee = E.user_id ) ON P.proc_id = F.proc_id 
where "Execute" = 'YES'
order by 1,2,4,3

[reimer_pods · Accepted solution]

Nice info! I had to change your statement a bit to get it working with ASA9 and SA11: first line is >SELECT T.table_type as "Object"<, and all occurences of SYSUSER replaced with SYSUSERPERM and likewise SYSTAB with SYSTABLE. Are you using some special views?