Home » RDBMS Server » Security » Security and Stored procedures/anonymous PL/SQL blocks
Security and Stored procedures/anonymous PL/SQL blocks [message #2535] Wed, 24 July 2002 08:11 Go to next message
Mark Grimshaw
Messages: 73
Registered: June 2002
Member
I have a set of stored procedures which do updates, selects etc and also Fortran code with PL/SQL blocks using these procedures and also views and statements directly. I want to restrict the users ability to use procedures based on whether they have the correct permissions. I am aware of how to set up the roles, privileges etc but don't know how Oracle checks the user say on each invocation of a procedure. Do you have to have parameters for the procedure that accept username and password to determine use or are there Oracle 'environment variables' that can be used?

Thanks,

Mark Grimshaw
Re: Security and Stored procedures/anonymous PL/SQL blocks [message #2538 is a reply to message #2535] Wed, 24 July 2002 10:26 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The security context of a procedure is controlled by whether the procedure is defined with definer (default) or invoker rights. Under definer rights, the procedure executes with the privileges of the procedure owner (and all object names are resolved in that context). Under invoker rights, the procedure executes with the privileges of the caller (and all object names are resolved with the context of the caller).

Check out the documentation under invoker and definer rights (authid current_user or authid definer).
Re: Security and Stored procedures/anonymous PL/SQL blocks [message #2543 is a reply to message #2538] Thu, 25 July 2002 03:43 Go to previous messageGo to next message
Mark Grimshaw
Messages: 73
Registered: June 2002
Member
Thanks for the lead - one qestion I have before ooking at the documentation however is can the Oracle server distinguish between the different session contexts when say a client is a compiled C/Fortran program execution or is a java client using an Oracle JDBC driver. In other words how does identity get transmitted across application tiers/layers ?

Mark
Re: Security and Stored procedures/anonymous PL/SQL blocks [message #2550 is a reply to message #2543] Thu, 25 July 2002 09:44 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The identity is based on whoever establishes the connection. A program executing at the client will obviously connect directly to the database using a specific user login. In a multi-tier environment, whatever user the middle tier is using to connect to the database will be used for identity purposes.
Previous Topic: insufficient privilege to access object SYS.DBA_SEGMENTS
Next Topic: Change Password
Goto Forum:
  


Current Time: Tue Oct 26 07:46:07 CDT 2021