Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » PL/SQL to grant privileges to roles (Oracle 10gR2,Red Hat Ent Linux)
PL/SQL to grant privileges to roles [message #495787] Tue, 22 February 2011 04:39 Go to next message
preet_kumar
Messages: 204
Registered: March 2007
Senior Member
I am trying to assign different roles which could be assigned to users.
The below given error message appears when i run the statement,could anyone help me where i go wrong

BEGIN
FOR Rec IN (SELECT object_name, object_type FROM all_objects WHERE owner='SOURCE' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
IF Rec.object_type IN ('TABLE','VIEW') THEN
EXECUTE IMMEDIATE 'GRANT SELECT ON SOURCE.'||Rec.object_name||' to SELECT_ONLY_ROLE;
EXECUTE IMMEDIATE 'GRANT SELECT,UPDATE,INSERT ON SOURCE.'||Rec.object_name||' to SELECT_INSERT_UPDATE_DELETE_ROLE;
ELSIF Rec.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SOURCE.'||Rec.object_name||' TO EXECUTE_ROLE;
END IF;
END LOOP;
END;
/

ERROR at line 5:
ORA-06550: line 5, column 20:
PLS-00103: Encountered the symbol "GRANT" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like LIKE2_ LIKE4_ LIKEC_ between into using || bulk member
SUBMULTISET

[Updated on: Tue, 22 February 2011 04:44]

Report message to a moderator

Re: PL/SQL to grant privileges to roles [message #495788 is a reply to message #495787] Tue, 22 February 2011 04:43 Go to previous message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Code is invalid. This line, for example,
EXECUTE IMMEDIATE 'GRANT SELECT ON SOURCE.'||Rec.object_name||' to SELECT_ONLY_ROLE;
lacks the closing single quote.

I'd say that it is useless to ask for help without even TRYING to fix some obvious mistakes you made.

Besides, rule of thumb with dynamic queries: put the statement (you are about to run) into a variable; display it so that you'd see how it looks like. If you can run it successfully in SQL*Plus, it'll (most probably) work fine from PL/SQL as well.
Previous Topic: CSS Header doctype
Next Topic: What is Oracle Application Express (Apex)
Goto Forum:
  


Current Time: Fri Aug 07 13:35:27 CDT 2020