1

I am using TOAD for Oracle and connecting to an 11g database. I have a user who has the create role system privilage designated by a role association, however when I run the statement: 

create role myNewRole;

I get the error:

ora-01031 insufficient privileges

I don't understand what the issue is here because I've done this before a bunch of times and never had problems. I couldn't find any similar issues relating to the create role statement via google or stack.

Anyone have any ideas of what is going on here?

user1567453
  • 1,837
  • 2
  • 19
  • 22

2 Answers2

1

Problem was solved by looking at the role association against the user. It turns out that it was not set as a default role so the solution was one of two:

  • Set the role as a default one with alter user <username> default role <role_list>;
  • Set the role as active in the session using SET ROLE <role>;

Of course if your role has security then you will have to use identified by <password> after the <role>.

Doco for SET ROLE and Doco for Default Role

My understanding of the problem is that Oracle can have cases where you want a role associated with a user however you don't want it active by default so you must explicitly enable it during a session to use it which is extra protection for your database. When a role is set as a default role then it is active every time you log on since the privilages from the role are expected to be used more often and/or not have a large impact(negative) on the database.

Thanks for your comment @ChrisFarmer because it led me in the direction of the answer I was looking for once I realised the sys privilage wasn't in my session.

user1567453
  • 1,837
  • 2
  • 19
  • 22
0

It explains why a user who I granted DBA role received ORA-01031 when try to change password for another user. On the session he needs to SET ROLE ;

user1617237
  • 143
  • 1
  • 3
  • 11