Tom

We were under the impression that invokers rights allow you to compile and execute a procedure even though the schema creating the procedure or function has privileges over the underlying objects through roles, but not directly.

But your explanation tells us that this is not so.

So this means, if the schema doesnt have direct privileges over say table T, there is no way you can use that object in creating your sub program subprogram.

That is final.

Is it so?

Hello,
In your book Effective Oracle by Desgin on page 586, you say "You flood the Oracle shared pool".
What I understand from this is, similar procedure when invoked by N different users then each will parsed differently (even though the sql statements are same) and each will have their own sql area rather than sharing.
Am I correct ?

Tom,

I was seeing some strange behaviour with SESSION_USER and CURRENT_USER earlier, so returned to your chapter on Invokers Rights in your first book. I'm seeing no difference between invokers or definers with respect to CURRENT_USER, SESSION_USER or CURRENT_SCHEMA. Could you please comment - it's the bit where I change users that is confusing me.

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jan 24 12:15:24 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


ABB>
ABB> CREATE OR REPLACE PROCEDURE definer_proc IS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE( 'Current user=' || sys_context('userenv','current_user') );
4 DBMS_OUTPUT.PUT_LINE( 'Session user=' || sys_context('userenv','session_user') );
5 DBMS_OUTPUT.PUT_LINE( 'Current schema=' || sys_context('userenv','current_schema') );
6 END definer_proc;
7 /

Procedure created.

ABB>
ABB> CREATE OR REPLACE PROCEDURE invoker_proc AUTHID CURRENT_USER IS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE( 'Current user=' || sys_context('userenv','current_user') );
4 DBMS_OUTPUT.PUT_LINE( 'Session user=' || sys_context('userenv','session_user') );
5 DBMS_OUTPUT.PUT_LINE( 'Current schema=' || sys_context('userenv','current_schema') );
6 END invoker_proc;
7 /

Procedure created.

ABB>
ABB> SELECT object_name, authid
2 FROM user_procedures
3 WHERE object_name LIKE '%PROC';

OBJECT_NAME AUTHID
------------------------------ ------------
INVOKER_PROC CURRENT_USER
DEFINER_PROC DEFINER

ABB>
ABB> exec definer_proc;
Current user=ABB
Session user=ABB
Current schema=ABB

PL/SQL procedure successfully completed.

ABB> exec invoker_proc;
Current user=ABB
Session user=ABB
Current schema=ABB

PL/SQL procedure successfully completed.

ABB>
ABB> GRANT EXECUTE ON definer_proc TO ac;

Grant succeeded.

ABB> GRANT EXECUTE ON invoker_proc TO ac;

Grant succeeded.

ABB>
ABB> conn ac/ac
Connected.
AC>
AC> exec abb.definer_proc;
Current user=AC
Session user=AC
Current schema=AC

PL/SQL procedure successfully completed.

AC> exec abb.invoker_proc;
Current user=AC
Session user=AC
Current schema=AC

PL/SQL procedure successfully completed.

Regards
Adrian

>> you should select them from dual

But surely the advantage of application contexts is that SQL and PL/SQL can use them. Selecting from dual to get the "correct" answer hides an implementation that just wouldn't be obvious to people. For example, I just came along and used the SYS_CONTEXT function in PL/SQL and assumed it should give me the correct answer. Surely many more developers will have made the same "mistake".

Is there a good reason why we would need to select this from dual to have it respond correctly ?

Regards
Adrian

Tom,

In case you were interested, there is already a bug with this and to get the correct behaviour in PL/SQL, you must use SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) i.e. capitals. Strange but true.

Regards
Adrian

Tom I'm schema A and I need to run a procedure in schema B with all roles enabled. How do I do that?

That means I can call an invoker rights procedure in Schema B because I'll have roles disabled.

Should I call a definer rights procedure in Schema B, which in turn calls an invoker rights procedure in Schema B to do this?

To add one bit, my code in procedure that I want to execute is a NDS using execute immediate.

Tom, I should rephrase the Question,
When I have logged on as Schema A and then call a Definer procedure in Schema B. But in Schema B, I want SCHEMA B's roles enabled, because one of its roles gives me a special DCL privliege.

So if this procedure now calls ANOTHER procedure in Schema B which is running as invoker (Authid current_user), while this second procedures run, would I have Schema B's roles enabled.

Important that I dont want Schema A's roles enabled, but Schema B's.

So instead of directly calling a Invoker rights procedure in Schema B (which means I'd have A's roles enabled), I am calling a definer rights first so that my current_user becomes SCHEMAB. Then I call another invoker procedure in Schema B to have Schema B's roles enabled?

I have a standard definer right stored procedure owned by FOO that references the builtin USER keyword.

If I grant execute to A and A runs it, the USER in the code will return A. If I grant execute to B and B runs it, USER will return B.

How can I refer to the value of the "owner" (definer) of the procedure in the stored procedure code?

Thanks

sdaf