GRANT OPTION

if a user has the GRANT OPTION he can pass this privilege on to other users through a view, without granting the privilege on the original source view:
SQL> grant select on dba_users to dbatest with grant option;
SQL> conn dbatest
SQL> create view da_users as select * from sys.dba_users;
SQL> grant select on da_users to dbatest2;
SQL> conn dbatest2
SQL> select count(*) from dbatest.da_users;

COUNT(*)
----------
40

SQL> select count(*) from sys.dba_users;
select count(*) from sys.dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist


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');