1 Reply Latest reply: Jan 11, 2013 10:11 AM by Srini Chavali-Oracle RSS

    Granting privileges through role error

    aziz
      Oracle 10.2.05
      Linux environment

      I just granted a role to a user, but the user does not have privileges base on the role.

      Here is what I did:

      First create a user (db_user) using system id
      Second, create role schema_admin_role
      Then run the script to grant privileges to the role
      (SELECT 'grant select, insert, update, delete on ' ||owner|| '.'||table_name || ' to schema_admin_role;' from dba_tables WHERE OWNER = 'another_schema';

      Then run
      grant schema_admin_role to db_user;

      The problem:
      When db_user tries to update table X own by another_schema, he gets not sufficent privileges

      But when I run (select owner, table_name,privilege from dba_tab_privs where grantee = 'SCHEMA_ADMIN_ROLE'; ), I see all the privileges owned by this role.

      Any solution from your end will be appreciated.