Oracle® Database Security Guide 11g Release 2 (11.2) Part Number E16543-14 |
|
|
PDF · Mobi · ePub |
This chapter contains:
Authentication means verifying the identity of someone (a user, device, or other entity) who 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 permitted to that entity.
You can authenticate both database and nondatabase users for an Oracle database. For simplicity, the same authentication method is generally used for all database users, but Oracle Database allows a single database instance to use any or all methods. Oracle Database requires special authentication procedures for database administrators, because they perform special database operations. Oracle Database also encrypts passwords during transmission to ensure the security of network authentication.
After authentication, authorization processes can allow or limit the levels of access and action permitted to that entity. Authorization is described in Chapter 4, "Configuring Privilege and Role Authorization."
This section contains:
Ensuring Against Password Security Threats by Using the SHA-1 Hashing Algorithm
Managing the Secure External Password Store for Password Credentials
See also "Guidelines for Securing Passwords" for advice on securing passwords. If you want to configure Oracle XML DB to authenticate users by encrypting their passwords but you do not need to encrypt other data (for example, an Intranet email), see Oracle XML DB Developer's Guide for more information.
Oracle Database provides a set of built-in password protections designed to protect your users' passwords. These password protections are as follows:
Password encryption. Oracle Database automatically and transparently encrypts passwords during network (client-to-server and server-to-server) connections, using Advanced Encryption Standard (AES) before sending them across the network.
Password complexity checking. In a default installation, Oracle Database checks that new or changed passwords are sufficiently complex to prevent intruders who try to break into the system by guessing passwords. You can further customize the complexity of your users' passwords. See "Enforcing Password Complexity Verification" for more information.
Preventing passwords from being broken. If a user tries to log in to Oracle Database multiple times using an incorrect password, Oracle Database delays each login after the third try. This protection applies for attempts made from different IP addresses or multiple client connections. For the first three attempts, there is no delay. Afterwards, it gradually increases the time before the user can try another password, up to a maximum of about 10 seconds. If the user enters the correct password, he or she is able to log in successfully without any delay.
This feature significantly decreases the number of passwords that an intruder would be able to try when attempting to log in. It is designed to prevent repeated attacks on password checking.
Enforced case sensitivity for passwords. Passwords are case sensitive. For example, the password hPP5620qr
fails if it is entered as hpp5620QR
or hPp5620Qr
. In previous releases, passwords were not case sensitive. See "Enabling or Disabling Password Case Sensitivity" for information about how case sensitivity works, and how it affects password files and database links.
Passwords hashed using the Secure Hash Algorithm (SHA) cryptographic hash function SHA-1. Oracle Database uses the SHA-1 verifier is to authenticate the user password and establish the session of the user. In addition, it enforces case sensitivity and restricts passwords to 160 bits. The advantage of using the SHA-1 verifier is that it is commonly used by Oracle Database customers and provides much better security without forcing a network upgrade. It also adheres to compliance regulations that mandate the use of strong passwords being protected by a suitably strong password hashing algorithm. See "Ensuring Against Password Security Threats by Using the SHA-1 Hashing Algorithm" for more information.
Passwords must not exceed 30 characters or 30 bytes. For greater security, however, follow the additional guidelines described in "Guidelines for Securing Passwords".
To create passwords for users, you can use the CREATE USER
or ALTER USER
SQL statements. SQL statements that accept the IDENTIFIED BY
clause also enable you to create passwords. Example 3-1 shows several SQL statements that create passwords with the IDENTIFIED BY
clause.
Example 3-1 Password Creation SQL Statements
CREATE USER psmith IDENTIFIED BY password; GRANT CREATE SESSION TO psmith IDENTIFIED BY password; ALTER USER psmith IDENTIFIED BY password; CREATE DATABASE LINK AUTHENTICATED BY psmith IDENTIFIED BY password;
See Also:
"Enforcing Password Complexity Verification" for ways that you can ensure that passwords are sufficiently complex for your site
"Guidelines for Securing Passwords" for more ways to secure passwords
"Securing Passwords in Application Design" for password protection guidelines application developers should follow
Oracle Database SQL Language Reference for more information about the CREATE USER
, ALTER USER
, GRANT
, and CREATE DATABASE LINK
SQL statements
This section contains:
Disabling and Enabling the Default Password Security Settings
Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value
See Also:
Oracle Database SQL Language Reference for syntax and specific information about SQL statements discussed in this section
Database security systems that depend on passwords require that passwords be kept secret at all times. Because passwords are vulnerable to theft, forgery, and misuse, Oracle Database uses a password management policy. Database administrators and security officers control this policy through user profiles, enabling greater control of database security.
Use the CREATE PROFILE
statement to create a user profile. The profile is assigned to a user with the CREATE USER
or ALTER USER
statement. Details of creating and altering database users are not discussed in this section. This section describes password parameters that can be specified using the CREATE PROFILE
(or ALTER PROFILE
) statement.
When you create a database in Oracle Database 11g Release 2 (11.2), most of its default accounts are locked with the passwords expired. If you have upgraded from an earlier release of Oracle Database, you may have user accounts that have default passwords. These are default accounts that are created when you create a database, such as the HR
, OE
, and SCOTT
accounts.
For greater security, change the passwords for these accounts. Using a default password that is commonly known can make your database vulnerable to attacks by intruders. To find both locked and unlocked accounts that use default passwords, log onto SQL*Plus using the SYSDBA
privilege and then query the DBA_USERS_WITH_DEFPWD
data dictionary view.
For example, to find both the names of accounts that have default passwords and the status of the account:
SELECT d.username, u.account_status FROM DBA_USERS_WITH_DEFPWD d, DBA_USERS u WHERE d.username = u.username ORDER BY 2,1; USERNAME ACCOUNT_STATUS --------- --------------------------- SCOTT EXPIRED & LOCKED
Then change the passwords for any accounts that the DBA_USERS_WITH_DEFPWD
view lists. Oracle recommends that you do not assign these accounts passwords that they may have had in previous releases of Oracle Database.
ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY password;
Replace password
with a password that is secure. "Minimum Requirements for Passwords" describes the minimum requirements for passwords.
A profile is a collection of parameters that sets limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits. You can use profiles to configure database settings such as sessions per user, logging and tracing features, and so on. Profiles can also control user passwords. To find information about the current password settings in the profile, you can query the DBA_PROFILES
data dictionary view.
Table 3-1 lists the password-specific parameter settings in the default profile.
Table 3-1 Password-Specific Settings in the Default Profile
Parameter | Default Setting | Description |
---|---|---|
|
Sets the maximum times a user try to log in and to fail before locking the account. Notes:
|
|
|
Sets the number of days that a user has to change his or her password before it expires. See "Controlling Password Aging and Expiration" for more information. |
|
|
Sets the number of days the user can use his or her current password. See "Controlling Password Aging and Expiration" for more information. |
|
|
Sets the number of days an account will be locked after the specified number of consecutive failed login attempts. After the time passes, then the account becomes unlocked. This user's profile parameter is useful to help prevent brute force attacks on user passwords but not to increase the maintenance burden on administrators. See "Automatically Locking a User Account After a Failed Login" for more information. |
|
|
Sets the number of password changes required before the current password can be reused. See "Controlling User Ability to Reuse Previous Passwords" for more information. |
|
|
Sets the number of days before which a password cannot be reused. See "Controlling User Ability to Reuse Previous Passwords" for more information. |
For greater security, use the default settings described in Table 3-1, based on your needs. You can create or modify the password-specific parameters individually by using the CREATE PROFILE
or ALTER PROFILE
statement. For example:
ALTER PROFILE prof FAILED_LOGIN_ATTEMPTS 9 PASSWORD_LOCK_TIME 10;
See Oracle Database SQL Language Reference for more information about CREATE PROFILE
, ALTER PROFILE
, and the password-related parameters described in this section.
If your applications use the default password security settings from Oracle Database 10g Release 2 (10.2), then you can revert to these settings until you modify the applications to use the Release 11g password security settings. To do so, run the undopwd.sql
script.
After you have modified your applications to conform to the Release 11g password security settings, you can manually update your database to use the password security configuration that suits your business needs, or you can run the secconf.sql
script to apply the Release 11g default password settings. You can customize this script to have different security settings if you like, but remember that the settings listed in the original script are Oracle-recommended settings.
If you created your database manually, then you should run the secconf.sql
script to apply the Release 11g default password settings to the database. Databases that have been created with Database Configuration Assistant (DBCA) will have these settings, but manually created databases do not.
The undopwd.sql
and secconf.sql
scripts are in the $ORACLE_HOME/rdbms/admin
directory. The undopwd.sql
script affects password settings only, and the secconf.sql
script affects both password and audit settings. They have no effect on other security settings.
Oracle Database can lock a user's account after a specified number of consecutive failed log-in attempts. You can set the PASSWORD_LOCK_TIME
user's profile parameter to configure the account to unlock automatically after a specified time interval or to require database administrator intervention to be unlocked. The database administrator also can lock accounts manually, so that they must be unlocked explicitly by the database administrator.
You can specify the permissible number of failed login attempts by using the CREATE PROFILE
statement. You can also specify the amount of time accounts remain locked.
Example 3-2 sets the maximum number of failed login attempts for the user johndoe
to 10 (the default), and the amount of time the account locked to 30 days. The account will unlock automatically after 30 days.
Example 3-2 Locking an Account with the CREATE PROFILE Statement
CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME 30; ALTER USER johndoe PROFILE prof;
Each time the user unsuccessfully logs in, Oracle Database increases the delay exponentially with each login failure.
If you do not specify a time interval for unlocking the account, then PASSWORD_LOCK_TIME
assumes the value specified in a default profile. (The recommended value is 1 day.) If you specify PASSWORD_LOCK_TIME
as UNLIMITED
, then you must explicitly unlock the account by using an ALTER USER
statement. For example, assuming that PASSWORD_LOCK_TIME
UNLIMITED
is specified for johndoe
, then you use the following statement to unlock the johndoe
account:
ALTER USER johndoe ACCOUNT UNLOCK;
After a user successfully logs into an account, Oracle Database resets the unsuccessful login attempt count for the user, if it is non-zero, to zero.
The security officer can also explicitly lock user accounts. When this occurs, the account cannot be unlocked automatically, and only the security officer should unlock the account. The CREATE USER
or ALTER USER
statements explicitly lock or unlock user accounts. For example, the following statement locks the user account, susan
:
ALTER USER susan ACCOUNT LOCK;
You can ensure that users do not reuse their previous passwords for a specified amount of time or for a specified number of password changes. To do so, configure the rules for password reuse with CREATE
PROFILE
or ALTER PROFILE
statements. For the complete syntax of these statements, see the Oracle Database SQL Language Reference.
Table 3-2 lists the CREATE PROFILE
and ALTER PROFILE
parameters that control ability of a user to reuse a previous password.
Table 3-2 Parameters Controlling Reuse of a Previous Password
If you do not specify a parameter, then the user can reuse passwords at any time, which is not a good security practice.
If neither parameter is UNLIMITED
, then password reuse is allowed, but only after meeting both conditions. The user must have changed the password the specified number of times, and the specified number of days must have passed since the previous password was last used.
For example, suppose that the profile of user A had PASSWORD_REUSE_MAX
set to 10
and PASSWORD_REUSE_TIME
set to 30
. User A cannot reuse a password until he or she has reset the password 10 times, and until 30 days had passed since the password was last used.
If either parameter is specified as UNLIMITED
, then the user can never reuse a password.
If you set both parameters to UNLIMITED
, then Oracle Database ignores both, and the user can reuse any password at any time.
Note:
If you specifyDEFAULT
for either parameter, then Oracle Database uses the value defined in the DEFAULT
profile, which sets all parameters to UNLIMITED
. Oracle Database thus uses UNLIMITED
for any parameter specified as DEFAULT
, unless you change the setting for that parameter in the DEFAULT
profile.You can specify a password lifetime, after which the password expires and must be changed before logging into the account is permitted again. In addition, you can set a grace period, during which each attempt to log in to the database account receives a warning message to change the password. If the user does not change it by the end of that period, then Oracle Database expires the account. No further logins to that account are allowed without assistance by the database administrator.
You can also manually set the password state to expired, which sets the user account status to expired. The user or the database administrator must then change the password, using either the PASSWORD
or ALTER USER
statement, before the user can log in to the database.
Use the CREATE PROFILE
or ALTER PROFILE
statement to specify a maximum lifetime for passwords. When the specified amount of time passes and the password expires, the user or DBA must change the password.
Example 3-3 demonstrates how to create and assign a profile to user johndoe
, and the PASSWORD_LIFE_TIME
parameter specifies that johndoe
can use the same password for 180 days before it expires.
Example 3-3 Setting Password Aging and Expiration with CREATE PROFILE
CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 30 PASSWORD_LIFE_TIME 180; ALTER USER johndoe PROFILE prof;
Accounts are considered to be in grace from the time the account has expired until the account's password profile grace period has elapsed. At this point, Oracle Database locks the account. During the grace period, each time a user attempts to log in to the account, he or she is prompted for a new password, and unless the user supplies a new password for the account (and the new password complies with the password policy guidelines, if the system has enabled password complexity checking), access to the account is denied. If the user fails to change the password for the account after it has expired and while it is in grace, and instead lets the grace period elapse without providing a new password, the account becomes locked, and access to the account is denied.
Figure 3-1 shows the chronology of the password lifetime and grace period.
Figure 3-1 Chronology of Password Lifetime and Grace Period
In the following example, the profile assigned to johndoe
includes the specification of a grace period: PASSWORD_GRACE_TIME = 3
(the recommended value). The first time johndoe
tries to log in to the database after 90 days (this can be any day after the 90th day, that is, the 91st day, 100th day, or another day), he receives a warning message that his password will expire in 3 days. If 3 days pass, and if he does not change his password, then the password expires. After this, he receives a prompt to change his password on any attempt to log in, and cannot log in until he does so.
CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 30 PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 3; ALTER USER johndoe PROFILE prof;
You can explicitly expire a password by using the CREATE USER
and ALTER USER
statements. The following statement creates a user with an expired password. This setting forces the user to change the password before the user can log in to the database.
CREATE USER jbrown
IDENTIFIED BY password
...
PASSWORD EXPIRE;
Be careful if you plan to set the PASSWORD_LIFE_TIME
parameter of CREATE PROFILE
or ALTER PROFILE
to a low value (for example, 1 day). If the user who is assigned this profile is concurrently logged in when you make this modification, then Oracle Database sets the user's account status from OPEN
to EXPIRED(GRACE)
with the warning error ORA-28002: the password will expire within n days
. You may not be notified of this change because the user can still connect to the Oracle database. You can find the concurrently logged in users by querying the USERNAME
column of the V$SESSION
dynamic performance view.
Note the following:
If the user is not logged in when you set PASSWORD_LIFE_TIME
to a low value, then the user's account status does not change when the user does log in.
You can set the PASSWORD_LIFE_TIME
parameter to UNLIMITED
, but this only affects accounts that have not entered their grace period. If the user has already entered the grace period, then he or she must change the password.
Complexity verification checks that each password is complex enough to provide reasonable protection against intruders who try to break into the system by guessing passwords. This forces users to create strong, secure passwords for database user accounts. You need to ensure that the passwords for your users are complex enough to provide reasonable protection against intruders who try to break into the system by guessing passwords.
How Oracle Database Checks the Complexity of Passwords
Oracle Database provides a sample password verification function in the PL/SQL script UTLPWDMG.SQL
(located in ORACLE_BASE
/
ORACLE_HOME
/RDBMS/ADMIN
) that, when enabled, checks whether users are correctly creating or modifying their passwords. The UTLPWDMG.SQL
script provides two password verification functions: one for previous releases of Oracle Database and an updated version for Oracle Database Release 11g.
The UTLPWDMG.SQL
script checks for the following requirements when users create or modify passwords:
The password contains no fewer than 8 characters and does not exceed 30 characters.
The password is not the same as the user name, nor is it the user name spelled backward or with the numbers 1–100 appended.
The password is not the same as the server name or the server name with the numbers 1–100 appended.
The password is not too simple, for example, welcome1
, database1
, account1
, user1234
, password1
, oracle
, oracle123
, computer1
, abcdefg1
, or change_on_install
.
The password is not oracle
or oracle
with the numbers 1–100 appended.
The password includes at least 1 numeric and 1 alphabetic character.
The password differs from the previous password by at least 3 letters.
Customizing Password Complexity Verification
You can create your own password complexity verification function by backing up and customizing the PASSWORD_VERIFY
function in the UTLPWDMG.SQL
script. In fact, Oracle recommends that you do so to further secure your site's passwords. See also Guideline 1 in "Guidelines for Securing Passwords" for general advice on creating passwords. However, be aware that the password complexity checking is not enforced for user SYS
.
By default, password complexity verification is not enabled. To enable the password complexity verification:
Log in to SQL*Plus with administrative privileges and then run the UTLPWDMG.SQL
script (or your modified version of this script) to create the password complexity function in the SYS
schema.
CONNECT SYS/AS SYSDBA
Enter password: password
@$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql
In the default profile or the user profile, set the PASSWORD_VERIFY_FUNCTION
setting to either the sample password complexity function in the UTLPWDMG.SQL
script, or to your customized function. Use one of the following methods:
Log in to SQL*Plus with administrator privileges and use the CREATE PROFILE
or ALTER PROFILE
statement to enable the function. For example, to update the default profile to use the verify_function_11G
function:
ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11G;
In Oracle Enterprise Manager, go to the Edit Profiles page and then under Complexity, select the name of the password complexity function from the Complexity function list.
After you have enabled password complexity verification, it takes effect immediately.
Note:
TheALTER USER
statement has a REPLACE
clause. With this clause, users can change their own unexpired passwords by supplying the previous password to authenticate themselves.
If the password has expired, then the user cannot log in to SQL to issue the ALTER USER
command. Instead, the OCIPasswordChange()
function must be used, which also requires the previous password.
A database administrator with ALTER ANY USER
privilege can change any user password (force a new password) without supplying the old one.
This section contains:
About Enabling or Disabling Password Case Sensitivity
When you create or modify user accounts, by default, passwords are case sensitive. To control the use of case sensitivity in passwords, set the SEC_CASE_SENSITIVE_LOGON
initialization parameter. Only users who have the ALTER SYSTEM
privilege can set the SEC_CASE_SENSITIVE_LOGON
parameter. Set it to TRUE
to enable case sensitivity or FALSE
to disable case sensitivity.
For greater security, Oracle recommends that you enable case sensitivity in passwords. However, if you have compatibility issues with your applications, you can use this parameter to disable password case sensitivity. Examples of application compatibility issues are passwords for your applications being hard-coded to be case insensitive, or different application modules being inconsistent about case sensitivity when sending credentials to start a database session.
Do not set the SEC_CASE_SENSITIVE_LOGON
parameter to FALSE
when exclusive mode is enabled (the SQLNET.ALLOWED_LOGON_VERSION
parameter is set to 11)
, because the more secure verifiers used in exclusive mode only support case-sensitive password checking. For compatibility reasons, Oracle Database does not prevent the use of the FALSE
setting for SEC_CASE_SENSITIVE_LOGON
when the SQLNET.ALLOWED_LOGON_VERSION
parameter is set to 11
. This can result in accounts becoming inaccessible if these settings are in effect when users change their passwords or you create new user accounts.
Procedure for Enabling Password Case Sensitivity
To enable case sensitivity in passwords:
If you are using a password file, ensure that it was created with the IGNORECASE
parameter set to N
.
The IGNORECASE
parameter overrides the SEC_CASE_SENSITIVE_LOGON
parameter. By default, IGNORECASE
is set to Y
, which means that passwords are treated as case-insensitive. For more information about password files, see Oracle Database Administrator's Guide.
Enter the following ALTER SYSTEM
statement:
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE
Finding the Password Versions of User Accounts
In previous releases of Oracle Database, passwords were not case sensitive. If you import user accounts from a previous release, for example, Release 10g, into the current database release, the case-insensitive passwords in these accounts remain case insensitive until the user changes his or her password. If the account was granted SYSDBA
or SYSOPER
privilege, it is imported to the password file. (See "How Case Sensitivity Affects Password Files" for more information.) When a password from a user account from the previous release is changed, it then becomes case sensitive.
You can find users who have case sensitive or case insensitive passwords by querying the DBA_USERS
view. The PASSWORD_VERSIONS
column in this view indicates the release in which the password was created. For example:
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS; USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- JONES 10G 11G ADAMS 10G 11G CLARK 10G 11G PRESTON 11G BLAKE 10G
The passwords for accounts jones
, adams
, and clark
were originally created in Release 10g and then reset in Release 11g. Their passwords, assuming case sensitivity has been enabled, are now case sensitive, as is the password for preston
. However, the account for blake
is still using the Release 10g standard, so it is case insensitive. Ask him to reset his password so that it will be case sensitive, and therefore more secure.
See Oracle Database Reference for more information about the DBA_USERS
view.
How Case Sensitivity Affects Password Files
You can enable or disable case sensitivity for password files by using the ignorecase
argument in the ORAPWD
command line utility. The default value for ignorecase
is n
(no), which enforces case sensitivity.
Example 3-4 shows how to enable case sensitivity in password files.
Example 3-4 Enabling Password Case Sensitivity
orapwd file=orapw entries=100 ignorecase=n
Enter password for SYS: password
This creates a password file called orapwd
. Because ignorecase
is set to n (no), the password entered for the password
parameter will be case sensitive. Afterwards, if you connect using this password, it succeeds—as long as you enter it using the exact case sensitivity in which it was created. If you enter the same password but with different case sensitivity, it will fail.
If you set ignorecase
to y
, then the passwords in the password file are case insensitive, which means that you can enter the password using any capitalization that you want.
If you imported user accounts from a previous release and these accounts were created with SYSDBA
or SYSOPER
privileges, then they will be included in the password file. The passwords for these accounts are case insensitive. The next time these users change their passwords, and assuming case sensitivity is enabled, the passwords become case sensitive. For greater security, have these users change their passwords.
See Oracle Database Administrator's Guide for more information about password files.
How Case Sensitivity Affects Accounts Created for Database Link Connections
When you create a database link connection, you must define a user name and password for the connection. When you create the database link connection, the password is case sensitive. How this user enters his or her password for connections depends on the release in which the database link was created:
Users can connect from a pre-Release 11g database to a Release 11g database. If case sensitivity is disabled in the Release 11g database, then the user can enter the password using any case. If case sensitivity is enabled, however, then the user must enter the password using the case in which the password was created in the Release 11g database.
If the user connecting from a Release 11g database to a pre-Release 11g database, he or she can enter his or her password using any case, because the password is still case insensitive.
You can find the user accounts for existing database links by running the V$DBLINK
view. For example:
SELECT DB_LINK, OWNER_ID FROM V$DBLINK;
See Oracle Database Reference for more information about the V$DBLINK
view.
The SHA-1 cryptographic hashing algorithm protects against password-based security threats by including support for mixed case characters, special characters, and multibyte characters in passwords. In addition, the SHA-1 hashing algorithm adds a salt to the password when it is hashed, which provides additional protection. This enables your users to create far more complex passwords, and therefore, makes it more difficult for an intruder to gain access to these passwords. Oracle recommends that you use the SHA-1 hashing algorithm.
The password versions (also known as password hash values) are considered to be extremely sensitive, because they are used as a "shared secret" between the server and person who is logging in. If an intruder learns this secret, then the protection of the authentication is immediately and severely compromised. Remember that administrative users who have account management privileges, administrative users who have the SYSDBA
system privilege, or even users who have the EXP_FULL_DATABASE
role can immediately access the password hash values. Therefore, this type of administrative user must be trustworthy if the integrity of the database password-based authentication is to be preserved. If you cannot trust these administrators, then it is better to deploy a directory server (such as Oracle Database Enterprise User Security) so that the password versions remain within the Enterprise User Security directory and are never accessible to anyone except the Enterprise User Security administrator.
You optionally can configure Oracle Database to run in exclusive mode for Release 11 or later. When you enable exclusive mode, then Oracle Database uses the SHA-1 hashing algorithm exclusively. Oracle Database 11g exclusive mode is compatible with Oracle Database 10g and later products that use OCI-based drivers, including SQL*Plus, ODBC, Oracle .NET, Oracle Forms, and various third-party Oracle Database adapters. However, be aware that exclusive mode for Release 11g is not compatible with JDBC type-4 (thin) versions earlier than Oracle Database 11g or Oracle Database Client interface (OCI)-based drivers earlier than Oracle Database 10g. After you configure exclusive mode, Oracle recommends that you remove the previous password hash values from the data dictionary.
Enable exclusive mode.
Create a back up copy of the sqlnet.ora
parameter file, by default located in the $ORACLE_HOME/network/admin
directory on UNIX operating systems and the %ORACLE_HOME%\network\admin
directory on Microsoft Windows operating systems.
Ensure that the sqlnet.ora
file has the following line:
SQLNET.ALLOWED_LOGON_VERSION=12
If you have applied the October 2012 CPU or if you are using Oracle Database Release 11.2.0.3, then ensure that you set SQLNET.ALLOWED_LOGON_VERSION
to 12, not 11
.
Save and exit the sqlnet.ora
file.
Verify that the passwords in test scripts or batch jobs are consistent in their use of mixed case and special characters.
Change all passwords to include mixed case and special characters.
Oracle recommends that you use random passwords with a length of at least twelve characters. See Guideline 1 under "Guidelines for Securing Passwords" for additional guidelines for creating passwords, and techniques for creating complex but easy to remember passwords.
This section contains:
You can store password credentials for connecting to databases by using a client-side Oracle wallet. An Oracle wallet is a secure software container that stores authentication and signing credentials.
This wallet usage can simplify large-scale deployments that rely on password credentials for connecting to databases. When this feature is configured, application code, batch jobs, and scripts no longer need embedded user names and passwords. This reduces risk because the passwords are no longer exposed, and password management policies are more easily enforced without changing application code whenever user names or passwords change.
See Also:
"Using Proxy Authentication with the Secure External Password Store"
Oracle Database Advanced Security Administrator's Guide for general information about Oracle wallets
Note:
The external password store of the wallet is separate from the area where public key infrastructure (PKI) credentials are stored. Consequently, you cannot use Oracle Wallet Manager to manage credentials in the external password store of the wallet. Instead, use the command-line utilitymkstore
to manage these credentials.Typically, users (and as applications, batch jobs, and scripts) connect to databases by using a standard CONNECT
statement that specifies a database connection string. This string can include a user name and password, and an Oracle Net service name identifying the database on an Oracle Database network. If the password is omitted, the connection prompts the user for the password.
For example, the service name could be the URL that identifies that database, or a TNS alias you entered in the tnsnames.ora
file in the database. Another possibility is a host:port:sid
string.
The following examples are standard CONNECT
statements that could be used for a client that is not configured to use the external password store:
CONNECT salesapp@sales_db.us.example.com Enter password: password CONNECT salesapp@orasales Enter password: password CONNECT salesapp@ourhost37:1527:DB17 Enter password: password
In these examples, salesapp
is the user name, with the unique connection string for the database shown as specified in three different ways. You could use its URL sales_db.us.example.com
, or its TNS alias orasales
from the tnsnames.ora
file, or its host:port:sid
string.
However, when clients are configured to use the secure external password store, applications can connect to a database with the following CONNECT
statement syntax, without specifying database login credentials:
CONNECT /@db_connect_string CONNECT /@db_connect_string AS SYSDBA CONNECT /@db_connect_string AS SYSOPER
In this specification, db_connect_string
is a valid connection string to access the intended database, such as the service name, URL, or alias as shown in the earlier examples. Each user account must have its own unique connection string; you cannot create one connection string for multiple users.
In this case, the database credentials, user name and password, are securely stored in an Oracle wallet created for this purpose. The autologin feature of this wallet is turned on, so the system does not need a password to open the wallet. From the wallet, it gets the credentials to access the database for the user they represent.
See Also:
Oracle Database Advanced Security Administrator's Guide for information about autologin walletsIf your client is already configured to use external authentication, such as Windows native authentication or Secure Sockets Layer (SSL), then Oracle Database uses that authentication method. The same credentials used for this type of authentication are typically also used to log in to the database.
For clients not using such authentication methods or wanting to override them for database authentication, you can set the SQLNET.WALLET_OVERRIDE
parameter in sqlnet.ora
to TRUE
. The default value for SQLNET.WALLET_OVERRIDE
is FALSE
, allowing standard use of authentication credentials as before.
If you want a client to use the secure external password store feature, then perform the following configuration task:
Create a wallet on the client by using the following syntax at the command line:
mkstore -wrl wallet_location -create
For example:
mkstore -wrl c:\oracle\product\11.2.0\db_1\wallets -create
Enter password: password
wallet_location
is the path to the directory where you want to create and store the wallet. This command creates an Oracle wallet with the autologin feature enabled at the location you specify. The autologin feature enables the client to access the wallet contents without supplying a password. See Oracle Database Advanced Security Administrator's Guide for information about autologin wallets.
The mkstore
utility -create
option uses password complexity verification. See "Enforcing Password Complexity Verification" for more information.
Create database connection credentials in the wallet by using the following syntax at the command line:
mkstore -wrl wallet_location -createCredential db_connect_string username Enter password: password
For example:
mkstore -wrl c:\oracle\product\11.2.0\db_1\wallets -createCredential orcl system
Enter password: password
In this specification:
wallet_location
is the path to the directory where you created the wallet in Step 1.
db_connect_string
is the TNS alias you use to specify the database in the tnsnames.ora
file or any service name you use to identify the database on an Oracle network. By default, tnsnames.ora
is located in the $ORACLE_HOME/network/admin
directory on UNIX systems and in ORACLE_HOME
\network\admin
on Windows.
username
is the database login credential. When prompted, enter the password for this user.
Repeat this step for each database you want accessible using the CONNECT /@
db_connect_string
syntax.
Note:
Thedb_connect_string
used in the CONNECT /@
db_connect_string
statement must be identical to the db_connect_string
specified in the -createCredential
command.In the client sqlnet.ora
file, enter the WALLET_LOCATION
parameter and set it to the directory location of the wallet you created in Step 1.
For example, if you created the wallet in $ORACLE_HOME/network/admin
and your Oracle home is set to /private/ora11
, then you need to enter the following into your client sqlnet.ora
file:
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /private/ora11/network/admin)
)
)
In the client sqlnet.ora
file, enter the SQLNET.WALLET_OVERRIDE
parameter and set it to TRUE
as follows:
SQLNET.WALLET_OVERRIDE = TRUE
This setting causes all CONNECT /@
db_connect_string
statements to use the information in the wallet at the specified location to authenticate to databases.
When external authentication is in use, an authenticated user with such a wallet can use the CONNECT /@
db_connect_string
syntax to access the previously specified databases without providing a user name and password. However, if a user fails that external authentication, then these connect statements also fail.
Note:
If an application uses SSL for encryption, then thesqlnet.ora
parameter, SQLNET.AUTHENTICATION_SERVICES
, specifies SSL and an SSL wallet is created. If this application wants to use secret store credentials to authenticate to databases (instead of the SSL certificate), then those credentials must be stored in the SSL wallet. After SSL authentication, if SQLNET.WALLET_OVERRIDE = TRUE
, then the user names and passwords from the wallet are used to authenticate to databases. If SQLNET.WALLET_OVERRIDE = FALSE
, then the SSL certificate is used.Example 3-5 shows a sample sqlnet.ora
file with the WALLET_LOCATION
and the SQLNET.WALLET_OVERRIDE
parameters set as described in Steps 3 and 4.
This section summarizes the following tasks you can perform to manage credentials in the external password store by using the mkstore
command-line utility:
Periodically, you may want to view all contents of a client wallet external password store, or you may need to check specific credentials by viewing them. Listing the external password store contents provides information you can use to decide whether to add or delete credentials from the store.
To list the contents of the external password store, enter the following command at the command line:
mkstore -wrl wallet_location -listCredential
For example:
mkstore -wrl c:\oracle\product\11.2.0\db_1\wallets -listCredential
wallet_location
specifies the path to the directory where the wallet, whose external password store contents you want to view, is located. This command lists all of the credential database service names (aliases) and the corresponding user name (schema) for that database. Passwords are not listed.
You can store multiple credentials in one client wallet. For example, if a client batch job connects to hr_database
and a script connects to sales_database
, then you can store the login credentials in the same client wallet. You cannot, however, store multiple credentials (for logging in to multiple schemas) for the same database in the same wallet. If you have multiple login credentials for the same database, then they must be stored in separate wallets.
To add database login credentials to an existing client wallet, enter the following command at the command line:
mkstore -wrl wallet_location -createCredential db_alias username
For example:
mkstore -wrl c:\oracle\product\11.2.0\db_1\wallets -createCredential orcl system
Enter password: password
In this specification:
wallet_location
is the path to the directory where the client wallet to which you want to add credentials is stored.
db_alias
can be the TNS alias you use to specify the database in the tnsnames.ora
file or any service name you use to identify the database on an Oracle network.
username
is the database login credential for the schema to which your application connects. When prompted, enter the password for this user.
If the database connection strings change, then you can modify the database login credentials that are stored in the wallet.
To modify database login credentials in a wallet, enter the following command at the command line:
mkstore -wrl wallet_location -modifyCredential dbase_alias username
For example:
mkstore -wrl c:\oracle\product\11.2.0\db_1\wallets -modifyCredential sales_db
Enter password: password
In this specification:
wallet_location
is the path to the directory where the wallet is located.
db_alias
is a new or different alias you want to use to identify the database. It can be a TNS alias you use to specify the database in the tnsnames.ora
file or any service name you use to identify the database on an Oracle network.
username
is the new or different database login credential. When prompted, enter the password for this user.
If a database no longer exists or if you want to disable connections to a specific database, then you can delete all login credentials for that database from the wallet.
To delete database login credentials from a wallet, enter the following command at the command line:
mkstore -wrl wallet_location -deleteCredential db_alias
For example:
mkstore -wrl c:\oracle\product\11.2.0\db_1\wallets -deleteCredential orcl
In this specification:
wallet_location
is the path to the directory where the wallet is located.
db_alias
is the TNS alias you use to specify the database in the tnsnames.ora
file, or any service name you use to identify the database on an Oracle Database network.
Database administrators perform special operations, such as shutting down or starting up a database, that should not be performed by non-administrative database users. Oracle Database provides the following methods to secure the authentication of database administrators who have either SYSDBA
or SYSOPER
privileges:
Strong Authentication and Centralized Management for Database Administrators
Authenticating Database Administrators by Using the Operating System
Authenticating Database Administrators by Using Their Passwords
Strong authentication lets you centrally control SYSDBA
and SYSOPER
access to multiple databases. Consider using this type of authentication for database administration for the following situations:
You have concerns about password file vulnerability.
Your site has very strict security requirements.
You want to separate the identity management from your database. By using a directory server such as Oracle Internet Directory (OID), for example, you can maintain, secure, and administer that server separately.
To enable the Oracle Internet Directory server to authorize SYSDBA
and SYSOPER
connections, use one of the following methods, depending on your environment:
Configuring Directory Authentication for Administrative Users
Configuring Kerberos Authentication for Administrative Users
Configuring Secure Sockets Layer Authentication for Administrative Users
To configure directory authentication for administrative users:
Configure the administrative user by using the same procedures you would use to configure a typical user.
In Oracle Internet Directory, grant the SYSDBA
or SYSOPER
privilege to the user for the database that this user will administer.
Grant SYSDBA
or SYSOPER
only to trusted users. See "Guidelines for Securing User Accounts and Privileges" for advice on this topic.
Set the LDAP_DIRECTORY_SYSAUTH
initialization parameter to YES
:
ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH = YES;
When set to YES
, the LDAP_DIRECTORY_SYSAUTH
parameter enables SYSDBA
and SYSOPER
users to authenticate to the database by using a strong authentication method.
See Oracle Database Reference for more information about LDAP_DIRECTORY_SYSAUTH
.
Set the LDAP_DIRECTORY_ACCESS
parameter to either PASSWORD
or SSL
. For example:
ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = PASSWORD;
Ensure that the LDAP_DIRECTORY_ACCESS
initialization parameter is not set to NONE
. Setting this parameter to PASSWORD
or SSL
ensures that users can be authenticated using the SYSDBA
or SYSOPER
privileges through Oracle Internet Directory. See Oracle Database Reference for more information about LDAP_DIRECTORY_ACCESS
.
Afterward, this user can log in by including the net service name in the CONNECT
statement in SQL*Plus. For example, to log on as SYSDBA
if the net service name is orcl
:
CONNECT SOMEUSER@ORCL AS SYSDBA
Enter password: password
If the database is configured to use a password file for remote authentication, Oracle Database checks the password file first.
To configure Kerberos authentication for administrative users:
Configure the administrative user by using the same procedures you would use to configure a typical user.
See Oracle Database Advanced Security Administrator's Guide for more information.
Configure Oracle Internet Directory for Kerberos authentication.
See Oracle Database Enterprise User Security Administrator's Guide for more information.
In Oracle Internet Directory, grant the SYSDBA
or SYSOPER
privilege to the user for the database that this user will administer.
Grant SYSDBA
or SYSOPER
only to trusted users. See "Guidelines for Securing User Accounts and Privileges" for advice on this topic.
Set the LDAP_DIRECTORY_SYSAUTH
initialization parameter to YES
:
ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH = YES;
When set to YES
, the LDAP_DIRECTORY_SYSAUTH
parameter enables SYSDBA
and SYSOPER
users to authenticate to the database by using strong authentication methods. See Oracle Database Reference for more information about LDAP_DIRECTORY_SYSAUTH
.
Set the LDAP_DIRECTORY_ACCESS
parameter to either PASSWORD
or SSL
. For example:
ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = SSL;
Ensure that the LDAP_DIRECTORY_ACCESS
initialization parameter is not set to NONE
. Setting this parameter to PASSWORD
or SSL
ensures that users can be authenticated using SYSDBA
or SYSOPER
through Oracle Internet Directory. See Oracle Database Reference for more information about LDAP_DIRECTORY_ACCESS
.
Afterward, this user can log in by including the net service name in the CONNECT
statement in SQL*Plus. For example, to log on as SYSDBA
if the net service name is orcl
:
CONNECT /@orcl AS SYSDBA
To configure Secure Sockets Layer (SSL) authentication for administrative users:
Configure the client to use SSL:
Configure the client wallet and user certificate. Update the wallet location in the sqlnet.ora
configuration file.
You can use Wallet Manager to configure the client wallet and user certificate. See Oracle Database Advanced Security Administrator's Guide for more information.
Configure the Oracle net service name to include server DNs and use TCP/IP with SSL in tnsnames.ora
.
Configure TCP/IP with SSL in listener.ora
.
Set the client SSL cipher suites and the required SSL version, and then set SSL as an authentication service in sqlnet.ora
.
Configure the server to use SSL:
Enable SSL for your database listener on TCPS and provide a corresponding TNS name. You can use Net Configuration Assistant to configure the TNS name.
Store the database PKI credentials in the database wallet. You can use Wallet Manager do this.
Set the LDAP_DIRECTORY_ACCESS
initialization parameter to SSL
:
ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = SSL;
See Oracle Database Reference for more information about LDAP_DIRECTORY_ACCESS
.
Configure Oracle Internet Directory for SSL user authentications.
See Oracle Database Enterprise User Security Administrator's Guide for information on configuring enterprise user security SSL authentication.
In Oracle Internet Directory, grant the SYSDBA
or SYSOPER
privilege to the user for the database that the user will administer.
On the server computer, set the LDAP_DIRECTORY_SYSAUTH
initialization parameter to YES
.
ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH = YES;
When set to YES
, the LDAP_DIRECTORY_SYSAUTH
parameter enables SYSDBA
and SYSOPER
users to authenticate to the database by using a strong authentication method. See Oracle Database Reference for more information about LDAP_DIRECTORY_SYSAUTH
.
Afterward, this user can log in by including the net service name in the CONNECT
statement in SQL*Plus. For example, to log on as SYSDBA
if the net service name is orcl
:
CONNECT /@orcl AS SYSDBA
Operating system authentication for a database administrator typically involves establishing a group on the operating system, granting DBA
privileges to that group, and then adding the names of persons who should have those privileges to that group. (On UNIX systems, the group is the dba group.)
On Microsoft Windows systems, users who connect with the SYSDBA
privilege can take advantage of the Windows native authentication. If these users work with Oracle Database using their domain accounts, then you must explicitly grant them local administrative privileges and ORA_DBA
membership.
See Also:
Your Oracle Database operating system-specific documentation for information about configuring operating system authentication of database administratorsOracle Database uses database-specific password files to keep track of database user names that have been granted the SYSDBA
and SYSOPER
privileges. These privileges enable the following activities:
The SYSOPER
system privilege lets database administrators perform STARTUP
, SHUTDOWN
, ALTER DATABASE
OPEN/MOUNT
, ALTER
DATABASE
BACKUP
, ARCHIVE
LOG
, and RECOVER
operations. SYSOPER
also includes the RESTRICTED
SESSION
privilege.
The SYSDBA
system privilege has all system privileges with ADMIN
OPTION
, including the SYSOPER
system privilege, and permits CREATE
DATABASE
and time-based recovery.
A password file containing users with SYSDBA
or SYSOPER
privileges can be shared between different databases. You can have a shared password file that contains users in addition to the SYS
user. To share a password file among different databases, set the REMOTE_LOGIN_PASSWORDFILE
parameter in the init.ora
file to SHARED
.
If you set the REMOTE_LOGIN_PASSWORDFILE
initialization parameter to EXCLUSIVE
or SHARED
from NONE
, then ensure that the password file is in sync with the dictionary passwords. See Oracle Database Administrator's Guide for more information.
Password file-based authentication is enabled by default. This means that the database is ready to use a password file for authenticating users that have SYSDBA
or SYSOPER
system privileges. Password file based authentication is activated as soon as you create a password file using the ORAPWD
utility.
Anyone who has EXECUTE
privileges and write privileges to the $ORACLE_HOME/dbs
directory can run the ORAPWD
utility.
However, be aware that using password files may pose security risks. For this reason, consider using the authentication methods described in "Strong Authentication and Centralized Management for Database Administrators". Examples of password security risks are as follows:
An intruder could steal or attack the password file.
Many users do not change the default password.
The password could be easily guessed.
The password is vulnerable if it can be found in a dictionary.
Passwords that are too short, chosen perhaps for ease of typing, are vulnerable if an intruder obtains the cryptographic hash of the password.
Note:
Connections requestedAS SYSDBA
or AS SYSOPER
must use these phrases; without them, the connection fails. The Oracle Database parameter O7_DICTIONARY_ACCESSIBILITY
is set to FALSE
by default, to limit sensitive data dictionary access only to those authorized. The parameter also enforces the required AS SYSDBA
or AS SYSOPER
syntax.See Also:
Oracle Database Administrator's Guide for information about creating and maintaining password filesOracle Database can authenticate users attempting to connect to a database by using information stored in that database itself. To configure Oracle Database to use database authentication, you must create each user with an associated password. User names can be multibyte, but each password must be composed of single-byte characters, even if your database uses a multibyte character set. The user must provide this user name and password when attempting to establish a connection. Oracle Database stores user passwords in the data dictionary in an encrypted format.
To identify the authentication protocols that are allowed by a client or a database, a database administrator can explicitly set the SQLNET.ALLOWED_LOGON_VERSION
parameter in the server sqlnet.ora
file. Each connection attempt is tested, and if the client or server does not meet the minimum version specified by its partner, authentication fails with an ORA-28040 No matching authentication protocol
error. The parameter can take the values 11, 10, 9, or 8. The default value is 8. These values represent database server versions. Oracle recommends the value 11 for the strongest protection. However, be aware that if you set SQLNET.ALLOWED_LOGON_VERSION
to 11, then pre-Oracle Database Release 11.1 client applications or JDBC thin clients cannot authenticate to the Oracle database using password-based authentication.
To enhance security when using database authentication, Oracle recommends that you use password management, including account locking, password aging and expiration, password history, and password complexity verification. See "Using a Password Management Policy" for more information about password management.
The advantages of database authentication are as follows:
User accounts and all authentication are controlled by the database. There is no reliance on anything outside of the database.
Oracle Database provides strong password management features to enhance security when using database authentication.
It is easier to administer when there are small user communities.
Some operating systems permit Oracle Database to use information they maintain to authenticate users. This has the following benefits:
Once authenticated by the operating system, users can connect to Oracle Database more conveniently, without specifying a user name or password. For example, an operating system-authenticated user can invoke SQL*Plus and omit the user name and password prompts by entering the following command at the command line:
SQLPLUS /
Within SQL*Plus, you enter:
CONNECT /
With control over user authentication centralized in the operating system, Oracle Database need not store or manage user passwords, although it still maintains user names in the database.
Audit trails in the database and operating system can use the same user names.
You can authenticate both operating system and non-operating system users in the same system. For example:
Authenticate users by the operating system. You create the user account using the IDENTIFIED EXTERNALLY
clause of the CREATE USER
statement, and then you set the OS_AUTHENT_PREFIX
initialization parameter to specify a prefix that Oracle Database uses to authenticate users attempting to connect to the server.
Authenticate non-operating system users. These are users who are assigned passwords and authenticated by the database.
Authenticate Oracle Database Enterprise User Security users. These user accounts where created using the IDENTIFIED GLOBALLY
clause of the CREATE USER
statement, and then authenticated by Oracle Internet Directory (OID) currently in the same database.
However, you should be aware of the following drawbacks to using the operating system to authenticate users:
A user must have an operating system account on the computer that must be accessed. Not all users have operating system accounts, particularly non-administrative users.
If a user has logged in using this method and steps away from the terminal, another user could easily log in because this user does not need any passwords or credentials. This could pose a serious security problem.
When an operating system is used to authenticate database users, managing distributed database environments and database links requires special care. Operating system-authenticated database links can pose a security weakness. For this reason, Oracle recommends that you do not use them.
See Also:
Oracle Database Administrator's Guide for more information about authentication, operating systems, distributed database concepts, and distributed data management
Operating system-specific documentation by Oracle Database for more information about authenticating by using your operating system
You can authenticate users over a network by using Secure Sockets Layer with third-party services.
The Secure Sockets Layer (SSL) protocol is an application layer protocol. You can use it for user authentication to a database, and it is independent of global user management in Oracle Internet Directory. That is, users can use SSL to authenticate to the database without a directory server in place.
See Oracle Database Advanced Security Administrator's Guide for instructions about configuring SSL.
You need to use third-party network authentication services if you want to authenticate Oracle Database users over a network. Prominent examples include Kerberos, PKI (public key infrastructure), the RADIUS (Remote Authentication Dial-In User Service), and directory-based services, as described in the following sections.
If network authentication services are available to you, then Oracle Database can accept authentication from the network service. If you use a network authentication service, then some special considerations arise for network roles and database links.
Note:
To use a network authentication service with Oracle Database, you need Oracle Database Enterprise Edition with the Oracle Database Advanced Security option.See Also:
Oracle Database Advanced Security Administrator's Guide for information about Oracle Enterprise Edition with the Oracle Database Advanced Security optionAuthenticating Using Kerberos
Kerberos is a trusted third-party authentication system that relies on shared secrets. It presumes that the third party is secure, and provides single sign-on capabilities, centralized password storage, database link authentication, and enhanced PC security. It does this through a Kerberos authentication server, or through Cybersafe Active Trust, a commercial Kerberos-based authentication server.
See Also:
Oracle Database Advanced Security Administrator's Guide for more information about KerberosAuthenticating Using RADIUS
Oracle Database supports remote authentication of users through the Remote Authentication Dial-In User Service (RADIUS), a standard lightweight protocol used for user authentication, authorization, and accounting. This feature also enables users to use the RSA One-Time Password Specifications (OTPS) to authenticate to the Oracle database.
See Also:
Oracle Database Advanced Security Administrator's Guide for information about configuring RADIUS
RSA documentation about OTPS
Authenticating Using Directory-Based Services
Using a central directory can make authentication and its administration efficient. Directory-based services include the following:
Oracle Internet Directory, which uses the Lightweight Directory Access Protocol (LDAP), uses a central repository to store and manage information about users (called enterprise users) whose accounts were created in a distributed environment. Although database users must be created (with passwords) in each database that they need to access, enterprise user information is accessible centrally in the Oracle Internet Directory. You can also integrate this directory with Microsoft Active Directory and SunOne.
For more information about Oracle Internet Directory, see Oracle Internet Directory Administrator's Guide.
Oracle Enterprise Security Manager lets you store and retrieve roles from Oracle Internet Directory, which provides centralized privilege management to make administration easier and increase security levels. For more information about Oracle Enterprise Security Manager, see Oracle Enterprise Manager Advanced Configuration.
Authenticating Using Public Key Infrastructure
Authentication systems based on public key infrastructure (PKI) issue digital certificates to user clients, which use them to authenticate directly to servers in the enterprise without directly involving an authentication server. Oracle Database provides a PKI for using public keys and certificates, consisting of the following components:
Authentication and secure session key management using SSL. See "Authentication Using Secure Sockets Layer" for more information.
Trusted certificates. These are used to identify third-party entities that are trusted as signers of user certificates when an identity is being validated. When the user certificate is being validated, the signer is checked by using trust points or a trusted certificate chain of certificate authorities stored in the validating system. If there are several levels of trusted certificates in this chain, then a trusted certificate at a lower level is simply trusted without needing to have all its higher-level certificates reverified. For more information about trusted certificates, see Oracle Database Advanced Security Administrator's Guide.
OracleAS Certificate Authority. This is a component of the Oracle Identity Management infrastructure, which provides an integrated solution for provisioning X.509 version 3 certificates for individuals, applications, and servers that require certificates for PKI-based operations such as authentication, SSL, S/MIME, and so on. For more information about OracleAS Certificate Authority, see Oracle Application Server Certificate Authority Administrator's Guide.
Oracle Wallet Manager. An Oracle wallet is a data structure that contains the private key of a user, a user certificate, and the set of trust points of a user (trusted certificate authorities). See Oracle Database Advanced Security Administrator's Guide for information about managing Oracle wallets.
You can use Oracle Wallet Manager to manage Oracle wallets. This is a standalone Java application used to manage and edit the security credentials in Oracle wallets. It performs the following operations:
Generates a public-private key pair and creates a certificate request for submission to a certificate authority, and creates wallets
Installs a certificate for the entity
Manages X.509 version 3 certificates on Oracle Database clients and servers
Configures trusted certificates for the entity
Opens a wallet to enable access to PKI-based services
X.509 version 3 certificates obtained from (and signed by) a trusted entity, a certificate authority. Because the certificate authority is trusted, these certificates verify that the requesting entity's information is correct and that the public key on the certificate belongs to the identified entity. The certificate is loaded into an Oracle wallet to enable future authentication.
You can use Oracle Advanced Security to centralize the management of user-related information, including authorizations, in an LDAP-based directory service. This allows users and administrators to be identified in the database as global users, meaning that they are authenticated by SSL and that the management of these users is handled outside of the database by the centralized directory service. Global roles are defined in a database and are known only to that database, but the directory service handles authorizations for global roles.
Note:
You can also have users authenticated by SSL, whose authorizations are not managed in a directory, that is, they have local database roles only. See Oracle Database Advanced Security Administrator's Guide for details.This centralized management enables the creation of enterprise users and enterprise roles. Enterprise users are defined and managed in the directory. They have unique identities across the enterprise and can be assigned enterprise roles that determine their access privileges across multiple databases. An enterprise role consists of one or more global roles, and might be thought of as a container for global roles.
See Also:
"Strong Authentication and Centralized Management for Database Administrators" if you want to centralize the management ofSYSDBA
or SYSOPER
accessYou have the following options to specify users who are authorized by a directory service:
The following statement shows the creation of a global user with a private schema, authenticated by SSL, and authorized by the enterprise directory service:
CREATE USER psmith IDENTIFIED GLOBALLY AS 'CN=psmith,OU=division1,O=oracle,C=US';
The string provided in the AS
clause provides an identifier (distinguished name, or DN) meaningful to the enterprise directory.
In this case, psmith
is a global user. But, the disadvantage here is that user psmith
must then be created in every database that he must access, plus the directory.
Multiple enterprise users can share a single schema in the database. These users are authorized by the enterprise directory service but do not own individual private schemas in the database. These users are not individually created in the database. They connect to a shared schema in the database.
To create a schema-independent user:
Create a shared schema in the database using the following example:
CREATE USER appschema IDENTIFIED GLOBALLY AS '';
In the directory, create multiple enterprise users and a mapping object.
The mapping object tells the database how you want to map the DNs for the users to the shared schema. You can either create a full DN mapping (one directory entry for each unique DN), or you can map, for each user, multiple DN components to one schema. For example:
OU=division,O=Oracle,C=US
See Also:
Oracle Database Enterprise User Security Administrator's Guide for an explanation of these mappingsMost users do not need their own schemas, and implementing schema-independent users separates users from databases. You create multiple users who share the same schema in a database, and as enterprise users, they can also access shared schemas in other databases.
Some advantages of global user authentication and authorization are as follows:
Provides strong authentication using SSL, Kerberos, or Windows native authentication.
Enables centralized management of users and privileges across the enterprise.
Is easy to administer: You do not have to create a schema for every user in every database in the enterprise.
Facilitates single sign-on: Users need to sign on once to only access multiple databases and services. Further, users using passwords can have a single password to access multiple databases accepting password-authenticated enterprise users.
Because global user authentication and authorization provide password-based access, you can migrate previously defined password-authenticated database users to the directory (using the User Migration Utility) to be centrally administered. This makes global authentication and authorization available for earlier Oracle Database release clients that are still supported.
CURRENT_USER
database links connect as a global user. A local user can connect as a global user in the context of a stored procedure, that is, without storing the global user password in a link definition.
See Also:
The following manuals for additional information about global authentication and authorization and enterprise users and roles:This section contains:
When you use external authentication for user accounts, Oracle Database maintains the user account, but an external service performs the password administration and user authentication. This external service can be the operating system or a network service, such as Oracle Net.
With external authentication, your database relies on the underlying operating system or network 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, then it can authenticate users before they can log in to the database. To enable this feature, set the initialization parameter OS_AUTHENT_PREFIX
, and use this prefix in Oracle Database user names. The OS_AUTHENT_PREFIX
parameter defines a prefix that Oracle Database adds to the beginning of the operating system account name of every user. Oracle Database compares the prefixed user name with the Oracle Database user names in the database when a user attempts to connect.
You should set OS_AUTHENT_PREFIX
to a null string (an empty set of double quotation marks: ""
). Using a null string eliminates the addition of any prefix to operating system account names, so that Oracle Database user names exactly match operating system user names.
OS_AUTHENT_PREFIX=" "
After you set OS_AUTHENT_PREFIX
, it should remain the same for the life of a database. If you change the prefix, then any database user name that includes the old prefix cannot be used to establish a connection, unless you alter the user name to have it use password authentication.
The default value of this parameter is OPS$
for backward compatibility with previous versions of Oracle Database. For example, assume that you set OS_AUTHENT_PREFIX
as follows:
OS_AUTHENT_PREFIX=OPS$
Note:
The text of theOS_AUTHENT_PREFIX
initialization parameter is case-sensitive on some operating systems. See your operating system-specific Oracle Database documentation for more information about this initialization parameter.If a user with an operating system account named tsmith
is to connect to an Oracle database installation and be authenticated by the operating system, then Oracle Database checks that there is a corresponding database user OPS$tsmith
and, if so, lets the user connect. All references to a user authenticated by the operating system must include the prefix, OPS$
, as seen in OPS$tsmith
.
The advantages of external authentication are as follows:
More choices of authentication mechanisms are available, such as smart cards, fingerprints, Kerberos, or the operating system.
Many network authentication services, such as Kerberos support single sign-on, enabling users to have fewer passwords to remember.
If you are already using an external mechanism for authentication, such as one of those listed earlier, then there may be less administrative overhead to use that mechanism with the database.
The following statement creates a user who is identified by Oracle Database and authenticated by the operating system or a network service. This example assumes that the OS_AUTHENT_PREFIX
parameter has been set to a blank space (" "
).
CREATE USER psmith IDENTIFIED EXTERNALLY;
Using the CREATE USER ... IDENTIFIED EXTERNALLY
statement, you create database accounts that must be authenticated by the operating system or network service. Oracle Database then relies on this external login authentication when it provides that specific operating system user with access to the database resources of a specific user.
See Also:
Oracle Database Advanced Security Administrator's Guide for more information about external authenticationBy default, Oracle Database allows operating system-authenticated logins only over secure connections, which precludes using Oracle Net and a shared server configuration. This restriction prevents a remote user from impersonating another operating system user over a network connection.
Setting the REMOTE_OS_AUTHENT
parameter to TRUE
in the database initialization parameter file forces the database to accept the client operating system user name received over an unsecure connection and use it for account access. Because clients, in general, such as PCs, are not trusted to perform operating system authentication properly, it is very poor security practice to turn on this feature.
The default setting, REMOTE_OS_AUTHENT = FALSE
, creates a more secure configuration that enforces proper, server-based authentication of clients connecting to an Oracle database.
Any change to this parameter takes effect the next time you start the instance and mount the database. Generally, user authentication through the host operating system offers faster and more convenient connection to Oracle Database without specifying a separate database user name or password. Also, user entries correspond in the database and operating system audit trails.
Be aware that the REMOTE_OS_AUTHENT
parameter was deprecated in Oracle Database 11g Release 1 (11.1), and is retained only for backward compatibility.
Oracle Advanced Security performs network authentication, which you can configure to use a third-party service such as Kerberos. If you are using Oracle Advanced Security as your only external authentication service, then the REMOTE_OS_AUTHENT
parameter setting is irrelevant, because Oracle Advanced Security allows only secure connections.
In a multitier environment, Oracle Database controls the security of middle-tier applications by limiting their privileges, preserving client identities through all tiers, and auditing actions taken on behalf of clients. In applications that use a very busy middle tier, such as a transaction processing monitor, the identity of the clients connecting to the middle tier must be preserved. One advantage of using a middle tier is connection pooling, which allows multiple users to access a data server without each of them needing a separate connection. In such environments, you need to be able to set up and break down connections very quickly.
For these environments, you can use the Oracle Call Interface to create lightweight sessions, which enable database password authentication for each user. This method preserves the identity of the real user through the middle tier without the overhead of a separate database connection for each user.
You can create lightweight sessions with or without passwords. However, if a middle tier is outside of or on a firewall, then security is better when each lightweight session has its own password. For an internal application server, lightweight sessions without passwords might be appropriate.
In a multitier environment, an application server provides data for clients and serves as an interface from them to one or more database servers. The application server can validate the credentials of a client, such as a Web browser, and the database server can audit operations performed by the application server. These auditable operations include actions performed by the application server on behalf of clients, such as requests that information be displayed on the client. A request to connect to the database server is an example of an application server operation not related to a specific client.
Authentication in a multitier environment is based on trust regions. Client authentication is the domain of the application server. The application server itself is authenticated by the database server. The following operations are performed:
The end user provides proof of authenticity to the application server, typically, by using a password or an X.509 certificate.
The application server authenticates the end user and then authenticates itself to the database server.
The database server authenticates the application server, verifies that the end user exists, and verifies that the application server has the privilege to connect for the end user.
Application servers can also enable roles for an end user on whose behalf they connect. The application server can obtain these roles from a directory, which serves as an authorization repository. The application server can only request that these roles be enabled. The database verifies the following requirements:
That the client has these roles by checking its internal role repository
That the application server has the privilege to connect on behalf of the user and thus to use these roles as the user could
Figure 3-2 shows an example of multitier authentication.
The following actions take place:
The user logs on using a password or Secure Sockets Layer. The authentication information is passed through Oracle Application Server.
Oracle Internet Directory authenticates the user, gets the roles associated with that user from the wallet, and then passes this information back to Oracle Application Server.
Oracle Application Server checks the identity of the user in Oracle Database, which contains a wallet that stores this information, and then sets the role for that user.
Security for middle-tier applications must address the following key issues:
Accountability. The database server must be able to distinguish between the actions of the application and the actions an application takes on behalf of a client. It must be possible to audit both kinds of actions.
Least privilege. Users and middle tiers should be given the fewest privileges necessary to perform their actions, to reduce the danger of inadvertent or malicious unauthorized activities.
Many organizations want to know who the user is through all tiers of an application without sacrificing the benefits of a middle tier. Oracle Database supports the following ways to preserve user identity through the middle tier of an application:
The following sections explain how to use proxy authentication:
Creating Proxy User Accounts and Authorizing Users to Connect Through Them
Using Proxy Authentication with the Secure External Password Store
Passing Through the Identity of the Real User by Using Proxy Authentication
Authorizing a Middle Tier to Proxy a User Authenticated by Other Means
Reauthenticating the User Through the Middle Tier to the Database
Oracle Database provides proxy authentication in Oracle Call Interface (OCI), JDBC/OCI, or JDBC Thin Driver for database users or enterprise users. Enterprise users are those who are managed in Oracle Internet Directory and who access a shared schema in the database.
You can design a middle-tier server to authenticate clients in a secure fashion by using the following three forms of proxy authentication:
The middle-tier server authenticates itself with the database server and a client, in this case an application user or another application, authenticates itself with the middle-tier server. Client identities can be maintained all the way through to the database.
The client, in this case a database user, is not authenticated by the middle-tier server. The clients identity and database password are passed through the middle-tier server to the database server for authentication.
The client, in this case a global user, is authenticated by the middle-tier server, and passes one of the following through the middle tier for retrieving the client's user name.
Distinguished name (DN)
Certificate
Note:
The use of certificates for proxy authentication may not be supported in future Oracle Database releases.In all cases, an administrator must authorize the middle-tier server to act on behalf of the client.
See Also:
Oracle Call Interface Programmer's Guide and Oracle Database Advanced Application Developer's Guide or details about designing a middle-tier server to proxy usersIn multitier environments, proxy authentication controls the security of middle-tier applications by preserving client identities and privileges through all tiers and by auditing actions taken on behalf of clients. For example, this feature allows the identity of a user using a Web application (which acts as a proxy) to be passed through the application to the database server.
Three-tier systems provide the following benefits to organizations:
Organizations can separate application logic from data storage, partitioning the former in application servers and the latter in databases.
Application servers and Web servers enable users to access data stored in databases.
Users like using a familiar, easy-to-use browser interface.
Organizations can also lower their cost of computing by replacing many thick clients with numerous thin clients and an application server.
In addition, Oracle Database proxy authentication provides the following security benefits:
A limited trust model, by controlling the users on whose behalf middle tiers can connect and the roles that the middle tiers can assume for the user
Scalability, by supporting user sessions through OCI, JDBC/OCI, or JDBC Thin driver and eliminating the overhead of reauthenticating clients
Accountability, by preserving the identity of the real user through to the database, and enabling auditing of actions taken on behalf of the real user
Flexibility, by supporting environments in which users are known to the database, and in which users are merely application users of which the database has no awareness
Note:
Oracle Database supports this proxy authentication functionality in three tiers only. It does not support it across multiple middle tiers.To create proxy user accounts, users must have the following minimum privileges:
The CREATE USER
system privilege to create a database user account that will be used as a proxy user account
The DV_ACCTMGR
role if Oracle Database Vault is enabled, to create the proxy user account
The ability to grant the CREATE SESSION
system privilege to the proxy user account
The ALTER USER
system privilege to enable existing user accounts to connect to the database through the proxy account
Follow these guidelines when you create proxy user accounts:
For better security and to adhere to the principle of least privilege, only grant the proxy user account the CREATE SESSION
privilege. Do not grant this user any other privileges. The proxy user account is designed to only enable another user to connect using the proxy account. Any privileges that must be exercised during the connection should belong to the connecting user, not to the proxy account.
As with all passwords, ensure that the password you create for the proxy user is strong and not easily guessed. Remember that multiple users will be connecting as the proxy user, so it is especially important that this password be strong. See "Guidelines for Securing Passwords" for advice about creating strong passwords.
Consider using the Advanced Security option network connection features, to prevent network eavesdropping.
For further fine-tuning of the amount of control that the connecting user has, consider restricting the roles used by the connecting user when he or she is connected through the proxy account. The ALTER USER
statement enables you to configure the user to connect using specified roles, any role except a specified role, or with no roles at all.
The CREATE USER
statement enables you to create the following types of user accounts, all of which can be used as proxy accounts:
Database user accounts, which are authenticated by passwords
External user accounts, which are authenticated by external sources, such as Secure Socket Layer (SSL) or Kerberos
Global user accounts, which are authenticated by an enterprise directory service (Oracle Internet Directory).
To create a proxy user account and authorize users to connect through it:
Use the CREATE USER
statement to create the proxy user account.
For example:
CREATE USER appuser IDENTIFIED AS password;
Use the GRANT CONNECT THROUGH
clause of the ALTER USER
statement to enable an existing user to connect through the proxy user account.
For example:
ALTER USER preston GRANT CONNECT THROUGH appuser;
Suppose user preston
has a large number of roles, but you only want her to use one role (for example, the appuser_role
) when she is connected to the database through the appuser
proxy account. You can use the following ALTER USER
statement:
ALTER USER preston GRANT CONNECT THROUGH appuser WITH ROLE appuser_role;
Any other roles that user preston
will not be available to her as long as she is connecting as the appuser
proxy.
After you complete these steps, user preston
can connect using the appuser
proxy user as follows:
CONNECT appuser[preston]
Enter password: appuser_password
Note the following:
The proxy user can only perform activities that user preston has privileges to perform. Remember that the proxy user itself, appuser
, only has the minimum privileges (CREATE SESSION
).
Using roles with middle-tier clients. You can also specify roles that the middle tier is permitted to activate when connecting as the client. Operations performed on behalf of a client by a middle-tier server can be audited.
Finding proxy users. To find the users who are currently authorized to connect through a middle tier, query the PROXY_USERS
data dictionary view, for example:
SELECT * FROM PROXY_USERS;
Removing proxy connections. Use the REVOKE CONNECT THROUGH
clause of ALTER USER
to disallow a proxy connection. For example, to revoke user preston
from connecting through the proxy user appuser
, enter the following statement:
ALTER USER preston REVOKE CONNECT THROUGH appuser
Password expiration and proxy connections. Middle-tier use of password expiration does not apply to accounts that are authenticated through a proxy. Instead, lock the account rather than expire the password.
See Also:
Oracle Database SQL Language Reference for detailed information about the CREATE USER
statement
Oracle Database SQL Language Reference for detailed information about the ALTER USER
statement
Oracle Database Enterprise User Security Administrator's Guide for information about managing proxy users in an enterprise user environment
Oracle Database Advanced Security Administrator's Guide for more information about using network encryption and strong authentication
"Auditing SQL Statements and Privileges in a Multitier Environment" for details about auditing operations done on behalf of a user by a middle tier
If you are concerned about the password used in proxy authentication being obtained by a malicious user, then you can use the secure external password store with the proxy authentication to store the password credentials in a wallet. Connecting to Oracle Database using proxy authentication and the secure external password store is ideal for situations such as running batch files. When a proxy user connects to the database and authenticates using a secure external password, the password is not exposed in the event that a malicious user tries to obtain the password.
To use proxy authentication with the secure external password store:
Configure the proxy authentication account, as shown in the procedure under "Creating Proxy User Accounts and Authorizing Users to Connect Through Them".
Configure the secure external password store. See "Configuring Clients to Use the External Password Store" for more information.
Afterward, the user can connect using the proxy but without having to specify a password. For example:
sqlplus [preston]/@db_alias
When you use the secure external password store, the user logging in does not need to supply the user name and password. Only the SERVICE_NAME
value (that is, db_alias
) from the tnsnames.ora
file must be specified.
For enterprise users or database users, Oracle Call Interface, JDBC/OCI, or Thin driver enables a middle tier to set up several user sessions within a single database connection, each of which uniquely identifies a connected user (connection pooling). These sessions reduce the network overhead of creating separate network connections from the middle tier to the database.
If you want to authenticate from clients through a middle tier to the database, the full authentication sequence from the client to the middle tier to the database occurs as follows:
The client authenticates to the middle tier, using whatever form of authentication the middle tier will accept. For example, the client could authenticate to the middle tier by using a user name and password or an X.509 certificate by means of SSL.
The middle tier authenticates itself to the database by using whatever form of authentication the database accepts. This could be a password or an authentication mechanism supported by Oracle Advanced Security, such as a Kerberos ticket or an X.509 certificate (SSL).
The middle tier then creates one or more sessions for users using OCI, JDBC/OCI, or Thin driver.
If the user is a database user, then the session must, as a minimum, include the database user name. If the database requires it, then the session can include a password (which the database verifies against the password store in the database). The session can also include a list of database roles for the user.
If the user is an enterprise user, then the session may provide different information depending on how the user is authenticated.
Example 1: If the user authenticates to the middle tier using SSL, then the middle tier can provide the DN from the X.509 certificate of the user, or the certificate itself in the session. The database uses the DN to look up the user in Oracle Internet Directory.
Example 2: If the user is a password-authenticated enterprise user, then the middle tier must provide, as a minimum, a globally unique name for the user. The database uses this name to look up the user in Oracle Internet Directory. If the session also provides a password for the user, then the database will verify the password against Oracle Internet Directory. User roles are automatically retrieved from Oracle Internet Directory after the session is established.
The middle tier may optionally provide a list of database roles for the client. These roles are enabled if the proxy is authorized to use the roles on behalf of the client.
The database verifies that the middle tier has the privilege to create sessions on behalf of the user.
The OCISessionBegin
call fails if the application server cannot perform a proxy authentication on behalf of the client by the administrator, or if the application server is not allowed to activate the specified roles.
Least privilege is the principle that users should have the fewest privileges necessary to perform their duties and no more. As applied to middle tier applications, this means that the middle tier should not have more privileges than it needs. Oracle Database enables you to limit the middle tier such that it can connect only on behalf of certain database users, using only specific database roles. You can limit the privilege of the middle tier to connect on behalf of an enterprise user, stored in an LDAP directory, by granting to the middle tier the privilege to connect as the mapped database user. For instance, if the enterprise user is mapped to the APPUSER
schema, then you must at least grant to the middle tier the ability to connect on behalf of APPUSER
. Otherwise, attempts to create a session for the enterprise user will fail.
However, you cannot limit the ability of the middle tier to connect on behalf of enterprise users. For example, suppose that user Sarah wants to connect to the database through a middle tier, appsrv
(which is also a database user). Sarah has multiple roles, but it is desirable to restrict the middle tier to use only the clerk
role on her behalf.
An administrator could effectively grant permission for appsrv
to initiate connections on behalf of Sarah using her clerk
role only, using the following syntax:
ALTER USER sarah GRANT CONNECT THROUGH appsrv WITH ROLE clerk;
By default, the middle tier cannot create connections for any client. The permission must be granted for each user.
To allow appsrv
to use all of the roles granted to the client Sarah, the following statement would be used:
ALTER USER sarah GRANT CONNECT THROUGH appsrv;
Each time a middle tier initiates an OCI, JDBC/OCI, or Thin driver session for another database user, the database verifies that the middle tier is authorized to connect for that user by using the role specified.
Note:
Instead of using default roles, create your own roles and assign only necessary privileges to them. Creating your own roles enables you to control the privileges granted by them and protects you if Oracle Database changes or removes default roles. For example, theCONNECT
role now has only the CREATE SESSION
privilege, the one most directly needed when connecting to a database.
However, CONNECT
formerly provided several additional privileges, often not needed or appropriate for most users. Extra privileges can endanger the security of your database and applications. These have now been removed from CONNECT
.
See Chapter 4, "Configuring Privilege and Role Authorization," for more information about roles.
The following statement authorizes the middle-tier server appserve
to connect as user bill
. It uses the WITH ROLE
clause to specify that appserve
activate all roles associated with bill
, except payroll
.
ALTER USER bill GRANT CONNECT THROUGH appserve WITH ROLE ALL EXCEPT payroll;
To revoke the middle-tier server (appserve
) authorization to connect as user bill
, the following statement is used:
ALTER USER bill REVOKE CONNECT THROUGH appserve;
Use the AUTHENTICATION REQURED
clause of the ALTER USER ... GRANT CONNECT THROUGH
statement to authorize a user to be proxied, but not authenticated, by a middle tier. Currently, PASSWORD
is the only means supported.
The following statement illustrates this form of authentication:
ALTER USER mary GRANT CONNECT THROUGH midtier AUTHENTICATION REQUIRED;
In the preceding statement, middle-tier server midtier
is authorized to connect as user mary
, and midtier
must also pass the user password to the database server for authorization.
Administrators can specify that authentication is required by using the AUTHENTICATION REQUIRED
proxy clause with the ALTER USER
SQL statement. In this case, the middle tier must provide user authentication credentials.
For example, suppose that user Sarah wants to connect to the database through a middle tier, appsrv
. An administrator could require that appsrv
provides authentication credentials for Sarah by using the following syntax:
ALTER USER sarah GRANT CONNECT THROUGH appsrv AUTHENTICATION REQUIRED;
The AUTHENTICATION REQUIRED
clause ensures that authentication credentials for the user must be presented when the user is authenticated through the specified proxy.
Note:
For backward compatibility, if you use theAUTHENTICATED USING PASSWORD
proxy clause, then Oracle Database transforms it to AUTHENTICATION REQUIRED
.When you use password-based proxy authentication, Oracle Database passes the password of the client to the middle-tier server. The middle-tier server then passes the password as an attribute to the data server for verification. The main advantage to this is that the client computer does not have to have Oracle software installed on it to perform database operations.
To pass the password of the client, the middle-tier server calls the OCIAttrSet()
function as follows, passing OCI_ATTR_PASSWORD
as the type of the attribute being set.
OCIAttrSet( session_handle, /* Pointer to a handle whose attribute gets modified. */ OCI_HTYPE_SESSION, /* Handle type: OCI user session handle. */ password_ptr, /* Pointer to the value of the password attribute. */ 0, /* The size of the password attribute value is already known by the OCI library. */ OCI_ATTR_PASSWORD, /* The attribute type. */ error_handle); /* An error handle used to retrieve diagnostic information in the event of an error. */
If the middle tier connects to the database as a client who is an enterprise user, then either the distinguished name, or the X.509 certificate containing the distinguished name is passed over instead of the database user name. If the user is a password-authenticated enterprise user, then the middle tier must provide, as a minimum, a globally unique name for the user. The database uses this name to look up the user in Oracle Internet Directory.
To pass over the distinguished name of the client, the application server would call the Oracle Call Interface method OCIAttrSet()
with OCI_ATTR_DISTINGUISHED_NAME
as the attribute type, as follows:
OCIAttrSet(session_handle, OCI_HTYPE_SESSION, distinguished_name, 0, OCI_ATTR_DISTINGUISHED_NAME, error_handle);
To pass over the entire certificate, the middle tier would call OCIAttrSet()
with OCI_ATTR_CERTIFICATE
as the attribute type, as follows.
OCIAttrSet(session_handle, OCI_HTYPE_SESSION, certificate, certificate_length, OCI_ATTR_CERTIFICATE, error_handle);
If the type is not specified, then the database uses its default certificate type of X.509.
Note:
OCI_ATTR_CERTIFICATE
is Distinguished Encoding Rules (DER) encoded.
Certificate based proxy authentication using OCI_ATTR_CERTIFICATE
will not be supported in future Oracle Database releases. Use the OCI_ATTR_DISTINGUISHED_NAME
or OCI_ATTR_USERNAME
attribute instead
If you are using proxy authentication for password-authenticated enterprise users, then use the same OCI attributes as for database users authenticated by password (OCI_ATTR_USERNAME
). Oracle Database first checks the user name against the database. If it finds no user, then the database checks the user name in the directory. This user name must be globally unique.
The following sections explain how to use client identifiers:
Using the CLIENT_IDENTIFIER Attribute to Preserve User Identity
Using CLIENT_IDENTIFIER Independent of Global Application Context
Using the DBMS_SESSION PL/SQL Package to Set and Clear the Client Identifier
Oracle Database provides the CLIENT_IDENTIFIER
attribute of the built-in USERENV
application context namespace for application users. These users are known to an application but unknown to the database. The CLIENT_IDENTIFIER
attribute can capture any value that the application uses for identification or access control, and passes it to the database. The CLIENT_IDENTIFIER
attribute is supported in OCI, JDBC/OCI, or Thin driver.
Many applications use session pooling to set up several sessions to be reused by multiple application users. Users authenticate themselves to a middle-tier application, which uses a single identity to log in to the database and maintains all the user connections. In this model, application users are users who are authenticated to the middle tier of an application, but who are not known to the database. You can use a CLIENT_IDENTIFIER
attribute, which acts like an application user proxy for these types of applications.
In this model, the middle tier passes a client identifier to the database upon the session establishment. The client identifier could actually be anything that represents a client connecting to the middle tier, for example, a cookie or an IP address. The client identifier, representing the application user, is available in user session information and can also be accessed with an application context (by using the USERENV
naming context). In this way, applications can set up and reuse sessions, while still being able to keep track of the application user in the session. Applications can reset the client identifier and thus reuse the session for a different user, enabling high performance.
You can use the CLIENT_IDENTIFIER
predefined attribute of the built-in application context namespace, USERENV
, to capture the application user name for use with global application context. You also can use the CLIENT_IDENTIFIER
attribute independently. When you use the CLIENT_IDENTIFIER
attribute independently from a global application context, you can set CLIENT_IDENTIFIER
with the DBMS_SESSION
interface. The ability to pass a CLIENT_IDENTIFIER
to the database is supported in Oracle Call Interface (OCI), JDBC/OCI, or Thin driver.
When you use the CLIENT_IDENTIFIER
attribute with global application context, it provides flexibility and high performance for building applications. For example, suppose a Web-based application that provides information to business partners has three types of users: gold partner, silver partner, and bronze partner, representing different levels of information available. Instead of each user having his or her own session set up with individual application contexts, the application could set up global application contexts for gold partners, silver partners, and bronze partners. Then, use the CLIENT_IDENTIFIER
to point the session at the correct context to retrieve the appropriate type of data. The application need only initialize the three global contexts once and use the CLIENT_IDENTIFIER
to access the correct application context to limit data access. This provides performance benefits through session reuse and through accessing global application contexts set up once, instead of having to initialize application contexts for each session individually.
See Also:
"Using Global Application Contexts" for how to implement global application contexts
"Tutorial: Creating a Global Application Context That Uses a Client Session ID"
Using the CLIENT_IDENTIFIER
attribute is especially useful for those applications in which the users are unknown to the database. In these situations, the application typically connects as a single database user and all actions are taken as that user. Because all user sessions are created as the same user, this security model makes it difficult to achieve data separation for each user. These applications can use the CLIENT_IDENTIFIER
attribute to preserve the real application user identity through to the database.
With this approach, sessions can be reused by multiple users by changing the value of the CLIENT_IDENTIFIER
attribute, which captures the name of the real application user. This avoids the overhead of setting up a separate session and separate attributes for each user, and enables reuse of sessions by the application. When the CLIENT_IDENTIFIER
attribute value changes, the change is added to the next OCI, JDBC/OCI, or Thin driver call for additional performance benefits.
For example, the user Daniel connects to a Web Expense application. Daniel is not a database user; he is a typical Web Expense application user. The application accesses the built-in application context namespace and sets DANIEL
as the CLIENT_IDENTIFIER
attribute value. Daniel completes his Web Expense form and exits the application. Then, Ajit connects to the Web Expense application. Instead of setting up a new session for Ajit, the application reuses the session that currently exists for Daniel, by changing the CLIENT_IDENTIFIER
to AJIT
. This avoids the overhead of setting up a new connection to the database and the overhead of setting up a global application context. The CLIENT_IDENTIFIER
attribute can be set to any value on which the application bases access control. It does not have to be the application user name.
To set the CLIENT_IDENTIFIER
attribute with OCI, use the OCI_ATTR_CLIENT_IDENTIFIER
attribute in the call to OCIAttrSet()
. Then, on the next request to the server, the information is propagated and stored in the server sessions. For example:
OCIAttrSet (session,
OCI_HTYPE_SESSION, (dvoid *) "appuser1", (ub4)strlen("appuser1"), OCI_ATTR_CLIENT_IDENTIFIER, *error_handle);
For applications that use JDBC, be aware that JDBC does not set the client identifier. To set the client identifier in a connection pooling environment, use Dynamic Monitoring Service (DMS) metrics. If DMS is not available, then use the connection.setClientInfo
method. For example:
connection.setClientInfo("E2E_CONTEXT.CLIENT_IDENTIFIER", "appuser");
See Also:
Oracle Call Interface Programmer's Guide about how the OCI_ATTR_CLIENT_IDENTIFIER
user session handle attribute is used in middle-tier applications
Oracle Database JDBC Developer's Guide for more information about configuring client connections using JDBC
To use the DBMS_SESSION
package to set and clear the CLIENT_IDENTIFIER
value on the middle tier, use the following interfaces:
SET_IDENTIFIER
CLEAR_IDENTIFIER
The middle tier uses SET_IDENTIFIER
to associate the database session with a particular user or group. Then, the CLIENT_IDENTIFIER
is an attribute of the session and can be viewed in session information.
If you plan to use the DBMS_SESSION.SET_IDENTIFIER
procedure, be aware that the DBMS_APPLICATION_INFO.SET_CLIENT_INFO
procedure can overwrite the value of the client identifier. Typically, these values should be the same, so if SET_CLIENT_INFO
is set, its value can be automatically propagated to the value set by SET_IDENTIFIER
if the CLIENTID_OVERWRITE
event is set to ON
.
To check the status of the CLIENTID_OVERWRITE
event, log in to SQL*Plus and then enter the SHOW PARAMETER
command. For example, assuming that CLIENTID_OVERWRITE
is enabled:
SHOW PARAMETER EVENT NAME TYPE VALUE ------------------------------ ------------------ ------------------ event string clientid_overwrite
To enable the CLIENTID_OVERWRITE
event system-wide, connect to SQL*Plus as SYS
using the SYSDBA
privilege, and then enter the following ALTER SYSTEM
statement:
ALTER SYSTEM SET EVENTS 'CLIENTID_OVERWRITE';
Or, enter the following line in your init.ora
file:
event="clientid_overwrite"
Then restart the database. To disable the CLIENTID_OVERWRITE
event, log in to SQL*Plus as SYS
with the SYSDBA
privilege, and then run the following ALTER SYSTEM
statement:
ALTER SYSTEM SET EVENTS 'CLIENTID_OVERWRITE OFF';
If you prefer to change the CLIENTID_OVERWRITE
value for the session only, then use the ALTER SESSION
statement.
Afterwards, if you set the client identifier using the DBMS_APPLICATION_INFO.SET_CLIENT_INFO
procedure, you must then run DBMS_SESSION.SET_IDENTIFIER
so that the client identifier settings are the same.
See Also:
"Using Global Application Contexts" for information about using client identifiers in a global application context
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SESSION
package
Table 3-3 lists data dictionary views that contain information about user authentication. For detailed information about these views, see Oracle Database Reference.
Table 3-3 Data Dictionary Views That Describe User Authentication
View | Description |
---|---|
|
Displays information about profiles, including their settings and limits. |
|
Displays the kind of authentication used for a database role to log in to the database, such as |
|
Among other user information, displays the following:
|
|
Displays whether the user account password is a default password |
|
Displays users who are currently authorized to connect through a middle tier |
|
Displays user accounts for existing database links ( |
|
Querying the |