Use Microsoft Active Directory with Autonomous Database

Configure CMU with Microsoft Active Directory on Autonomous Database

The integration of Autonomous Database with Centrally Managed Users (CMU) provides integration with Microsoft Active Directory. CMU with Active Directory works by mapping Oracle database users and roles to Microsoft Active Directory users and groups.

Note:

CMU supports Microsoft Active Directory servers but does not support the Azure Active Directory service.

See Configuring Centrally Managed Users with Microsoft Active Directory for information on Centrally Managed Users (CMU).

The following are required before you configure the connection from Autonomous Database to Active Directory:

You must have Microsoft Active Directory installed and configured. See the following for more information:
- How To Configure Authentication For The Centrally Managed Users In An 18c Database (Doc ID 2462012.1)
- AD DS Getting Started
You must create an Oracle service directory user in Active Directory. See Connecting to Microsoft Active Directory for information on the Oracle service directory user account.
An Active Directory system administrator must have installed Oracle password filter on the Active Directory servers, and set up Active Directory groups with Active Directory users to meet your requirements. Only password authentication is supported with CMU for Autonomous Database, so you must use the included utility, opwdintg.exe, to install the Oracle password filter on Active Directory, extend the schema, and create three new ORA_VFR groups for three types of password verifier generation. See Connecting to Microsoft Active Directory for information on installing the Oracle password filter.
The Active Directory servers must be accessible from Autonomous Database through the public internet and the port 636 of the Active Directory servers must be open to Autonomous Database in Oracle Cloud Infrastructure, so that Autonomous Database can have secured LDAP access over TLS/SSL to the Active Directory servers through the internet.

You can also extend your on-premise Active Directory to Oracle Cloud Infrastructure, where you can set up Read Only Domain Controllers (RODCs) for the on-premise Active Directory. Then you can use these RODCs in Oracle Cloud Infrastructure to authenticate and authorize the on-premise Active Directory users for access to Autonomous Databases. See Microsoft Windows: Extending Active Directory to Oracle Cloud Infrastructure for more information.
You need to create or obtain the CMU configuration database wallet, cwallet.sso, and the CMU configuration file dsi.ora. These are the files you need to obtain and copy to configure your Autonomous Database to use Active Directory. You can configure CMU for an on-premise database and validate the wallet and the dsi.ora by verifying that an Active Directory user can successfully log on to the on-premise database with these configuration files.

For details on the wallet file for CMU, see Create the Wallet for a Secure Connection and Verify the Oracle Wallet.

For details on the dsi.ora file for CMU, see Creating the dsi.ora File.

The following limitation applies for CMU Active Directory with Autonomous Database:

Only "password authentication" is supported for CMU with Autonomous Database. Other CMU authentication methods including Kerberos and PKI are not supported with Autonomous Database.

Note:

When you perform the configuration steps, connect to the database as the ADMIN user.

To configure CMU on Autonomous Database, as the ADMIN user, you upload the CMU configuration database wallet, cwallet.sso, and the CMU configuration file, dsi.ora, to your Object Store, and then use DBMS_CLOUD.GET_OBJECT to copy these files to a file system directory path specified by a directory object on the database.

To configure Autonomous Database to connect to Active Directory servers:

On your database, create a new directory object or choose an existing directory object. This is the directory where you store the wallet and the configuration file for connecting to Active Directory:
For example:
```
CREATE OR REPLACE DIRECTORY cmu_wallet_dir AS 'cmu_wallet';
```
Upload the CMU configuration database wallet, cwallet.sso, and the CMU configuration file, dsi.ora, to your Object Store. This step depends on the Object Store you use.

The dsi.ora configuration file contains the information to find the Active Directory servers.

If you are using Oracle Cloud Infrastructure Object Store, see Putting Data into Object Storage for details on uploading files.
Using DBMS_CLOUD.GET_OBJECT copy the CMU configuration database wallet and CMU configuration file from your Object Store to the directory that you created or chose in Step 1.
For example, use DBMS_CLOUD.GET_OBJECT to copy the files from Object Store to CMU_WALLET_DIR as follows:
```
BEGIN
   DBMS_CLOUD.GET_OBJECT(
      credential_name => 'DEF_CRED_NAME',
      object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
      directory_name => 'CMU_WALLET_DIR');
   DBMS_CLOUD.GET_OBJECT(
      credential_name => 'DEF_CRED_NAME',
      object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/dsi.ora',
      directory_name => 'CMU_WALLET_DIR');
END;
/
```
In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.
See GET_OBJECT Procedure and Function for more information.

Set the database property CMU_WALLET to the name of the directory object that you created or chose in Step 1.

ALTER DATABASE PROPERTY SET CMU_WALLET='directory_object_name';

For example:

ALTER DATABASE PROPERTY SET CMU_WALLET='CMU_WALLET_DIR';

Use the following SQL statement to query the property value of the database property CMU_WALLET:

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='CMU_WALLET';

For example:

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='CMU_WALLET';

PROPERTY_VALUE
--------------------------------
CMU_WALLET_DIR

Use the following SQL statement to query the directory path of the directory object set to the property CMU_WALLET:

SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='directory_object_name';

For example:

SQL> SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='CMU_WALLET_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/file_system_directory_path_example/cmu_wallet

In the CREATE OR REPLACE DIRECTORY statement in Step 1, if you want to preserve case for the directory object name then you need to include double quotes. For example:

CREATE OR REPLACE DIRECTORY "CMU_wallet_dir" AS 'cmu_wallet';

Then the case would be preserved and you would set the property CMU_WALLET as follows:

ALTER DATABASE PROPERTY SET CMU_WALLET='CMU_wallet_dir';

To maintain security remove the CMU configuration database wallet, cwallet.sso and the CMU configuration file dsi.ora from Object Store. For example, on Autonomous Database use DBMS_CLOUD.DELETE_OBJECT to delete these files from Object Store.
Set the LDAP_DIRECTORY_ACCESS parameter value to PASSWORD to enable the access from Autonomous Database to Active Directory.
```
ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS=PASSWORD;
```

Note:

See Disable Active Directory Access on Autonomous Database for instructions to disable the access from Autonomous Database to Active Directory.

See GET_OBJECT Procedure and Function for more information on DBMS_CLOUD.GET_OBJECT.

See DELETE_OBJECT Procedure for more information on DBMS_CLOUD.DELETE_OBJECT.

See Configuring Centrally Managed Users with Microsoft Active Directory for more information on configuring CMU with Microsoft Active Directory.

Add Microsoft Active Directory Roles on Autonomous Database

To add Active Directory roles, map the database global roles to Active Directory groups with CREATE ROLE or ALTER ROLE statements (and include the IDENTIFIED GLOBALLY AS clause).

To add global roles for Active Directory groups on Autonomous Database:

Log in as the ADMIN user to the database that is configured to use Active Directory (the ADMIN user has the CREATE ROLE and ALTER ROLE system privileges that you need for these steps).
Set the database authorization for Autonomous Database roles with CREATE ROLE or ALTER ROLE statement. Include the IDENTIFIED GLOBALLY AS clause and specify the DN of an Active Directory group.
Use the following syntax to map a directory user group to a database global role:
```
CREATE ROLE global_role IDENTIFIED GLOBALLY AS 
     'DN_of_an_AD_GROUP_of_WHICH_the_AD_USER_IS_a_MEMBER';
```
For example:
```
CREATE ROLE widget_sales_role IDENTIFIED GLOBALLY AS
     'CN=widget_sales_group,OU=sales,DC=production,DC=example,DC=com';
```
In this example all members of the widget_sales_group are authorized with the database role widget_sales_role when they log in to the database.
Use GRANT statements to grant the required privileges or other roles to the global role.
For example:
```
GRANT CREATE SESSION TO WIDGET_SALES_ROLE;
GRANT DWROLE TO WIDGET_SALES_ROLE;
```
DWROLE is a predefined role that has common privileges defined. See Manage User Privileges on Autonomous Database - Connecting with a Client Tool for information on setting common privileges for Autonomous Database users.
If you want to add additional roles, follow these steps for each role.

See Configuring Authorization for Centrally Managed Users for more information on configuring roles with Microsoft Active Directory.

Add Microsoft Active Directory Users on Autonomous Database

To add Active Directory users to access a database, map database global users to Active Directory groups or users with CREATE USER or ALTER USER statements (with IDENTIFIED GLOBALLY AS clause).

The integration of Autonomous Database with Active Directory works by mapping Microsoft Active Directory users and groups directly to Oracle database global users and global roles.

To add global users for Active Directory groups or users on Autonomous Database:

Log in as the ADMIN user to the database that is configured to use Active Directory (the ADMIN user has the required CREATE USER and ALTER USER system privileges that you need for these steps).
Set database authorization for Autonomous Database users with CREATE USER or ALTER USER statements and include the IDENTIFIED GLOBALLY AS clause, specifying the DN of an Active Directory user or group.
Use the following syntax to map a directory user to a database global user:
```
CREATE USER global_user IDENTIFIED GLOBALLY AS 'DN_of_an_AD_USER';
```
Use the following syntax to map a directory group to a database global user:
```
CREATE USER global_user IDENTIFIED GLOBALLY AS
    'DN_of_an_AD_GROUP_of_WHICH_the_AD_USER_IS_a_MEMBER';
```
For example, to map a directory group named widget_sales_group in the sales organization unit of the production.example.com domain to a shared database global user named WIDGET_SALES:
```
CREATE USER widget_sales IDENTIFIED GLOBALLY AS
     'CN=widget_sales_group,OU=sales,DC=production,DC=example,DC=com';
```
This creates a shared global user mapping. The mapping, with global user widget_sales, is effective for all users in the Active Directory group. Thus, anyone in the widget_sales_group can log in to the database using their Active Directory credentials (through the shared mapping of the widget_sales global user).
If you want Active Directory users to use an existing database user, own its schema, and own its existing data, then use ALTER USER to alter an existing database user to map the user to an Active Directory group or user.
- Use the following syntax to alter an existing database user to map to an Active Directory user:
```
ALTER USER existing_database_user IDENTIFIED GLOBALLY AS 'DN_of_an_AD_USER';
```
- Use the following syntax to alter an existing database user to map to an Active Directory group:
```
ALTER USER existing_database_user 
     IDENTIFIED GLOBALLY AS 'DN_of_an_AD_GROUP_of_WHICH_the_AD_USER_IS_a_MEMBER';
```
If you want to create additional global user mappings for other Active Directory groups, follow these steps for each Active Directory group.

See Configuring Authorization for Centrally Managed Users for more information on configuring users with Microsoft Active Directory.

Connect to Autonomous Database with Active Directory User Credentials

After the ADMIN user completes the CMU Active Directory configuration steps and creates global roles and global users, users log in to the database using their Active Directory username and password.

Note:

Do not log in using a Global User name. Global User names do not have a password and connecting with a Global User name will not be successful.

To log in to the database using an Active Directory username and password, connect as follows:
```
CONNECT "AD_DOMAIN\AD_USERNAME"/AD_USER_PASSWORD@TNS_ALIAS_OF_THE_AUTONOMOUS_DATABASE;
```
For example:
```
CONNECT "production\pfitch"/password@adbname_medium;
```
You need to include double quotes when the Active Directory domain is included along with the username, as with this example: "production\pfitch".

In this example, the Active Directory username is pfitch in domain production. The Active Directory user is a member of widget_sales_group group which is identified by its DN 'CN=widget_sales_group,OU=sales,DC=production,DC=example,DC=com'.

After configuring CMU with Active Directory on Autonomous Database and setting up Active Directory authorization, with global roles and global users, you can connect to your database using any of the connection methods described in Connecting to Autonomous Database. When you connect, if you want to use an Active Directory user then use Active Directory user credentials. For example, provide a username in this form, "AD_DOMAIN\AD_USERNAME" (double quotes must be included), and use your AD_USER_PASSWORD for the password.

If your Autonomous Database instance is in Restricted Mode, this mode only allows users with the RESTRICTED SESSION privilege to connect to the database. The ADMIN user has this privilege. You can use restricted access mode to perform administrative tasks such as indexing, data loads, or other planned activities. See Change Autonomous Database Operation Mode to Read/Write Read-Only or Restricted for more information.

Tools Restrictions with Active Directory on Autonomous Database

Notes for using Autonomous Database tools with Active Directory:

Oracle Application Express is not supported for Active Directory users with Autonomous Database. See Create Oracle Application Express Workspaces in Autonomous Database for information on using regular database users with Autonomous Database.
Oracle SQL Developer Web is not supported for Active Directory users with Autonomous Database. See Provide SQL Developer Web Access to Database Users for information on using regular database users with Autonomous Database.
Oracle Machine Learning Notebooks are not supported for Active Directory users with Autonomous Database. See Add Existing Database User Account to Oracle Machine Learning Notebooks for information on using regular database users with Autonomous Database.

Verify Active Directory User Connection Information with Autonomous Database

When users log in to the database using their Active Directory username and password, you can verify and audit the user activity.

For example, when the user pfitch logs in:

CONNECT "production\pfitch"/password@exampleadb_medium;

The Active Directory user's log on username (samAccountName) is pfitch and widget_sales_group is the Active Directory Group name, and widget_sales is the database global user.

After pfitch logs in to the database, the command SHOW USER shows the global user name:

SHOW USER;

USER is "WIDGET_SALES"

The following command shows the DN (Distinguished Name) of the Active Directory user:

SELECT SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY') FROM DUAL;

For example you can verify this centrally managed user's enterprise identity:

SQL> SELECT SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
----------------------------------------------------------------------
cn=Peter Fitch,ou=sales,dc=production,dc=examplecorp,dc=com

The following command shows the "AD_DOMAIN\AD_USERNAME":

SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

For example, the Active Directory authenticated user identity is captured and audited when the user logs on to the database:

SQL> SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
----------------------------------------------------------------------
production\pfitch

See Verifying the Centrally Managed User Logon Information for more information.

Remove Active Directory Users and Roles on Autonomous Database

To remove Active Directory users and roles from Autonomous Databases, use standard database commands. This does not remove the related Active Directory users or groups that were mapped from the dropped database user or role.

To remove users from Autonomous Database:

Log in to the database that is configured to use Active Directory as a user who has been granted the DROP USER or DROP ROLE system privilege.
Drop the global users or the global roles that are mapped to Active Directory groups or users with DROP USER or DROP ROLE.
See Remove Users on Autonomous Database for more information.

Disable Active Directory Access on Autonomous Database

Describes the steps to disable the LDAP access from your Autonomous Database to Active Directory, and covers the steps to remove the CMU configuration from your Autonomous Database.

After you configure your database to access Active Directory, you can disable the access as follows:

Set the LDAP_DIRECTORY_ACCESS parameter value to NONE to disable the access from Autonomous Database to Active Directory:
```
ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS=NONE;
```
You can re-enable the Active Directory access by setting the parameter value to PASSWORD:
```
ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS=PASSWORD;
```

Note:

If you no longer want to maintain the connection to Active Directory, then do the following:

Remove the database property CMU_WALLET by executing the following SQL statement:
```
ALTER DATABASE PROPERTY REMOVE CMU_WALLET;
```
Remove the CMU configuration files, the database wallet cwallet.sso and dsi.ora, from the directory that you created or chose when you configured CMU. For example, use DBMS_CLOUD.DELETE_FILE to remove these files from CMU_WALLET_DIR.

See DELETE_FILE Procedure for more information on DBMS_CLOUD.DELETE_FILE.