DBMentors is a solution oriented group, started by a team of qualified and committed professionals with vast experience in IT industry. The team has in-depth technical and design expertise with highest standards of programming quality.

Pages

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 -- role3- Assign role "myrole" to a user "USER1"grant myrole to USER1;4- Connected to the user "USER1" and ran update statmentupdate 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 belowSQL> select * from session_roles;