Re: Question RE: GRANTS

A copy of this was sent to NeedaHoliday (if that email address didn't require changing) On Thu, 06 May 1999 13:59:37 GMT, you wrote:

>I am responsible for implementing exception handling in a system. My
>problem is, I have been working with the Oracle administrator trying
>to create Oracle problems that can be trapped by the program logic.
>We thought this would be a simple task but we are having problems
>creating the test environment.
>
>Scenerio
>
>All access to the tables has been revoked and all synonms removed. If
>we enter the online system, the system cannot be used. If we enter
>SQLPLUS and query the tables, all access is revoked. So far so good.
>If I EXEC a procedure from the SQL command line, the routine goes
>ahead and performs the task. The tables are queried and updated. The
>administrator has revoke all access except the EXEC function. Not so
>good.
>
>Can anyone tell us, why the EXEC has access when we believe it should
>not.
>

Its the way procedures were designed to operate. In Oracle8.0 and before, procedures always run with DEFINERS RIGHTS -- the base set of priveleges the creator of the procedure has is what the procedure runs with. It is in fact a security feature.

Lets say the owner of some data is SCOTT. SCOTT has an employee table. He wants others to be able to modify this table and query this table *however* he has some business logic that must be used to process the update or do the query. Rather then letting those people that need access to the information have SELECT or UPDATE on the emp table (which lets them do pretty much anything to them), SCOTT writes a pair of procedures, update_emp and select_emp that implement his business logic and modify/query the emp table in the manner he feels is correct. Scott now grantes execute on these procedures to the right people. These people *do not* need select/update on the emp table (be a bad thing if they had it) but are able to update/select the emp table via these procedures.

This allows you to extend the security model beyond the all or nothing "grant select on T" to "you may run my procedure which will intelligently select data for you using my rules and perhaps using my audits as well".

In Oracle8i, release 8.1 there is a mode called invokers right in which a procedure will execute with the base set of priveleges of the currently logged in user in which case the person running the stored procedure will in fact need update/select in order to update/select the base tables.

>Thanks in advance.
>
>james.stewart_at_nospamPWGSC.GC.CA
>
>Please remove nospam to email.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 06 1999 - 09:32:23 CDT