| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Roles are still ignored with Invoker's Rights?
A copy of this was sent to "Finn Ellebaek Nielsen" <fen_at_changegroup.dk>
(if that email address didn't require changing)
On Tue, 27 Apr 1999 23:46:32 +0100, you wrote:
>Oracle 8.1.5 on NT 4.0 Workstation SP3 128MB RAM.
>
>My understanding of the new Invoker's Rights scheme for stored procedures
>was that where Definer's Rights has always ignored privileges granted
>through roles this would no longer be the case with Invoker's Rights because
>that would be checked at run-time.
>
Access to the objects would be checked at run time but during the initial compilation -- roles are not used. The definer of the procedure must have direct access to the underlying objects -- the invoker of the procedure may have access to the objects via a role. here are 2 examples. One just shows that roles are in place during the execution of an invokers rights procedure (all_objects shows you the objects you have access to. historically in a stored procedure the count(*) from all_objects was less then the count(*) when run in sqlplus -- since roles where not enable in the procedure)...
SQL> create or replace procedure count_objects_ir
2 authid current_user
3 as
4 begin
5 for x in ( select count(*) cnt from all_objects ) loop 6 dbms_output.put_line( 'IR count = ' || x.cnt ); 7 end loop;
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> create or replace procedure count_objects_dr
2 as
3 begin
4 for x in ( select count(*) cnt from all_objects ) loop 5 dbms_output.put_line( 'DR count = ' || x.cnt ); 6 end loop; 7 count_objects_ir;
Procedure created.
SQL> select count(*) from all_objects;
COUNT(*)
12721
SQL> exec count_objects_ir
IR count = 12721
PL/SQL procedure successfully completed.
SQL> exec count_objects_dr
DR count = 10094
IR count = 10094
PL/SQL procedure successfully completed.
so, that shows that the count from the invokers rights procedure (12721) is now the same as what the query run in sqlplus would be -- we can 'see' everything in the procedure we could 'see' without the procedure. Also, i called the invokers rights procedure from the definers rights procedure to demonstrate that once you 'break the chain', once you call a definers rights procedure -- the roles are gone. We cannot 'see' as many objects in the invokers rights procedure when called from a definers rights procedure.
the other example now. i try to create a procedure that accesses dba_tab_privs. I know I can cause I can describe it:
SQL> connect sys/manager
Connected.
SQL> revoke select on dba_tab_privs from tkyte;
Revoke succeeded.
SQL> connect tkyte/tkyte
Connected.
SQL>
SQL> set role all;
Role set.
SQL> desc dba_tab_privs
Name Null? Type ------------------------------------ -------- ------------------------- GRANTEE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3)
SQL>
SQL> create or replace procedure count_rows_ir
2 AUTHID CURRENT_USER
3 as
4 l_cnt number;
5 begin
6 for x in ( select * from dba_tab_privs ) 7 loop 8 exit; 9 end loop;
Warning: Procedure created with compilation errors.
SQL>
SQL> show errors
Errors for PROCEDURE COUNT_ROWS_IR:
LINE/COL ERROR
-------- ----------------------------------------------------------------- 6/13 PL/SQL: SQL Statement ignored 6/27 PLS-00201: identifier 'SYS.DBA_TAB_PRIVS' must be declared
But I cannot compile it. If I grant myself access:
SQL> connect sys/manager
Connected.
SQL> grant select on dba_tab_privs to tkyte;
Grant succeeded.
SQL> connect tkyte/tkyte
Connected.
SQL> create or replace procedure count_rows_ir
2 AUTHID CURRENT_USER
3 as
4 l_cnt number;
5 begin
6 for x in ( select * from dba_tab_privs ) 7 loop 8 exit; 9 end loop;
Procedure created.
I can create the procedure. To see where the roles come into play at runtime, we grant access on this procedure to scott and run it:
SQL> grant execute on count_rows_ir to scott;
Grant succeeded.
SQL> exec count_rows_ir
PL/SQL procedure successfully completed.
SQL> connect scott/tiger
Connected.
SQL> exec tkyte.count_rows_ir
BEGIN tkyte.count_rows_ir; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist ORA-06512: at "TKYTE.COUNT_ROWS_IR", line 6 ORA-06512: at line 1
so, scott cannot SEE dba_tab_privs. Lets make it so he can:
SQL> connect tkyte/tkyte
Connected.
SQL> grant dba to scott;
Grant succeeded.
SQL> connect scott/tiger
Connected.
SQL> exec tkyte.count_rows_ir
PL/SQL procedure successfully completed.
So, that shows that scott's roles are in fact enabled. In you want to create a procedure that accesses the DBA_* tables without usig direct grants, we can do that as well. it would look like this:
SQL> set role all;
Role set.
SQL> desc dba_tab_privs
Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3)
SQL>
SQL> create or replace procedure count_rows_ir
2 AUTHID CURRENT_USER
3 as
4 l_cnt number;
5 begin
6 for x in ( select * from dba_tab_privs ) 7 loop 8 exit; 9 end loop;
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE COUNT_ROWS_IR:
LINE/COL ERROR
-------- ----------------------------------------------------------------- 6/16 PL/SQL: SQL Statement ignored 6/30 PLS-00201: identifier 'SYS.DBA_TAB_PRIVS' must be declared
that shows how *not* to do it..... this is one way *to* do it:
SQL>
SQL> create or replace procedure count_rows_ir
2 authid current_user
3 as
4 type refCur is ref cursor; 5 y refCur; 6 l_grantee varchar2(30); 7 l_table_name varchar2(30); 8 l_privilege varchar2(30); 9 begin 10 open y for 'select grantee, table_name, privilege from dba_tab_privs'; 11 12 loop 13 fetch y into l_grantee, l_table_name, l_privilege; 14 exit when y%notfound; 15 dbms_output.put_line( l_grantee || '....' ); 16 exit; 17 end loop; 18 close y;
Procedure created.
SQL> show errors
No errors.
SQL> SQL> set serveroutput on SQL> exec count_rows_ir
SQL> set role none;
Role set.
SQL> exec count_rows_ir
BEGIN count_rows_ir; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist ORA-06512: at "TKYTE.COUNT_ROWS_IR", line 10 ORA-06512: at line 1
showing that it works.....
>OK, I'm trying to access DBA_OBJECTS through a stored procedure created in a
>schema with the DBA role granted (thus access to DBA_OBJECTS):
>
>create or replace procedure p2 authid current_user as
>
> n number;
>
>begin
> select count(*)
> into n
> from dba_objects;
>
> dbms_output.put_line('n = ' || to_char(n));
>end;
>/
>
>show errors
>
>Errors for PROCEDURE P2:
>
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>6/3 PL/SQL: SQL Statement ignored
>8/8 PLS-00201: identifier 'SYS.DBA_OBJECTS' must be declared
>
>But I can in fact see DBA_OBJECTS:
>
>select count(*)
>from dba_objects;
>
>If I from SYS grants select priviliges:
>
>grant select on dba_objects to scott;
>
>it works. But with Invoker's Rights I would now need to grant that access to
>all users!
>
>It this a bug?
>
>Thanks.
>
>Finn
>
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue May 04 1999 - 12:52:25 CDT
 |
 |