?
Solved

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

Posted on 2011-09-21
1
Medium Priority
?
3,372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Check out the latest tech news, community articles, and expert highlights in August's newsletter.
The top devops trends for 2017 are focused on improved deployment frequency, decreased lead time for change and decreased MTTR.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month10 days, 3 hours left to enroll

762 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