Thanks for the explanation
Mark J. Bobak, July 10, 2001 - 12:47 am UTC
9i Catalog Views access on Forms 6i
Bruno Di Rei Araujo, October 16, 2002 - 2:08 pm UTC
Great Tom, that is really what I was looking for !
Although it worked on sqlplus, I just can't do the same on my Forms 6i: I logged with the privilegded user and tried to compile the form, but it still reports "DBA_ROLES identifier must be declared" on my cursors... The *same* cursor works fine in sqlplus.
Why? Do I have to update all the clients with Oracle9i Client for it to work, I mean, does the Forms 6i client works fine with Oracle DB 9i?
Thanks a lot !
October 16, 2002 - 2:55 pm UTC
should not matter the client.
Suggestion: try creating a view yourself (select * from dba_views) and see what happens.
how to connect from command line
Sikandar Hayat, September 12, 2003 - 1:12 pm UTC
I have Oracle 9i R2 on RH7.2 and I am trying to connect in the following way,
$ sqlplus / as sysdba
$ sqlplus sys/password as sysdba
both the above are not working?
but the following worked,
$ sqlplus "/ as sysdba"
Why this?
September 12, 2003 - 2:11 pm UTC
because that is the way parameters work
the "username/password as sysdba" is a SINGLE parameter
the way to do that in unix/linux is via ""
Very clear explanation and very helpful.
Pauline, September 12, 2003 - 3:28 pm UTC
Explained the reason for install problems with DBTools 4.x in 9i environments !
Sven Bleckwedel, January 05, 2004 - 12:30 pm UTC
Hi Tom,
Thanks very much for explaining this. I was searching at the forum site from the DBTools utility for a reason for these ORA-28009 problems and remembered that in your site some answers can be the easiest way to obtain it...
Thanks again,
Sven
O7_DICTIONARY_ACCESSIBILITY
reader, February 22, 2004 - 7:56 pm UTC
As this parameter is much of a security issue, why does oracle have this parameter in the first place and even made it as true as default in pre 9i? Any history about this parameter that you could share? Why is it named with o7 for example? Thanks.
February 22, 2004 - 9:19 pm UTC
because the behaviour became "changeable" with 8.x (either do it the way O7 did or do it the new way). In 9i, the "new" way became "the default way"
I can connect as sys
Mylah, March 24, 2004 - 9:12 am UTC
I did review this article and found it helpful however, I have 07_Dictionary_Accessiblity set to false and can still connect as sys. From a sqlplus prompt I simply do the following:
conn sys/password@sid AS SYSDBA
I connect fine.
March 24, 2004 - 9:40 am UTC
that is not as sys.
that is however AS SYSDBA which is a totally different beast.
connect sys/password
and let us know what "happens" then.
A reader, March 24, 2004 - 10:45 am UTC
Can not connect as sysdba after shutdown
Laxman S Kondal, October 19, 2004 - 10:31 am UTC
Created a new instance and works fine except if I shutdown and connect from new sql*plus session I get this error as sysdba and system
C:>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Oct 19 09:21:11 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys/ops456d@opsd.ops1.com as sysdba
ERROR:
ORA-01031: insufficient privileges
Enter user-name: system/ops123d@opsd.ops1.com
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
And if I use sqlplus to shutdown and startup without disconnecting from sqlplus it works:
sys@OPSD> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@OPSD> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
sys@OPSD> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@OPSD> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
sys@OPSD>
Other instance on same Linux box are working fine.
October 19, 2004 - 10:57 am UTC
can you REMOTELY connect as sysdba at any time.
if you are on the server, it should just be / as sysdba.
(time to change your passwords now...)
Laxman S Kondal, October 19, 2004 - 11:31 am UTC
Thanks Tom
Yes I can connect from server any time, stratup and shutdown works fine from server.
October 19, 2004 - 11:35 am UTC
show me that you can
connect user/pass@server as sysdba
when it is up, something like:
$ sqlplus user/pass@server as sysdba
SQL> shutdown;
SQL> exit
$ sqlplus user/pass@server as sysdba
do that and cut and paste the results -- show "as sysdba" only working when the DB is up.
Laxman S Kondal, October 19, 2004 - 11:44 am UTC
I changed the password also and no difference
sys@OPSD> connect sys/lkondal@opsd.ops1.com as sysdba
Connected.
sys@OPSD> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@OPSD> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
C:\>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Oct 19 11:24:02 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys/lkondal@opsd.ops1.com as sysdba
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
Thanks
October 19, 2004 - 1:06 pm UTC
kindly connect in the same fashion both times
sqlplus /nolog
SQL> connect .....
here is what He should do
Manish Upadhyay, October 19, 2004 - 12:18 pm UTC
C:\>sqlplus
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 19 10:52:24 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys@opsd.ops1.com as sysdba
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL>
i.e. username@alias as sysdba
enter the password when prompted.
Can not connect as sysdba after shutdown
Laxman S Kondal, October 19, 2004 - 12:32 pm UTC
C:\>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Oct 19 12:05:14 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys@opsd.ops1.com as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
can not connect as sysdba after shutdown
A reader, October 19, 2004 - 4:29 pm UTC
Hi Tom
Using same fashion both time did'nt make difference
[lkondal@ops-dbs lkondal]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 19 16:01:27 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/kondal@opsandd as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[lkondal@ops-dbs lkondal]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 19 16:03:17 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/kondal@opsd as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL>
Thanks for you help...
can not connect as sysdba after shutdown
Laxman S Kondal, October 19, 2004 - 4:57 pm UTC
Hi Tom
Sorry there is slight mistake in instance name 'opsd' and not 'opsandd' for some reason 'and' comes in between cut past and I know you may not like such error. I am sorry for the reasons you know why.
Thanks
October 19, 2004 - 8:44 pm UTC
well, curious how a cut and paste could add "and" but...
what is your:
remote_login_passwordfile
set to and your are sure no one else could be using the password file in $ORACLE_HOME/dbs right?
Can not login as sysdba after shutdown
A reader, October 20, 2004 - 8:22 am UTC
Hi Tom
Thanks for reply
remote_login_passwordfile was EXCLUSIVE and now changed to SHARED and still same error
[lkondal@ops-dbs lkondal]$ sqlplus
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Oct 20 07:49:04 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys/kondal@opsd as sysdba
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> show parameter remote_login
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string SHARED
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[lkondal@ops-dbs lkondal]$ sql /nolog
bash: sql: command not found
[lkondal@ops-dbs lkondal]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Oct 20 07:50:33 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/kondal@opsd as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL>
October 20, 2004 - 11:20 am UTC
it should be exclusive -- and I cannot reproduce this issue at all. sorry please contact support.
can not connect as sysdba after shutdown
Laxman S Kondal, October 20, 2004 - 12:17 pm UTC
Hi Tom
Thanks for your help and I have one question to ask about login.
When instance is down and a user or say any user with or without sysdba/sysoper privilege wants to login remotely - what's the process. I guess first connect sting is resolve and then usename/pwd. Where is this username/pwd is resolved? Is it password file or some where else it looks for correct username/pwd?
Thanks for your help...
October 20, 2004 - 12:19 pm UTC
....When instance is down and a user or say any user with or without sysdba/sysoper privilege wants to login remotely - what's the process..
the process is they get on the phone, they call the DBA and they ask them -- please start the database, i need it.
they need the database to be up before they can connect.
A reader, October 20, 2004 - 1:31 pm UTC
Hi Tom
You are great.
What I wish to know, if you please, when instance is down and a user, lets say user with sysdba privs - otherwise just call or email to DBA - wants to startup the instance, how username/pwd and privs are resolved. Is it only pwd file or more to it?
Or if you could direct me to some document.
Thanks.
October 20, 2004 - 4:59 pm UTC
if you use user/pass@tnsconnect as sysdba -- the password file created via orapwd is used.
if you connect locally -- on the server -- the OS is used (if you are in the right "group" -- eg: dba group on unix) -- then all you need is "/ as sysdba"
can not connect as sysdba from remote after shutdown
Laxman S Kondal, October 21, 2004 - 8:34 am UTC
Hi Tom
Thanks for reply.
You brought me to a point where I have another question.
If I use user/pass@tnsconnect as sysdba, password file created via orapwd is used. My question is if this file is not changed after shutdown what makes the system not to read the same password file to connect the same user with same privilege. It seems there is some difference in the process while system is up and running and when itÂ’s not running.
I will appreciate if you could explain this:
If I shutdown and rename password file and then recreate new password file with different name at
1. same location
2. Different location – where?
3. or at oracle_home/dbs and
Set remote_login_passwordfile=EXCLUSIVE
And then startup.
How will the system know where to look for new password file? Is there any parameter to be set/change or some thing else for system know where to look for new password file?
Thanks
October 21, 2004 - 2:25 pm UTC
it looks in the default location.
Still confused..
Dave Martin, January 27, 2005 - 4:07 am UTC
I have a running instance, TWO_TASK is defined as the same as ORACLE_SID. O7_DICTIONARY_ACCESSIBILITY IS TRUE.
Im logged into an account NOT in the dba group.
bscs451@deuxbn32:/pkg/ben451/bscs451> sqlplus /nolog
SQL*Plus: Release 9.2.0.5.0 - Production on Thu Jan 27 09:53:46 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges
??
January 27, 2005 - 8:58 am UTC
TWO_TASK is *not* defined to be the same as an ORACLE_SID
it might have the same string value, but two task is a string that is taken to the tnsnames.ora (or a directory) and converted into a bigger string.
ORACLE_SID is used in conjunction with the ORACLE_HOME to connect directly to the instance -- no net.
TWO_TASK and ORACLE_SID are completely unrelated to eachother -- if they are both set to X, that means *nothing*, you can draw NO conclusions from that.
with TWO_TASK set, connect sys as sysdba is really connect sys@$TWO_TASK as sysdba
meaning it is attempting to connect via the network and you would have had to of setup and configured the remote login password file.
(o7 dictionary is not relevant here either...)
A reader, January 27, 2005 - 3:31 pm UTC
Tom,
What does sqlplus -s or sqlplus -S exactly mean in unix scipt? For example,
sqlplus -s user/password
Thanks.
January 27, 2005 - 4:44 pm UTC
"silent", don't print banners.
A reader, January 27, 2005 - 4:53 pm UTC
Tom,
Thanks very much for your quick and helpful response. Where can I find such information? Before I asked this question, I tried to find it in sqlplus user's guide but ...
January 27, 2005 - 5:28 pm UTC
Ask Sqlplus
Greg, January 27, 2005 - 5:58 pm UTC
You can also get a list of options from the command itself:
sqlplus -?
SQL*Plus: Release 10.1.0.3.0 - Production
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-C <v>] [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_identifier>] | / | /NOLOG
<start> ::= @<URL>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-C" sets SQL*Plus compatibility version <v>
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode
I do this for sqlldr too since I keep forgetting the options....
Very helpful
A reader, January 28, 2005 - 11:01 am UTC
Thanks for both Tom's and Greg's answers.
Dave Martin, February 28, 2005 - 10:00 am UTC
Thanks for your help - I always assumed that a password file was only used to connect remotely to an instance that wasnt running and that after the db was up, the normal user authentitacion would take over, but I guess not..?
February 28, 2005 - 10:33 am UTC
as sysdba over the network is done via the password file (no password file, no as sysdba over the network)
ok, my database is down, my SYS password is "MANAGER", I just:
[ora9ir2@localhost dbs]$ orapwd file=orapwora9ir2 password=networkpw entries=10
[ora9ir2@localhost dbs]$
idle> connect sys/manager@ora9ir2.us.oracle.com as sysdba;
ERROR:
ORA-01017: invalid username/password; logon denied
<b>that shows my password in the password file right now is not "manager", it is networkpw</b>
idle> connect sys/networkpw@ora9ir2.us.oracle.com as sysdba;
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 269554828 bytes
Fixed Size 451724 bytes
Variable Size 201326592 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> connect sys/networkpw@ora9ir2.us.oracle.com
ERROR:
ORA-01017: invalid username/password; logon denied
<b>Now, just connecting as sys doesn't work - because networkpw is NOT the sys password, manager is:</b>
Warning: You are no longer connected to ORACLE.
SQL> connect sys/manager@ora9ir2.us.oracle.com
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
<b>still did not work but for another reason. So, database is up:
</b>
SQL> connect scott/tiger@ora9ir2.us.oracle.com
Connected.
SQL> connect sys/manager@ora9ir2.us.oracle.com as sysdba;
ERROR:
ORA-01017: invalid username/password; logon denied
<b>but still -- manager is not the right "sysdba" password, networkpw is:</b>
Warning: You are no longer connected to ORACLE.
SQL> connect sys/networkpw@ora9ir2.us.oracle.com as sysdba;
Connected.
<b>in order to update the password file with a new password, we change sys's password:</b>
SQL> alter user sys identified by manager;
User altered.
SQL> connect sys/networkpw@ora9ir2.us.oracle.com as sysdba;
ERROR:
ORA-01017: invalid username/password; logon denied
<b>and now networkpw is no longer the password...</b>
Warning: You are no longer connected to ORACLE.
SQL> connect sys/manager@ora9ir2.us.oracle.com as sysdba;
Connected.
SQL>
<b>manager is -- but it used the password file</b>
normal user with sysdba privilage?
Rajesh, March 02, 2005 - 3:42 am UTC
Tom,
In my test instance, I granted sysdba/sysoper privilages to one of the test users. Then, I connected with that user name as sysdba. But, when I issue "Show user" command, Oracle shows its "Sys". Can you please explain me, how this works?
C:\>sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Mar 2 14:04:06 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: masked/masked@masked as sysdba
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> show user
USER is "SYS"
SQL>
Thanks
Rajesh S
March 02, 2005 - 7:34 am UTC
"as sysdba" is always SYS
"as sysoper" is always PUBLIC
The point of "as sysdba" is to get you into the database as the super user using YOUR OWN credentials -- you have to give your user/your pass and then you are allowed in (instead of sharing a common user/pass with many people)
You can audit these operations as well and see who is using the extremely powerful "as sysdba" command to do things and what they are doing with it.
Please ignore my previous review.
Rajesh, March 02, 2005 - 5:22 am UTC
Tom,
I searched your site and got the answer.
<quote>
So, if I want to restrict privs among the 'DBAs', then I can grant sysoper to a
'newly' joined dba until I get confidence. Because, connecting as sysoper, one
cannot view or drop any table in any schema. after connecting as sysoper, 'show
user' results, 'PUBLIC'. any specific reason?
SQL> connect a as sysoper;
Enter password: *
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
RESTRICTED SESSION
SYSOPER
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table scott.x;
drop table scott.x
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show user
USER is "PUBLIC" <------------------------
THANKS.
Followup:
same reason that after you connect as SYSDBA it shows SYS.
you can only do things PUBLIC can do as sysoper....
</quote>
Thanks
Rajesh S
Thanks, Tom
Dave Martin, March 04, 2005 - 4:52 am UTC
So, any connect as sys needs to be 'as sysdba' whereupon the password used is the one in the password file. Also any user can have sysdba role - they will be placed in the password file also. Using the orapwd file utility can cause the passwords to be out of sync for sysdba users, but using 'alter user xxx identified by ' will cure that.
So, without a password file, you CANNOT connect as sys, except by 'connect / as sysdba' from the DBA group.
Right?
March 04, 2005 - 8:21 am UTC
any connect as sys needs to be as sysdba if O7_DICTIONARY_ACCESSIBILITY is set to make it so.
this is regardless of the presence or not of a remote login password file.
The password file is used when connecting AS SYSDBA over the network.
Otherwise OS AUTHENTICATION (you must be in the right group) is used locally.
the only password that can get out of sync is "SYS's" -- you can only specify one password, upon creation and it is associated with sys.
without a password file, you can connect as sys using AS SYSDBA.
with a password file, you can connect as sys using AS SYSDBA
if you turn off the O7_DICTIONARY_ACCESSIBILITY you can connect as sys with or without sysdba (but don't do that)
Many Many Many Many Thanks...
Dave Martin, March 04, 2005 - 9:27 am UTC
..and then some more..
Connect using SQLPLUS says Oracle not available
Database Explorer, March 14, 2005 - 6:36 pm UTC
I have an Oracle instance up and running and am able to connect to that instance remotely using the tns connect string.
But when I do
export ORACLE_HOME=..BLAH..
export ORACLE_SID=..BLAH..
sqlplus "/as sysdba"
It says connected to an idle instance
This happens in Linux.
sqlplus sys/<passwd>@mydb as sysdba works fine.
March 14, 2005 - 8:05 pm UTC
well, oracle_home+oracle_sid is used to connect *to a local instance* only.
in order to connect to a remote instance, you have to use @remote...
OS authantication and sysdba
totu, March 28, 2005 - 1:56 am UTC
Dear Tom.
I tried to use OS authentication.
I have Oracle 9iR2/Win2000
alter system set remote_login_passwordfile=NONE scope=spfile;
shutdown immediate;
disc
add os user to group ora_dba
conn /testdb1 as sysdba
Error: insufficient privileges.
What can I do?
Thanks in advance.
March 28, 2005 - 7:49 am UTC
log into the OS as someone with the privilege to connect as sysdba.... and connect / as sysdba
Same result
Totu, March 29, 2005 - 1:12 am UTC
Thanks Tom.
I have already logged, but result is the same.
Any idea?
Thanks in advance.
March 29, 2005 - 8:12 am UTC
cut and paste it for us, and share the contents of your sqlnet.ora
SQLNET.ORA
Totu, March 30, 2005 - 1:47 am UTC
Thanks.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES = (NONE)
March 30, 2005 - 7:10 am UTC
missing the cut and paste
Cut & Paste
Totu, March 31, 2005 - 12:21 am UTC
SQL> conn /@testdb1 as sysdba
ERROR:
ORA-01031: insufficient privileges
Thanks.
March 31, 2005 - 7:38 am UTC
why are you using @testdb1
you see that is very different from what you said:
add os user to group ora_dba
conn /testdb1 as sysdba
Error: insufficient privileges.
What can I do?
Thanks in advance.
that is invalid (what you originally posted), i was trying to confirm you were doing network authentication.
and you have disabled that capability:
alter system set remote_login_passwordfile=NONE scope=spfile;
you said "no remote sysdba's"
and you are trying to be a remote sysdba.
lose the @testdb1
connect / as sysdba
when logged directly into the server as an account that is allowed to connect as sysdba.
As in Oracle admin Guide
Totu, April 01, 2005 - 4:26 am UTC
Thanks.
I have taken below lines from Oracle DB. Admin. Guide:
-------------------------------------
Preparing to Use OS Authentication
To enable authentication of an administrative user using the operating system you
must do the following:
1. Create an operating system account for the user.
2. Add the user to the OSDBA or OSOPER operating system defined groups.
3. Ensure that the initialization parameter, REMOTE_LOGIN_PASSWORDFILE, is set to NONE. This is the default value for this parameter.
Connecting Using OS Authentication
A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:
CONNECT / AS SYSDBA
CONNECT / AS SYSOPER
For a remote database connection over a secure connection, the user must also specify the net service name of the remote database:
CONNECT /@net_service_name AS SYSDBA
CONNECT /@net_service_name AS SYSOPER
---------------------------------------
Any idea?
Thanks
April 01, 2005 - 8:35 am UTC
can you just connect / as sysdba
No, I cannot
Totu, April 04, 2005 - 2:06 am UTC
No I cannot.
Thanks.
April 04, 2005 - 6:51 am UTC
then you are not logged into the OS as someone allowed to. Log in as that person (typically that which installed oracle)
you have explicity disabled remote logins here.
how to force the sys as sysdba to login with password?
john, May 10, 2005 - 8:35 pm UTC
hi ,tom
this is great site for oracle!
thank you for all you have done here .
my server is a windows2000 ,oracle version is 9i.
when i login using sys as sysdba,i can login with any password (i login windows using administrator ),it means if i can login the server using administrator,then oracle is open ,without any protection. please tell me ,how can i avoid this .how can i force sys must provide the right password to login.
thanks in advance
john
May 10, 2005 - 11:31 pm UTC
do not login as the software owner or anyone in the OS group that is allowed to just log in.
that software owner can log in (has to be able to).
if you don't want that, lock that os account.
reader
A reader, May 25, 2005 - 11:17 am UTC
If SYS and SYSTEM accounts' password expires, what would be
the effect if any on the database or password file
May 25, 2005 - 1:33 pm UTC
nothing
remember "as sysdba" appears to be SYS but isn't. it won't be affected by password expires.
Unable to connect as user
Ram, July 28, 2005 - 1:49 am UTC
Hi,
I have created an user with sysdba privilege. I am able to connect as sysdba, but, when I try connecting without "as sysdba", I get error "ORA-01017: invalid username/password; logon denied". I am giving the correct username and password. My application does not allow me to login as conn username/password as sysdba; I can only connect as conn username/password, with the user having sysdba privileges.
Kindly help
July 28, 2005 - 8:50 am UTC
insufficient data, you don't mention network/no network, operating system...
I don't know what you did really or if you have a password file setup or anything.
sorry, insufficient information here. It is best if you can cut and paste what you've done step by step by step from sqlplus .
connection as sysdba takes forever in 9i
A reader, July 31, 2005 - 11:25 am UTC
Hi Tom,
I hope you can help me to find out why this is happening. I have created a new database in 9.2.0.6 on AIX 5L2. I did not set up the listener since I am not done with the load of the data yet, so the only way to connect is doing a local connection in the AIX box where the database is. From time to time (specially after a period of not doing anything inside the database) if I try to connect as sysdba or using my own account using sqlplus the login takes an average of 10 minutes to able to access the database. Why do you think is taking so long to be able to establish a local connection?. In this case the listener is no set up yet so I know the listener is not the problem. If you can give any advice on this I will really appreciate it.
Thanks
July 31, 2005 - 11:29 am UTC
I'm not an AIX user, but if this happened to me on Solaris or Linux (not that it has, it hasn't), I'd attach to the process with truss (solaris) or strace (linux) to see what OS calls it might be making and getting blocked on - I'm sure AIX has the functional equivalent of truss/strace. It might be useful.
Forcing to give password for sysdba
Kumar, August 03, 2005 - 3:51 am UTC
Hi Tom,
In one of the previous review, you had mentioned that to avoid logging as sysdba with any password lock the OS account or not to login as software owner. Is there any other way around ? Like setting the Authentication_services=None in the SQLNET.ORA file ?
I tried this, it didnot work.
August 03, 2005 - 11:00 am UTC
OS authentication is always on for this, you lock down the OS accounts.
reader
A reader, November 23, 2005 - 4:33 pm UTC
rdbms 9.2.0.1 windows XP
SQL> show user
USER is "SYS"
SQL> select name from v$database;
NAME
---------
ORA92
SQL> shutdown immediate;
ORA-01031: insufficient privileges
I could not figure why I get this error
November 23, 2005 - 8:05 pm UTC
because you must be in as SYSDBA or SYSOPER. Not just as sys
connect / as sysdba
reader
A reader, November 24, 2005 - 12:46 pm UTC
I created the service with oradim and I wanted to
create a new database and I am getting ora 1031
I am trying to figure out why
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\>set ORACLE_SID=ora922
C:\>set PATH=/oracle/ora92/bin;%PATH%
C:\>sqlplus "sys as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Nov 24 12:24:42 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup nomount pfile=c:\oracle\ora92\database\initora922.ora
ORA-01031: insufficient privileges
SQL>
November 24, 2005 - 5:12 pm UTC
it would not be asking for a password for a local connection.
try this
sqlplus /nolog
SQL> connect sys as sysdba
if that doesn't "just connect" you are no logged into the OS as an account in the right group (the one that is allowed to connect as sysdba)
reader
A reader, November 24, 2005 - 8:36 pm UTC
I understand this sysdba group in unix OS and ensure that
account is assigned this group, default "DBA" group
But I am not sure how this is done in windows XP system
Thanks
A reader, November 27, 2005 - 10:25 am UTC
Cannot connect as sysdba
Smita Acharya, December 06, 2005 - 3:01 pm UTC
Hi Tom,
This is 10.2.0.1 database on AIX 5.3
My OS userid is in the privileged "dba" group.
remote_login_passwordfile=exclusive.
I cannot login to the database using OS authentication.
>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 6 13:34:44 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
It says "connected to an idle instance" even though the database is up.
I assume "remote_login_passwordfile" does not have to be set to "NONE" and there are other databases where it is set to the same value and OS authentication works.
I am not sure what is wrong in the setup.
December 06, 2005 - 3:54 pm UTC
if it says connected to idle instance but the database is up then you either
o have your oracle home set wrong
o have your oracle sid set wrong.
Thanks Tom
Smita Acharya, December 06, 2005 - 7:01 pm UTC
Thanks a lot Tom. I noticed that I had entered my ORACLE_SID in lower case.
reader
A reader, January 20, 2006 - 12:34 pm UTC
Hi Tom,
WHEN I DO THIS :
define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
substr( global_name, 1, decode( dot,
0, length(global_name),
dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot
from global_name );
set sqlprompt '&gname> '
it works fine for all the users except sys..when i do
@connect sys/change_on_install as sysdba
it comes out of sql*plus i.e exits out of it...may i know how to correct this
January 20, 2006 - 12:55 pm UTC
@connect "sys/x as sysdba"
it is not because of the "when I do this", it is because my connect.sql just does
connect &1
but &1 would be sys/x and that won't (generally) work....
Very helpful
Aaron, November 08, 2006 - 1:47 am UTC
This was very helpful
Not able to login thru sys user...
Chandan Singh, November 08, 2006 - 7:55 am UTC
Hi Tom,
Again i need your help to solve this sys login problem on my test database.
I am not able to connect to sys user thru sqlplus.
I am able to connect thru toad as password was saved. So i thought of changing the password of sys by connecting thru system user which i am able to change with alter user command and it says "User Altered" but when i try to login sys with changed password then throws error invalid username/password. I am not able to understand why its throwing this error. This all i have tested when my OS account is a part of ORA_DBA group. When i remove my OS account from ORA_DBA group then it should connect with connect /as sysdba but it says insufficient privileges.
My remote_login_passwordfile is set to SHARED. Can you please tell me what could be the problem?
I hope i am clear what i want to say.
Thanks
Chandan Singh
November 08, 2006 - 8:33 am UTC
is toad connecting "/ as sysdba" - that connects as sys but requires no password.
you should NOT be able to
connect sys/password
at all, that should be NOT PERMITTED
it is never necessary.
not able to login
lakshmi, May 20, 2010 - 1:48 am UTC
When i try to connect database by usin
connect sys/oracle as sysdba.......... It doesn't work. Just cursor blinks on the screen. Why this would happended?
Please clarify my doubt.
May 24, 2010 - 11:28 am UTC
my car won't start, just sits there with the dashboard lights on. Why would this happen?
We have the same amount of information.
I can reproduce - if I put a pause in my login.sql - could be as simple as that, but given the level of information here - sorry, no idea for you.
You don't tell us if it works for others
You don't tell us versions
You don't tell us if you verified you are using sqlplus directly - not a alias, not a script
etc.
How to connect to remote database with OS authentication but not using password file
Kulkarni, September 10, 2012 - 12:36 pm UTC
Tom,
We are in a peculiar situation. There is a script to monitor standby database which usually runs from standby server. The script makes connections to both standby and primary databases. It takes password from a text file. Now due to security issue we cannot keep the sys password in a text file. Please can you tell me how to connect to remote database without password?
September 14, 2012 - 2:53 pm UTC