Solved

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

Posted on 2011-09-21
1
2,737 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

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SyBase SQL DataTime Format 6 294
SQL Syntax 10 70
Default Read Only User Sybase DB 1 124
QUerry require with a procedure. 4 14
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Data breaches are on the rise, and companies are preparing by boosting their cybersecurity budgets. According to the Cybersecurity Market Report (http://www.cybersecurityventures.com/cybersecurity-market-report), worldwide spending on cybersecurity …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

785 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