privileges in bulk

have been dealing with oracle privileges today for the first time seiously.

It turned out that granting privileges is quite a pain (as everything else) in oracle. I needed to grant select (or any other) privilege on all tables in a schema at once. But oracle standard command only makes it possible to grant privs on a single object. So I had to write my own procedure, which would grant privileges in bulk. Here it is:

CREATE OR REPLACE PROCEDURE GRANT_BULK_PRIV (inSchema IN varchar,
inObjType IN varchar,
inPriv IN varchar,
inUser IN varchar) IS
/******************************************************************************
NAME: GRANT_MULTI_PRIV
PURPOSE: to grant priviledges in bulk
NOTES:
PARAMETERS:
inObjType must specify the oracle defined type of the object (in upper case)
inSchema: Name of the schema where the objects reside (must be in upper case)
inPriv: what priviledge to give (oracle defined privilege name)
inUser: a user or a role to whom the privilege will be granted
Sysdate: 8.02.2007
******************************************************************************/
cursor allobjects (inOwner IN varchar, inType IN varchar) IS
Select o.object_name, o.status
from sys.all_objects o
where o.object_type = inType
and o.owner = inOwner
and o.status = 'VALID' ;
allobjects_rec allobjects%rowtype;
cmdtext varchar(255);
BEGIN
OPEN allobjects (inSchema, inObjType);
LOOP
FETCH allobjects INTO allobjects_rec;
EXIT WHEN allobjects%NOTFOUND;
cmdtext:= 'grant ' ||inPriv|| ' on ' ||inSchema|| '."' || allobjects_rec.object_name ||'" to '|| inUser;
--DBMS_OUTPUT.PUT_LINE(cmdtext);
execute immediate cmdtext;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END GRANT_BULK_PRIV;
/

usage like this:
execute grant_bulk_priv ('SHEMA', 'TABLE', 'SELECT', 'SOME_ROLE');