This discussion is archived
1 Reply Latest reply: Jan 11, 2013 8:11 AM by Srini Chavali-Oracle RSS

Granting privileges through role error

aziz Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points