5 Configuring Centrally Managed Users with Microsoft Active Directory
Oracle Database can authenticate and authorize Microsoft Active Directory users with the database directly without intermediate directories or Oracle Enterprise User Security.
Introduction to Centrally Managed Users with Microsoft Active Directory
Centrally managed users provides for a simpler integration with Microsoft Active Directory to allow centralized authentication and authorization of users.
About the Oracle Database-Microsoft Active Directory Integration
Centrally managed users provides for a simpler integration with Microsoft Active Directory to allow centralized authentication and authorization of users.
This integration enables organizations to use Active Directory to centrally manage users and roles in multiple Oracle databases with a single directory along with other Information Technology services. Users can authenticate to the Oracle Database using credentials stored in Active Directory and also be associated with Database schemas and roles using Active Directory groups. Microsoft Active Directory users can be mapped to exclusive or shared Oracle Database schemas and associated with database roles in the directory. Active Directory password policies such as password expiration time and lockout after # failed login attempts are honored by the Oracle Database when users login.
Before Oracle Database 18c release 1 (18.1), users integrated user authentication and authorization with Active Directory by configuring Oracle Enterprise User Security and installing and configuring Oracle Internet Directory (or Oracle Universal Directory). This architecture is still available and will be used going forward by users who must use trusted database links, complex enterprise roles and having a single place for auditing database access privileges and roles.
The majority of organizations don’t require these complex requirements. Instead, they can use centrally managed users (CMUs) with Active Directory. This integration is designed for organizations who prefer to use Active Directory as their centralized identity management solution. Oracle Net Naming Services continues to work as it did before with directory services.
Organizations can use Kerberos, PKI, or password authentication with CMU with Active Directory. Use of CMU with Active Directory is backwards compatible with currently supported Oracle Database clients. This means that LDAP bind operations are not used for password authentication and you will need to add an Oracle filter to Active Directory along with an extension to the Active Directory schema to store password verifiers. Organizations using Kerberos or PKI will not need to add the filter or extend Active Directory.
The Oracle Database-Active Directory integration is particularly beneficial for the following types of users:
-
Users who are currently using strong authentication tools such as Kerberos or Public Key Infrastructure (PKI). These users already use a centralized identity management system
-
Users who currently use Oracle Enterprise User Security, Oracle Internet Directory, Oracle Unified Directory, Oracle Virtual Directory, and need to integrate with Active Directory.
How Centrally Managed Users with Microsoft Active Directory Works
The integration works by creating a mapping between database users and roles with Microsoft Active Directory users and groups.
In order for the Oracle Database CMU with Active Directory integration to work, the Oracle database must be able to login to a service account specifically created for the database in Active Directory. The database uses this service account to query Active Directory for user and group information when a user logs into the database. This Active Directory service account must have all the privileges required to query the user and group information as well as being able to write updates related to the password policies in Active Directory (for example, failed login attempts, clear failed login attempts). Users can authenticate using passwords, Kerberos, or PKI and either be assigned to an exclusive schema or a shared schema. Mapping of an Active Directory user to a shared schema is determined by the association of the user to an Active Directory group that is mapped to the shared schema. Active Directory groups can also be mapped to database global roles. Active Directory security administrators can assign users to a shared schema and a global role mapped groups to change and update privileges and roles assigned to the Active Directory user in a database.
Centrally Managed User-Microsoft Active Directory Architecture
The CMU with Active Directory architecture enables Oracle Database users and roles to be managed in Active Directory.
The following figure illustrates the Oracle Database CMU feature. In this figure, users, either through applications as non-administrative users or administrative users, connect to the Oracle database with either password, Kerberos, or public key infrastructure (PKI) authentication. The database connection to Active Directory enables these users and roles to be mapped with Active Directory users and groups. If you plan to use password authentication, then you must install an Oracle filter in Active Directory. This filter will create Oracle password verifiers and extend the Active Directory schema to hold the Oracle password verifiers. With Oracle Database centrally managed users, an Active Directory administrator can control the authentication, user management, account policies, and group assignments of Active Directory users and groups who have been mapped to Oracle Database users and roles.
Supported Authentication Methods
The Oracle Database-Microsoft Active Directory integration supports three common authentication methods.
These authentication methods are as follows:
-
Password authentication
-
Kerberos authentication
-
Public key infrastructure (PKI) authentication (certificate-based authentication)
Related Topics
Users Supported by Centrally Managed Users with Microsoft Active Directory
CMU with Active Directory supports exclusively mapped users, users mapped to shared schemas, and administrative users.
These users are as follows:
-
Directory users that access an Oracle database using a shared schema.
This type of directory user can connect to a shared schema in the database by being part of a directory group that is mapped to the shared schema (database user). Using shared schemas allows centralized Active Directory management of database users and is the recommended best practices over using exclusive schemas (described next). Even if there is only one user associated with a schema (for example, an administrator responsible for database backup), it is easier to manage adding another backup administrator or removing the existing administrator by making changes only in Active Directory instead of making changes in all associated databases as well.
Users will be given additional privileges appropriate to their task using global roles that are mapped to groups in Active Directory. With this design, a user can change his or her tasks within an organization and have new privileges through a new group in Active Directory.
Active Directory users could accidentally (or on purpose) be a member of multiple groups in Active Directory that are mapped to different shared schemas on the same database. The user could also have an exclusive mapping to a database schema. In cases where the user has multiple possible schema mappings when they login, the following precedence rules apply:
-
If an exclusive mapping exists for a user, then that mapping takes precedence over any other shared mappings.
-
If multiple shared schema mappings exist for a user, then the oldest shared user mapping takes precedence.
Oracle recommends only having one possible mapping per user so unexpected schema mappings don’t occur.
-
-
Exclusively mapped global users who are regular Oracle Database users in two- and three-tier applications, or users who have direct privilege grants in the database.
Oracle recommends that you grant privileges to these users through global roles. This type of privilege grant facilitates authorization management by centrally managing privileges and roles for a user instead of having to log in into each database to update privileges and roles for the user.
-
Administrative global users, who have the following administrative privileges:
SYSDBA
,SYSOPER
,SYSDG
,SYSKM
, andSYSRAC
.You cannot directly grant these administrative privileges through global roles. To authorize an Active Directory user with these administrative privileges, you must map the directory user to a database user (exclusively or with a shared schema) that has the system administrative privilege already granted to the database user account.
Related Topics
How the Oracle Multitenant Option Affects Centrally Managed Users
Multitenant database users in a pluggable database (PDB) can connect to a central Microsoft Active Directory or, if required, connect each PDB to a different Microsoft Active Directory.
An entire multitenant database can authenticate and authorize against a single Active Directory instance. Alternatively, individual pluggable databases (PDBs) can authenticate and authorize against different Active Directory domains.
Configuring the Oracle Database-Microsoft Active Directory Integration
Before you can use Microsoft Active Directory to authenticate and authorize users, you must configure the connection from the Oracle database to Active Directory.
About Configuring the Oracle Database-Microsoft Active Directory Connection
Before you configure this connection, you must have Microsoft Active Directory installed and configured.
You must create an Oracle service directory user in Active Directory, configure the Oracle Database connection to Active Directory, and then depending on the authentication type, configure the database and Active Directory for password, Kerberos, or public key infrastructure (PKI) authentication. Before you map Database users and global roles to Active Directory users and groups, you must ensure that the Active Directory users and groups have been created. You will map the database users and global roles to Active Directory users and groups by using the CREATE USER
, CREATE ROLE
, ALTER USER
, ALTER ROLE
SQL statements with the GLOBALLY
clause. An Active Directory system administrator must also set up new Active Directory groups with Active Directory users to meet your requirements.
Connecting to Microsoft Active Directory
You can configure a Microsoft Active Directory connection during the Oracle Database installation process or to an existing Oracle database.
Step 1: Create an Oracle Service Directory User Account on Microsoft Active Directory
The Oracle service directory user account is for the interaction between Oracle Database and the LDAP directory service.
Step 2: For Password Authentication, Install the Password Filter and Extend the Microsoft Active Directory Schema
You can use the Oracle opwdintg.exe
executable on the Active Directory server to install the password filter and extend the Active Directory schema.
opwdintg.exe
executable installs the Oracle password filter, extends the AD schema, and creates the authorization Active directory groups to allow Oracle Database password authentication with Active Directory. This procedure adds an orclCommonAttribute
attribute to the Active Directory schema for user accounts.
- Go to the
$ORACLE_HOME/bin
directory. - Find the
opwdintg.exe
(Oracle Password Integration) utility. - Using a secure method of copying (such as
sftp
), copyopwdintg.exe
to a temporary directory (for example,C:\temp
) on each Windows domain controller. - Connect to the Windows computer as the Active Directory administrator.
- Run the
opwdintg.exe
utility. - Answer the following prompts:
- Do you want to extend AD schema? [Yes/No]: Enter
Yes
. - Schema extension for this domain will be permanent. Continue? [Yes/No]: Enter
Yes
.Note the following:
-
You can only extend the Active Directory schema from a Windows domain controller. If you try to extend the schema again, error messages appear, but you can ignore these errors.
-
This step creates the following three verifier groups. If these groups already exist, then errors will appear, but you can ignore these errors.
-
ORA_VFR_MD5
is required when the Oracle Database WebDAV client is used. -
ORA_VFR_11G
enables the use of the Oracle Database 11G password verifier. -
ORA_VFR_12C
enables the user of the Oracle Database 12C password verifier.
-
-
- Found password filter installed already. Do you want to deinstall? [Yes/No]: Enter
Yes
if you want to deinstall the password filter. Enter No if you do not want to deinstall the password filter. - Do you want to install Oracle password filter? [Yes/No]: Enter
Yes
. - The change requires machine reboot. Do you want to reboot now? [Yes/No]: Enter
Yes
.
- Do you want to extend AD schema? [Yes/No]: Enter
Step 3: If Necessary, Install the Oracle Database Software
If you have not done so yet, then use Oracle Universal Installer (OUI) to install the Oracle database.
- Follow the instructions in the Oracle Database Installation Guide for your platform to install the Oracle database.
Step 4: Create the dsi.ora or ldap.ora File
The dsi.ora
file specifies connections for centrally managed users for Active Directory.
The ldap.or
a file can also specifies the connection to the Active Directory server. But because ldap.ora
may already be used (or may be used in the future) for other services like net naming services, Oracle recommends the use of dsi.ora
for centrally managed users.
About Using a dsi.ora File
You use a dsi.ora
file to specify an Active Directory service for centrally managed users.
You must manually create the dsi.ora
file to identify the Active Directory server. The dsi.ora
file provides Active Directory connection information for all containers if it is located in the same places where the ldap.ora
file can be placed. A dsi.ora
file and a wallet in the $ORACLE_BASE/admin/db_unique_name/pdb_guid/wallet
location takes precedence over the main dsi.ora
file for that PDB only.
Note:
If you are usingldap.ora
for naming services, then do not make any changes to ldap.ora
and its wallet for the CMU with Active Directory configuration. Only use dsi.ora
to configure CMU-Active Directory.
When you create the dsi.ora
file, Oracle Database searches for it in the following order (same locations as the Database would search for the ldap.ora
file):
-
$LDAP_ADMIN
environment variable setting -
$ORACLE_HOME/ldap/admin
directory -
$TNS_ADMIN
environment variable setting -
$ORACLE_HOME/network/admin
directory
Oracle recommends the use of directories under $ORACLE_BASE
that are not under $ORACLE_HOME
. Starting with Oracle Database 18c, there is an option to have read-only $ORACLE_HOME
so dsi.ora
should be placed in a directory outside of $ORACLE_HOME
for future-proofing your architecture.
Oracle recommends only using dsi.ora to identify the Active Directory server for centrally managed users. If both dsi.ora
and ldap.ora
happen to be configured in the same database for centrally managed users for Active Directory, dsi.ora will take precedence over the ldap.ora
file if both are located in the same directory. If they are located in different directories, then the first one that Oracle finds in the location precedence list above will be used to find the Active Directory server.
You can specify dsi.ora
files for individual PDBs in a multitenant database. A PDB specific dsi.ora file will override the CMU-Active Directory settings for the general database for that one PDB. Different PDBs can connect to different Active Directory servers for CMU. The dsi.ora
files for PDBs are located in the same directory as the wallet for that PDB.
Wallet locations for individual container in a multitenant database should place the wallet files in the $ORACLE_BASE/admin/db_unique_name/pdb_guid/wallet
directory. To find the db unique
name, connect to the CDB root and execute the following query:
SELECT INSTANCE_NAME FROM V$INSTANCE;
To find the pdb_guid
, from the CDB root, execute the following query:
SELECT PDB_ID,PDB_NAME,GUID FROM DBA_PDBS;
You can also place dsi.ora
in the wallet directory for the CDB root container, but that will only connect the CDB root container to the Active Directory – not the entire database.
Creating the dsi.ora File
The dsi.ora
configuration file sets the information to find the Active Directory server for centrally managed users.
dsi.ora
configuration file:
- Log in to the server where the Oracle database is located.
- Go to the directory where you want to create the
dsi.ora
file. - Add the following parameters to the
dsi.ora
file:DSI_DIRECTORY_SERVERS
, which sets the Active Directory server host and port name, and alternate directory servers. The directory server name must be a fully qualified name. For example:DSI_DIRECTORY_SERVERS = (ldap-server.us.example.com:389:636, sparky.us.example.com:389:636)
DSI_DEFAULT_ADMIN_CONTEXT
, which sets the names of the Active Server domains in which the Active Server directory users are located. For example:DSI_DEFAULT_ADMIN_CONTEXT = "o=Example,c=US"
DSI_DIRECTORY_SERVER_TYPE
, which determines the Active Directory server access. You must set it toAD
for Active Directory. Enter this value in upper case.DSI_DIRECTORY_SERVER_TYPE = AD
About Using an ldap.ora File
You can use an ldap.ora
file to specify an Active Directory server for centrally managed services.
If you are already using an ldap.ora
file for another purpose such as net naming services, then you must use the dsi.ora
file to configure centrally managed users to connect with Active Directory for user authentication and authorization. Even if Active Directory is already being used for net naming services, you must create and use a dsi.ora
file to identify the Active Directory server for centrally managed users. Even if the database currently isn’t using ldap.ora
for another service, Oracle recommends using dsi.ora
in case ldap.ora
will be used at a future time for net naming services..
The benefit of using ldap.or
a is that you can use the DBCA graphical interface or the DBCA silent mode to complete the connection to the Active Directory server. When using dsi.ora
, the steps to complete the connection to Active Directory must be done separately.
If ldap.ora
is being used for naming services, then do not make any changes to ldap.ora
and its wallet for the CMU with Active Directory configuration. Only use dsi.ora
to configure CMU-Active Directory.
Typically, the ldap.ora
file is stored in the $ORACLE_HOME/network/admin
directory. Usually, the ldap.ora
file cannot be in the same directory as the WALLET_LOCATION
that is specified in the sqlnet.ora
file, unless the WALLET_LOCATION
is set to $ORACLE_HOME/network/admin
.
ldap.ora
file, Oracle Database searches for it in the following order:
-
$LDAP_ADMIN
environment variable setting -
$ORACLE_HOME/ldap/admin
directory -
$TNS_ADMIN
environment variable setting -
$ORACLE_HOME/network/admin
directory
Step 5: Request an Active Directory Certificate for a Secure Connection
After you have configured the dsi.ora
or ldap.ora
file, you are ready to prepare Microsoft Active Directory and Oracle Database certificates for a secure connection.
- Request the Active Directory certificate from an Active Directory administrator.
Step 6: Create the Wallet for a secure connection
After you have copied the Active Directory certificate, you are ready to add it to the Oracle wallet.
Step 7: Configure the Microsoft Active Directory Connection
Next, you are ready to connect the database to Active Directory using the settings you have so far.
About Configuring the Microsoft Active Directory Connection
To configure the Microsoft Active Directory connection, you can set the parameters in the database or use DBCA.
DBCA creates the wallet so you do not need to create it separately. DBCA only recognizes ldap.ora
that is configured for centrally managed users. If you are using dsi.ora
then you must use the manual configuration methods.
Note:
Oracle recommends using dsi.ora
for CMU-Active Directory.
Configuring the Access Manually Using Database System Parameters
You can configure the Active Directory services connection manually by using LDAP-specific Oracle Database system parameters.
show parameter ldap
Configuring the Access Using the Database Configuration Assistant GUI
Oracle Database Configuration Assistant (DBCA) completes the LDAP connection configuration and automatically creates the wallet and stores the Active Directory certificate for use. DBCA only works when ldap.ora
is configured for CMU-Active Directory.
ldap.ora
file (not dsi.ora
) to identify the Active Directory server for the centrally managed users. If you have not installed the database yet, then you can install the software using Oracle Universal Installer (OUI) and then use DBCA to create the database at the same time as it configures the Active Directory centrally managed user connection.
Configuring Authentication for Centrally Managed Users
You can configure password authentication, Kerberos authentication, or public key infrastructure (PKI) authentication.
Configuring Password Authentication for Centrally Managed Users
Configuring password authentication for centrally managed users entails the use of a password filter with Active Directory to store Oracle Database password verifiers.
About Configuring Password Authentication for Centrally Managed Users
To configure password authentication, you must deploy a password filter, set a password file, extend the Active Directory schema, and create groups for password verifiers.
For password authentication, because Oracle Database does not use ldapbind
command to connect with Active Directory, you must install an Oracle filter and extend the Active Directory schema. The Oracle filter that you install in Active Directory creates Oracle-specific password verifiers when Active Directory users update their passwords. The Oracle filter does not generate all required Oracle password verifiers when it is first installed; the Oracle filter only generates the Oracle password verifier for a user when the user changes their Active Directory password.
To maintain backwards compatibility (if your site requires it), the Oracle filter can generate password verifiers to work with Oracle Database clients for releases 11g, 12c, and 18c. The Oracle password filter uses Active Directory groups named ORA_VFR_MD5
(for WebDAV
), ORA_VFR_11G
(for release 11g) and ORA_VFR_12C
(for releases 12c and 18c) to determine which Oracle Database password verifiers to generate. These groups must be created in Active Directory for the Oracle password verifiers to be generated for group member users. These are separate groups that dictate which specific verifiers should be generated for the Active Directory users. For example, if ten directory users need to log in to a newly created Oracle Database release 18c database that only communicated with Oracle Database release 18c and 12c clients, then an Active Directory group ORA_VFR_12C
will have ten Active Directory users as members. The Oracle filter will only generate 12C verifiers for these ten Active Directory users when they change passwords with Active Directory (18c verifiers are the same as 12c verifiers)
Configuring Password Authentication for a Centrally Managed User
You must perform the password authentication configuration in both the Active Directory server and the Oracle database.
Logging in to an Oracle Database Using Password Authentication
For password authentication, centrally managed users users have two choices of how to log in to the database.
To log in to the database that is configured to connect to Active Directory, an Active Directory user can use the following logon user name syntax if he or she is using password authentication:
sqlplus / nolog connect "Windows_domain\Active_Directory_user_name"@tnsname_of_database Password: password
Alternatively, the user can use their Active Directory Windows user logon name with the DNS domain name. For example:
connect "Active_Directory_user_name@domain.example.com"@tnsname_of_database Password: password
Configuring Kerberos Authentication for Centrally Managed Users
If you plan to use Kerberos authentication, then you must configure Kerberos in the Oracle database that will be integrated with Microsoft Active Directory.
Related Topics
Configuring Authorization for Centally Managed Users
With centrally managed users, you can manage the authorization of Oracle Database users.
Users can be added, modified, or dropped from an organization by using Active Directory without your having to add, modify, or drop the user from every database in your organization.
About Configuring Authorization for Centrally Managed Users
You can manage user authorization for a database within Active Directory.
Most Oracle Database users will be mapped to a shared database schema (user). This minimizes the work that must be done in each Oracle database when directory users are hired, change jobs within the company, or leave the company. A directory user will be assigned to an Active Directory group that is mapped to an Oracle database global user (schema). When the user logs into the database, the database will query Active Directory to find the groups the user is a member of. If your deployment is using shared schemas, then one of the groups will map to a shared database schema and the user will be assigned to that database schema. The user will have the roles and privileges that granted to the database schema. Because multiple users will be assigned to the same shared database schema, only the minimal set of roles and privileges should be granted to the shared schema. In some cases, no privileges and roles should be granted to the shared schema. Users will be assigned the appropriate set of roles and schemas through database global roles. Global roles are mapped to Active Directory groups. This way, different users can have different roles and privileges even if they are mapped to the same database shared schema. A newly hired user will be assigned to an Active Directory group mapped to a shared schema and then to one or more additional groups mapped to global roles to gain the additional roles and privileges required to complete their tasks. The combination of shared schemas and global roles allows for centralized authorization management with minimal changes to the database operationally. The database must be initially provisioned with the set of shared schemas and global roles mapped to the appropriate Active Directory groups, but then user authorization management can happen within Active Directory.
Users can also be exclusively mapped to the database global user. This requires a new user in the database that is mapped directly to the Active Directory user. New users and departing users will require updates to each database they are members of.
Users requiring administrative privileges such as SYSOPER
and SYSBACKUP
cannot be granted these through global roles. Administrative privileges can only be granted to a schema and not a role. But even in these cases with administrative privileges, shared schemas can be used to provide ease of user authorization management. Using a shared schema with the SYSOPER
privilege will allow new users to be easily added to the Active Directory group mapped to the schema with SYSOPER
without having to create a new user schema in the database. Even if only one user is assigned to the shared schema, it can still be managed centrally.
When using global roles to grant privileges and roles to the user, remember that the maximum number of enabled roles in a session is 150.
The following types of global user mappings are supported for authorization:
-
Map shared global users, in which directory users are assigned to a shared database schema (user) through the mapping of a directory group to the shared schema. The directory users that are members of the group can connect to the database through this shared schema. Use of shared schemas allows for centralized management of user authorization in Active Directory.
-
Exclusive global user mappings, in which a dedicated database user is exclusively mapped to a directory user. Not as common as the shared database schema, this user is created for direct database access by using either SQL*Plus or the schema user for two-tier or three-tier applications. Oracle recommends that you grant database privileges to these users through global roles, which facilitates authorization management. However, these users also can have direct privilege grants in the Oracle database, although this is not recommended. This is because two-tier and three-tier applications can use the global user as the database schema, so the global user has the full database privileges on the schema objects as the owner.
It is common for a directory user to be a member of multiple groups. However, only one of these groups should be mapped to a shared schema.
Mapping a Directory Group to a Shared Database Global User
Most users of the database will be mapped to a shared global database user (schema) through membership in a directory group.
CREATE USER
and ALTER USER
privileges to perform these mappings. This configuration can be used for users who have the password authentication, Kerberos authentication, and public key infrastructure (PKI) authentication methods.
Mapping a Directory Group to a Global Role
Database global roles mapped to directory groups give member users additional privileges and roles above what they have granted through their login schemas.
Exclusively Mapping a Directory User to a Database Global User
You can map a Microsoft Directory user exclusively to an Oracle Database global user.
CREATE USER
and ALTER USER
privileges to perform these mappings. This configuration can be used for users who have the password authentication, Kerberos authentication, and public key infrastructure (PKI) authentication methods.
Altering or Migrating a User Mapping Definition
You can update a Directory User-to-global-user mapping by using the ALTER USER
statement.
CREATE USER
statement clauses: IDENTIFIED BY password
, IDENTIFIED EXTERNALLY
, or IDENTIFIED GLOBALLY
. This is useful when migrating users to using CMU. For example, a database user that is externally authenticated to Kerberos will be identified by their user principal name (UPN). To migrate the user to use CMU with Kerberos authentication, you would need to run the ALTER USER
statement to declare a global user and identify the user with their Active Directory domain name.
Configuring Administrative Users
Administrative users can work as they have in the past, but with CMUs, they can be controlled with centralized authentication and authorization if they are using shared schemas.
Configuring Database Administrative Users with Shared Access Accounts
Using shared accounts simplifies the management of database administrators for multiple databases as they join, move, and leave the organization.
ad_dba_backup_users
Active Directory group will be assigned to the new database shared schema with the SYSBACKUP
administrative privilege.
Configuring Database Administrative Users Using Exclusive Mapping
Database administrators can also be mapped in exclusive schemas in databases, which requires a separate global user to be created in each database for each exclusively mapped DBA.
Integration of Oracle Database with Microsoft Active Directory Password Policies
As part of the Oracle Database-Microsoft Active Directory integration, Oracle Database enforces the Active Directory password policies when Active Directory users log into the Oracle database.
As part of the Oracle Database-CMU-Active Directory integration, the Oracle Database enforces the Active Directory password policies when Active Directory users try to log into the Oracle Database. Preventing users with expired passwords and locking accounts after a specified number of failed login attempts are examples of Active Directory password policies that the Oracle database will enforce.