RE: grants on a schema

From: Fergal Taheny <>
Date: Fri, 11 Jan 2013 21:56:03 +0000
Message-ID: <>

I developed a utility for this

I 've made it available for download here:

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


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


2. To revoke schema level privileges use xpriv.revoke_schema

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


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


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



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;

On 11 Jan 2013 21:34, "Don Granaman" <> 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: []
> On Behalf Of Jared Still
> Sent: Friday, January 11, 2013 2:07 PM
> To: Brian Zelli
> Cc: oracle-l (
> Subject: Re: grants on a schema
> On Fri, Jan 11, 2013 at 11:47 AM, Zelli, Brian
> <>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:
> Home Page:
> --
> --

Received on Fri Jan 11 2013 - 22:56:03 CET

Original text of this message