Monday, 8 December 2014

ORA-01031: insufficient privileges While Connect as New SYSDBA USER in RAC Database

Recently Temporarily purpose I have Created sysdba user  on RAC database. I have logged into the one of the instance and Executed below commands for New sysdba user creation. 


CREATE USER "OBIEE_TEMP" PROFILE "XXXXXXX" IDENTIFIED BY "XXXXXXXXXX" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;


GRANT ALTER SESSION TO "OBIEE_TEMP";

GRANT CREATE SESSION TO "OBIEE_TEMP";
GRANT SYSDBA TO "OBIEE_TEMP" WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO "OBIEE_TEMP";


I have set Credentials to app team to connect db and proceed their steps. While they are trying connect through database they got Below errors. 




ORA-01031: insufficient privileges


While I am trying to connect through toad as well I got the Same issue but Don't know why we are getting this error even user having sysdba privileges. 


I have checked what are the  privileges OBIEE_TEMP user having by querying v$pwusers_users.






I am seeing that OBIEE_TEMP user got SYSDBA privileges.    Don't know what to do . After trouble shoot a lot time Above query executed one of the instance where i have given or created that users. Let me query gv$pwfile_users check.




Now I got the issue Above OBIEE_TEMP user doesn't have SYSDBA privileges on 2nd instance due i have execute sysdba grant from 1st instance so its applicable for that instance. Let give sysdba grant from 2nd node and query again. 





 Now we can see sysdba grant for OBIEE_TEMP in both instance one and 2nd.   I.e whenever we are creating or giving sysdba grant for users in RAC database we need to execute the privileges on all the instances. 


Thank you
Sunil 


No comments:

Post a Comment