Re: nonsense from Oracle 9i Database Concepts

In article <NMCq9.38376$NW3.7002_at_sccrnsc03>, "Michael says...
>
>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?
>

no, its been there for a while (it is in old docs as well)

It is a "faster" recompile, but not a skipping of it. they all go invalid, they'll all be recompiled during their next execute.

>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
>>
>
>

--
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