RESOURCE role
Jim B, June 20, 2001 - 12:32 pm UTC
Good reply, even tho this subject get beaten to death almost every day.
Many times a user can create a table in sqlplus becausethey have been granted the RESOURCE role. As the link identifies, privs thru a role cannot be used in PL/SQL
Even select access..??
A reader, May 31, 2003 - 1:15 am UTC
Hi Tom,
I am using dynamic sql to access a view form some other users schema, i am unable to do so unless i have select access on the table,
for eg user bis owner of table a & view a1
user A has select access on view A1 of user B schema, 'not' given via role.
Dynamic sql in a procedure invoked by users A gives insufficient privilige.unless user a is given select access on table a
Is this expected behavaviour or something wrong.
cheers
May 31, 2003 - 11:49 am UTC
A reader, June 01, 2003 - 10:48 pm UTC
Hi Tom,
I did read that, but as mentioned i hv given the select access directly to the user and not via a role,
why does the user need a select access on the table too when accessing a view as dynamic sql in a procedure,
dont u think it beats one of the very basic purposes of view.
cheers
June 02, 2003 - 7:21 am UTC
Here is my test case that shows if A had been granted select on B.V by B and V is a view, A can dynamically access it. Where is yours that shows otherwise?
(try
SQL> set role none
and see if that view is still accessible, if it isn't - its a role)
ops$tkyte@ORA817DEV> select * from dba_sys_privs where grantee='PUBLIC';
no rows selected
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> drop user a cascade;
User dropped.
ops$tkyte@ORA817DEV> drop user b cascade;
User dropped.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create user a identified by a;
User created.
ops$tkyte@ORA817DEV> create user b identified by b;
User created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> grant create session, create procedure to a;
Grant succeeded.
ops$tkyte@ORA817DEV> grant create session, create table, create view to b;
Grant succeeded.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> alter user b default tablespace users quota unlimited on users;
User altered.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> @connect b/b
ops$tkyte@ORA817DEV> set termout off
b@ORA817DEV> REM GET afiedt.buf NOLIST
b@ORA817DEV> set termout on
b@ORA817DEV>
b@ORA817DEV> create table t ( x int );
Table created.
b@ORA817DEV> insert into t values ( 1 );
1 row created.
b@ORA817DEV> create view v as select * from t;
View created.
b@ORA817DEV>
b@ORA817DEV> grant select on v to a;
Grant succeeded.
b@ORA817DEV>
b@ORA817DEV> @connect a/a
b@ORA817DEV> set termout off
a@ORA817DEV> REM GET afiedt.buf NOLIST
a@ORA817DEV> set termout on
a@ORA817DEV>
a@ORA817DEV> create procedure p
2 as
3 type rc is ref cursor;
4 l_cursor rc;
5 l_x number;
6 begin
7 open l_cursor for 'select * from b.v';
8 loop
9 fetch l_cursor into l_x;
10 exit when l_cursor%notfound;
11 dbms_output.put_line( l_x );
12 end loop;
13 close l_cursor;
14 end;
15 /
Procedure created.
a@ORA817DEV>
a@ORA817DEV> exec p
1
PL/SQL procedure successfully completed.
Lost link
Galen Boyer, September 18, 2009 - 2:56 pm UTC
September 28, 2009 - 8:14 am UTC
I need to upload my "public html" stuff into my files and recreate that stuff. I'll do that this week.
Reference link is not working
David, November 16, 2009 - 6:35 pm UTC
November 23, 2009 - 12:33 pm UTC
see home page for a link to where those articles moved.