Re: Problem with granting object permissions to a role

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sat, 8 Feb 2003 11:17:57 +1100
Message-ID: <dUX0a.42913$jM5.108688@newsfeeds.bigpond.com>

> I didn't see any version numbers in your posting, so there might be two
answers
> to this. Prior to 9.2 you needed to log on as the owner of the object or
> someone who had been granted access WITH GRANT OPTION (that last bit
allows them
> to pass it on). In 9.2, this is no longer the case (sounds of the
Hallelujah
> Chorus resound!). ANyone with DBA privileges can grant access in 9.2
(can't
> remember off the top of my head if this comes with the DBA role or
SYSDBA). Oh dear, Pete!! Pete!!!! What are you doing????? We most emphatically do NOT want to recommend the DBA role to anyone. It's a dog's breakfast, and deserves to put down (and I seem to recall a little bird telling me that it would be before we hit 11.5i).

The ability to grant any object privilege arises purely and simply because there is a new *system* privilege to permit it: namely, 'grant any object privilege' (privilege number 167 in the system_privilege_map view). So you grant 'grant any object privilege' to Fred, and Fred can now grant 'select on scott.emp' to Margaret:

SQL> create user fred identified by fred; User created.
SQL> create user margaret identified by margaret; User created.
SQL> grant create session, grant any object privilege to fred; Grant succeeded.
SQL> grant create session to margaret;
Grant succeeded.

SQL> connect fred/fred
Connected.
SQL> grant select on scott.emp to margaret; Grant succeeded.

SQL> connect margaret/margaret
Connected.
SQL> select * from scott.emp;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ----------

      7369 SMITH      CLERK           7902 17/DEC/80       1300
      7499 ZEBEDEE    SALESMAN        7698 20/FEB/81       1300        300
      7521 WARD       SALESMAN        7698 22/FEB/81       1300        500

..etc etc etc

Nothing to do with nasty, dubious, naff, insecure and inherently dodgy roles whatsoever, thank the Lord. And nothing intrinsically to do with the SYSDBA privilege, either.

Regards
HJR Received on Fri Feb 07 2003 - 18:17:57 CST