Re: invoker rights

Thanks Tom.

I have also noticed that the words 'authid current_user' make their way into dba_source and the PIECE column of sys.idl_char$ (I did some snooping with Logminer to find that out!)

-John

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:a66dsq01iuo_at_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 Thu Mar 07 2002 - 10:09:05 CST