Werner Still

oracle authentication - mystery made easy

Blog Post created by Werner Still on Mar 3, 2016

I had recently some queries about the different authentication possibilities with the Oracle db. This especially in conjunction with the options we offer in HDID. There is not really a mystery behind this, but Oracle has a lot of different ways to authenticate and this makes it sometimes a bit confusing.

 

Oracle authentication

The first method to authenticate with Oracle is the database user/password. This is the normal way if some kind of application works with the database. The user, the password and the access rights are stored within the database. Database system administration is possible with the sys user (which is the build in user with the highest administration level) or another user created that has granted the sysdba rights.

 

All operations can be processed with this type of authentication, except one: startup of a database. As the credentials are stored within the database it is not possible to access these credentials if the database is completely down (or even not in mount state, as only now the system tablespace is available). To get around this the oracle password file (orapw) is used. This small file contains the user/password combination for the users that have sysdba rights. If there is a connection attempt to a database that is not started the check is performed against this special file. The sys user (as the build in account) is always included in this file, all other entries are created at the time a user gets granted the sysdba privilege.

 

The second method to authenticate is to have an operating system user that is a member of the database administration group. This operating system user is now able to connect to the database locally (not via a network, here only the first method will work) without specifying a user/password combination. The operating system user will be the sys user within the database. This type of authentication works for all states of the database, so even a startup can be issued this way.

 

HDID Oracle authentication

HDID has now several options to authenticate with Oracle. The simplest way is to provide only the database name for the database. With that information the selection of the oracle binary for this database is done and the owner of the binary is fetched. This operating system user is now used to connect to the database. The positive points are that the configuration is very simple and all detection's are done automatically. The only point not possible this way is to audit the operations of HDID as oracle only recognizes the user as sys.

To be able to overcome this audit issue it is possible to add an oracle user/password. The user has to have sysdba privilege as the connection to the database needs this privilege. For online operations it is not necessary to have an oracle password file, but for all operations on a database that is not started the file needs to be available. With a special oracle user for HDID it is now possible to audit all activities from this user. Any password change on the other hand side needs to be reflected in the HDID configuration. Up to the time that the password is changed and the configuration is distributed to all clients all operations will fail due to the wrong password.

If it is necessary to have an additional audit on the operating system side an operating system user can be supplied for the HDID configuration. This operating system user has to be in the Oracle database administration group for the specified database.

A combination of the two audit options is possible as well.

 

The recommendation is to use the simplest configuration method and supply only the database name within the HDID configuration. Only if it is requested to have the audit capabilities a separate Oracle/operating system user with all the necessary privilege's should be supplied in the HDID configuration.

Outcomes