OS Level Authentication - UNIX

The version of my Database is :-

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.1.0

Methods to connect as SYSDBA :-

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database.

To connect as "Database Administrator" i.e. sysdba , we can choose between operating system authentication or password files.
In this post , we will concentrate on "OS Auth" , next post with password file will be coming soon ..

OS Authentication (UNIX) :-

Connecting as SYSDBA :-

To connect as sysdba using OS Authentication ; your UNIX OS user must be a part of OSDBA group. Once the user is part of OSDBA group , irrespective of the username/password you provide , you successfully connect to sqlplus as SYS user as sysdba .

Example :-

The "timepass" user here is part of "DBA" group and hence , irrespective whether we provide the password or not ; or provide the wrong password, it connects successfully .

[timepass@adc2110341 bin]$ id
uid=60000(timepass) gid=8500(dba) groups=8500(dba)

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:31:04 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"

[timepass@adc2110341 bin]$ ./sqlplus sys/asffg as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:31:04 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"


[timepass@adc2110341 bin]$ ./sqlplus sys/welcome as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:31:04 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"

Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:

1. If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
2. If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.
3. If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER ( using OS Auth) , the CONNECT command fails.

Connecting as Normal User :-

Till now we saw connecting to the database as sysdba , and that is possible only if you belong to the above mentioned groups.
Second important part to OS Authentication is connecting to the database as normal user and not the DBA.

Example :-

If we consider timepass as a UNIX user which doesnt belong to the above mentioned privilege group and belong to normal user group ( timepass).

First , we will create the user timepass with default group :-

With root user :-

bash-3.00# /usr/sbin/useradd -d /scratch/timepass timepass

bash-3.00# passwd timepass
Changing password for user timepass.
New UNIX password: 
BAD PASSWORD: it is too short
Retype new UNIX password: 
passwd: all authentication tokens updated successfully.

Now login as timepass user and check the ID :-

bash-3.00$ su timepass
Password: 

[timepass@adc2110341 bin]$ id
uid=60000(timepass) gid=60000(timepass) groups=60000(timepass)

Consider the cases :-

1. Connecting with normal UNIX user as normal DB user :-

[timepass@adc2110341 bin]$ ./sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:22:48 2011

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

ERROR:
ORA-01017: invalid username/password; logon denied

2. Connecting with normal UNIX user as SYSDBA user :-

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:30:01 2011

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

ERROR:
ORA-01031: insufficient privileges

Both the cases give an error and we are not able to connect .

Now if we want the UNIX user to connect to database as normal user , we need to define the user in the database as OS user.

Lets take an example of how we do it.

First from the SQL Prompt , we will check the prefix for the OS User :-

SQL> show parameter prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

Now we will define the user "timepass" using the prefix "ops$" :-

SQL> create user ops$timepass identified externally;
User created.

SQL> GRANT CONNECT TO ops$timepass;
Grant succeeded.

Now consider the 2 cases again :-

1. Connecting as normal user :-

[timepass@adc2110341 bin]$ ./sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:33:05 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "OPS$TIMEPASS"

2. Connecting as Sysdba :-

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:33:32 2011

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

ERROR:
ORA-01031: insufficient privileges

As we can see , connecting as sysdba fails because for OS Authentication to work , your user must be a part of privileged USER group ; which is negative in our case. But connecting as normal user succeeds because we have identified this user as DB user ( external user).


If we see one last thing , if your user belong to the DBA group and is identified externally , you can connect both ways :-

Change the group to dba :-

[timepass@adc2110341 bin]$/usr/sbin/usermod -g dba timepass

[timepass@adc2110341 bin]$ id
uid=60000(timepass) gid=8500(dba) groups=8500(dba)

[timepass@adc2110341 bin]$ ./sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:34:25 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "OPS$TIMEPASS"

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:34:34 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"

One thing to notice is whenever you connect as sysdba using OS level authentication , show user will always give username as "sys".

Another important thing is you can't grant to sysdba privilege to externally identified user :-

SQL> grant sysdba to OPS$timepass;
grant sysdba to OPS$timepass
*
ERROR at line 1:
ORA-01997: GRANT failed: user 'OPS$TIMEPASS' is identified externally

REMOTE OS AUTHENTICATION and SQLNET.ORA File :-

REMOTE OS AUTHENTICATION

SQL> show parameter remote_os;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE

"remote_os_authent" :- Specifies whether remote clients will be authenticated with the value of the os_authent_prefix parameter.

SQLNET.ora

The "sqlnet.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" or "$ORACLE_HOME/net80/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself.

Now we have few combinations for both of them :-

1. remote_os_authent=false

Remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using OPS$ as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.

1.1 SQLNET.ORA doesnt have "SQLNET.AUTHENTICATION_SERVICES=(NONE)"

"IDENTIFIED EXTERNALLY " user works fine from localhost . But when tried with same user from remote host , gives the error as :-

bash-3.00$ sqlplus /@adc_orcl

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 8 02:36:51 2011

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

ERROR:
ORA-01017: invalid username/password; logon denied

1.2 SQLNET.ORA has "SQLNET.AUTHENTICATION_SERVICES=(NONE)"

There is no affect on remote host , because it is disabled anyway . But this entry disables local OS authentication as sysdba as well.

2. remote_os_authent=true

Allows other O/S authentication. Oracle assumes that the remote OS has authenticated the user. In order to use OPS$, remote_os_authent = TRUE and remote_os_roles = TRUE.

Lets change the parameter :-

SQL>  alter system set remote_os_authent=TRUE scope=spfile;
  alter system set remote_os_roles=TRUE scope=spfile;
System altered.

SQL>
System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

In case of 11g , this parameter is deprecated , hence gives the warning.


2.1 SQLNET.ORA doesnt have "SQLNET.AUTHENTICATION_SERVICES=(NONE)"

2.2 SQLNET.ORA has "SQLNET.AUTHENTICATION_SERVICES=(NONE)"

Irrespective of SQLNET.ora entry , the output from remote host is :-

 bash-3.00$ sqlplus /@adc_orcl

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 8 02:45:27 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

bash-3.00$ sqlplus /@adc_orcl as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 8 02:45:46 2011

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

ERROR:
ORA-01031: insufficient privileges

The one conclusion from this irrespective of the entry in sqlnet.ora / remote_os_auth parameter ; remote host can't use OS Authentication to connect as sysdba and local host OPS$ user cant be disabled by modifying these two.