| "No data found" stops the loop action inside PL/SQL block [message #588994] |
Mon, 01 July 2013 04:20  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I am trying to create an anonymous PL/SQL block to output privilege information for each of the users listed in DBA_USERS
In a loop. This is my block so far (not finished):
declare
v_usr varchar2(30);
v_out_header varchar2(100);
v_output varchar2(100);
v_qry varchar2(150);
--
cursor uc is select username from dba_users order by 1;
begin
v_out_header := '***User-Role Privilege report***'||chr(10)||'-----------------------------------';
dbms_output.put_line(v_out_header);
open uc;
loop
fetch uc into v_usr;
exit when uc%notfound;
select 'username: '|| username||' , '||'profile: '||profile into v_output
from dba_users where username=v_usr;
dbms_output.put_line(v_output);
dbms_output.put_line('SYSTEM privileges granted directly to the user(not through ROLE) : '
--||chr(10)
);
v_output := '';
v_qry := 'select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in
(select role from dba_roles) and grantee ='||'''' ||v_usr||'''' ;
execute immediate v_qry into v_output;
--
dbms_output.put_line(v_output);
dbms_output.put_line('------------------------------'||chr(10));
end loop;
close uc;
exception
when no_data_found
then dbms_output.put_line('no_data_found');
end;
The output is as follows:
***User-Role Privilege report***
-----------------------------------
username: ANDREY , profile: DEFAULT
SYSTEM privileges granted directly to the user(not through ROLE) :
no_data_found
A problem I am encountering is that for some users I have no direct privileges that are not granted through roles,
And when I have the expression v_qry (which is basically "'select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in
(select role from dba_roles) and grantee ='||'''' ||v_usr||''''") not initialized with values because the select statement retrieved 0 results,
I have the process interfered by the no_data_found error/exception.
Questions:
Any ideas on how I can preferrably simply, avoid/overcome my problem?
Some way to make the loop go on in spite of no data found? maybe something similar to NVL?
Thanks in advance.
Regards,
Andrey
Edit: added brackets to the topic ("No data found"....)
[Updated on: Mon, 01 July 2013 04:23] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: No data found stops the loop action inside PL/SQL block [message #589009 is a reply to message #588997] |
Mon, 01 July 2013 07:23  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
cookiemonster wrote on Mon, 01 July 2013 12:49You don't need dynamic sql for this
cookiemonster wrote on Mon, 01 July 2013 12:55Also the query that's throwing no_data_found can return multiple rows - which will give the too_many_rows exception the way you've coded it.
Use a for loop instead and then you don't need to worry about either error.
Thanks cookiemonster & littlefoot for your replies.
How can I use a for loop and avoid dynamic SQL for displaying the multiple privileges for each user?
I've given it some thought but couldn't think of a way... What do I use as the range for the "for" loop?
Regards,
Andrey
|
|
|
|
| Re: No data found stops the loop action inside PL/SQL block [message #589014 is a reply to message #589009] |
Mon, 01 July 2013 07:28 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Andrey_R wrote on Mon, 01 July 2013 13:23
How can I use a for loop and avoid dynamic SQL for displaying the multiple privileges for each user?
Take the query you have - remove execute immediate and extraneous quotes. There's nothing remotely dynamic about that query.
Andrey_R wrote on Mon, 01 July 2013 13:23
What do I use as the range for the "for" loop?
Range? You realise that you can use for loops with select statments? If not I suggest a read of the documentation.
FOR rec IN (select grantee ||','|| privilege as output
from DBA_SYS_PRIVS
where grantee not in (select role from dba_roles)
and grantee = v_usr) LOOP
It really is that simple.
[EDIT: removed some quotes]
[Updated on: Mon, 01 July 2013 07:29] Report message to a moderator |
|
|
|
|
|
| Re: No data found stops the loop action inside PL/SQL block [message #589040 is a reply to message #589015] |
Mon, 01 July 2013 10:28 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
I've amended my code to this:
declare
v_usr varchar2(30);
v_out_header varchar2(100);
v_output varchar2(100);
v_qry varchar2(150);
--
cursor uc is select username from dba_users order by 1;
begin
v_out_header := '***User-Role Privilege report***'||chr(10)||'-----------------------------------';
dbms_output.put_line(v_out_header);
open uc;
loop
fetch uc into v_usr;
exit when uc%notfound;
select 'username: '|| username||' , '||'profile: '||profile into v_output
from dba_users where username=v_usr;
dbms_output.put_line(v_output);
dbms_output.put_line('SYSTEM privileges granted directly to the user(not through ROLE) : '
--||chr(10)
);
v_output := '';
begin
for grantee in (select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in
(select role from dba_roles) and grantee =v_usr) loop
--
--
dbms_output.put_line(v_output);
end loop;
end;
--
dbms_output.put_line(v_output);
dbms_output.put_line('------------------------------'||chr(10));
end loop;
close uc;
exception
when no_data_found
then dbms_output.put_line('no_data_found');
end;
However, it is failing because v_output is not being initialized with anything in this part.
I can't seem to understand how to catch the contents of my user-relevant(defined by current v_usr value, by the outer loop) privileges.
Can you give me another hint on a technique to do it?
Thanks & Best Regards,
Andrey
|
|
|
|
|
|
| Re: No data found stops the loop action inside PL/SQL block [message #589055 is a reply to message #589042] |
Mon, 01 July 2013 13:26 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
To be fair Michel - he's going to struggle to get the format he wants with pure sql, and he should learn where he's going wrong even if sql is better.
@Andrey_R - You really should read the documentation on cursor for loops. If you did then you would know how to access the for loop record variables. This is basic PL/SQL.
|
|
|
|
|
|
| Re: No data found stops the loop action inside PL/SQL block [message #589102 is a reply to message #589060] |
Tue, 02 July 2013 04:20 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi Michel & cookiemonster,
I've read a little about how it's done in PL/SQL and I've amended my code accordingly to this:
declare
v_usr varchar2(30);
v_out_header varchar2(200);
v_output varchar2(100);
v_qry varchar2(150);
--
cursor uc is select username from dba_users where username not in ('SYS','SYSTEM') order by 1;
begin
v_out_header := ' -----------------------------------'||chr(10)||'/****User-Role Privilege report****/'||chr(10)||'-----------------------------------'||chr(10);
dbms_output.put_line(v_out_header);
open uc;
loop
fetch uc into v_usr;
exit when uc%notfound;
select chr(10)||'username: '|| username||' , '||'profile: '||profile into v_output
from dba_users where username=v_usr;
dbms_output.put_line(v_output);
--direct system privs
dbms_output.put_line('SYSTEM privileges granted directly to the user(not through ROLE) : '||chr(10)||'-------------------------------------------------------------------');
--
begin
for item in (select privilege direct_sys_priv from DBA_SYS_PRIVS where grantee =v_usr) loop
--
--
dbms_output.put_line(item.direct_sys_priv);
end loop;
end;
--
--role system privs
dbms_output.put_line(chr(10)||'SYSTEM privileges granted through ROLES : '||chr(10)||'-------------------------------------------------------------------');
begin
for item in (select 'Role: "'||grantee||'", Privilege: ' ||privilege role_sys_priv from DBA_SYS_PRIVS where grantee in
(select granted_role from DBA_ROLE_PRIVS where grantee = v_usr) order by role_sys_priv) loop
--
--
dbms_output.put_line(item.role_sys_priv);
end loop;
end;
--
--
dbms_output.put_line('------------------------------'||chr(10));
end loop;
close uc;
exception
when no_data_found
then dbms_output.put_line('no_data_found');
end;
It seems to work fine now.
I will do the same for non system privs.
I also think that there is a possibility for roles to be granted to roles,
And I need to think how to represent it in my code.
Now, regarding the possibility of using only SQL - is it possible to loop in a SQL query at all (without begin..loop...end loop....end...) ?
I didn't find documentation for this anywhere, only for PL/SQL...
Regards,
Andrey
|
|
|
|
|
|
| Re: No data found stops the loop action inside PL/SQL block [message #589109 is a reply to message #589103] |
Tue, 02 July 2013 05:53 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can shorten your code quite a bit - the cursor loop at the start should be a for loop.
The subsequent select does not need to exist - the cursor can select profile.
You don't need any of the variables.
You don't need the exception handler.
You don't need to wrap each for loop in a begin end - it's pointless unless you're going to add an exception handler.
|
|
|
|
| Re: No data found stops the loop action inside PL/SQL block [message #589119 is a reply to message #589109] |
Tue, 02 July 2013 07:44 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
cookiemonster wrote on Tue, 02 July 2013 13:53You can shorten your code quite a bit - the cursor loop at the start should be a for loop.
The subsequent select does not need to exist - the cursor can select profile.
You don't need any of the variables.
You don't need the exception handler.
You don't need to wrap each for loop in a begin end - it's pointless unless you're going to add an exception handler.
Thanks for the tip!
This is my code so far:
--
begin
dbms_output.put_line( ' -----------------------------------'||chr(10)||'/****User-Role Privilege report****/'||chr(10)||'-----------------------------------'||chr(10));
for item_uname in (select username,chr(10)||'username: '|| username||' , '||'profile: '||profile user_prof_info from dba_users where username not in ('SYS','SYSTEM') order by 1)
loop --outer loop start
dbms_output.put_line(item_uname.user_prof_info);
--
--
--direct system privs
dbms_output.put_line('SYSTEM privileges granted directly to the user(not through ROLE) : '||chr(10)||'-------------------------------------------------------------------');
--
for item_drct_sys_priv in (select privilege direct_sys_priv from DBA_SYS_PRIVS where grantee =item_uname.username)
loop --inner loop start1
--
--
dbms_output.put_line(item_drct_sys_priv.direct_sys_priv);
end loop;--inner loop end
--
--role system privs
dbms_output.put_line(chr(10)||'SYSTEM privileges granted through ROLES : '||chr(10)||'-------------------------------------------------------------------');
--
for item_role_sys_priv in (select 'Role: "'||grantee||'", Privilege: ' ||privilege role_sys_priv from DBA_SYS_PRIVS where grantee in
(select granted_role from DBA_ROLE_PRIVS where grantee = item_uname.username) order by role_sys_priv)
loop --inner loop start2
dbms_output.put_line(item_role_sys_priv.role_sys_priv);
end loop;--inner loop end2
dbms_output.put_line('----------------------------**END OF INFO**-------------------------------'||chr(10));
end loop;--outer loop end
--
end;
Regards,
Andrey
[Updated on: Tue, 02 July 2013 07:44] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
