Implementing schema level privileges

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Wed, 6 Jun 2012 20:34:24 +0100
Message-ID: <CAOuMUT6iHseFDy5ys1PDNf1U+fXbfMy=_0p+fSZ=nmKtGoxwrA_at_mail.gmail.com>

Hi,
I mentioned in another thread that I had a utility for maintaining schema level privileges.

A few of you were interested in getting the code so I 've made it available for download here:

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

If anyone has trouble downloading it, let me know.

---

• 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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 06 2012 - 14:34:24 CDT