Database Administrator Authentication

We sometimes need such special operation on database like, startup or shutdown the database or change the archival mode of the task. To do this, two special system privileges, SYSDBA and SYSOPER are required. You must have one of these privileges granted to you, depending upon the level of authorization you require.

One thing we need to remember when you connect with SYSDBA or SYSOPER privileges, you connect with a default schema. For SYSDBA this schema is SYS and for SYSOPER the schema is PUBLIC.

There are two methods are available for authenticating database administrators.

1)Operating System(OS) Authentication.
2)Password File Authentication.

Priority of Database Administrator Authentication Methods:
------------------------------------------------------------
Remote Database Administration ->If have Secure Connection?->If yes->Want to use OS authentication?->If yes then use OS Authentication.

Remote Database Administration ->If have Secure Connection?->If yes->Want to use OS authentication?->If No then use Password file Authentication.

Remote Database Administration ->If have Secure Connection?->If No then use Password file Authentication.

Local Database Administration ->Want to use OS authentication?->If No then use Password file Authentication.

Local Database Administration ->Want to use OS authentication?->If yes then use OS Authentication.


Using Operating System Authentication
-------------------------------------------
Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER.
On unix is is usually referred as dba/oper and on windows as ORA_DBA/ORA_OPER.

If you are a member of the OSDBA/ORAOPER group and you specify AS SYSDBA/ AS SYSOPER when you connect to the database, then you connect to the database with the SYSDBA/SYSOPER system privilege.

If you are not a member of OSDBA/ORAOPER group then OS authentication fails.

How to Use Operating System Authentication

To enable operating system authentication of an administrative user:

1.Create an operating system account for the user.
2.Add the account to the OSDBA or OSOPER operating system defined groups.

On unix the system is

# id -a oracle
uid=100(oracle) gid=100(oinstall) groups=101(dba)
# useradd -g oinstall -G dba test
# exit
exit
bash-3.00$ su test
Password:
sh-3.00$ sqlplus / as sysdba

On windows, click on my computer>Manage>Local Users and Groups>Add user and group.

Using Password File Authentication
--------------------------------------------

1.If you don't have password file create one,
2.Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).
3.Connect to the database as user SYS (or as another user with the administrative privileges).
4.If the user does not already exist in the database, create the user.
5.Grant the SYSDBA or SYSOPER system privilege to the user:

Here is the steps,
1.orapwd FILE=newpwdfile.pwd PASSWORD=test ENTRIES=30
2.alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile
3.SQL> conn / as sysdba
4.SQL> create user test identified by t;
5.SQL> !lsnrctl status and see the service name. Here my one is arju.arjubd.com
6.grant sysdba to test
And here is the test after step 5,

SQL> grant create session to test;

Grant succeeded.

SQL> conn test/t as sysdba
Connected.
SQL> conn test/t@neptune/arju.arju.com as sysdba
ERROR:
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant sysdba to test;

Grant succeeded.

SQL> conn test/t@neptune/arju.arju.com as sysdba
Connected.

You can check who of you have password file authentication using,

select username, SYSDBA,SYSOPER from v$pwfile_users;

Related Documents
Login to Dbconsole, Authentication failed!null Returned