Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Inssuficient previleges ERROR

SOLVED
Go to solution
Pinki Meggi
Frequent Advisor

Inssuficient previleges ERROR

Hi all,

Im having the folowwing problem with my database:
- As oracle user when i try to connect with: conn sys as sysdba
password: xxx
connected.


It seems to be ok. But when trying to connect with: conn / as sysdba
password: xxx
ERROR:
ORA-01031: insufficient privileges

I have trying to solve this but cant do, so need help. The user oracle have all the permissions so i dont understand why it says insufficint privileges.

I need to connect like this because the dbshut scipt try to connect as / and it cant shutdown the database automatically, and i need to do that.

Any idea, please?

Best Regards

Pinki
4 REPLIES
Steven E. Protter
Exalted Contributor

Re: Inssuficient previleges ERROR

Shalom Pinki,

I ran into this a year ago. The oracle password file a security file that permits database access is missing or has ownership other than the database binary owner on it.

http://www.adp-gmbh.ch/ora/admin/password_file.html
http://www.orafaq.com/faqdbase.htm

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Pinki Meggi
Frequent Advisor

Re: Inssuficient previleges ERROR

Hello, thanks for reply.

If i create the password file, then when i connect with: conn / as sysdba it will prompt me for the password.

But i need a login without a password prompt. conn / as sysdba is supposed to login without a password. Thats why the dbshut script uses conn / as sysdba to login and shutdown.

So im little confused how to login without password.


Best Regards.

And thanks once again.

Pinki
Ariel Cary
Frequent Advisor
Solution

Re: Inssuficient previleges ERROR

Pinki,

Among several ways you can connect to the Oracle database, what you want to achieve is OS authentication, so no password file is needed for this.
When you issue 'CONNECT / AS SYSDBA' Oracle checks if your Unix account belongs to the OSDBA group, and if so then you are allowed to connect to the database.
OSDBA and OSOPER are defined at installation time. You can check what these groups are in $ORACLE_HOME/rdbms/lib/config.c
Let's assume the group is 'dba'. So in order for a OS user 'sam' (so to speak) to be able to 'conn / as sysdba', he should belong to the dba group. See example below. Also, notice that in doing so, sam will get connected as SYS user.

[root@archost ~]# id sam
uid=502(sam) gid=507(sam) groups=507(sam)
[root@archost ~]# su - sam
[sam@archost ~]$ export ORACLE_SID=ARC
[sam@archost ~]$ export ORACLE_BASE=/u01/dba/app/oracle
[sam@archost ~]$ export ORACLE_HOME=/u01/dba/app/oracle/product/10.2.0
[sam@archost ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[sam@archost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Sep 27 11:32:33 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:
[sam@archost ~]$ logout

[root@archost ~]# usermod -G dba sam
[root@archost ~]# id sam
uid=502(sam) gid=507(sam) groups=507(sam),505(dba)
[root@archost ~]# su - sam
[sam@archost ~]$ export ORACLE_SID=ARC
[sam@archost ~]$ export ORACLE_BASE=/u01/dba/app/oracle
[sam@archost ~]$ export ORACLE_HOME=/u01/dba/app/oracle/product/10.2.0
[sam@archost ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[sam@archost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Sep 27 11:30:33 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> sho user
USER is "SYS"
SQL> select logins, status, instance_name
2 from v$instance;

LOGINS STATUS INSTANCE_NAME
---------- ------------ ----------------
ALLOWED OPEN ARC

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[sam@archost ~]$ logout

Hope this clarifies,

-Ariel
Pinki Meggi
Frequent Advisor

Re: Inssuficient previleges ERROR

Thank u so much for your explanation. Thats clarifies a lot and now i understand well what was going on.
Really the user i was trying to connect ( oracle ) was belong only to the oinstall and osoper group and not dba group. It was my mistake, i just dont know how, but during the installation the user created have to belong to the dba group and i have done mistake and it was not.
so i have done:
usermod -G dba oracle, and then trying to connect "conn / as sysdba", it get connected.

Thanks a lot.


Best Regards

Pinki