how to grant select, insert on all tables to role

Posted on 2009-02-18
Last Modified: 2013-12-18
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.
Question by:gudii9
    LVL 20

    Accepted Solution


    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
    LVL 7

    Author Comment

    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
    LVL 20

    Assisted Solution

    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.  
    LVL 7

    Author Closing Comment

    thank you very much for your help. I appreciate it.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now