Use Microsoft Active Directory with Autonomous Database

With 18c new features Oracle supports Microsoft Active Directory. İf you are using Active Directory you do not need Enterprise User Security (EUS) anymore.

Here is Oracle Support Doc.

How To Configure Authentication For The Centrally Managed Users In An 18c Database (Doc ID 2462022.1)

APPLIES TO:
Oracle Database – Enterprise Edition – Version 18.1.0.0.0 and later
Advanced Networking Option – Version 18.3.0.0.0 to 18.3.0.0.0 [Release 18.1]
Information in this document applies to any platform.
GOAL
Starting with 18c database users can be directly authenticated and authorized against Active Directory without using Oracle Enterprise User Security (EUS) or another intermediary directory service. 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.

This note is intended to provide a quick overview on the steps needed to quickly setup authentication for CMU users and a few troubleshooting steps for the known issues.

Please check the documentation for details.

Important !!!

1. The minimum version requirement for Active Directory server is Windows 2008.

2. CMU is not available as a feature in Standard Edition, see Licensing Documentation Page.

SOLUTION
The Oracle Database users can be authenticated against Active Directory using one of the following methods:

Password authentication
Kerberos authentication
SSL authentication

I Configure the integration between Microsoft Active Directory and the Oracle Database

Steps to be performed on the Active Directory Server

Log to the Active Directory with a privileged account and create the oracle service user:

ad_user

user_pass

2. Grant the Oracle service directory user account the Read properties and Write lockoutTime

deleg0 deleg1

read write

3. Export the trusted certificate from Active Directory server:

C:WindowsSystem32>certutil -ca.cert root.crt

CA cert[0]: 3 — Valid

CA cert[0]:

—–BEGIN CERTIFICATE—–

MIIDlTCCAn2gAwIBAgIQPvTQKsXf26VDm6xnziB1VzANBgkqhkiG9w0BAQsFADBd

MRMwEQYKCZImiZPyLGQBGRYDY29tMRcwFQYKCZImiZPyLGQBGRYHZXhhbXBsZTEU

MBIGCgmSJomT8ixkARkWBG15YWQxFzAVBgNVBAMTDm15YWQtRlJPU1RZLUNBMB4X

……………………………………………………..
unRZRxOiczBv9fB1iM13/JTm31dz8GBElDkxGEOcsH8pt1aj/V IeGQJGSRaPIPO

xgmzWLzJjmAvT6SBXPutG0pygIuaqx9/3vk zQDF0xxBLnd37B9YHcxfFIVbQIcV

5BzhOAtcSP9m

—–END CERTIFICATE—–

CertUtil: -ca.cert command completed successfully.

C:WindowsSystem32>

Steps to be performed on the Database Server

Create the wallet to store the oracle service user credentials and the trusted certificate from the Active Directory Server. The wallet can be created in the default location or it can be placed in a custom location specified by the parameter WALLET_LOCATION in sqlnet.ora.
$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet

$ cd $ORACLE_BASE/admin/$ORACLE_SID/wallet

$ orapki wallet create -wallet . -pwd -auto_login

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Operation is successfully completed.

$ mkstore -wrl . -createEntry ORACLE.SECURITY.USERNAME oracleservice

Oracle Secret Store Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Enter wallet password:

$ mkstore -wrl . -createEntry ORACLE.SECURITY.DN cn=oracleservice,cn=users,dc=myad,dc=example,dc=com

Oracle Secret Store Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Enter wallet password:

$ mkstore -wrl . -createEntry ORACLE.SECURITY.PASSWORD

Oracle Secret Store Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Enter wallet password:

$ orapki wallet add -wallet . -trusted_cert -cert root.crt

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Cannot modify auto-login (sso) wallet

Enter wallet password:

Operation is successfully completed.

$orapki wallet display -wallet .

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Requested Certificates:

User Certificates:

Oracle Secret Store entries:

ORACLE.SECURITY.USERNAME

ORACLE.SECURITY.DN

ORACLE.SECURITY.PASSWORD

Trusted Certificates:

Subject: CN=myad-FROSTY-CA,DC=myad,DC=example,DC=com

If wallet location is not specified in the sqlnet.ora file, then the database will search the following locations in this order for the wallet. The directory location may need to be created.
For a non-multitenant database, or for the CDB root container of a multitenant database:
$ORACLE_BASE/admin/db_unique_name/wallet/
$ORACLE_HOME/admin/db_unique_name/wallet/
For a PDB in a multitenant database:

$ORACLE_BASE/admin/db_unique_name/pdb_guid/wallet/
$ORACLE_HOME/admin/db_unique_name/pdb_guid/wallet/

2. Create the dsi.ora file (For more information on the location of dsi.ora, refer to the documentation page) with the Active Directory server information:

$ cat $ORACLE_HOME/network/admin/dsi.ora
DSI_DIRECTORY_SERVERS = (ADSERVER.EXAMPLE.COM:389:636)
DSI_DEFAULT_ADMIN_CONTEXT = “ou=MyOU1,dc=example,dc=com”
DSI_DIRECTORY_SERVER_TYPE = AD

Note:

1. On the location of dsi.ora:

The preferred location for dsi.ora is the database wallet location. (For more information on the location of dsi.ora, refer to the documentation page)

2. On the content of dsi.ora:

The DSI_DIRECTORY_SERVERS should use fully qualified domain name, e.g.: ADSERVER.EXAMPLE.COM.

In case that multiple Windows domains or multiple AD servers are to be used, set them in DSI_DIRECTORY_SERVERS, and separate each AD server information with a common(,), e.g.:

DSI_DIRECTORY_SERVERS = (AD1.DOM1.EXAMPLE.COM:389:636, AD2.DOM2.EXAMPLE.COM:389:636)

In case that multiple DN of search bases are to be used, set them in DSI_DEFAULT_ADMIN_CONTEXT, and separate each DN with semicolon(;) , e.g.:

DSI_DEFAULT_ADMIN_CONTEXT =”ou=MyOU1,dc=example,dc=com; ou=MyOU2,dc=example,dc=com”

3. Set the parameters ldap_directory_access to PASSWORD

SQL> alter system set ldap_directory_access=’PASSWORD’;

System altered.

4. Set the ldap_directory_sysauth parameter to YES, so that administrative users from Active Directory can log in to Oracle Database with the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, or SYSRAC administrative privilege.

SQL> alter system set ldap_directory_sysauth =’YES’ scope=spfile;

System altered.

Note: LDAP_DIRECTORY_SYSAUTH is static parameter and change in this parameter value needs DB bounce

II Configure Password Authentication for Centrally Managed Users

Steps to be performed in the Active Directory:

Install the Password Filter and Extend the Microsoft Active Directory Schema using opwdintg.exe from $ORACLE_HOME/bin. This step creates the following three verifier groups:
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 use of the Oracle Database 12C password verifier.

newextend

2) Add the Active Directory users to any of the groups ORA_VFR_MD5, ORA_VFR_11G, and ORA_VFR_12C.

vfr

3) Update the database password file to version 12.2.

cd $ORACLE_HOME/dbs

orapwd FILE=’$ORACLE_HOME/dbs/orapworcl18′ FORMAT=12.2

Configuring Authentication for Centrally Managed Users

The users can be created in the database using exclusive schema or shared schema. In case of exclusive schema one Active Directory user is mapped to one global database user while in case of shared schema one directory group can be mapped to a shared global database user.

1. Create the globally identified user using an exclusive schema:

SQL> create user abc identified globally as ‘cn=abc,cn=users,dc=myad,dc=example,dc=com’;

User created.

SQL> grant create session to abc;

Grant succeeded.

SQL> connect “myadabc”@orcl18

Enter password:

Connected.

select SYS_CONTEXT(‘USERENV’,’NETWORK_PROTOCOL’) net_proto,sys_context(‘USERENV’, ‘AUTHENTICATED_IDENTITY’) auth_identity,SYS_CONTEXT(‘USERENV’,’SESSION_USER’) db_user,sys_context(‘USERENV’,’ENTERPRISE_IDENTITY’) ent_identity from dual;

NET_PROTO AUTH_IDENTITY DB_USER ENT_IDENTITY

———- —————————————- —————————————- ————————————————————

TCP myadabc ABC cn=abc,cn=Users,dc=myad,dc=example,dc=com

2. Create the globally identified user using a shared schema:

Note:
– The DN below ‘cn=db-access,cn=Users,dc=myad,dc=example,dc=com’ refers to an AD Group and is not an AD user.
– The AD user ‘myadabc’ is a member of the AD group ‘db-access’.

SQL> create user shared_user identified globally as ‘cn=db-access,cn=Users,dc=myad,dc=example,dc=com’;

User created.

SQL> grant create session to shared_user;

Grant succeeded.

SQL> connect abc@orcl18

Enter password:

Connected.

SQL>

NET_PROTO AUTH_IDENTITY DB_USER ENT_IDENTITY

———- —————————————- ——————– ————————————————–

TCP MYADABC SHARED_USER cn=abc,cn=Users,dc=myad,dc=example,dc=com

III Configure Authorization for Centrally Managed Users

Besides Authentication with CMU it is possible to configure authorization. There are flexibility and manageability advantages when using database global roles instead of granting privileges to a database global user/schema directly.

If an AD user is added to or removed from an AD group, the authorization will change automatically on database side.

For example:

Suppose that the AD user ‘myadabc’ is a member of the AD group ‘db-access’, then the AD user will have ‘create session’ privilege when accessing oracle database, and can further obtain different authorization after being added to or removed from other AD groups such as ‘support’ group or ‘developers’ group, as illustrated below.

SQL> create user shared_user identified globally as ‘cn=db-access,cn=Users,dc=myad,dc=example,dc=com’;
SQL> create role db_access_role identified globally as ‘cn=db-access,cn=Users,dc=myad,dc=example,dc=com’;
SQL> grant create session to db_access_role;

SQL> create role support_role identified globally as ‘cn=support,cn=Users,dc=myad,dc=example,dc=com’;
SQL> create role developer_role identified globally as ‘cn=developers,cn=Users,dc=myad,dc=example,dc=com’;

SQL> grant to support_role;

SQL> grant to developer_role;

SQL> connect abc@orcl18

Depending on which groups the AD user is a member of, the AD user will have appropriate privileges when logging in Oracle database through the shared_user.

IV Configure Kerberos Authentication
To configure Kerberos settings on the database server and on the client please use as a reference one of the following notes:

Note 1996329.1 How To Configure Kerberos Authentication In A 12c Database

Note 1304004.1 Configuring ASO Kerberos Authentication with a Microsoft Windows 2008 R2 Active Directory KDC

The user will be created this way:

SQL> create user shared_user identified globally as ‘cn=db-access,cn=Users,dc=myad,dc=example,dc=com’;
User created

SQL> grant create session to shared_user;

Grant succeeded.

$ sqlplus /@orcl18

SQL*Plus: Release 18.0.0.0.0 Production on Tue Oct 16 14:00:44 2022
Version 18.1.0.0.0

Last Successful login time: Tue Oct 16 2022 13:56:16 03:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0

SQL> show user
USER is “SHARED_USER”
SQL>

SQL> select SYS_CONTEXT(‘USERENV’,’NETWORK_PROTOCOL’) net_proto,sys_context(‘USERENV’, ‘AUTHENTICATED_IDENTITY’) auth_identity,SYS_CONTEXT(‘USERENV’,’SESSION_USER’) db_user,sys_context(‘USERENV’,’ENTERPRISE_IDENTITY’) ent_identity from dual;

NET_PROTO AUTH_IDENTITY DB_USER ENT_IDENTITY
—————————— —————————————- —————————— ————————————————————
tcp [email protected] SHARED_USER cn=def,cn=Users,dc=myad,dc=example,dc=com

V Configure SSL authentication

The general steps to configure SSL authentication and encryption in the database are detailed in the following MOS note:

Note 1381035.1 Configuring SSL Authentication With Client Certificates Signed By The Server Using orapki

In case of Centrally Managed Users the database must be integrated with Active Directory so the database client must use SSL user certificates that have been issued for the Active Directory users (with correct DN of AD users).

For the SSL setup we will use the database server wallet created at the step I and we will create the self_signed certificate for Oracle Certification Authority:

$ orapki wallet add -wallet . -dn “cn=orcl18” -keysize 2048 -self_signed -validity 365 -pwd

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Operation is successfully completed.

$ orapki wallet display -wallet /u02/app/oracle/admin/orcl18/wallet

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Requested Certificates:

User Certificates:

Subject: CN=orcl18

Oracle Secret Store entries:

ORACLE.SECURITY.DN

ORACLE.SECURITY.PASSWORD

ORACLE.SECURITY.USERNAME

Trusted Certificates:

Subject: CN=myad-FROSTY-CA,DC=myad,DC=example,DC=com

Subject: CN=orcl18

The next steps are to create and export the request for the client certificate and signed it using the Oracle CA.

On the client we create and export the request for the client certificate:

$ orapki wallet add -wallet . -dn “cn=def,cn=users,dc=myad,dc=example,dc=com” -keysize 2048 -pwd

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Operation is successfully completed.

$ orapki wallet export -wallet . -dn “cn=def,cn=users,dc=myad,dc=example,dc=com” -request cert_user.txt -pwd

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Operation is successfully completed.

We copy the file cert_user.txt in the server wallet to be signed using Oracle CA:

$ orapki cert create -wallet . -request cert_user.txt -cert def.crt -validity 365 -pwd

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Operation is successfully completed.

Still in the server wallet we export the trusted certificate and copy it along with the signed user certificate:

$ orapki wallet export -wallet . -dn “CN=orcl18” -cert trust_orcl18.crt

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Operation is successfully completed.

$ cp trust_orcl18.crt def.crt /home/oracle/TNS/client_wallet

In the client wallet we will import the signed user certificate and the trusted certificate:

$ orapki wallet add -wallet . -user_cert -cert def.crt -pwd

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Operation is successfully completed.

$ orapki wallet add -wallet . -trusted_cert -cert trust_orcl18.crt -pwd

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Operation is successfully completed.

Client wallet will have the following content:

$ orapki wallet display -wallet .

Oracle PKI Tool Release 18.0.0.0.0 – Production

Version 18.1.0.0.0

Requested Certificates:

User Certificates:

Subject: CN=def,CN=users,DC=myad,DC=example,DC=com

Trusted Certificates:

Subject: CN=orcl18

To test the connection we will use the shared_user identified globally created at the step IV

$ sqlplus /@orcl18_ssl

SQL*Plus: Release 18.0.0.0.0 Production on Sat Nov 17 15:33:01 2022

Version 18.1.0.0.0

Last Successful login time: Sat Nov 17 2022 15:28:56 03:00

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

Version 18.3.0.0.0

SQL> set linesize 800

column NET_PROTO format a10

column AUTH_IDENTITY format a60

column DB_USER format a80

column ENT_IDENTITY format a80

column DB_USER format a20

SQL>

NET_PROTO AUTH_IDENTITY DB_USER ENT_IDENTITY

————– —————————————————————— ———————- ———————————————————–

tcps CN=def,CN=users,DC=myad,DC=example,DC=com SHARED_USER cn=def,cn=users,dc=myad,dc=example,dc=com

VI Known Issues and Troubleshooting Steps

1. Connection using password authentication fails with ORA-28276:

SQL> connect “myaddef”@orcl18

Enter password:

ERROR:

ORA-28276: Invalid ORACLE password attribute.

Cause: The orclCommonAttribute attribute has not been correctly populated with user password.

Example:

[oracle@seclin lib]$ ldapsearch -h -p 389 -D “cn=oracleservice,cn=users,dc=myad,dc=example,dc=com” -w **** -U 2 -W “file:” -P -b “dc=myad,dc=example,dc=com” -s sub “(sAMAccountName=def*)” dn orclCommonAttribute

CN=def,CN=Users,DC=myad,DC=example,DC=com

orclCommonAttribute=

Solution: Run ‘opwdintg.exe’ to install password filter on AD server, restart the machine, then assign AD user to appropriate ORA_VFR group, reset user password on Active Directory and run ldapsearch to check that password has been generated. (Note: the Windows domain controller must be restarted after installing the password filter, otherwise the password filter will not start to work.)

2. The connection with a kerberos user fails with ORA-28030 while the connection with password authentication fails with ORA-28043. Ldapbind on the port 636 completes successfully.

$ sqlplus /@orcl18

SQL*Plus: Release 18.0.0.0.0 Production on Mon Oct 1 16:53:01 2022

Version 18.1.0.0.0

ERROR:

ORA-28030: Server encountered problems accessing LDAP directory service

Enter user-name:

$ sqlplus “myaddef”

SQL*Plus: Release 18.0.0.0.0 Production on Mon Nov 5 15:52:34 2022

Version 18.1.0.0.0

Enter password:

ERROR:

ORA-28043: invalid bind credentials for DB-OID connection

$ ldapbind -h -p 636 -D “cn=oracleservice,cn=users,dc=myad,dc=example,dc=com” -U 2 -W “file:” -P

bind successful

Cause: Bug 28994890

Solution: Apply Patch 28994890

3. Connection fails with ORA-28293:

$ sqlplus /@orcl18

SQL*Plus: Release 18.0.0.0.0 Production on Mon Oct 1 16:53:01 2022

Version 18.1.0.0.0

ERROR:

ORA-28293: No matched Kerberos Principal found in any user entry

Cause: Bug 28994890

Solution: Apply Patch 28994890

4. Connection fails with ORA-28300: No permission to read user entry in LDAP directory service.

ORA-28300 error is observed in the CMU trace as follows:

2022-06-27 19:51:55.0 – KZLG_ERR: failed to modify user status Insufficient access
2022-06-27 17:57:27.0 – KZLG_ERR: LDAPERR=50, OER=28300
Cause: The Oracle service directory user does not have required permissions to access the AD user who tries to login to Oracle database.

Solution: Follow the instructions in this document to grant the service user the required permissions to access the properties of the AD user who tries to login to database.

– Steps to be performed on the Active Directory Server
2. Grant the Oracle service directory user account the Read properties and Write lockoutTime (permissions to access the properties of the AD user who tries to login to the database).

VII How to investigate connection issues

1) KZLG tracing using Note 2470608.1 Tracing CMU connection issues

Active directory on oracle database

Using oracle autonomous database on shared exadata infrastructure

Добавить комментарий Отменить ответ