Home » RDBMS Server » Security » Stored Procedure - Security Question (Oracle 10 g)
Stored Procedure - Security Question [message #534715] Thu, 08 December 2011 12:00 Go to next message
venuspvr
Messages: 7
Registered: December 2011
Location: a
Junior Member
Hi,

I built a view ITEM_VIEW which gets records based on the user that queries it. I used user_name=sys_context('userenv','current_user')) in the where clause in the view. Now I have written a stored procedure in a generic(GENSCH) schema in which I am querying this view. I gave user1 and user2 permissions to execute the procedure. When I run the procedure GENSCH.procedure_name either using user1 or user2 it gives me all the records. I think it is running the view in the stored proc as user GENSCH rather than user1 or user2 irrespective of who I login as.

Please suggest how to accomplish this. I do not want to create local procedures for each user.

Let me know if you need more information with the query.

Thanks and Regards,
Veena
Re: Stored Procedure - Security Question [message #534716 is a reply to message #534715] Thu, 08 December 2011 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:24065646637395

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Stored Procedure - Security Question [message #534717 is a reply to message #534716] Thu, 08 December 2011 12:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#sthref799
Re: Stored Procedure - Security Question [message #534718 is a reply to message #534715] Thu, 08 December 2011 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use USER instead of SYS_CONTEXT:

SQL> create or replace procedure p is
  2    user1 varchar2(30) := sys_context('userenv','current_user');
  3    user2 varchar2(30) := user;
  4  begin
  5    dbms_output.put_line('sys_context='||user1||', user='||user2)
  6  end;
  7  /

Procedure created.

SQL> exec p;
sys_context=MICHEL, user=MICHEL

PL/SQL procedure successfully completed.

SQL> grant execute on p to test;

Grant succeeded.

SQL> connect test/test;
Connected.
TEST> exec michel.p
sys_context=MICHEL, user=TEST

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Thu, 08 December 2011 12:30]

Report message to a moderator

Re: Stored Procedure - Security Question [message #534739 is a reply to message #534718] Thu, 08 December 2011 14:44 Go to previous message
venuspvr
Messages: 7
Registered: December 2011
Location: a
Junior Member
Thank you every one. I am a MS SQL server developer and little new to pl/sql. your guidance is very helpful.
@blackswan: I will surely follow the posting guidelines now on.
@Michel: The code you provided helped. It resolved my issue.
Previous Topic: Design question regarding security
Next Topic: how to enable auditing in oracle
Goto Forum:
  


Current Time: Thu Nov 21 10:54:31 CST 2024