Re: invoker rights

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 6 Mar 2002 16:56:58 -0800
Message-ID: <a66dsq01iuo@drn.newsguy.com>

In article <fXwh8.12855$L7.18519_at_rwcrnsc52.ops.asp.att.net>, "John says...
>
>Does anyone know where in the data dictionary invoker rights are stored?
>I want to be able to tell using some SYS-owned table or view if a certain
>object has been created with AUTHID CURRENT_USER or AUTHID DEFINER.
>
>Thanks,
>
>-John
>
>

It seems the only way to tell if a procedure was created with invokers rights is through the 'options' column of 'procedure$'. The options column will have '17' for invoker rights 'AUTHID CURRENT_USER' and 1 for 'AUTHID DEFINER'. ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace procedure p1   2 authid current_user
  3 as
  4 begin
  5 null;
  6 end;
  7 /

Procedure created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace procedure p2   2 as
  3 begin
  4 null;
  5 end;
  6 /

Procedure created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select object_name, 2 decode( (select options from sys.procedure$ where obj# = object_id ),

  3                                   1,  'DEFINER',
  4                                   17, 'INVOKER',
  5                           'BUMMER' )

  6 from user_objects
  7 where object_type = 'PROCEDURE'
  8 /

OBJECT_NAME                    DECODE(
------------------------------ -------
P1                             INVOKER
P2                             DEFINER

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Wed Mar 06 2002 - 18:56:58 CST