what about packages
Steve, August 30, 2001 - 5:09 pm UTC
For packages do you declare in the specification or the body
August 30, 2001 - 8:01 pm UTC
what is the difference
Kulguru, January 13, 2002 - 6:29 pm UTC
Lets say we have two users
user1
user2
user 1 creates a procedure proc1, which accesses table t.
Now lets tryto understand the authid concept( which I guess facilitates invokers and definers rights).
user 1 has privileges on proc1 as he created it, and on table t without which he could not have compiled the procedure.
Now user2 wants to executes theprocedure. If the procedure is qualified with authid current_user clause, are you trying to say that , user2 can execute the procedure even if he doesnt have privileges to proc1.
In my opinion user2 needs
1.Execute privilege granted to him by user1 on proc1.
2.Necessary privileges on table t
only then can he execute the procedure successfully.
Further, if you are saying that , if user 2 had a table tlocal to his schema, then authid facilitates the use of that table t to execute the procedure.. then
1.How is this functionality different from previous versions.
2.Is this all invokers and definers rights about.
Thank you
January 13, 2002 - 8:06 pm UTC
to get the entire story behind invokers/definers rights -- i can recommend my book. I spent a good 30 some odd pages on this with tons of examples.
In the above, if user1 created:
create or replace procedure proc1
authid current_user
as
begin
for x in ( select * from t ) loop
null;
end loop;
end;
/
If user2 wants to execute this procedure, they need:
1) execute granted on proc1 to them or some role or public
2) an object (table or view) named T -- this T can be different from the T that user1 has. if user2 user has a table T -- this will do. If there is a public synonym T for USER500.T and user2 has select on user500.t -- this will do. If there is a view T owned by user2 -- this will do (and so on).
basically, a current_user (invoker rights) routine acts alot like an anonymous block as far as references to tables/views go. Every one that runs a current_user procedure might have it execute against totally different objects.
but how is this different
A reader, January 13, 2002 - 10:09 pm UTC
How is this functionality different from previous versions.
January 14, 2002 - 7:35 am UTC
In prior releases, there was only DEFINERS rights procedures.
If user1 issues:
create package pkg
as
procedure proc( p_x in number );
end;
/
create table t ( x int );
create or replace procedure p
as
begin
for x in ( select * from T )
loop
pkg.proc( t.x );
end loop;
end;
Then the table T that is referenced is forever fixed. Regardless of WHO runs this procedure P -- the same exact table T will be referenced. It will be user1.t
If user1 then issues:
grant execute on p to user2;
and we log in as user2, we can successfully:
begin user1.p; end;
which selects from user1.T and executes user1.pkg.proc. user2 cannot however:
select * from user1.t;
begin user1.pkg.proc( 55 ); end;
since they were not granted select on user1.t or execute on user1.pkg. They can only use these objects when they run user1.p.
It is a fairly complex topic -- it takes lots of examples to get the point across (in my opinion). If you want a full understanding of it -- i've got a rather large chapter on nothing but in my book.
Kalita, November 05, 2002 - 10:34 am UTC
Hi Tom,
I have a procedure which would call other procedures dynamically based on the name of the procedure passed.
customer@HL163DV1> create or replace procedure ktest1(p_proc_name in varchar2) authid current_user i
s
2 v_sql varchar2(2000) := 'begin ' || p_proc_name || '; end;';
3 begin
4 execute immediate v_sql;
5 end ktest1;
6 /
Procedure created.
Now I have another procedure which creates a table
customer@HL163DV1> create or replace procedure ktest2 authid current_user is
2 v_sql varchar2(2000) := 'create table t1(c1 varchar2(10))';
3 begin
4 execute immediate v_sql;
5 end ktest2;
6 /
Procedure created.
If I execute ktest2 directly I can create the table without any problem. But if I try the execute it dynamically using my first procedure it gives me
customer@HL163DV1> exec ktest1('ktest2');
BEGIN ktest1('ktest2'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "CUSTOMER.KTEST2", line 4
ORA-06512: at line 1
ORA-06512: at "CUSTOMER.KTEST1", line 4
ORA-06512: at line 1
I think its because I am trying to execute it dynmically using the block. Is there anyway it could be done.
Thanks,
Kalita
November 05, 2002 - 12:16 pm UTC
I'll have to assume you are in 8i -- in 8i the Invoker rights chain is "broken" by execute immediate. The roles are turned "off" and the user must have the ability to create that table via a role. This shows in 9i this works. HOWEVER, I would say this looks like a shaky infrastructure you are building -- lots of dynamic "this that and the other thing". I would question what you are doing before you finish doing it
ops$tkyte@ORA920.US.ORACLE.COM> drop user a cascade;
User dropped.
ops$tkyte@ORA920.US.ORACLE.COM> create user a identified by a default tablespace users quota unlimited on users;
User created.
ops$tkyte@ORA920.US.ORACLE.COM> drop role a_role;
Role dropped.
ops$tkyte@ORA920.US.ORACLE.COM> create role a_role;
Role created.
ops$tkyte@ORA920.US.ORACLE.COM> grant create table to a_role;
Grant succeeded.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> grant create session, create procedure, a_role to a;
Grant succeeded.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> @connect a/a
ops$tkyte@ORA920.US.ORACLE.COM> set termout off
a@ORA920.US.ORACLE.COM> REM GET afiedt.buf NOLIST
a@ORA920.US.ORACLE.COM> set termout on
a@ORA920.US.ORACLE.COM>
a@ORA920.US.ORACLE.COM> create or replace procedure p( tname in varchar2 )
2 authid current_user
3 as
4 begin
5 dbms_output.put_line( '------------' );
6 for x in ( select * from session_roles )
7 loop
8 dbms_output.put_line( x.role );
9 end loop;
10 execute immediate 'create table ' || tname || ' ( x int )';
11 dbms_output.put_line( '------------' );
12 end;
13 /
Procedure created.
a@ORA920.US.ORACLE.COM>
a@ORA920.US.ORACLE.COM> create or replace procedure p2
2 authid current_user
3 as
4 begin
5 dbms_output.put_line( 'calling p statically' );
6 p( 't1' );
7 dbms_output.put_line( 'calling p dynamically' );
8 execute immediate 'begin p(''t2''); end;';
9 end;
10 /
Procedure created.
a@ORA920.US.ORACLE.COM> set linesize 70
a@ORA920.US.ORACLE.COM> exec p2
calling p statically
------------
A_ROLE
------------
calling p dynamically
------------
A_ROLE
------------
PL/SQL procedure successfully completed.
a@ORA920.US.ORACLE.COM> desc t1
Name Null? Type
----------------------------------- -------- ------------------------
X NUMBER(38)
a@ORA920.US.ORACLE.COM> desc t2
Name Null? Type
----------------------------------- -------- ------------------------
X NUMBER(38)
a@ORA920.US.ORACLE.COM>
Kalita, November 05, 2002 - 12:53 pm UTC
Thanks for your quick reply Tom. We were discussing this possibility of executing some procedures dynamically and were checking if it would be possible to handle procedures having dynamic sqls inside. It was something that was supposed to happen very very rarely and so did not really give much importance to the dynamic calls. Anyway good that now its out of question. Thanks a lot.
I have a related question.
John, April 01, 2003 - 4:24 pm UTC
I have actually posted the question in another thread - "How to get current schema". Your answer was I cannot do it simply with "set current_schema". Somebody suggested to me that AUTHID CURRENT_USER might be a feasible approach.
My task is how to use one set of stored procedures to access data from different user schema. All my stored procedures are in packages, so I create my package header with "AUTHID CURRENT_USER" clause.
Under user A, I did:
CREATE OR REPLACE PACKAGE alert_batch_pkg
AUTHID CURRENT_USER
IS
......
END alert_batch_pkg;
I then grant execute privilege to another user B, and create synonym under user B. I can now execute packages by login as user B, and it accesses data from user B. All work well except that in two procedures I have object type as parameter. Whenever I called those procedures, I got error:
ERROR at line 32:
ORA-06550: line 32, column 16:
PLS-00306: wrong number or types of arguments in call to 'ADD_INVENTORY'
ORA-06550: line 32, column 16:
PLS-00306: wrong number or types of arguments in call to 'ADD_INVENTORY'
ORA-06550: line 32, column 4:
PL/SQL: Statement ignored
The same test script run fine under user A.
Here is the procedure interface:
FUNCTION add_inventory (
p_inventory inv_input_data,
p_inventory_item table_of_inv_input_prod_data,
p_status_msg OUT VARCHAR2
)
RETURN NUMBER;
Here is the script that creates the object type (were created in both user A and B):
create or replace type dist_input_data
as object (
form_id varchar2(15),
form_date date,
pract_id number,
pract_addr_id number,
form_comment varchar2(1000),
user_id integer,
rep_id number)
/
create or replace type dist_input_prod_data
as object (
product_id number,
lot varchar2(20),
qty number(5),
call_type varchar2(10),
call_pos number(5))
/
create or replace type table_of_dist_input_prod_data as table of dist_input_prod_data
/
I also tried to create the first 2 objects with "AUTHID CURRENT_USER" clause in user A, but I got the same error message. I would appreciate it if you can help me on this.
April 01, 2003 - 7:35 pm UTC
they have to be the SAME object type (not the same structure in 2 schemas -- the SAME type). You will want to prefix that object type with the single schema name that it belongs to. Just like us with ORDSYS or MDSYS or any of our object types.
Thanks for your prompt response.
John, April 02, 2003 - 11:53 am UTC
Hope I understand you correctly. What you said is that if I want to use invoker rights in packages, object types used in those packages must come from one schema, instead of the schema of the invokers.
Here is what I tried and it worked:
1) I created object types in user A as usual, without "AUTHID CURRENT_USER" clause.
2) Grant execute privilege of those object to user B.
3) Create synonyms for those object types in user B, since my test script that calls the package need to recognize the object types.
4) Execute my test script in user B and it worked! Data were inserted into B.table.
Is this the correct way to implement invoker rights?
Some more questions:
1) Should the object type be created with "AUTHID CURRENT_USER" clause? In cases the object need to access data, it should access data from invokers schema (my test scenario is just using the object to pass data to stored procedures which in turn add them to the database). How about the collection TABLE created based on object type? It seems that I cannot use "AUTHID CURRENT_USER" clause there.
2) How Oracle manage the situation when two or more invokers simultaneously access the objects? Do they interfere with each other?
3) My third question is somehow related to the previous one. In the case of global variables (declared in package header), how does Oracle manage the threading issues when two or more users access them simultaneously? I've always been wonder about this.
BTW, after being at this website for so long, I realized that I need to get a copy of you One-on-One book. I got it today, and I'm very impressed with the topics you covered in that book!
April 02, 2003 - 12:52 pm UTC
that is one way -- but the use of synonyms could be (is to me) confusing. rather have an "object_type schema" that holds them and just points to them.
1) if you want the queries (if any) in the type bodies to be executed as queries in the calling packages are -- yes.
The table collection, having NO CODE associated with it - and hence no sql -- doesn't count. You don't need authid current user, there is no privelege set to change.
2) nope, it is as if each were in its own database
3) the code is shared -- the data (like any real OS) is private. This is not a DLL, this is just like real code in a real OS....
Thanks a lot!
John, April 02, 2003 - 4:39 pm UTC
I've learnt a great deal from the discussions with you!
Sorry for the synonym part that was confusing. Here is the interface of my function (object types were created in "jw2"):
FUNCTION add_inventory (
p_inventory jw2.inv_input_data,
p_inventory_item jw2.table_of_inv_input_prod_data,
p_status_msg OUT VARCHAR2
)
RETURN NUMBER;
The synonyms were solely for my test script to run under JW000. Without synonyms, my test script would look like this:
declare
v_inventory jw2.inv_input_data := jw2.inv_input_data (null, null, null, null, null, null, null);
vo_inventory_item jw2.inv_input_prod_data := jw2.inv_input_prod_data (null, null, null);
vot_inventory_item jw2.table_of_inv_input_prod_data := jw2.table_of_inv_input_prod_data (vo_inventory_item);
v_status binary_integer := null;
v_status_msg varchar2(512);
begin
v_inventory.rep_id := 1;
v_inventory.form_id := 36;
v_inventory.form_date := to_date('07/28/2002', 'MM/DD/YYYY');
v_inventory.inventory_type_id := 1;
v_inventory.inv_name_id := 1;
v_inventory.form_comment := 'Testing alert AFDA009';
v_inventory.user_id := 1;
vot_inventory_item.extend;
vot_inventory_item(1).product_id := 1;
vot_inventory_item(1).prod_lot_no := null;
vot_inventory_item(1).quantity := 110;
vot_inventory_item(2) := jw2.inv_input_prod_data (null, null, null);
vot_inventory_item(2).product_id := 2;
vot_inventory_item(2).prod_lot_no := null;
vot_inventory_item(2).quantity := 210;
v_status := inventory_pkg.add_inventory(v_inventory, vot_inventory_item, v_status_msg);
dbms_output.put_line('v_status ='||v_status);
dbms_output.put_line('v_status_msg ='||v_status_msg);
end;
/
Package privileges (9ir2)
Ray, July 04, 2003 - 11:39 am UTC
I have a situation...
Package A declares
type varray is table of varchar2(32767) index by binary_integer;
Package B references this declaration as
PROCEDURE Processform
(num_entries in number,
name_array in golk_utility.varray,
value_array in golk_utility.varray,
reserved in golk_utility.varray);
THE_USER has execute rights on Package B
No user has execute rights on Package A
Package B is valid and works to a point.
If the data returned in one of the value_array cells is > 1K the procedure fails on entry from the webserver (Mod-Plsql) < 1k works like a charm.
I grant execute on Package A to THE_USER
and I can get up to 32K in a cell.
very wierd (to me anyway)
Dynamic SQL and Invoker/Definers Rights
abhay, November 19, 2004 - 1:19 pm UTC
I tried following example. As you can see, the CURRENT_USER, SESSION_USER and SCHEMA_USER values are same.
Why is it failing ?
SQL> grant dba to usr1
2 /
Grant succeeded.
SQL> connect usr1/usr1
Connected.
SQL> set serveroutput on size 1000000;
SQL> create table test1 ( fld1 number )
2 /
Table created.
<<< Create procedure with DEFINERS rights >>>
SQL> create or replace procedure do_sql ( sql_str in varchar2 )
2 as
3 begin
4 for x in
5 ( select sys_context ( 'USERENV', 'CURRENT_USER' ) curr_user,
6 sys_context ( 'USERENV', 'SESSION_USER' ) sess_user,
7 sys_context ( 'USERENV', 'CURRENT_SCHEMA' ) curr_sch
8 from dual
9 )
10 loop
11 dbms_output.put_line ( 'Current User: ' || x.curr_user );
12 dbms_output.put_line ( 'Session Usr: ' || x.sess_user );
13 dbms_output.put_line ( 'Curr Schema: ' || x.curr_sch );
14 end loop;
15 execute immediate sql_str;
16 end;
17 /
Procedure created.
SQL>
SQL> select object_id from dba_objects where rownum < 4
2 /
OBJECT_ID
----------
17897
7529
23332
SQL>
SQL> exec do_sql ( 'insert into test1 values ( 1 ) ' );
Current User: USR1
Session Usr: USR1
Curr Schema: USR1
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
SQL> exec do_sql ( 'insert into test1 select object_id from dba_objects where rownum < 4');
Current User: USR1
Session Usr: USR1
Curr Schema: USR1
BEGIN do_sql ( 'insert into test1 select object_id from dba_objects where rownum < 4'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "USR1.DO_SQL", line 15
ORA-06512: at line 1
<<<< Create under INVOKERS rights >>>>
SQL> create or replace procedure do_sql ( sql_str in varchar2 )
2 authid current_user
...............
................
Procedure created.
SQL>
SQL> select object_id from dba_objects where rownum < 4
2 /
OBJECT_ID
----------
17897
7529
23332
SQL>
SQL> exec do_sql ( 'insert into test1 values ( 1 ) ' );
Current User: USR1
Session Usr: USR1
Curr Schema: USR1
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
SQL> exec do_sql ( 'insert into test1 select object_id from dba_objects where rownum < 4');
Current User: USR1
Session Usr: USR1
Curr Schema: USR1
PL/SQL procedure successfully completed.
-->>>> Why is it succeeding here ? All values are same.
SQL> exec do_sql ( 'insert into usr1.test1 select object_id from dba_objects where rownum < 4');
Current User: USR1
Session Usr: USR1
Curr Schema: USR1
PL/SQL procedure successfully completed.
November 19, 2004 - 2:15 pm UTC
do this
SQL> desc dba_objects
SQL> set role none;
SQL> desc dba_objects
the table it is complaining about is dba_objects -- you have access to that via a role and roles are never enabled in the execution of a definer rights routine and with invokers rights -- the SQL is executed with the privs of the currently logged in invoker (hence the role is there -- but only because that user has the role)
if you have my book "Expert One on One Oracle" -- i go into this in huge detail.
A reader, November 19, 2004 - 2:20 pm UTC
Nice
Gerhard, February 17, 2005 - 12:09 pm UTC
Dear Tom,
What is the actual benefit of using "authid" clause?
I am not clear in this even after reading your book.
If we grant execute privilege on the procedures created by
us,then others can use that.Does using "authid" clause
provide any special advantage?
Please do reply.
Bye!
February 17, 2005 - 2:00 pm UTC
the SQL run inside of authid current_user procedures is processed with the privileges and access controls of the INVOKER of the routine (the sql runs as if you typed it into sqlplus)
with the authid of the definer, it runs with the privs of the definer of the routine (minus roles)
So, you can write a utility that takes any query and writes it out to disk using utl_file -- and this utility's owner need not have access to EVERY table in the database, the procedure runs as if it were an anonymous block -- using the invokers privs, not the definers privs.
INVOKER and DEFINER RIGHTS
Maryam Talakoob, April 26, 2005 - 2:13 pm UTC
This article was extremely useful. Needless to say, that you ought to thoroughly see through and through the usage of AUTHID on procedure as well as package level. Try out a few test cases to really understand what it is meant to do.
Execute Immediate and Invoker Rights
Thiru, August 23, 2005 - 10:29 am UTC
Tom,
I create a procedure that uses Execute Immediate. I am not able to exec the procedure. Gives out "insufficient privileges" error. Same statement from a plsql block has no issues.
create or replace procedure t_pro
as
begin
execute immediate 'create table test_tbl(a number)';
end;
/
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SH.T_PRO", line 4
ORA-06512: at line 1
The user has DBA role.
Is it something to do with AUTHID? Executing the procedure by the owner should not have any issue, that's what I understand.
Thanks
August 24, 2005 - 4:26 am UTC
table or view does not exists
Vinayak Awasthi, July 25, 2006 - 7:43 am UTC
hi Tom,
sorry for not posting as original question as couldn't wait more for a free slot.
I have a schema on which i have the following roles/privleges:
1. CONNECT
2. EXP_FULL_DATABASE
3. IMP_FULL_DATABASE
4. RESOURCE
I am able to query system view dba_objects, i am able to retreive data through sql*plus but if I query the same through procedure, it says table or view does not exists for dba_objects. here is the code for my procedure:
CREATE OR REPLACE PROCEDURE test
AS
cursor c_cur is
SELECT 'CREATE SYNONYM '||OBJECT_NAME|| ' FOR '|| OWNER||'.'||OBJECT_NAME stmt
FROM DBA_OBJECTS
WHERE OWNER=user
AND OBJECT_TYPE='TABLE';
BEGIN
FOR i in c_cur LOOP
EXECUTE IMMEDIATE i.stmt;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||':'||sqlerrm);
END;
/
what could be the case or I need any other privlege.
July 25, 2006 - 11:41 am UTC
sql vs. anonymous block vs. pl/sql difference, is it related to rights or what?
Kevin, January 03, 2007 - 1:25 pm UTC
Hi Tom, you are your readers always asking good stuff. So I came across this as well, is it related to rights or related to the definition of the view, or something else. I am having difficulty explaining it to others. Basically, why did the rowcount change when the code became stored in the database. I would have liked the count to be the same since I am using definer's rights. FYI, this is run from a DBA account with select any table and select any dictionary.
Thanks, Kevin.
SQL> SELECT COUNT(*) FROM ALL_OBJECTS;
COUNT(*)
----------
6979
1 row selected.
SQL> set serveroutput on
SQL> declare
2 rowcount number;
3 begin
4 select count(*) into rowcount from all_objects;
5 dbms_output.put_line(rowcount);
6 end;
7 /
6979
PL/SQL procedure successfully completed.
SQL> create or replace procedure p1
2 as
3 rowcount number;
4 begin
5 select count(*) into rowcount from all_objects;
6 dbms_output.put_line(rowcount);
7 end;
8 /
Procedure created.
SQL> show errors
No errors.
SQL> exec p1
5992
PL/SQL procedure successfully completed.
SQL>
January 05, 2007 - 8:28 am UTC
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html same reason, no roles in the procedure. try this:
ops$tkyte%ORA9IR2> set role all;
Role set.
ops$tkyte%ORA9IR2> select count(*) from all_objects;
COUNT(*)
----------
30328
ops$tkyte%ORA9IR2> set role none;
Role set.
ops$tkyte%ORA9IR2> select count(*) from all_objects;
COUNT(*)
----------
24945
What about with a SERVERERROR trigger
Steve Baldwin, February 21, 2007 - 4:36 am UTC
(Using 9.2.0.8)
I have a situation where I have a central SERVERERROR trigger. What I want to be able to do is :
In a user trigger [before statement level] call a central function to 'register' a cleanup function.
If a DB error occurs, the central SERVERERROR trigger fires which we are using to store the error and context info the the DB. At the end of the SERVERERROR trigger I was hoping to be able to execute the cleanup function if one had been registered.
This doesn't work as I had hoped because the SERVERERROR trigger (which is owned by a central schema not the executing schema) can't see the cleanup function. It is as if the SERVERERROR trigger is executing with AUTHID DEFINER.
Is there any way to allow a centralised SERVERERROR code execute another chunk of code with AUTHID CURRENT_USER?
Thanks
February 21, 2007 - 10:59 am UTC
triggers ALWAYS execute with definers rights - there is no other way.
you would need to grant execute on that cleanup function to the owner of the trigger is all.
A reader
A, June 06, 2007 - 4:12 am UTC
Hi,
My case is as follows..
1) I have a user called A and it has got a package which will access the objects of user B(It truncates one of the table of user B using execute immediate along with truncate command).
2)Above package will be executed by user B.
3)User A has given execute permission on that procedure to user B.
4)User B has given delete permission on the objects to user A.
Now when I run the procedure from User B ,it complains about insufficient priv. Any idea what could be wrong here.
Cheers
June 06, 2007 - 1:39 pm UTC
when you run it from A it complains as well.
In order to truncate the tables in another schema, you need special privileges.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10006.htm#sthref9971 I do NOT suggest you grant that privilege, rather that B creates:
create or replace procedure truncate_t
as
begin
execute immediate 'truncate table t';
end;
/
grant execute on truncate_t to a;
The procedure owned by A runs "as A", even when B runs it.
Strange behaviour
Paolo Conversano, June 22, 2007 - 7:47 am UTC
Hi Tom, I have this strange behaviour. Could you help me to understand it?
C:\Documents and Settings\Administrator>sqlplus it68125/it68125@Marketing_integrazione_rem
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 22 13:29:32 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE TEST(P_TABLE VARCHAR2, P_OWNER VARCHAR2, P_GRANTEE VARCHAR2)
2 IS
3 v_view VARCHAR2(30) := 'V'||SubStr(p_table,2)||'_'||p_grantee;
4 BEGIN
5 Dbms_Output.put_line('PROMPT');
6 Dbms_Output.put_line('alter session set current_schema = '||P_OWNER||';');
7 Dbms_Output.put_line('CREATE OR REPLACE VIEW '||v_view||'(');
8 for c1 in ( select Decode(column_id,1,'',',')||column_name c from ALL_tab_cols
9 where table_name = P_TABLE
10 AND OWNER = P_OWNER
11 ORDER BY column_id) loop
12 Dbms_Output.put_line(c1.c);
13 end loop;
14 Dbms_Output.put_line(') as SELECT ');
15 for c1 in ( select Decode(column_id,1,'',',')||column_name c from ALL_tab_cols
16 where table_name = P_TABLE
17 AND OWNER = P_OWNER
18 ORDER BY column_id) loop
19 Dbms_Output.put_line(c1.c);
20 end loop;
21 Dbms_Output.put_line( ' FROM '||P_OWNER||'.'||P_TABLE||' WITH READ ONLY;');
22 END;
23 /
Procedure created.
SQL> BEGIN
2 TEST('TMKT0020','MKT','AVC');
3 END;
4 /
PROMPT
alter session set current_schema = MKT;
CREATE OR REPLACE VIEW VMKT0020_AVC(
) as SELECT
FROM MKT.TMKT0020 WITH READ ONLY;
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE TEST(P_TABLE VARCHAR2, P_OWNER VARCHAR2, P_GRANTEE VARCHAR2) AUTHID CURRENT_USER
2 IS
3 v_view VARCHAR2(30) := 'V'||SubStr(p_table,2)||'_'||p_grantee;
4 BEGIN
5 Dbms_Output.put_line('PROMPT');
6 Dbms_Output.put_line('alter session set current_schema = '||P_OWNER||';');
7 Dbms_Output.put_line('CREATE OR REPLACE VIEW '||v_view||'(');
8 for c1 in ( select Decode(column_id,1,'',',')||column_name c from ALL_tab_cols
9 where table_name = P_TABLE
10 AND OWNER = P_OWNER
11 ORDER BY column_id) loop
12 Dbms_Output.put_line(c1.c);
13 end loop;
14 Dbms_Output.put_line(') as SELECT ');
15 for c1 in ( select Decode(column_id,1,'',',')||column_name c from ALL_tab_cols
16 where table_name = P_TABLE
17 AND OWNER = P_OWNER
18 ORDER BY column_id) loop
19 Dbms_Output.put_line(c1.c);
20 end loop;
21 Dbms_Output.put_line( ' FROM '||P_OWNER||'.'||P_TABLE||' WITH READ ONLY;');
22 END;
23 /
Procedure created.
SQL> BEGIN
2 TEST('TMKT0020','MKT','AVC');
3 END;
4 /
PROMPT
alter session set current_schema = MKT;
CREATE OR REPLACE VIEW VMKT0020_AVC(
CID
,CRETE_DATEMPPGM_OREMPBRK
,CRETE
,DDATA_EMISS_PREV_PROGRAMMA
,DORA_EMISS_PREV_BREAK
) as SELECT
CID
,CRETE_DATEMPPGM_OREMPBRK
,CRETE
,DDATA_EMISS_PREV_PROGRAMMA
,DORA_EMISS_PREV_BREAK
FROM MKT.TMKT0020 WITH READ ONLY;
PL/SQL procedure successfully completed.
SQL>
I expect same results....why not?
Thanks a lot, Paolo
June 22, 2007 - 10:36 am UTC
i don't even know what I'm looking at there (CODE BUTTON!!!)
I see a bunch of code.
I see a bunch of code I cannot actually run (because, well, I don't have your table)
I see a bunch of code you say "it doesn't do what I expect" - which seems to imply you want me to debug it?
I do see "authid current_user" however in there.
do you understand what the huge difference between definer rights and invoker rights there is - and what they do - and what it means?
I would never expect all_* views to behave the same in a stored procedure with definers rights vs invokers rights - BY DEFINITION they won't (that is part of the goal there!!!)
do you have access to Expert One on One Oracle - if so, please see the chapter on invokers and definers rights...
Same review
A reader, June 26, 2007 - 5:19 am UTC
The question simply is:
In the same session of the same user...
I have the same procedure defined the first time with default (definer right), the second time (with authid current user).
The definer is the same of the current user. I expect no difference about the results of select * from all_tables.
I expect difference if I change the definer and the invoker.
"I would never expect all_* views to behave the same in a stored procedure with definers rights vs invokers rights - BY DEFINITION they won't (that is part of the goal there!!!) "
Me too, otherwise I haven't asked to you (by the way I have bought all your books)
Thanks, have a good vacation.
Paolo
July 02, 2007 - 9:14 am UTC
roles are NOT ENABLED during the execution of any definers rights routines.
the dictionary views are based on enabled roles.
so, if you have a definers rights routine - no roles, all_* views return one set of data.
if you have an invokers rights routine - roles are enabled, all_* views return a DIFFERENT set of data.
ops$tkyte%ORA10GR2> select * from session_roles;
ROLE
------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA
16 rows selected.
ops$tkyte%ORA10GR2> select count(*) from all_objects;
COUNT(*)
----------
51378
ops$tkyte%ORA10GR2> set role none;
Role set.
ops$tkyte%ORA10GR2> select * from session_roles;
no rows selected
ops$tkyte%ORA10GR2> select count(*) from all_objects;
COUNT(*)
----------
48237
Sorry for taking your time
A reader, June 26, 2007 - 9:17 am UTC
After reading again your chapter 23, I realize the difference
....the role.....
Sorry for my stupid question.
Paolo
Invoker rights to resolve dependencies
Lise, March 20, 2008 - 3:22 am UTC
I have a core schema where all code is shipped to. Each developer has their own schema with execute privilege to the code in the core schema.
However, if I need to make some changes to packageA, I bring this into my developer schema. If it calls into several other packages, I need to bring these into my own schema too, since I need to recompile them too.
Do you think it would be a good idea to declare all the code in the core schema with invoker rights to the current user? This would remove the need for every developer to bring in code they are not actually changing, but need to recompile.
March 24, 2008 - 10:25 am UTC
I think it is a horrible idea for each developer to have their own schema - so, turn the problem around, you are looking for a solution to a problem that rightfully should NOT EXIST.
You want a SINGLE SCHEMA, not the "ability" for your code to access many schemas (you'll defeat shared sql, there will be all kinds of integration issues as each developer makes their schema unique - this is not a path you want to go down, not even a little bit)
Invokers rights vs one development schema
Lise, February 23, 2009 - 2:19 pm UTC
A development team might consist of 20 people, and it would therefore be difficult for all to work in one schema.
Say each developer code in their own schema. Once completed and unit tested, they will move it into a shared schema.
If their code has lots of dependent packages, they will have to recreate all these in their own schema. I thought I could use invoker rights to resolve this dilemma, but it only works for data and not code.
Giving execute immediate will not work either, since once you have called something from another schema, all code is called from this other schema too. It will not go back into your schema and check if the code is there.
A dilemma that might be impossible to resolve.
February 23, 2009 - 5:48 pm UTC
or have them use explicit schema references (which is not a horrible idea) and when they install - the provide as input to the install the "bindings"
superior to private synonyms - I like explicit references, not soft links.
Also, the invoker trick - would never have worked, it changes too many things, your test would not be testing anything 'real'