Search code examples
oracle-databaserolesprivileges

Oracle Roles and privileges hierarchy

Good morning,

I'm trying to understand one thing in Oracle database Roles and privileges:

So, i am trying to create two Roles: Programmer and Manager.

The idea for Programmer role users, is to create and insert into tables. The idea for Manager role users, is to have acess to Programmer role privileges, PLUS update records.

And i thought that if i granted the Programmer role to Manager role, this last one could:

  1. Create a table (from programmer role);
  2. Insert into a table (from programmer role);
  3. Update a record in the table (privilege set to the Manager role);

But through SQL developer, i grant Programmer role to Manager role, and when i connect to the database using a Manager User, i can't find tables created on SYSTEM.A (for example).

Do i need to grant explicity on Manager role also can create and insert? If so, what's the point of the inheritance?

SOLUTION

Programmer role: Insert && create table privileges; Manager role: Update && Select any table privileges;

Since my goal was to put Manager users inherit Programmer role privileges, this can be achieved like this:

(After setting the roles and privileges):

  • DBA > ROLES > EDIT MANAGER ROLE > GRANTED ROLES > SELECT PROGRAMMER ROLE.
  • Disconnect any manager session, and re-connect.

Open a Manager SQL sheet and try to create a table and select, insert and update it. You'll see that Manager has adopter privileges from "programmer" role.

Solution

  • SQL is a non-procedural language. Having that said,you don't need to think about inheritance here. Instead, Grant create,insert and update privileges on manager role explicitly.

    Here's what you can do:

    1- Create programmer and manager role:

    SQL> CREATE ROLE role_name IDENTIFIED by pass_word;

    2- Then GRANT privileges (your requirement here) to each role:

    SQL> GRANT privilege TO role_name;

    3- Grant users(programmers & managers) privileges by granting each user(depends whether he is a manager or a programmer) to a particular role.

    SQL> GRANT role_name TO user_name;

    You may find the following link useful for more details: http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#21065