DBA Hub

📋Steps in this guide1/6

OS Authentication

A guide to configuring OS authentication on UNIX/Linux and Windows servers.

oracle miscconfigurationintermediate
by OracleDba
28 views
1

Overview

OS authentication allows Oracle to pass control of user authentication to the operating system. Non-priviliged OS authentication connections take the following form. When a connection is attempted from the local database server, the OS username is passed to the Oracle server. If the username is recognized, the Oracle the connection is accepted, otherwise the connection is rejected. This article presents the configuration steps necessary to set up OS authentication on UNIX/Linux and Windows platforms. First, create an OS user, in this case the user is called "tim_hall". In UNIX and Linux environments this is done using the and commands.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
sqlplus /
sqlplus /@service

# useradd tim_hall
# passwd tim_hall
Changing password for tim_hall.

New password:
Retype new password:
#
2

Section 2

On Windows, local users are created using the Computer Management dialog (Start > Programs > Administrative Tools > Computer Management), or domain users can be created in Active Directory. Next, try to connect to Oracle as an OS authenticated user. We expect this to fail! It may be necessary to set up a few environment variables so that SQL*Plus works correctly. Under UNIX or Linux you would expect something like the following. The following is the Windows equivalent. In both cases, the connections failed because we have not told Oracle the users are OS authenticated. To do this, we must create an Oracle user, but first we must check the value of the Oracle initialization parameter.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# su - tim_hall
$ export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
$ export PATH=$PATH:$ORACLE_HOME/bin
$ export ORACLE_SID=DEV1
$ sqlplus /

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Jun 7 08:43:30 2006

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

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

C:\> set ORACLE_SID=DB10G

C:\> sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 17 11:17:55 2006

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

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

Section 3

As you can see, the default value is "ops$". If this is not appropriate it can be changed using the command, but for now we will use this default value. Now we know the OS authentication prefix, we can create a database user to allow an OS authenticated connection. To do this, we create an Oracle user in the normal way, but the username must be the prefix value concatenated to the OS username. So for the OS user "tim_hall", we would expect an Oracle username of "ops$tim_hall" on a UNIX or Linux platform. The situation is complicated slightly on Windows platforms as the domain or machine name forms part of the username presented to Oracle. On Windows platforms you would expect an Oracle username of "OPS$DOMAIN\TIM_HALL" for the Windows user "tim_hall".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> SHOW PARAMETER os_authent_prefix

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

-- UNIX
CREATE USER ops$tim_hall IDENTIFIED EXTERNALLY;
GRANT CREATE SESSION TO ops$tim_hall;

-- Windows
CREATE USER "OPS$ORACLE-BASE.COM\TIM_HALL" IDENTIFIED EXTERNALLY;
GRANT CREATE SESSION TO "OPS$ORACLE-BASE.COM\TIM_HALL";
4

Section 4

When using a Windows server, there is an additional consideration. The following option must be set in the "%ORACLE_HOME%\network\admin\sqlnet.ora" file. With the configuration complete, we can repeat our OS authentication connection tests. First, in a UNIX or Linux environment. Then the same test in a Windows environment.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQLNET.AUTHENTICATION_SERVICES= (NTS)

su - tim_hall
export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=DEV1
sqlplus /

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Jun 7 08:41:15 2006

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL>

C:\> set ORACLE_SID=DB10G

C:\> sqlplus /

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 17 11:47:01 2006

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
5

Section 5

As you can see, the database servers in both environments are now configured to allow the user "tim_hall" to connect using OS authentication. If you prefer to have no prefix for the oracle user you need to set the parameter to null (empty string). The syntax doesn't cope well with zero length strings, so you have to make the change the long way. Add this to the "/tmp/pfile.txt" file. Recreate the pfile.

Code/Command (click line numbers to comment):

1
2
3
4
create pfile='/tmp/pfile.txt' from spfile;
shutdown immediate;

os_authent_prefix=''
6

Section 6

For more information see: - Selecting an Authentication Method - Secure External Password Store Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
sqlplus / as sysdba
create spfile from pfile='/tmp/pfile.txt';
startup
show parameter os_authent_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!