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
answered
03 Aug '10, 14:27
Justin Willey
7.6k●137●179●248
accept rate:
19%