how to grant select, insert on all tables to role

Hi,
I have few roles  like roleA, roleB, roleC, roleD etc created.  Few Schemas created like schemaL, schemaM, SchemaN, SchemaP
On first role say roleA
I need to grant all privileges  on all objects within the schemaM including all tables (like select, insert, update, delete), stored procedures, triggers.
On second role say roleB
I need to grant privilege  on few stored procedures(say 12  of them) which updates a particular table in  schemaM
On third roleC
I need to grant all privilege  on all objects within the schemaM including all tables (like select, insert, update, delete), stored procedures, triggers similar to roleA. Later on  based on need i will remove some privileges
On fourth roleD.
I need to give only select privilege on tables and execute access on particular stored procedures (say 10 of them) within the SchemaM.
How can i achieve this. Any syntax, ideas, resources highly appreciated. How can i achieve it. Thanks in advance.
LVL 7
gudii9Asked:
Who is Participating?
 
flow01Connect With a Mentor Commented:
sqlplus

prompt create a list with grants
spool execute_grants
select 'grant '  || case object_type
                          when 'package'    then    'execute'
                          when 'procedure' then 'execute'
                          when 'function'     then 'execute'
                          else 'all'
                          end  ||  ' to roleA;'  
            from dba_objects
            where owner =   'schemaM'
           and object_type in ( 'package', 'procedure','function', 'view',  ...etc)
/

others selects alike

spool off

prompt execute the generated command list
@@execute_grants.lst
0
 
gudii9Author Commented:
how to give for triggers. It worked for tables and procedures but not for triggers.
What kind of privilege needs to be given on trigger usually just execute?.How to give execute privilege on trigger. Any syntax, links, resource highly appreciated. thanks ina advance
0
 
flow01Connect With a Mentor Commented:
I don't know of triggers that need priviliges, triggers are (in general) assigned to a table, When you are granted to manipulate the table  the triggers will fire.  
0
 
gudii9Author Commented:
thank you very much for your help. I appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.