Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Roles are still ignored with Invoker's Rights?

Re: Roles are still ignored with Invoker's Rights?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 04 May 1999 17:52:25 GMT
Message-ID: <372f2e29.13339791@192.86.155.100>


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;

  8 end;
  9 /

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;

  8 end;
  9 /

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;

 10 end;
 11 /

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;

 10 end;
 11 /

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;

 10 end;
 11 /

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;

 19 end;
 20 /

Procedure created.

SQL> show errors
No errors.

SQL> 
SQL> set serveroutput on
SQL> exec count_rows_ir

WEBDB.... PL/SQL procedure successfully completed.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US