Oracle Database User

To access the database, a user must specify a valid database user account and successfully authenticate as required by that user account. Each database user has a unique database account.

This is Oracle's best practice recommendation to avoid potential security holes and provide meaningful data for certain audit activities.

However, users may sometimes share a common database account. In these rare cases, the operating system and applications must provide adequate security for the database.

There are 3 privileges that can be assigned typically not to the same user. They are SYSDBA, SYSOPER, and SYSASM.

The SYS and SYSTEM accounts are required accounts in the database. They cannot be dropped. These accounts have the database administrator (DBA) role granted to them by default.

In addition, the SYS account has all privileges with ADMIN OPTION and owns the data dictionary. The SYSTEM account is granted the DBA role by default but not the SYSDBA privilege.
To connect to the SYS account, you must use the AS SYSDBA clause for a database instance and AS SYSASM for an Automatic Storage Management (ASM) instance.

Any user that is granted the SYSDBA privilege can connect to the SYS account by using the AS SYSDBA clause.

SYSOPER allows access as the PUBLIC user, and is a limited version of the SYSDBA privilege.
In 11g, a new privileged user is introduced, SYSASM, which will eventually replace SYSDBA on ASM instances, but both work on ASM instances.

SYSASM is only available for ASM instances. Only "privileged" users who are granted the SYSDBA, SYSOPER, or SYSASM privileges are allowed to start up and shut down instances.
Applying the principle of least privilege, the SYS and SYSTEM accounts are not used for routine operations. Users who need DBA privileges have separate accounts with the required privileges granted to them.


Authentication

Authentication means verifying the identity of someone or something – a user, device, or other entity – that wants to use data, resources, or applications. Validating that identity establishes a trust relationship for further interactions.

Authentication also enables accountability by making it possible to link access and actions to specific identities. After authentication, authorization processes can allow or limit the levels of access and action that are permitted to that entity.
When you create a user, you must decide on the authentication technique to use, which can be modified later. You can choose from three options:
password
external authentication
global authentication
Passwords are also referred to as authentication by the Oracle database.
You should create each user with an associated password that must be supplied when the user attempts to establish a connection.
When setting up a password, you can expire the password immediately, which forces the user to change the password after first logging in. If you decide on expiring user passwords, make sure that users have the ability to change the password. Some applications do not have this functionality.

All passwords created in Oracle Database 11g are case-sensitive by default. These passwords may also contain multibyte characters and are limited to 30 bytes. Each password created in a database that is upgraded to Oracle Database 11g remains case-insensitive until the password is changed.
Passwords are always automatically and transparently encrypted using the Advanced Encryption Standard (AES) algorithm during network – client/server and server/server – connections before sending them across the network.
External authentication is authentication by a method outside the database – operating system, Kerberos, or Radius.

The Advanced Security Option is required for Kerberos or Radius. Users can connect to the Oracle database without specifying a username or password. The Advanced Security Option – which is a strong authentication – allows users to be identified through the use of biometrics, x509 certificates, and token devices.
With external authentication, your database relies on the underlying operating system, network authentication service, or external authentication service to restrict access to database accounts. A database password is not used for this type of login.

If your operating system or network service permits, you can have it authenticate users.
If you use operating system authentication, you should set the OS_AUTHENT_PREFIX initialization parameter and use this prefix in Oracle usernames.

The OS_AUTHENT_PREFIX parameter defines a prefix that the Oracle database adds to the beginning of each user's operating system account name. The default value of this parameter is OPS$ for backward compatibility with the previous versions of the Oracle software.
The Oracle database compares the prefixed username with the Oracle usernames in the database when a user attempts to connect. For example, suppose that OS_AUTHENT_PREFIX is set to OS_AUTHENT_PREFIX=OPS$.


Administrator authentication

There are two types of security for administrator authentication: Operating system and Administrator security. Typical database users should not have the OS privileges to create or delete database files.

In UNIX and Linux, DBAs by default belong to the install OS group, which has the required privileges to create and delete database files.

Connections for the privileged users SYSDBA, SYSOPER, and SYSASM are authorized only after verification with the password file or with the OS privileges and permissions.
If OS authentication is used, the database does not use the supplied username and password. OS authentication is used if there is no password file, if the supplied username or password is not in that file, or if no username and password are supplied. The password file in Oracle Database 11g uses case-sensitive passwords by default.

However, if authentication succeeds by means of the password file, the connection is logged with the username. If authentication succeeds by means of the operating system, it is a CONNECT / connection that does not record the specific user.
OS authentication takes precedence over password file authentication. Specifically, if you are a member of the OSDBA or OSOPER group for the operating system and you connect as SYSDBA, SYSOPER, or SYSASM, you will be connected with the associated administrative privileges regardless of the username and password that you specify.
In Oracle Database 11g, a privileged user may use strong authentication methods – Kerberos, SSL, or directory authentication if the Advanced Security Option is licensed.
During installation and database creation, you can unlock and reset many of the Oracle-supplied database user accounts.

If you did not choose to unlock the user accounts at that time, you can unlock the users and reset the passwords by selecting the user on the Users page and clicking Unlock User.
Alternatively, you enter the new password in the Enter Password and Confirm Password fields on the Edit Users page.
And then select the Unlocked radio button.
And you click Apply to reset the password and unlock the user account.