asked on

How can I show grants in Sybase IQ? (for one or all objects)

Here is the solution for that question,

select sysuser.user_name,table_name,systableperm.*
from systable,systableperm,sysuser
where stable_id=systable.table_id
and systableperm.grantee=sysuser.user_id
and sysuser.user_name='put_user_name_here'

Ganesh Gurudu

I found this in the IQ svrlog when I showed permissions in Sybase Central. So I guess I can close this question.

-- Show table/view grants for table_owner_name.mytablename
--
SELECT E.user_name, E.user_group, P.selectauth, P.insertauth, P.deleteauth, P.updateauth, P.alterauth,
P.referenceauth, SUM( DISTINCT C.privilege_type )
FROM SYS.SYSTABLEPERM P
JOIN SYS.SYSUSERPERMS E ON E.user_id = P.grantee
JOIN SYS.SYSTABLE T ON T.table_id = P.stable_id
JOIN SYS.SYSUSERPERMS U ON U.user_id = T.creator
LEFT OUTER JOIN SYS.SYSCOLPERM C ON C.table_id = T.table_id AND C.grantee = P.grantee
WHERE U.user_name = 'table_owner_name' AND T.table_name = 'mytablename'
GROUP BY E.user_name, E.user_group, P.selectauth,
P.insertauth, P.deleteauth, P.updateauth, P.alterauth, P.referenceauth
ORDER BY E.user_name

-- Show users who can execute proc_owner_name.myprocname
--
SELECT E.user_name, E.user_group
FROM SYS.SYSPROCPERM R
JOIN SYS.SYSUSERPERMS E ON E.user_id = R.grantee
JOIN SYS.SYSPROCEDURE P ON P.proc_id = R.proc_id
JOIN SYS.SYSUSERPERMS U ON U.user_id = P.creator
WHERE U.user_name = 'proc_owner_name' AND P.proc_name = 'myprocname'
ORDER BY E.user_name

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.