Inssuficient previleges ERROR

[Pinki Meggi]

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

[Steven E. Protter]

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

[Pinki Meggi]

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 · Accepted solution]

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]

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