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?
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?
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?
ASKER
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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
thank you very much!
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!)
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