-
sql help with grant script
Calling out for some SQL help..
I am trying to modify this free script I have obtained but only require select access for the Sequence object but require insert,update,delete,select etc for all other object types such as tables, views , procedures etc. How can I amend this script so that it caters for both types of grants. At presnt i have had to comment out the sequence object but do require the select permission only for sequences. Please advise. I have tried declaring another for loop but not had much luck.
DECLARE
l_sql varchar2(254);
t_sql varchar2(254);
cursor_id integer;
result integer;
admrole varchar2(80) := 'FLEETADMROLE';
userrole varchar2(80) := 'FLEETUSERROLE';
cursor get_obj is
select object_name,owner from all_objects
where owner = 'FLEET_DBA'
-- and object_type IN ('PACKAGE','FUNCTION','PROCEDURE','TABLE','VIEW','SEQUENCE','CLUSTER');
and object_type IN ('PACKAGE','FUNCTION','PROCEDURE','TABLE','VIEW','CLUSTER');
-- and object_name not in (select synonym_name
-- from all_synonyms
-- where owner='PUBLIC');
BEGIN
cursor_id:=dbms_sql.open_cursor;
FOR obj_rec in get_obj LOOP
l_sql := 'GRANT UPDATE,SELECT,INSERT,DELETE on '||obj_rec.object_name||' to '||admrole;
t_sql := 'GRANT UPDATE,SELECT,INSERT,DELETE on '||obj_rec.object_name||' to '||userrole;
dbms_sql.parse(cursor_id,l_sql,1);
dbms_sql.parse(cursor_id,t_sql,1);
dbms_output.put_line(l_sql);
dbms_output.put_line(t_sql);
result := dbms_sql.execute(cursor_id);
END LOOP;
dbms_sql.close_cursor(cursor_id);
END;
/
-
Hm, I wonder how your database reacts when you try to grant SELECT privilege on PACKAGE,FUNCTION or PROCEDURE. Mine only allows me to grant EXECUTE on those type of objects.
Anyhow, a simple usage of an IF-ELSIF-ENDIF will solve your problem:
Code:
DECLARE
....
cursor get_obj is
select object_name,owner, object_type
from all_objects
where .....;
....
BEGIN
....
IF obj_rec.object_type IN ('PACKAGE','FUNCTION','PROCEDURE') THEN
l_sql := 'GRANT EXECUTE';
ELSIF obj_rec.object_type = 'SEQUENCE' THEN
l_sql := 'GRANT SELECT';
ELSE
l_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE';
END IF;
t_sql := l_sql || ' on '||obj_rec.object_name||' to '||userrole;
l_sql := l_sql || ' on '||obj_rec.object_name||' to '||admrole;
....
END;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|