| Security and Stored procedures/anonymous PL/SQL blocks [message #2535] |
Wed, 24 July 2002 08:11  |
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  |
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 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
