Re: nonsense from Oracle 9i Database Concepts

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Mon, 14 Oct 2002 22:36:54 +1000
Message-ID: <XHyq9.52502$g9.153164@newsfeeds.bigpond.com>

Hi All

Comments at end

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

Just to add to what Tom has said and as the doco kinda says, it doesn't necessarily have to be the revoke of a privilege that's *actually relevant* to the dependent object that causes the invalidation but the revoke of any privilege that *might* be relevant. For as Tom has stated, Oracle doesn't keep tabs on which privilege was actually used to OK a dependent object, only that a particular object is dependent on another.

Therefore even though a procedure may only query a table, Oracle only knows the procedure is dependent on the table, not *how* it's dependent. Therefore the revoke any DML privilege (say UPDATE) on the table will cause the procedure to be invalidated.

Let me illustrate.

SQL> create user thin_white_duke identified by s_to_s;

User created.

SQL> grant create session, create procedure to thin_white_duke;

Grant succeeded.

SQL> grant select, update on bowies_table to thin_white_duke;

Grant succeeded.

SQL> connect thin_white_duke/s_to_s
Connected.

SQL> create procedure ziggy_proc
  2 as
  3 v_major_tom number;
  4 begin

  5      select major_tom into v_major_tom from bowie.bowies_table
  6      where major_tom = 1;

  7 end;
  8 /

Procedure created.

SQL> select status from user_objects where object_name = 'ZIGGY_PROC';

STATUS

---

VALID SQL> connect bowie/bowie
Connected.
SQL> revoke update on bowies_table from thin_white_duke;

Revoke succeeded.

SQL> connect thin_white_duke/s_to_s
Connected.
SQL> select status from user_objects where object_name = 'ZIGGY_PROC';

STATUS

---

INVALID SQL> alter procedure ziggy_proc compile;

Procedure altered.

SQL> select status from user_objects where object_name = 'ZIGGY_PROC';

STATUS

---

VALID SQL> connect bowie/bowie
Connected.
SQL> revoke select on bowies_table from thin_white_duke;

Revoke succeeded.

SQL> connect thin_white_duke/s_to_s
Connected.
SQL> select status from user_objects where object_name = 'ZIGGY_PROC';

STATUS

---

INVALID SQL> alter procedure ziggy_proc compile;

Warning: Procedure altered with compilation errors.

SQL> show errors
Errors for PROCEDURE ZIGGY_PROC:

LINE/COL ERROR

-------- -----------------------------------------------------------------
5/5      PL/SQL: SQL Statement ignored
5/50     PL/SQL: ORA-00942: table or view does not exist

SQL> As you can see, the revoke of the update privilege caused the procedure that only queried the table to be invalidated.

Cheers

Richard Received on Mon Oct 14 2002 - 07:36:54 CDT