Home » RDBMS Server » Server Administration » while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient privil
while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient privil [message #271553] Tue, 02 October 2007 00:48 Go to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
hi guru,


when I ve issued $sqlplus "/as sysdba" , i got message ORA-01031: insufficient privileges??????

$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 2 08:36:35 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:

but when i use the following method i can connect

$sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 2 08:42:54 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>

I wanna connect directly like first method
but how??

here is my sqlnet.ora and id

$more sqlnet.ora
sqlnet.authentication_services=(NTS)

$id
uid=1025(oracle) gid=103(dba)

thx for your help...
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271558 is a reply to message #271553] Tue, 02 October 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove sqlnet.authentication_services line.
NTS is for Windows and does not exist on Unix.

Regards
Michel
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271563 is a reply to message #271553] Tue, 02 October 2007 01:05 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

If you want to keep,
then use,

sqlnet.authentication_services=ALL

Risky.
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271577 is a reply to message #271553] Tue, 02 October 2007 01:53 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
hi guru

i changed as you see
sqlnet.authentication_services=(ALL)

but unfortunatly it is still going on same error

Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271579 is a reply to message #271553] Tue, 02 October 2007 02:01 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Copy and paste of errors.

It is sqlnet.authentication_services=ALL

Not is sqlnet.authentication_services=(ALL)

And connect sys as sysdba

For, OS authentication

connect / as sysdba
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271584 is a reply to message #271577] Tue, 02 October 2007 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the line, it is easier and better.

Regards
Michel
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271588 is a reply to message #271553] Tue, 02 October 2007 02:12 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
$ more sqlnet.ora
sqlnet.authentication_services=ALL


$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 2 10:10:45 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:


but ı can connect

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 2 10:12:23 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn sys as sysdba
Enter password:xxxxx
Connected.
SQL>




Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271591 is a reply to message #271553] Tue, 02 October 2007 02:24 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
sqlplus "/as sysdba"

Why use this one instead of

sqlplus / as sysdba

Note this your OS authentication is enabled to be able to log on this way.
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271595 is a reply to message #271553] Tue, 02 October 2007 02:33 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
it doesn't matter whatever i use

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 2 10:30:42 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges

Enter user-name:
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271598 is a reply to message #271553] Tue, 02 October 2007 02:39 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I already answered you that to use , sqlplus / as sysdba
Note this your OS authentication is enabled to be able to log on this way.
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271603 is a reply to message #271595] Tue, 02 October 2007 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

WHAT DON'T YOU UNDERSTAND IN REMOVE THE LINE?

Regards
Michel
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271614 is a reply to message #271553] Tue, 02 October 2007 03:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I totally agree with you that we should use OS authentication , It is easier.

But in this thread,
Quote:
Remove the line, it is easier and better.


I think this step is not necessary/ simply ignored as he is performing local database administration.Moreover, he will not be able to log on as sys after removing the line.

As, OS authentication had the priority over password file authentication. So at first it will check OS authentication , if fail then look at password file.
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271616 is a reply to message #271553] Tue, 02 October 2007 03:49 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
I ve removed line and tried it again but it isi still same

regards
gokhan
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271617 is a reply to message #271553] Tue, 02 October 2007 03:55 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

If you remove the line then make the user as dba group. ie, Add oracle to dba group if you invoke sqlplus command by oracle user.
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271618 is a reply to message #271553] Tue, 02 October 2007 03:59 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
$ more group
root::0:
other::1:root
bin::2:root,daemon
sys::3:root,bin,adm
adm::4:root,daemon
uucp::5:root
mail::6:root
tty::7:root,adm
lp::8:root,adm
nuucp::9:root
staff::10:
daemon::12:root
sysadmin::14:
smmsp::25:
gdm::50:
webservd::80:
nobody::60001:
noaccess::60002:
nogroup::65534:
bpadmin::102:oper
oradba::501:oracle

i did but same
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271619 is a reply to message #271553] Tue, 02 October 2007 04:08 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
oradba::501:oracle

Which is oradba.

I said,

Add oracle to dba group.
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271624 is a reply to message #271614] Tue, 02 October 2007 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Moreover, he will not be able to log on as sys after removing the line.

This is wrong:
X> cat sqlnet.ora
#sqlnet.authentication_services=NTS
X> sqlplus /nolog

SQL*Plus: Release XXXXX - Production on Tue Oct 2 11:20:38 2007

(c) Copyright XXXX Oracle Corporation.  All rights reserved.

?> connect sys as sysdba
Enter password:
Connected.
?> show user
USER is "SYS"


@dba4bank
What is your OSDBA group (the one you gave at installation time), is it oradba or dba?
How could you have:
$id
uid=1025(oracle) gid=103(dba)

and
oradba::501:oracle

If you lie we can't help you.

Regards
Michel
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271633 is a reply to message #271553] Tue, 02 October 2007 04:44 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
I have two different server I am sorry I mixed it all when i issued $id
why sould I say lie????
I ve never said lie..

here is right group


$ id
uid=1001(oracle) gid=501(oradba)


$more group
.
.
.
oradba::501:oracle

Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271647 is a reply to message #271633] Tue, 02 October 2007 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't say lie, you lied, didn't say the truth.
You don't answer my question:
Quote:
What is your OSDBA group (the one you gave at installation time), is it oradba or dba?

Regards
Michel
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271651 is a reply to message #271553] Tue, 02 October 2007 06:13 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member

OSDBA group is oradba.


Regards
gokhan
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271655 is a reply to message #271651] Tue, 02 October 2007 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this should work:
X> cat sqlnet.ora
#sqlnet.authentication_services=NTS
X> sqlplus "/ as sysdba"

SQL*Plus: Release X.X.X.X.X - Production on Tue Oct 2 13:30:09 2007

(c) Copyright 20XX Oracle Corporation.  All rights reserved.


Connected to:
OracleXXX Enterprise Edition Release X.X.X.X.X - Production
JServer Release X.X.X.X.X - Production

SQL> show user
USER is "SYS"
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> show user
USER is "SYS"
SQL> exit

You surely miss something.
Or think something is otherwise than it really is.

Regards
Michel
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271777 is a reply to message #271553] Tue, 02 October 2007 21:44 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I think that worked because it used OS Authentication.

# id
uid=0(root) gid=0(root)
# groups
root other bin sys adm uucp mail tty lp nuucp daemon
# /oracle/app/oracle/product/10.2.0/db_1/bin/sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Oct 2 22:39:49 2007

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

ERROR:
ORA-01031: insufficient privileges
-bash-3.00$ id
uid=100(oracle) gid=100(oinstall)
-bash-3.00$ groups
oinstall dba
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Oct 2 22:40:35 2007

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
-bash-3.00$ cat /oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271833 is a reply to message #271553] Wed, 03 October 2007 01:52 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
hi guys,

i really appriciated for your answer and interested
i figured it out with your answers

problem was

You must create the OSDBA group. By default, the Oracle Universal Installer searches for a group called dba. If you choose to assign the OSDBA group privilege to a group with a name other than dba, then you are prompted for the group name during installation.

when oracle user group is ORADBA

$ id
uid=1001(oracle) gid=501(oradba)
$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 3 08:51:02 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges


when i change name of group DBA like this..

$ groups
dba
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 3 09:19:19 2007

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


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


REGARDS
GOKHAN DEDELER
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271835 is a reply to message #271553] Wed, 03 October 2007 02:04 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes, Thanks for feedback.
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #271845 is a reply to message #271833] Wed, 03 October 2007 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the OSDBA group you gave when you installed was dba and not oradba as you said.

Regards
Michel
Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #272593 is a reply to message #271617] Fri, 05 October 2007 12:22 Go to previous messageGo to next message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
Guys

Using conn / as sysdba is a very bad practice, do not encourage the use of it.

If you're the DBA, you should have the password for SYS... if not, then your personal DB user should have enough rights to change SYS' password. If you don't have a personal user... then you've another bad practice ... and don't have a personal door
to your DB.
Wink

Maybe you should regenerate your oracle password file (orapw{SID})... my two cents

Regards

[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]

[Updated on: Thu, 11 October 2007 12:40] by Moderator

Report message to a moderator

Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #272599 is a reply to message #272593] Fri, 05 October 2007 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Using conn / as sysdba is a very bad practice, do not encourage the use of it.

This is wrong this a good practice to automate maintenance job without storing password somewhere.

SYS password is only useful for remote maintenance actions which I don't advice unless you connect through SSL. Better connect locally (also through SSL) to execute maintenance actions.

Beside these actions, then you should have your personal account. (In our site, SYSTEM is disabled.)

Regards
Michel


Re: while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient pr [message #274038 is a reply to message #272599] Fri, 12 October 2007 18:41 Go to previous message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
Two options for user connection within job automation

1) DB jobs
2) operating system authenticated users

... unless the task involves bouncing, I guess both scenarios cover the requirement.

Regards

[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]

[Updated on: Sat, 13 October 2007 02:16] by Moderator

Report message to a moderator

Previous Topic: Windows Oracle services
Next Topic: I did not really understand this problem!
Goto Forum:
  


Current Time: Fri Dec 09 09:39:40 CST 2016

Total time taken to generate the page: 0.12771 seconds