Thanks Thomas,Richard and Mark,
Those were great explanations! Not only do I understand how it works, I
understand
the impliations. Hopefully, the next Concepts manual will be a little
less obtuse.
Thomas,
Here are the next couple of sentences from the Concepts manual:
Internally, Oracle notes that such objects do not have to be recompiled.
Only
security authorizations need to be validated, not the structure of any
objects. This
optimization eliminates unnecessary recompilations and prevents the need to
change a dependent object's time stamp.
Is this some sort of new mechanism with 9i which helps prevent the kind of
enmass recompile you described?
Regards,
Mike
"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
news:aod58s01s1f_at_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:
>
> a) the owner of that procedure had SELECT granted on SCOTT.EMP directly to
them
> b) PUBLIC had select on scott.emp
> c) the owner of that procedure had SELECT ANY TABLE granted to them
directly
> d) 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 Mon Oct 14 2002 - 12:02:37 CDT
