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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
That user needs "execute" granted on the procedure but doesn't need any privileges on the tables used by that procedure
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
ASKER
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.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1758
or more specifically
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1796
key phrase: "Within a definer's rights subprogram, all roles are disabled."
or more specifically
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1796
key phrase: "Within a definer's rights subprogram, all roles are disabled."
ASKER
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.CUR
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
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.CUR
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
please open a new question
ASKER