Basics of User and Operating System Authentication in Oracle 12c

By Chris Ruel, Michael Wessler

Authentication is about making sure your users are who they say they are. This process begins well before users try to access the Oracle 12c database. You need to set up a system or process that allows you to verify users’ identity.

You also need a method for users to access the system that both identifies them and restricts their privileges to their required needs. Finally, it is recommended you use a security mechanism such as a password or operating system account so access isn’t open to anyone who tries.

User authentication in Oracle 12c

After you set up your databases, the next step is to allow users access to the data. You may have all sorts of users in your environment, from people who need full access to the data and database (such as a DBA) to an application that runs on a machine for users connecting from the Internet.

User authentication, the first step to protecting your data, means verifying that a resource (user, program, another machine) trying to connect to your database is authorized to do so.

You can establish the following by authenticating users:

  • Accountability: Having an accountability system forces users to take responsibility for their actions. It helps track down the culprits when problems occur.

  • Trust: A system of authentication allows you to operate within a realm of trust. Make sure a potential user is qualified before she’s given data access. Qualifications can be as simple as a one-hour training class or as detailed as a full-blown, government-sponsored background investigation.

  • Proper privileges: You must restrict and grant access according to a resource’s identity and qualifications. Different resources have different types of access to accomplish different jobs. You can manage such restricting and granting of access through a system of varying roles and privileges.

  • Tracking mechanisms: Many databases need a Big Brother. When something goes wrong, a tracking mechanism can help you hunt down and plug any security holes. It can also help you make sure resources in your environment aren’t snooping.

Operating system authentication in Oracle 12c

You may not always want to require a user password. In those cases, operating system authentication can be useful and, if set up properly, offer some security advantage over using a password. Use operating system authentication with caution though.

Operating system authentication recognizes a user as logged in to the OS and waives the password requirement. Operating system authentication can be especially useful when you have an application that requires a log in to the database to run a program. Say a job runs every night to generate reports and deposit them into a directory.

How will the user inside your batch job connect? You could embed a password in the program, but that isn’t secure. Instead, create an account in the database that links to the OS user and configure it with OS authentication. That way, you protect the OS user’s password and avoid a traditional username/password combination for the user to log in to run the reports.

You’re safe as long as only authorized personnel know the OS user password.

Type this code to create an OS-authenticated user in Oracle for someone named REPORTS:

<CREATE USER OPS$REPORTS IDENTIFIED EXTERNALLY;>

You see this:

User created.

Notice how the OS user is called REPORTS and the Oracle user is called OPS$REPORTS.

The user prefix OPS$ must precede the OS username for the username to be identified externally.

External identification means that instead of the user requiring a password in the database, Oracle looks to the OS and matches the username (minus the OPS$) to a user on the operating system. Oracle assumes that because the user is logged in to the OS, the user must be authenticated. You can change that prefix, OPS$, by revising the Oracle parameter OS_AUTHEN_PREFIX.

After setting up all the necessary privileges for that user, the user can log in from the OS command line without a password:

<sqlplus />