Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, March 18, 2013

ORA-01031 insufficient privileges

Developer was getting "ORA-01031: insufficient privileges" error while updating a table even though the user had proper privileges already. Complete scenario is given below 


Scenario:
1- Developer created a role "myrole"
create role myrole;
2- Assign some DML grants to this role "myrole"
GRANT DELETE, INSERT, SELECT, UPDATE ON T1 TO myrole -- role
3- Assign role "myrole" to a user "USER1"
grant myrole to USER1;
4- Connected to the user "USER1" and ran update statment
update T1 set c1 ='a' where user_id='ABC'
5- got the below error , why as the privileges already given to user through role.
ORA-01031: insufficient privileges


Cause:
The error occurred because the role was not active in the user session as it was not a default role.

you can check the active sessions by query below
SQL> select * from session_roles;

You can perform these steps to resolve the issue:

sqlplus / as sysdba
alter user
USER1 default role all;

connect user1
select * from session_roles;
update T1 set c1 ='a' where user_id='ABC'

Note:
You may create the scripts for all users if required.
select 'ALTER USER '||username||' DEFAULT ROLE ALL;'
from dba_users
where username like 'APP_%'

No comments: