Home » RDBMS Server » Server Administration » Sys user can login with as sysdba privilege?
Sys user can login with as sysdba privilege? [message #295329] Tue, 22 January 2008 02:33 Go to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Hello friends

I have a strange situation, at least to me that when I
try to login to my database using sys user.
The attempt fails when try like below.
SQL> CONN /AS SYSDBA

ERROR IS INSUFFICIENT PRIVILEGE

BUT WHEN I TRY ONLY LIKE BELOW

SQL> CONN SYS@MYDATA
ENTER PASSWORD:
CONNECTED.

But sys must be login as sysdba. It will not allow sys user
without sysdba, but how it is in my case?
HERE I HAVE MANUALLY GIVEN THE SCENARIO SINCE I DO NOT HAVE ACCESS TO THE DATABASE RIGHT NOW.
AND I AM SURE THAT I LOG IN TO THE MACHINE WITH ORACLE OWNER
AND SQLNET.AUTHENTICATION_SERVICES = (NTS) IS SET.


please clarify , why this strange behavior
Re: Sys user can login with as sysdba privilege? [message #295331 is a reply to message #295329] Tue, 22 January 2008 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Version? WITH 4 DECIMALS!

Regards
Michel
Re: Sys user can login with as sysdba privilege? [message #295337 is a reply to message #295329] Tue, 22 January 2008 02:53 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
In addition, what is the value of the
O7_DICTIONARY_ACCESSIBILITY parameter:

SELECT value
FROM v$parameter
WHERE name LIKE '%7%'
Re: Sys user can login with as sysdba privilege? [message #295364 is a reply to message #295337] Tue, 22 January 2008 03:42 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thanks for your replies.

The database version is Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production on Solaris 5.8
and
O7_DICTIONARY_ACCESSIBILITY boolean TRUE

Re: Sys user can login with as sysdba privilege? [message #295370 is a reply to message #295364] Tue, 22 January 2008 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why did you set it to TRUE? It should be FALSE.

Regards
Michel
Re: Sys user can login with as sysdba privilege? [message #295371 is a reply to message #295329] Tue, 22 January 2008 03:52 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams134.htm#sthref574
Re: Sys user can login with as sysdba privilege? [message #295374 is a reply to message #295329] Tue, 22 January 2008 03:59 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I think O7_DICTIONARY_ACCESSIBILITY has no impact to connect to database.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE
SQL> conn / as sysdba
Connected.
SQL> conn sys/arju
Connected.


[edit: I mean in case of true.]

[Updated on: Tue, 22 January 2008 04:21]

Report message to a moderator

Re: Sys user can login with as sysdba privilege? [message #295379 is a reply to message #295374] Tue, 22 January 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it has an impact.
If it was set to FALSE, you could not do the second connexion, you should add "as sysdba".

It has no relation with the first statement which is only related to your membership of dba group.

Regards
Michel
Re: Sys user can login with as sysdba privilege? [message #295384 is a reply to message #295374] Tue, 22 January 2008 04:13 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Dear friends

I also do not find any thing which prevents logging as sysdba when it is TRUE for this parameter.

It is set because , it is test instance.

Regards,
Re: Sys user can login with as sysdba privilege? [message #295386 is a reply to message #295329] Tue, 22 January 2008 04:20 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Change the SQLNET.AUTHENTICATION_SERVICES settings.
Re: Sys user can login with as sysdba privilege? [message #295387 is a reply to message #295384] Tue, 22 January 2008 04:21 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thankyou Mr. Michel

I get your point.
But why it is not allowing as sysdba connection.
one more thing is that when i try to give this privilege(sysdba)
to system.

SQL> show user
USER is "SYS"
SQL> grant sysdba to system;
grant sysdba to system
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

Is this means SYS itself do not have sysdba privilege?
what shall i do? Please help me out.

Regards,

[Updated on: Tue, 22 January 2008 04:22]

Report message to a moderator

Re: Sys user can login with as sysdba privilege? [message #295391 is a reply to message #295387] Tue, 22 January 2008 04:28 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Have you tried
conn sys/password@db as sysdba

What happened?
Re: Sys user can login with as sysdba privilege? [message #295399 is a reply to message #295391] Tue, 22 January 2008 04:39 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thankyou all friends.

Yes I have tried it.

SQL> conn sys/pwd@db as sysdba
ERROR:
ORA-01031: insufficient privileges

and this as well

SQL> conn sys@db as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges


As i asked before is it possible that sys can lost its sysdba privilege? If it is how can i grant it againt to sys?
Please help.
Re: Sys user can login with as sysdba privilege? [message #295410 is a reply to message #295387] Tue, 22 January 2008 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"conn / as sysdba" only works if you are in OS "dba" group.
"conn sys/pwd@db as sysdba" only works if you have a password file
"grant sysdba to system" only works if there is a password file.

Regards
Michel
Re: Sys user can login with as sysdba privilege? [message #295415 is a reply to message #295399] Tue, 22 January 2008 05:17 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
What is the setting for
SQLNET.AUTHENTICATION_SERVICES
in your sqlnet.ora file?

Are you positive that you are using the correct password?
Is the user that you are logged in as, a member of the OS group ORA_DBA?
Re: Sys user can login with as sysdba privilege? [message #295419 is a reply to message #295410] Tue, 22 January 2008 05:20 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thanks for your reply.

Dear Michel,

As I know when i grant sysdba privilege to any user and the password file is not present it gives the following error.

SQL> GRANT SYSDBA TO SYSTEM;
GRANT SYSDBA TO SYSTEM
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file


And this error is different,
SQL> show user
USER is "SYS"
SQL> grant sysdba to system;
grant sysdba to system
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>


Please clarify me if i am wrong.

[Updated on: Tue, 22 January 2008 05:21]

Report message to a moderator

Re: Sys user can login with as sysdba privilege? [message #295427 is a reply to message #295419] Tue, 22 January 2008 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The second error raises when your remote_login_passwordfile parameter is set to exclusive and the password file does not exist.

Regards
Michel
Re: Sys user can login with as sysdba privilege? [message #295432 is a reply to message #295415] Tue, 22 January 2008 05:42 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
thanks for all that effort from you all.

yes I am sure that i am using correct password.

sqlnet.ora:
# SQLNET.ORA Network Configuration File: /u03/mydata/mydata/9.2.0/network/admin/
sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)

$ exit

SQL> conn /as sysdba
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL> exit
$ sqlplus

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Jan 22 14:30:00 2008

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

Enter user-name: /as sysdba
ERROR:
ORA-01031: insufficient privileges


AND NOW I HAVE USED DIFFERENT SETTINGS IN SQLNET.ORA

# SQLNET.ORA Network Configuration File: /u03/MYDATA/mydata/9.2.0/network/admin/
sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)

~
~

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Jan 22 14:35:20 2008

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

Enter user-name: /as sysdba
ERROR:
ORA-01031: insufficient privileges


Enter user-name:

please clarify
Re: Sys user can login with as sysdba privilege? [message #295433 is a reply to message #295432] Tue, 22 January 2008 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are not in the OS "dba" group.

Regards
Michel
Re: Sys user can login with as sysdba privilege? [message #295443 is a reply to message #295433] Tue, 22 January 2008 06:01 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Yes Michel you are correct in some or the other way.

Actually i have multiple oracle homes, i was not running correct environment file to set ORACLE HOME.

Now it is ok. My problem is solved after running correct environment file.


Thankyou all very much.
Re: ERROR while loggin as Sys [message #354623 is a reply to message #295443] Mon, 20 October 2008 10:38 Go to previous messageGo to next message
kkraj
Messages: 2
Registered: October 2008
Junior Member
Can any 1 say wat is the problem here...imnot able to login as SYS....

SQL> CONN /AS SYSDBA
Connected.
SQL> CONN SYS@MYDATA
Enter password:
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper


Warning: You are no longer connected to ORACLE.

Any help would be appriciated.
Re: ERROR while loggin as Sys [message #354632 is a reply to message #354623] Mon, 20 October 2008 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no problem, it is the way Oracle works.

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
 *Cause:    connect SYS/<password> is no longer a valid syntax
 *Action:   Try connect SYS/<password> as SYSDBA or
            connect SYS/<password> as SYSOPER

Regards
Michel
Re: ERROR while loggin as Sys [message #354634 is a reply to message #354632] Mon, 20 October 2008 10:59 Go to previous messageGo to next message
kkraj
Messages: 2
Registered: October 2008
Junior Member
Any thing i do in my database....im getting an error which says

"ORA-06553:PLS-232:package Standard not accessible"


I had my databse working fine...then i changed some privilages and roles of SYS user to execute an statement ..which changed the whole thing...

any command i give it says "package standard not accesible.."

I hav tried running standard.sql from
oracle-home/rdbms/admin...but unable to compile it...

I get an error "package standrad not accesible..."

Will i hav to reinstall oracle or can i login through an user id to run the standard package successfully..

please help me out.

Re: ERROR while loggin as Sys [message #354643 is a reply to message #354634] Mon, 20 October 2008 11:56 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reexecute catalog.sql and catproc.sql (as SYS of course).

Regards
Michel
Previous Topic: create table space
Next Topic: log_archive_format
Goto Forum:
  


Current Time: Sat Dec 10 10:46:01 CST 2016

Total time taken to generate the page: 0.13558 seconds