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

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?


sleepinglukeAsked:
Who is Participating?
 
Joe WoodhousePrincipal ConsultantCommented:
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
 
Joe WoodhousePrincipal ConsultantCommented:
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
 
sleepinglukeAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
sleepinglukeAuthor Commented:
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
 
sleepinglukeAuthor Commented:
thank you very much!
0
 
Joe WoodhousePrincipal ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.