After creating a role with grant update user1.table1, I gave grant role to user2, when executing the update I received Error ORA-01031

After creating role RL_WRITE and granting the grant update to table user1.table1 to role and granting role to user2 I get insufficient privileges error.

CREATE ROLE RL_WRITE;

GRANT UPDATE ON user1.table1 TO RL_WRITE;

GRANT RL_WRITE to user2;

logged in as user2, when running the command:

update user1.table1 set datmov = to_date('18/10/21','dd/mm/yy');

*01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges*

I don’t understand what might be missing.

Answer

I found out what the problem was. User user2 already had the role RL_READ_ONLY and I created the RL_WRITE and then grant it to user2.

But that user2 was set as ALTER USER user2 DEFAULT ROLE RL_READ_ONLY and so, even after having made the GRANT UPDATE ON user1.table1 TO RL_WRITE for the role, and granted this role to user2 the update command did not work.

SOLUTION

Only After doing the command ALTER USER user2 DEFAULT ROLE ALL; That user2 was able to make the updates, inserts and deletes commands that the role RL_WRITE had already had permission.