Home » SQL & PL/SQL » SQL & PL/SQL » Setting CURRENT_SCHEMA doesn't influence the stored procedure
Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #206857] Fri, 01 December 2006 14:58 Go to next message
yonieilon
Messages: 13
Registered: October 2006
Junior Member
From user MOSHE:
----------------
create table new_tab2 (a varchar2(10)); 


From user SYS:
--------------
CREATE OR REPLACE PACKAGE PACK1 AS 

PROCEDURE EXECUTE_DDL (ddl_string varchar2); 

END PACK1; 
/ 


CREATE OR REPLACE PACKAGE BODY PACK1 AS 

PROCEDURE EXECUTE_DDL (ddl_string varchar2) IS 
l_user varchar2(100); 
BEGIN 

select schemaname 
into l_user 
from v$session 
where audsid = userenv('sessionid'); 

DBMS_OUTPUT.PUT_LINE('schema user: ' || l_user); 
DBMS_OUTPUT.PUT_LINE('user: ' || USER); 
EXECUTE IMMEDIATE (ddl_string); 
EXCEPTION 
WHEN OTHERS THEN 
raise; 
END EXECUTE_DDL; 

END PACK1; 
/ 


create public synonym PACK1 for sys.PACK1; 

grant execute on PACK1 to TEST; 



From user TEST:
------------------
SQL> select username, schemaname from v$session where audsid = userenv('sessionid'); 

USERNAME SCHEMANAME 
------------------------------ ------------------------------ 
TEST TEST 

SQL> ALTER SESSION SET CURRENT_SCHEMA= moshe; 

Session altered. 

SQL> select username, schemaname from v$session where audsid = userenv('sessionid'); 

USERNAME SCHEMANAME 
------------------------------ ------------------------------ 
TEST MOSHE 

SQL> exec PACK1.execute_ddl('alter table new_tab2 add i number(2)'); 
schema user: SYS 
user: TEST 
BEGIN PACK1.execute_ddl('alter table new_tab2 add i number(2)'); END; 

* 
ERROR at line 1: 
ORA-00942: table or view does not exist 
ORA-06512: at "SYS.PACK1", line 17 
ORA-06512: at line 1 




As you can see the "schema user" in the PACK1.EXECUTE_DDL was SYS instead of MOSHE and so the alter table statement failed.
Can someone please help me understand why?
Re: Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #206866 is a reply to message #206857] Fri, 01 December 2006 17:44 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
By default your package uses definer rights - try adding invoker rights:

CREATE OR REPLACE package pkg1 
AUTHID current_user
is
...


Also - see all the different variables available in sys_context (specifically CURRENT_SCHEMA and SESSION_USER)
http://www.psoug.org/reference/sys_context.html

Also - "where audsid = userenv('sessionid')" isn't safe from dbms_job - try :
select ...  FROM v$session
where sid = (select sid from v$mystat where rownum = 1);

[Updated on: Fri, 01 December 2006 17:45]

Report message to a moderator

Re: Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #206867 is a reply to message #206857] Fri, 01 December 2006 17:45 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You could utilize INVOKER RIGHTS as follows:

SQL> CREATE OR REPLACE PACKAGE PACK1
  2  authid current_user AS
  3
  4  PROCEDURE EXECUTE_DDL (ddl_string varchar2);
  5
  6  END PACK1;
  7  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY PACK1 AS
  2
  3  PROCEDURE EXECUTE_DDL (ddl_string varchar2) is
  4  l_user varchar2(100);
  5  BEGIN
  6
  7  select schemaname
  8  into l_user
  9  from v$session
 10  where audsid = userenv('sessionid');
 11
 12  DBMS_OUTPUT.PUT_LINE('schema user: ' || l_user);
 13  DBMS_OUTPUT.PUT_LINE('user: ' || USER);
 14  EXECUTE IMMEDIATE (ddl_string);
 15  EXCEPTION
 16  WHEN OTHERS THEN
 17  raise;
 18  END EXECUTE_DDL;
 19
 20  END PACK1;
 21  /

Package body created.

SQL> conn test/test
Connected.
SQL> ALTER SESSION SET CURRENT_SCHEMA= moshe;

Session altered.

SQL> select username, schemaname from v$session where audsid = userenv('sessioni
d');

USERNAME                       SCHEMANAME
------------------------------ ------------------------------
TEST                           MOSHE

SQL> exec PACK1.execute_ddl('alter table new_tab2 add i number(2)');

PL/SQL procedure successfully completed.
Re: Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #207156 is a reply to message #206867] Mon, 04 December 2006 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As a rule, DON'T do things like this with the SYS user.

Re: Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #217416 is a reply to message #207156] Fri, 02 February 2007 01:51 Go to previous messageGo to next message
yonieilon
Messages: 13
Registered: October 2006
Junior Member
Still, does anyone know of a way to do what I'm trying?

Thanks,
Yoni.
Re: Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #217420 is a reply to message #217416] Fri, 02 February 2007 02:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you want to give people the privilege to do ANYTHING they want, simply grant DBA to them...
Re: Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #217509 is a reply to message #206857] Fri, 02 February 2007 10:20 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Frankly this is one of the worst security breaches that I have seen. To setup a general package that will run as sys and can execute any DDL command is simply idiotic. Why not make every use a DBA so that they can trash your entire database without using your procedure.
Re: Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #217519 is a reply to message #206857] Fri, 02 February 2007 11:17 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Bill B,
Please don't be such a pessimist.
Look on the bright side. With such a procedure in place the DBA will never have to get involved with DB object permissions & protections; since anybody can do anything to any object.
I just hope that tested backup & point in time recovery procedures exist.
Re: Setting CURRENT_SCHEMA doesn't influence the stored procedure [message #218030 is a reply to message #217519] Tue, 06 February 2007 08:19 Go to previous message
yonieilon
Messages: 13
Registered: October 2006
Junior Member
Thanks for your frank responses...

My question is, if we define to package using the
"authid current_user" clause, won't the statement run with the executing user's privileges, so this isn't such a security breach?
Previous Topic: Difference between VARCHAR2(80) and VARCHAR2(80 BYTE)
Next Topic: How Do I Concatenate SQL String in a procedure?
Goto Forum:
  


Current Time: Wed Dec 07 05:17:29 CST 2016

Total time taken to generate the page: 0.08493 seconds