on 2021 Mar 03 6:30 AM
Dear Guru's,
Is there any standard view to find out of privileges assigned to mass users?
Currently we have limitation to display privileges assigned t only one user at a time using "effective_privileges" system view. Please help.
Also how to find out a particular privilege assigned in which roles?
thanks in advance.
Help others by sharing your knowledge.
AnswerRequest clarification before answering.
System view EFFECTIVE_PRIVILEGES is the right source. To avoid the limitation to one user, you can a little anonymous block (like following) which gets for all users the effective privileges:
do
begin
declare v_i integer;
declare v_user nvarchar(256);
declare t_ep_all table like effective_privileges;
t_users = select user_name from users;
for v_i in 1..record_count(:t_users) do
v_user = :t_users.user_name[v_i];
t_ep = select * from effective_privileges where user_name = :v_user;
:t_ep_all.insert(:t_ep);
end for;
select * from :t_ep_all;
end;
Regarding the question how to find a to what roles a specific privilege is assigned you can use system view EFFECTIVE_PRIVILEGE_GRANTEES (GRANTEE_TYPE = 'ROLE' defines that the privilege is assigned to the role mentioned in column GRANTEE).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
78 | |
12 | |
9 | |
8 | |
7 | |
5 | |
4 | |
4 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.