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?

asked 03 Aug '10, 14:23

Justin%20Willey's gravatar image

Justin Willey
6.7k108141208
accept rate: 20%


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
permanent link

answered 03 Aug '10, 14:27

Justin%20Willey's gravatar image

Justin Willey
6.7k108141208
accept rate: 20%

Comment Text Removed
Comment Text Removed
Comment Text Removed
Comment Text Removed
2

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?

(03 Aug '10, 17:03) Reimer Pods
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×114
×24

question asked: 03 Aug '10, 14:23

question was seen: 1,095 times

last updated: 03 Aug '10, 14:27