Solved

sybase - get list of tables with permissions for each user group

Posted on 2009-04-02
6
6,561 Views
Last Modified: 2012-05-06
Hi I have query on Sybase DBA ....

Basically I need to create list of tables on sybase database with having permissions from each user group...
i.e.
columns
- table name
- user group 1
- user group 2
- user group 3..

values
- table name
- read
- write/execute

or something. So far I do not see good way to generate such a report.
For format itself I do not mind too much , would somebody please help me how I can workout?


0
Comment
Question by:sleepingluke
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 24056652
Are the number of groups fixed (and small)? Are you only considering groups? What about permissions granted individually to single users, and to server-wide roles?
0
 

Author Comment

by:sleepingluke
ID: 24056821
Hi Joe,

Numbers of groups fixed, say 5 or 6. And I am considering groups only as there should not have any individual to have special permission. But to server wide like to double check .... (if not too difficult) but I am fine without server wide..
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 24057740
Take a look at the source text for sp_helprotect in sybsystemprocs. It will give you most of what you want. From there it's a fairly simple matter to turn it into a crosstab of the sort you want.

I can't get in front of an ASE just at the moment or I'd have some SQL for you. I'll have a go later. :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:sleepingluke
ID: 24058457
thank you very much, this outcome looks useful to me, I can use excel to sort this result for my purpose.
I can't read such a SP in short time in proper way, but if you could give me example for my future would you please?
0
 

Author Closing Comment

by:sleepingluke
ID: 31566073
thank you very much!
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 24058545
I'll go through this in several steps, especially as I don't have an ASE to actually test any of this in! :)

The information you want is in sysprotects (in each database). We can get a very basic form like this, in code #1 below:

That will produce output like this:

table1    group1    select
table1    group1    insert
table1    group2    select
[etc]

If you're extracting into Excel or some other spreadsheet that should be all you need for what you want here. We can do it natively in SQL but I'm reluctant to try without an ASE to run it in... (don't want to give you bad SQL!)

-- #1
 

select    o.name as "Object name"

,         u.name as "User/group name"

,         case when p.action = 193 then "select"

               when p.action = 195 then "insert"

               when p.action = 196 then "delete"

               when p.action = 197 then "update"

               when p.action = 224 then "execute"

               else "other"

          end as "Permission"

from      sysobjects o

,         sysprotects p

,         sysusers u

where     o.id = p.id

and       p.uid = u.uid

order by  o.name, u.name, p.action

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
Scenario: Your operations manager has discovered an anomaly in your security system. The business will start to suffer within 15 minutes if it is a major IT incident. What should she do? We have 6 recommendations for managing major incidents (https:…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now