[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to grant select, insert on all tables to role

Posted on 2009-02-18
4
Medium Priority
?
1,500 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:gudii9
  • 2
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
flow01 earned 2000 total points
ID: 23676171
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
 
LVL 7

Author Comment

by:gudii9
ID: 23687362
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
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 2000 total points
ID: 23693533
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
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31548488
thank you very much for your help. I appreciate it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

872 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