OraFAQ Forum: Server Administration » while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient privil

Home » RDBMS Server » Server Administration » while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient privil

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

while connect sqlplus "/as sysdba" like this, i got message ORA-01031: insufficient privil [message #271553]

Tue, 02 October 2007 00:48

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...

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Remove sqlnet.authentication_services line.
NTS is for Windows and does not exist on Unix.

Regards
Michel

Report message to a moderator

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

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

If you want to keep,
then use,

sqlnet.authentication_services=ALL

Risky.

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Remove the line, it is easier and better.

Regards
Michel

Report message to a moderator

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

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>

Report message to a moderator

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

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.

Report message to a moderator

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

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:

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

WHAT DON'T YOU UNDERSTAND IN REMOVE THE LINE?

Regards
Michel

Report message to a moderator

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

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.

Report message to a moderator

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

dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY

Member

I ve removed line and tried it again but it isi still same

regards
gokhan

Report message to a moderator

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY

Member

OSDBA group is oradba.

Regards
gokhan

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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)

Report message to a moderator

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

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

Report message to a moderator

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

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Yes, Thanks for feedback.

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So the OSDBA group you gave when you installed was dba and not oradba as you said.

Regards
Michel

Report message to a moderator

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

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

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 07:40:59 CST 2024