Re: Why is Oracle letting me do this? Security issue?

PMG, I have tracked down a section of the Oracle8i manuals as follows,

Hope this helps,
Andrew

Procedure Execution and Security Domains

A user with the EXECUTE object privilege for a specific procedure can execute the procedure or compile a program unit that references the procedure. No runtime privilege check is
made when the procedure is called. A user with the EXECUTE ANY PROCEDURE system privilege can execute any procedure in the database.

A user can be granted privileges through roles to execute procedures. See "PL/SQL Blocks and Roles" for more information about roles.

Additional privileges on referenced objects are required for invoker-rights procedures, but not for definer-rights procedures (see "Definer Rights and Invoker Rights").

Definer Rights

A user of a definer-rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses, because a definer-rights
procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it. The procedure's owner must have all the necessary object privileges
for referenced objects. Fewer privileges have to be granted to users of a definer-rights procedure, resulting in tighter control of database access.

You can use definer-rights procedures to add a level of database security. By writing a definer-rights procedure and granting only EXECUTE privilege to a user, the user can be forced to
access the referenced objects only through the procedure (that is, the user cannot submit ad hoc SQL statements to the database).

The current privileges of the owner of a definer-rights stored procedure are always checked before the procedure is executed. If a necessary privilege on a referenced object has been
revoked from the owner of a definer-rights procedure, the procedure cannot be executed by the owner or any other user.

              Note:

              Trigger execution follows the same patterns as definer-rights procedures. The user executes a SQL statement, which that user is privileged to

              execute. As a result of the SQL statement, a trigger is fired. The statements within the triggered action temporarily execute under the security domain

              of the user that owns the trigger.

Invoker Rights

An invoker-rights procedure executes with all of the invoker's privileges, including enabled roles. A user of an invoker-rights procedure needs privileges on the underlying objects that the
procedure accesses for which names are resolved in the invoker's schema.

     For external references that are resolved in the invoker's schema (such as names in DML or dynamic SQL statements), the invoker needs privileges to access the underlying

     objects.

     For all other objects (such as functions and procedures), the owner's privileges are checked at compile time, and no runtime check is made.

See "Resolution of External References".

Program references embedded in DML statements or dynamic SQL statements are effectively recompiled at runtime and therefore are checked at runtime with the invoker's privileges.

Many packages provided by Oracle, such as most of the DBMS_* packages, run with invoker rights--they do not run as the owner (SYS) but rather as the current user. However, some
exceptions exist such as the DBMS_RLS package (see "Fine-Grained Access Control").

              Additional Information:

              See Oracle8i Supplied Packages Reference for detailed documentation of the Oracle supplied packages.

You can create a software bundle that consists of multiple program units, some with definer rights and others with invoker rights, and restrict the program entry points (controlled
step-in). A user who has the privilege to execute an entry-point procedure can also execute internal program units indirectly, but cannot directly call the internal programs.

PMG wrote:

> I'd love to find out what the official term for this, and the justification for
> it. It seems to be a loophole in security, since I can indirectly modify a table
> that I do not have direct permissions assigned.
>
> Pete
>
> Andrew Babb wrote:
>
> > Hi,
> >
> > I think you will find that the trigger fires as the owner, and not as the
> > person performing the initial insert. Therefore, it is the schema of table A
> > performing the insert into table B, not User X performing the insert into
> > table B.
> >
> > Oracle does have an official term for this, which someone might be able to
> > provide, but I cannot remember immediately.
> >
> > Rgds
> > Andrew
> >
Received on Sun Apr 11 1999 - 05:13:14 CDT