Stored Procedure - Security Question [message #534715] |
Thu, 08 December 2011 12:00 |
|
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 #534718 is a reply to message #534715] |
Thu, 08 December 2011 12:29 |
|
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 |
|
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.
|
|
|