[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2011-09-21
1
Medium Priority
?
3,861 Views
Last Modified: 2012-05-12
I guess I can do it by joining SYS tables, but I was expecting to find something like sp_iqhelpprotect.

Is there Sybase supplied a way to show grants for various types of objects (or for individual objects)?

If not, can anyone point me to a code snippet to do this?

Thanks in advance
Ben
0
Comment
Question by:BenSlade
1 Comment
 
LVL 1

Accepted Solution

by:
BenSlade earned 0 total points
ID: 36602202
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

Open in new window

1

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In current releases of Windows 10, MS removed the ability to format a volume with ReFS. That feature is moved to Windows 10 Pro for Workstations. The requisite files exist in Pro, but functionality is locked and/or otherwise unavailable. A $125 upgr…
Want to create a VM?  Here's how you do it.
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question