Re: nonsense from Oracle 9i Database Concepts

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 13 Oct 2002 18:05:32 -0700
Message-ID: <aod58s01s1f@drn.newsguy.com>

In article <Jjmq9.27532$uF6.44485_at_rwcrnsc52.ops.asp.att.net>, "Michael says...
>
>Would somebody be kind enough to tell me what this excerpt from
>
>"Oracle 9i Database Concepts" page 15-7 means?
>
>----------------------------------------------------------------------
>
>Security Authorizations
>
>Oracle notices when a DML object or system privilege is granted to or
>revoked from
>
>a user or PUBLIC and automatically invalidates all the owner's dependent
>objects.
>
>Oracle invalidates the dependent objects to verify that an owner of a
>dependent
>
>object continues to have the necessary privileges for all referenced
>objects.
>
>-------------------------------------------------------------
>
>An example would probably help.

When you create a compiled stored object (procedure, package, function, view, trigger, etc) Oracle sets up dependencies between it and every referenced object.

For example, you create a procedure:

create procedure p
as
begin

   for x in ( select * from scott.emp )
   loop
     null;
   end loop;
end;
/

as someone other then SCOTT. If that compiles successfully then one of a few things must have been true:

the owner of that procedure had SELECT granted on SCOTT.EMP directly to them
PUBLIC had select on scott.emp
the owner of that procedure had SELECT ANY TABLE granted to them directly
PUBLIC had select any table granted to them directly.

Now, when you compile P -- Oracle validates you have SELECT on EMP via SOME METHOD (any of the above for example). It does not remember which one it used (nor would it make sense to really) -- just that you did have access to emp when it was compiled.

Whenever the status of ANY of these four privs changes in a negative fashion (that could cause you to lose access to EMP) that procedure P will be invalidated and will need to be rechecked.

For example:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop user test cascade; User dropped.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create user test identified by test; User created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> grant connect, create procedure to test; Grant succeeded.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> @connect scott/tiger

scott_at_ORA817DEV.US.ORACLE.COM> grant select on emp to PUBLIC; Grant succeeded.

scott_at_ORA817DEV.US.ORACLE.COM> grant select on emp to TEST; Grant succeeded.

REM -- here we gave TEST two ways to get to EMP - via a grant to public, via a grant to TEST.

scott_at_ORA817DEV.US.ORACLE.COM> @connect test/test

test_at_ORA817DEV.US.ORACLE.COM> create procedure p   2 as
  3 l_cnt number;
  4 begin
  5 select count(*) into l_cnt from scott.emp;   6 end;
  7 /

Procedure created.

test_at_ORA817DEV.US.ORACLE.COM> select status from user_objects where object_name = 'P';

STATUS

VALID test_at_ORA817DEV.US.ORACLE.COM> select referenced_owner, referenced_name 2 from user_dependencies
3 where name = 'P';

REFERENCED_OWNER REFERENCED_NAME

SYS                            STANDARD
SYS                            SYS_STUB_FOR_PURITY_ANALYSIS
SCOTT                          EMP

test_at_ORA817DEV.US.ORACLE.COM> @connect scott/tiger

scott_at_ORA817DEV.US.ORACLE.COM> revoke select on emp from public; Revoke succeeded.

REM -- note, TEST still has FULL access to EMP!!! but:

scott_at_ORA817DEV.US.ORACLE.COM> @connect test/test

test_at_ORA817DEV.US.ORACLE.COM> select status from user_objects where object_name = 'P';

STATUS

INVALID REM -- the procedure must be revalidated to ensure that we still have access. It would recompile itself upon the next execution -- but we can force it right now to see that this is so:

test_at_ORA817DEV.US.ORACLE.COM> alter procedure p compile; Procedure altered.

test_at_ORA817DEV.US.ORACLE.COM> select status from user_objects where object_name = 'P';

STATUS

VALID On a test database, if you have LOTS OF TIME, try this one (seriously folks -- A TEST DATABASE that you have LOTS OF TIME to spend waiting for)

grant select any table to public;
revoke select any table from public;

consider what happens to any compiled stored object that references a TABLE!!! That revoke will invalidate pretty much the entire database. As Pink Floyd used to sing "careful with that axe Eugene" -- caveat emptor if you try this one at home.

>
>Thanks,
>
>Mike
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sun Oct 13 2002 - 20:05:32 CDT