Re: Problem with granting object permissions to a role

In article <dUX0a.42913$jM5.108688_at_newsfeeds.bigpond.com>, "Howard says...
>
>> 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
>
>

I crawl abjectly and offer my humblest apologies for being in a hurry and bashing out something without looking up the specific privilege. :)

I wasn't in fact attempting to indicate the DBA role was valuable, rather that this privilege was something that came with that role and was something that DBA's would want to use (rather than wanting to use the role).

Now, just how far do I need to crawl on my belly before HJR talks to me again? :)

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Sat Feb 08 2003 - 13:38:51 CST