Oracle operation problem

[KY.Chuang]

I have a Oracle operation problem and my oracle version is 8i.
When I use Oracle Database Administrattion Tool-Oracle DBA Studio to connect and manage Database.
Login Oracle Service with system connect as [SYSDBA]... But It give me a error [ORA-01031 insufficient privileges].
But, when I connect as [SYSOPER] it successed.
Does It have differents in
[SYSDBA] and [SYSOPER]?
And which setting make I can't
connect as [SYSDBA].

Thanks.

[malay boy]

This is plug off from metalink.See the attachment.

[Massimo Bianchi]

Hi,
did you do:
grant sysdba to system;

from sqlplus/svrmgrl ?
Massimo

[Yogeeraj_1]

hi,

SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.

This privilege allows the user to perform basic operational tasks without the ability to look at user data.


SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..

This is effectively the same set of privileges available when previously connected INTERNAL.



also note that:
SYS is special account. But sys is not good enough. You need INTERNAL or SYSDBA to do certain operations. I would recommend strongly against using SYS to do things. SYS cannot create triggers, SYS cannot have read only transactions, and so on. SYS should not be used.

System is just an account, nothing special about it. We use it to store various things.

Internal is a super special account. It is a SYSDBA/SYSOPER. It can startup/shutdown the database. You should avoid using it as it will be going away at some point in the future. The correct approach is to "connect user/password AS sysdba" to perform these operations.


hope this helps!

regards
Yogeeraj

[T G Manikandan]

This is because the OS authentication is done here and the OS user from where you are doing a

SQL>connect system/test@test as sysdba

will give a 1031 error.

Add that OS user to the dba group on the system. It should work then.

revert on the same


Thanks

[malay boy]

What are the minimal privileges/roles required for DBA Studio?
--------------------------------------------------------------
The minimum privileges or roles required for the DBA Studio are as follows
In Oracle version 8.1.7: connect + select_catalog_role
In Oracle version 9.0.1: connect + select any dictionary

[T G Manikandan]

ALso note that again after adding the user to the 'dba' group still 1031 error occurs.

This is because dba should be the primary group of the user.

What you can do is

#newgrp dba
#sqlplus system/@test
sql>connect / as sysdba



Revert

[Yogeeraj_1]

hi again,

Note that in the example above (by TG), the password used is the one when you created the password file.

$ orapwd file=orapw password=yog entries=40

I just recreated my password file with a password yog. My sys password is NOT yog.


$ svrmgrl

SVRMGR> connect internal
Connected.

SVRMGR> connect sys/change_on_install@ora817dev
Connected.

that works OK, lets try as sysdba:

SVRMGR> connect sys/change_on_install@ora817dev as sysdba;
ORA-01017: invalid username/password; logon denied

there I got your error. My SYS password is change_on_install but the password file has yog in it. SYS is special -- sys connecting as sysdba is like internal -- you have to use the password file password!

Lets try that:

SVRMGR> connect sys/yog@ora817dev as sysdba;
Connected.


the sysdba trick is generally used to allow other NON-SYS users to connect. BTW, it is recommended that you use your OWN account that has been granted sysdba!


SVRMGR> grant sysdba to scott;
Statement processed.

SVRMGR> connect scott/tiger@ora817dev as sysdba;
Connected.

Similarly, you can setup your own environment.

hope this helps too

regards
Yogeeraj

[Andreas D. Skjervold]

Hi

To use the orapw file for external "internal" logins (as sysdba) you must have the following in your init.ora file:
remote_login_passwordfile = exclusive

rgds
Andreas

[Massimo Bianchi]

If you change the remote_login_passwordfile to exclusize remeber also to create the password file with the orapw, as stated upper !

Otherwise your db won't start..
Massimo