DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: sql help with grant script

  1. #1
    Join Date
    Dec 2002
    Posts
    28

    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;
    /

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width