Home » RDBMS Server » Server Administration » insufficient privileges
insufficient privileges [message #251533] Sat, 14 July 2007 05:34 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
i am using oracle 10g on redhat AS 4

[oracle@linora /] sqlplus /nolog

SQL>conn system/gateway@linorcl Connected.

But whenever i tried to connect as sysdba like:
SQL>conn sys/gateway@linorcl as sysdbaERROR:
ORA-01031: insufficient priviliges


Warning :you are no longer connected to ORACLE

should i have to recreate password file or there is some other issues?
thanx in advance
Re: insufficient privileges [message #251536 is a reply to message #251533] Sat, 14 July 2007 07:14 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Shakeel...Check the remote_login_passwordfile is set to EXCLUSIVE in the pfile.

Re: insufficient privileges [message #251619 is a reply to message #251536] Sun, 15 July 2007 09:46 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> show parameter login

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> conn system as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant sysdba to system;

Grant succeeded.

SQL> conn system as sysdba
Enter password:
Connected.

Note: Grant SYSDBA privs to SYSTEM user.


If SQLNET.ORA file sqlnet.authentication_services=(NTS) then without given "SYSDBA" privs your able to connect to database as sysdba user"

C:\>type c:\oracle\product\10.1.0\db_1\network\admin\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network\a
dmin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
SQLNET.EXPIRE_TIME=2
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Jul 15 18:44:05 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

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

SQL> create user test identified by test;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

SQL> conn test as sysdba
Enter password:
Connected.
SQL>
Re: insufficient privileges [message #251868 is a reply to message #251533] Tue, 17 July 2007 00:56 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

arju@arju:~$ sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jul 17 11:52:47 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: system as sysdba
Enter password:              (didnot gave)
ERROR:
ORA-01031: insufficient privileges


Enter user-name: prod7
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production

SQL> show parameter login;
ORA-00942: table or view does not exist



How I can enter system account without giving password..
Re: insufficient privileges [message #251884 is a reply to message #251868] Tue, 17 July 2007 01:28 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

change SQLNET.AUTHENTICATION_SERVICES=NTS
and try

regards
Taj
Re: insufficient privileges [message #251885 is a reply to message #251533] Tue, 17 July 2007 01:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I have database and in sqlnet file nothing is there. Yet I can enter system account without giving password....


-bash-3.00$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 17 12:32:35 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: system as sysdba
Enter password:

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

SQL> !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: insufficient privileges [message #251989 is a reply to message #251885] Tue, 17 July 2007 11:02 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
DreamzZ:/export/home/oracle: cat $ORACLE_HOME/network/admin/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES=NTS

omzimp14ESPSVCS:/export/home/oracle: sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 17 16:01:11 2007

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

Enter password:
ERROR:
ORA-01031: insufficient privileges

Re: insufficient privileges [message #252125 is a reply to message #251533] Tue, 17 July 2007 22:06 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

So where is the problem? I think it is not problem about entry in password file or entry in SQLNET.AUTHENTICATION_SERVICES. The problem is somewhere else.
Re: insufficient privileges [message #252183 is a reply to message #252125] Wed, 18 July 2007 01:44 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
There is no problem.

For connect to database without password as SYSDBA privs.

1. OS user add OS DBA group
2. sqlnet.authentication_services=NTS
3. except SYS user must grant SYSDBA privs to user for connect sysdba privs.
4. we are able to connect without password only on Oracle Server not through CLIENT.
through client we must give Password for connection.


Default value is sqlnet.authentication_services=NTS.
In Documentation showing default value is NONE but when we create database through dbca then default value is NTS instead of NONE.


Regards
Taj
Re: insufficient privileges [message #252186 is a reply to message #252183] Wed, 18 July 2007 01:50 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Mohammad Taj wrote on Wed, 18 July 2007 01:44
Hi,
There is no problem.

For connect to database without password as SYSDBA privs.

1. OS user add OS DBA group
2. sqlnet.authentication_services=NTS
3. except SYS user must grant SYSDBA privs to user for connect sysdba privs.
4. we are able to connect without password only on Oracle Server not through CLIENT.
through client we must give Password for connection.


Default value is sqlnet.authentication_services=NTS.
In Documentation showing default value is NONE but when we create database through dbca then default value is NTS instead of NONE.


Regards
Taj

Re: insufficient privileges [message #252187 is a reply to message #251533] Wed, 18 July 2007 01:54 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What about this. I gave only dba privilege and it works..

SQL> create user test identified by test;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> connect test as sysdba
Enter password:
Connected.
Re: insufficient privileges [message #252192 is a reply to message #252187] Wed, 18 July 2007 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You could only give it CREATE SESSION and it would also work.
Its role is granted accordingly to the OS group.

Regards
Michel
Re: insufficient privileges [message #252197 is a reply to message #251533] Wed, 18 July 2007 02:12 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Thanks michel for clarifying this. That's why I told before the problem is not about SQLNET.AUTHENTICATION_SERVICES .

Then if my OS user belong to dba group then whenever I create any user though I dont give any privilege the user automatically get dba privilege. And Michel I saw create session also dont necessary to be connected.

SQL> create user test3 identified by t;

User created.

SQL> connect test3 as sysdba
Enter password:
Connected.
SQL> create table test (a number);

Table created.
Re: insufficient privileges [message #252201 is a reply to message #252183] Wed, 18 July 2007 02:27 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear taj
as u suggest,after making sure
1-sqlnet.authentication_services=NTS
2-REMOTE_LOGIN_PASSWORDFILE=exclusive
and re-creating the password file i have resolved the problem
now i am able to connect
sys/pass@linorcl as sysdba

thanx to u
can u please tell me how to automate dbstart and dbshut
in 10g
can u please provide the steps?
i have used the following steps

1-edit the "/etc/oratab" with the following
TSH1:/u01/app/oracle/product/10.2.0:Y

2-create a file called "/etc/init.d/dbora" as the root user

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
#
# Change the value of ORACLE to the login name of the
# oracle owner at your site.
#
ORACLE=oracle

PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
#
if [ ! "$2" = "ORA_DB" ] ; then
if [ "$PLATFORM" = "HP-UX" ] ; then
remsh $HOST -l $ORACLE -n "$0 $1 ORA_DB"
exit
else
rsh $HOST -l $ORACLE $0 $1 ORA_DB
exit
fi
fi
#
case $1 in
'start')
$ORACLE_HOME/bin/dbstart $ORACLE_HOME
;;
'stop')
$ORACLE_HOME/bin/dbshut $ORACLE_HOME
;;
*)
echo "usage: $0 {start|stop}"
exit
;;
esac
#
exit

5-Change the group of the dbora file to the OSDBA group (typically dba), and set the permissions to 750:

# chgrp dba dbora
# chmod 750 dbora

but no success ,can u please give me any suggestions regarding this?
it'll b highly appreciated
thanx in advance

Re: insufficient privileges [message #252270 is a reply to message #252197] Wed, 18 July 2007 07:45 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member


Hi,


C:\>type c:\oracle\product\10.1.0\db_1\network\admin\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network\a
dmin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NONE)
SQLNET.EXPIRE_TIME=2
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jul 18 16:39:03 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

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

SQL> create user asss identified by asss;

User created.

SQL> conn asss as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL> EXIT

C:\>type c:\oracle\product\10.1.0\db_1\network\admin\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network\a
dmin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
SQLNET.EXPIRE_TIME=2
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


C:\>SQLPLUS SYS/ORACLE AS SYSDBA

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jul 18 16:40:55 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


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

SQL> CONN ASSS AS SYSDBA
Enter password:
Connected.
SQL>




Note: my OS User is member of ORA_DBA group.


Regards
Taj
Re: insufficient privileges [message #252271 is a reply to message #252201] Wed, 18 July 2007 07:57 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

HI,
please go through below link.
http://www.togaware.com/linux/survivor/Starting_Stopping.html

hope this helps

Note: what problem you recevied ?
Oracle Version : 10gr?
Re: insufficient privileges [message #252366 is a reply to message #252271] Wed, 18 July 2007 12:59 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
check this

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b15658/strt_stp.htm#CFAHAHGA
Previous Topic: ORA-14452: while drop user cascade
Next Topic: ORA-12560: TNS:protocol adapter error ???
Goto Forum:
  


Current Time: Fri Dec 09 15:52:15 CST 2016

Total time taken to generate the page: 0.21575 seconds