grants to roles don't appear to work

I create a user then a role and grant the role to that user then I add more grants on the role. Shouldn't the user granted that role have access to the privileges granted that role.

specifically I create a user test and create a role test_role then I grant select, insert on test_table to the role. When I log in as test shouldn't I be able to select and insert records into test_table which is a table owned by another user? I ask because I keep getting errors that either the table doesn't exist when i try to insert or i don't have sufficient privileges
sikyalaSenior Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

is the role active for that user?

look in dba_role_privs to see if DEFAULT_ROLE = 'YES'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
How is your test user accessing that table?  Remember that you have to refer to it as owner_name.table_name.
sikyalaSenior Database AdministratorAuthor Commented:
a procedure is executed and in the code it is checking a sequence number in the table then inserting a record from another table into the test table. the procedure isn't explicitly checking the table using owner_name.table_name but it does have a parameter that is the schema name of the owner of the table
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

priveleges granted through roles don't apply inside procedures, functions and packages.

the privileges must be granted directly to the procedure owner
sdstuber is right.
you "might" be able to declare your procedure   AUTHID CURRENT_USER

but that's generally not the solution to this type of problem and if it's not, trying to force it will simply make things worse
sikyalaSenior Database AdministratorAuthor Commented:
I granted the privileges directly to the test user and I still can't get the procedure to work. I got it to work yesterday. Although I don't know what did it other than as you said granting it directly to the user. which was the last command I did before executing the procedure
the privileges need to be granted to the procedure owner,  not the user executing the procedure.

That user needs "execute" granted on the procedure but doesn't need any privileges on the tables used by that procedure
Mark GeerlingsDatabase AdministratorCommented:
Your basic problem is likely what sdstuber mentioned:
"privileges granted through roles don't apply inside PL\SQL procedures, functions and packages".

Another possibility is a missing synonym, if you do not use [schema].[table_name] syntax.

If your procedure is owned by the table_owner, you can avoid both of these problems.

You mentioned that your procedure has "a parameter that is the schema name of the owner of the table".  But that will only be useful if you use dynamic SQL in your procedure.  Be aware that PL\SQL is *NOT* optimized for dynamic SQL.  Yes, dynamic SQL statements can be used in PL\SQL procedures if you use "execute immediate" or the procedures and functions in: DBMS_SQL, but note  that there are performance and security penalties and/or risks with dynamic SQL.
sikyalaSenior Database AdministratorAuthor Commented:
sikyalaSenior Database AdministratorAuthor Commented:
sdstuber and johanntagle does Oracle have any documentation on the limitation of granting roles privileges that don't take affect in procedures, functions, and packages? I have to provide justification to the client in writing as to why they have to grant the privileges directly to the user.
sikyalaSenior Database AdministratorAuthor Commented:
markgeer: Sorry to bring this up after I closed the question. But I just received another email about not granting privileges to the user. So I am trying to explore other options.
you mentioned some things that I didn't address.

here is the procedure:

(targetSchema IN VARCHAR2, sourceSchema IN VARCHAR2, stiRecordId IN NUMBER)
   l_insert_stmt             VARCHAR2(4000);
   l_update_stmt               VARCHAR2(4000);
  l_insert_stmt := 'INSERT INTO ';
  l_insert_stmt := l_insert_stmt || targetSchema;
  l_insert_stmt := l_insert_stmt || '.BIB_HOLDINGS
(<list of fields>
l_insert_stmt := l_insert_stmt || sourceSchema;
l_insert_stmt := l_insert_stmt || '.WS_STI_RECORD WHERE id = ' || stiRecordId ;
execute immediate l_insert_stmt;
l_update_stmt := 'UPDATE ' || sourceSchema || '.WS_STIRECORD SET BIB_HOLDING = ';
l_update_stmt := l_update_stmt || ' (SELECT ' || targetSchema || '.RWU_BIB_HOLDINGS_SEQ.CURRVAL FROM DUAL)';
execute immediate l_update_stmt;  

I believe this procedure is creating dynamic sql statements. Correct me if you disagree.

>> Another possibility is a missing synonym, if you do not use [schema].[table_name] syntax.
I do have synonyms created but it doesn't seem to have made a difference.

>> If your procedure is owned by the table_owner, you can avoid both of these problems.
the the procedure is not owned by the table owner
please open a new question
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.