Creating and using OS-authenticated users

In this recipe, you'll learn about OS-authenticated users.

Getting ready

To complete this recipe, you'll need an existing user who has a dba role, for example, johndba. It is assumed that you are working on Linux.

How to do it...

1. Connect to the database as a user who has a DBA role:

   $ sqlplus johndba
   
   

2. Find the prefix for operating system authentication:

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

3. Create an OS-authenticated user:

   SQL> create user ops$zoran identified externally;
   
   

4. Grant this user the create session privilege:

   SQL> grant create session to ops$zoran;
   
   

5. Log in to the operating system as the user zoran:

   $ su - zoran
   
   

6. Connect to the database without entering a user name or password:

   $ sqlplus /
   
   

How it works...

In OS authentication, database delegates user authentication to the operating system. This means that in order for OS authentication to work, user must exist as the user of the operating system. In database, these users are created with a prefix that is defined in the os_authent_prefix parameter (default is ops$). If an OS-authenticated user has the create session privilege, he or she can connect to the database using the following syntax:

    SQL> connect /
    Connected.
    
    SQL> show user
    USER is "OPS$ZORAN"

Note

Note that you cannot grant a sysdba,  sysoper,  sysbackup,  sysdg, or  syskm privilege to users that are identified externally, using a  grant statement:

 SQL> grant sysdba to ops$zoran;
    grant sysdba to ops$zoran

ERROR at line 1: ORA-01997: GRANT failed: user
      'OPS$ZORAN' identified externally

If you want to connect as sysdba using OS authentication, you have to add OS user zoran to OS group DBA:

[root@db121 ~]# usermod -a -G dba zoran
[root@db121 ~]# su - zoran
[zoran@db121 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 03 20:14:03 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64 bit
Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

There's more...

You can change the os_authent_prefix parameter with custom value (or you can leave it blank if you want OS-authenticated database users to have the same name as OS users).