RE: grants on a schema

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Fri, 11 Jan 2013 21:56:03 +0000
Message-ID: <CAOuMUT6wsAxnY5288CUhdQ-XU66wxXbJfPov_VzW=yd2zb7y4A_at_mail.gmail.com>



Hi,
I developed a utility for this

I 've made it available for download here:

https://www.dropbox.com/s/avd1fa9raopfhiu/xpriv.zip


  • To Install xpriv

connect to database as sys and run install.sql


  • To Uninstall xpriv

connect to database as sys and run uninstall.sql


  • Using xpriv
    1. To grant schema level privileges use xpriv.grant_schema. Privileges will be granted on existing objects and automatically granted on new objects.

    e.g. To grant select on USER1's objects to USER2:

         xpriv.grant_schema('USER1','USER2','SELECT');

    e.g. To grant execute on USER1's objects to ROLE1 with grant option:

         xpriv.grant_schema('USER1','ROLE1','EXECUTE',TRUE);

2. To revoke schema level privileges use xpriv.revoke_schema

    e.g. To revoke select on USER1's objects from USER2:

         xpriv.revoke_schema('USER1','USER2','SELECT');

    e.g. To revoke all privs on USER1's objects from USER2:

         xpriv.revoke_schema('USER1','USER2','ALL');


  • Notes
    1. The parameters P_SCHEMA and P_GRANTEE are case sensitive cause user names in oracle are case sensitive (well kind of). Privileges are not case sensitive.
    2. The parameter P_GRANTEE can be a user,role or public.
    3. xpriv.revoke_schema does not differentiate between privs that were granted normally with the grant command from privs granted with this package. Both will be revoked.
    4. The following privilege are supported:

'INDEX', 'INSERT', 'SELECT', 'EXECUTE', 'DEQUEUE', 'MERGE VIEW', 'QUERY

REWRITE', 'REFERENCES','ON COMMIT REFRESH', 'READ', 'ALTER', 'DEBUG',
'UPDATE', 'DELETE',
'FLASHBACK', 'UNDER', 'WRITE','ALL'

e.g. select applies to tables, views, mviews and sequences. e.g. execute applies to PROCEDURE,PACKAGE,FUNCTION,LIBRARY,TYPE,OPERATOR and INDEXTYPE.

5: xpriv uses dbms_job to grant privs on newly created database objects. When a new database object is created, the xpriv_trig trigger fires and if privileges need to be granted a dbms_job is submitted. So if the automatic granting of privileges is not working check that dbms_job is configured.

6: To see what privileges have been granted with xpriv, look in sys.xpriv_schema_privs;

Regards,
Fergal
On 11 Jan 2013 21:34, "Don Granaman" <DonGranaman_at_solutionary.com> wrote:

> You haven't missed anything. Other than with the (justifiably) highly
> discouraged A-bomb of granting "% ANY %" system privileges, there is no way
> to grant privileges on every object in a schema.
>
> Don Granaman | Ph: 402-361-3073 | Cell: 402-960-6955 | Solutionary -
> Relevant | Intelligent | Security
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Jared Still
> Sent: Friday, January 11, 2013 2:07 PM
> To: Brian Zelli
> Cc: oracle-l (oracle-l_at_freelists.org)
> Subject: Re: grants on a schema
>
> On Fri, Jan 11, 2013 at 11:47 AM, Zelli, Brian
> <Brian.Zelli_at_roswellpark.org>wrote:
> > I've googled this but can you do grants on a schema? I have a little
> > proc that could loop thru and run the grant but I thought that maybe
> > there was a one-liner to do it.....
> > ciao,
> > Brian
> >
> >
> >
> grant DBA to <username>;
>
> But on a more serious note, I don't believe that can be done.
> Unless I've missed something (else) in more recent documentation.
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog:
> http://jkstill.blogspot.com Home Page: http://jaredstill.com
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 11 2013 - 22:56:03 CET

Original text of this message