The challenge that we have is to grant users update, insert and delete permissions on a table in another user's (USRALL) schema.
Here is what I've done:
1. Log in as the user USERALL and create an Oracle role through which the permissions will be given:
create role ROL_EH_COREP identified using pack_authentication;
grant select, insert, update, delete on EH_BII_REP_EXPECTED_DIM to ROL_EH_COREP;
2. Assign this role to users:
grant ROL_EH_COREP to johannes, soraya;
3. Log in to the database as user JOHANNES and do a select from the table in the schema USRALL:
alter session set current_schema=USRALL;
4. Now select data from the table just to show that there are data and that is selectable:
select partition_key, underlying_table, unique_row_id from eh_bii_rep_expected_dims;
(lots of rows are displayed, but removed from this question)
5. Now delete data from it:
0 rows deleted.
There is no error message and it states that nothing is deleted.
What do I need to do to enable another schema's user to delete data in another schema?