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?
 
sdstuberConnect With a Mentor Commented:
is the role active for that user?

look in dba_role_privs to see if DEFAULT_ROLE = 'YES'
0
 
johanntagleConnect With a Mentor Commented:
How is your test user accessing that table?  Remember that you have to refer to it as owner_name.table_name.
0
 
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
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
sdstuberConnect With a Mentor Commented:
priveleges granted through roles don't apply inside procedures, functions and packages.

the privileges must be granted directly to the procedure owner
0
 
johanntagleCommented:
sdstuber is right.
0
 
sdstuberCommented:
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
0
 
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
0
 
sdstuberCommented:
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
0
 
Mark GeerlingsConnect With a Mentor Database 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.
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Thanks
0
 
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.
0
 
sdstuberCommented:
0
 
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:

CREATE OR REPLACE PROCEDURE <procedure name>
(targetSchema IN VARCHAR2, sourceSchema IN VARCHAR2, stiRecordId IN NUMBER)
IS
   l_insert_stmt             VARCHAR2(4000);
   l_update_stmt               VARCHAR2(4000);
BEGIN
  l_insert_stmt := 'INSERT INTO ';
  l_insert_stmt := l_insert_stmt || targetSchema;
  l_insert_stmt := l_insert_stmt || '.BIB_HOLDINGS
(<list of fields>
FROM ';
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;  
END;

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
 
0
 
sdstuberCommented:
please open a new question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.