PART -2 Configuring Centrally Managed Users (CMU) with Microsoft Active Directory -> Kerberos authentication

 

For Kerberos authentication, first we need to configure Kerberos in the Oracle database that will be integrated with Microsoft Active Directory.

CMU-Active Directory only supports the Microsoft Active Directory Kerberos server. Other non-Active Directory Kerberos servers are not supported with CMU-Active Directory.

In this blog first I will setup the Kerberos authentication and then configure the CMU part.

NOTE: I am using the same active directory server which I used in my first blog.

https://manoj-dbahelp.blogspot.com/2022/07/part-1-configuring-centrally-managed.html

Configure Kerberos authentication:

To enable Kerberos authentication for Oracle Database, follow below configuration steps.

1. In Kerberos, users are known as or referred to as “principals”. The “service principal” is named in the format:

kservice/kinstance@REALM

For Oracle and CMU it is typically:

oracle/<DB Server FQDN>@<domain>

So, the actual principal name will be:

oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECMU.NET

To add the necessary principal or user to Active Directory, we can use below PowerShell command on AD server.

PS C:\Users\Administrator> New-ADUser `
>>    -Name "dbserv1" `
>>    -UserPrincipalName "dbserv1.ad1.cmuvnc.oraclevcn.com@stagecmu.net"
>>    -DisplayName "dbserv1.ad1.cmuvnc.oraclevcn.com" `
>>    -Description "ServiceOracle Kerberos Service Principal." `
>>    -Path "CN=Managed Service Accounts,DC=stagecmu,DC=net" `
>>    -ChangePasswordAtLogon $false `
>>    -PasswordNeverExpires $true `
>>    -CannotChangePassword $true `
>>    -Enabled $true `
>>    -AccountPassword(Read-Host -AsSecureString "Initial Password:")
Initial Password:: ********
PS C:\Users\Administrator>

To confirm user created successfully..

PS C:\Users\Administrator> dsquery user -name dbserv1
"CN=dbserv1,CN=Managed Service Accounts,DC=STAGECMU,DC=NET"
PS C:\Users\Administrator>

2. Extract a Service Key Table from Kerberos

PS C:\Users\Administrator> ktpass.exe -princ oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECMU.NET `
>> -mapuser dbserv1.ad1.cmuvnc.oraclevcn.com `
>> -crypto all `
>>  -pass * `
>> -out .\$Env:COMPUTERNAME.keytab
Targeting domain controller: DC1.STAGECMU.NET
Successfully mapped oracle/dbserv1.ad1.cmuvnc.oraclevcn.com to dbserv1.
Type the password for oracle/dbserv1.ad1.cmuvnc.oraclevcn.com:
Type the password again to confirm:
Password successfully set!
WARNING: pType and account type do not match. This might cause problems.
Key created.
Key created.
Key created.
Key created.
Key created.
Output keytab to .\DC1.keytab:
Keytab version: 0x502
keysize 79 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECMU.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x1 (DES-CBC-CRC)
keylength 8 (0x1c62b5d6d543765e)
keysize 79 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECMU.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x3 (DES-CBC-MD5)
keylength 8 (0x1c62b5d6d543765e)
keysize 87 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECMU.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x17 (RC4-HMAC) ke
ylength 16 (0xa3a685f89364d4a5182b028fbe79ac38)
keysize 103 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECMU.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x12 (AES256-SHA1
) keylength 32 (0x098cf83240ea92e715776aa2572ea36524d0df03e87993162357ed65c521f574)
keysize 87 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECMU.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x11 (AES128-SHA1)
 keylength 16 (0x2c8122256d70d39af3d81117613bb225)
PS C:\Users\Administrator>

3. Now, copy the exported keytable file to the Database Server (DBSERV1)

PS C:\Users\Administrator> scp .\$Env:computername.keytab opc@10.0.1.101:/tmp/
DC1.keytab                                                                                                                                     100%  457     0.5KB/s   00:00
PS C:\Users\Administrator>

4. Set database Initialization Parameters

[oracle@dbserv1 tmp]$ echo "
> alter system set os_authent_prefix='' scope=spfile;
> shutdown immediate
> startup
> " | sqlplus -s / as sysdba

System altered.

Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 4630509232 bytes
Fixed Size                  9143984 bytes
Variable Size            1560281088 bytes
Database Buffers         3053453312 bytes
Redo Buffers                7630848 bytes
Database mounted.
Database opened.
[oracle@dbserv1 tmp]$

Set this value to null because Kerberos user names can be long, and Oracle user names are limited to 30 bytes. Setting this parameter to null overrides the default value of OPS$.

5. Set sqlnet.ora Parameters

SQLNET.ORA file on the database server must be adjusted to provide the relevant Kerberos settings including where to access the copied key table file.

mkdir -p ${ORACLE_HOME}/network/admin/kerberos
cp /tmp/*.keytab ${ORACLE_HOME}/network/admin/kerberos/keytab
oklist -k -t -old ${ORACLE_HOME}/network/admin/kerberos/keytab

[oracle@dbserv1 tmp]$ echo "
> SQLNET.KERBEROS5_KEYTAB=${ORACLE_HOME}/network/admin/kerberos/keytab
> SQLNET.KERBEROS5_CONF=${ORACLE_HOME}/network/admin/kerberos/krb5.conf
> SQLNET.KERBEROS5_CONF_MIT=TRUE
> SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
> SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
> " >> ${ORACLE_HOME}/network/admin/sqlnet.ora
[oracle@dbserv1 tmp]$

6. Now update the Kerberos configuration file with the domain name we used here. (STAGECMU.NET)

[oracle@dbserv1 tmp]$ cat <<EOT >> ${ORACLE_HOME}/network/admin/kerberos/krb5.conf
> [libdefaults]
> default_realm = STAGECMU.NET
> [realms]
> STAGECMU.NET = {
>   kdc = DC1.STAGECMU.NET:88
> }
> [domain_realm]
> .`dnsdomainname` = STAGECMU.NET
> `dnsdomainname` = STAGECMU.NET
> EOT
[oracle@dbserv1 tmp]$
[oracle@dbserv1 tmp]$ cat ${ORACLE_HOME}/network/admin/kerberos/krb5.conf
[libdefaults]
default_realm = STAGECMU.NET
[realms]
STAGECMU.NET = {
  kdc = DC1.STAGECMU.NET:88
}
[domain_realm]
.ad1.cmuvnc.oraclevcn.com = STAGECMU.NET
ad1.cmuvnc.oraclevcn.com = STAGECMU.NET
[oracle@dbserv1 tmp]$

7. Now let's create Database Users to use Kerberos Authentication. I already have “Divya” user in Active Directory, let's create an IDENTIFIED EXTERNALLY database user.

[oracle@dbserv1 tmp]$ echo "
> alter session set container=ORCLPDB1;
> create user \"DIVYA@STAGECMU.NET\" identified externally;
> grant create session to \"DIVYA@STAGECMU.NET\";
> grant select on v_\$database to \"DIVYA@STAGECMU.NET\";
> " | sqlplus -s / as sysdba

Session altered.


User created.


Grant succeeded.


Grant succeeded.

[oracle@dbserv1 tmp]$

8. Before you can connect to the database, you must ask the Key Distribution Center (KDC) for an initial ticket. To obtain tickets manually, we can use the okinit and oklist utilities.

[oracle@dbserv1 tmp]$ okinit divya

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 08-JUL-2022 19:35:13

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /opt/oracle/product/19c/dbhome_1/network/admin/kerberos/krb5.conf.
Password for divya@STAGECMU.NET:
[oracle@dbserv1 tmp]$ oklist

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 08-JUL-2022 19:35:30

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /opt/oracle/product/19c/dbhome_1/network/admin/kerberos/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: divya@STAGECMU.NET

Valid starting     Expires            Service principal
07/08/22 19:35:23  07/09/22 05:35:23  krbtgt/STAGECMU.NET@STAGECMU.NET
        renew until 07/09/22 19:35:13
[oracle@dbserv1 tmp]$

9. Once the TGT is obtained, we can test the full connection and authentication. here, we provide no username and password in the connection string – instead the Oracle client is relying on the Kerberos ticket:

[oracle@dbserv1 tmp]$ echo "
> set heading off
> select 'DB_NAME (from v\$database) : '||name,
>        'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
>        'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
>        'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
>        'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
>        'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
>   from v\$database;
> " | sqlplus -s /@ORCL

DB_NAME (from v$database) : ORCLCDB
SESSION_USER              : DIVYA@STAGECMU.NET
AUTHENTICATED_IDENTITY    : divya@STAGECMU.NET
AUTHENTICATION_METHOD     : KERBEROS
LDAP_SERVER_TYPE          :
ENTERPRISE_IDENTITY       : divya@STAGECMU.NET


[oracle@dbserv1 tmp]$

As you see in step 7, created user is "identified externally" in the db. This means that our successful connection is authenticated with kerberos alone. It is not using cmu.

To use CMU, I have to drop the "identified externally" user and need to either create an exclusive schema for this user or map this user to a shared schema.

If you are going to use an exclusive schema you will just need to create a globally identified user for this user. like

---
create user "DIVYA@STAGECMU.NET" identified globally as 'CN=divya varma,CN=Users,DC=STAGECMU,DC=NET';
---

If you are going to use a shared schema you will need to create a group in AD, add the AD user to that group, and then create a global schema in the db identified by that group.

So, let me show you the second option with shared schema, I created an AD group "CMU_ROLE" and add user "DIVYA" to that group.

Here you can see the group:

PS C:\Users\Administrator> Get-ADGroup -Identity "cmu_role" -properties DistinguishedName


DistinguishedName : CN=CMU_ROLE,CN=Users,DC=STAGECMU,DC=NET
GroupCategory : Security
GroupScope : Global
Name : CMU_ROLE
ObjectClass : group
ObjectGUID : cca4f4cd-e677-424f-a5ea-591e95ae11df
SamAccountName : CMU_ROLE
SID : S-1-5-21-4248613581-873213861-3280574616-1120

Now login to the PDB and create a shared schema mapped to AD group CMU_ROLE:

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> create user cmu_user identified globally as 'CN=CMU_ROLE,CN=Users,DC=STAGECMU,DC=NET';

User created.

SQL>exit

Obtain ticket using okinit and connect to DB:

[oracle@dbserv1 ~]$ okinit divya

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 15-JUL-2022 17:01:17

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /opt/oracle/product/19c/dbhome_1/network/admin/kerberos/krb5.conf.
Password for divya@STAGECMU.NET:
[oracle@dbserv1 ~]$ sqlplus /@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 15 17:01:35 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Fri Jul 15 2022 16:54:35 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> show user
USER is "CMU_USER"
SQL> set head off
SQL> select 'DB_NAME                   : '||sys_context('USERENV','DB_NAME'),
'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY'),
'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
'IDENTIFICATION_TYPE       : '||sys_context('USERENV','IDENTIFICATION_TYPE')
from dual;
  2    3    4    5    6    7
DB_NAME                   : ORCLPDB1
SESSION_USER              : CMU_USER
AUTHENTICATED_IDENTITY    : divya@STAGECMU.NET
ENTERPRISE_IDENTITY       : cn=divya varma,cn=Users,dc=STAGECMU,dc=NET
AUTHENTICATION_METHOD     : KERBEROS_GLOBAL
IDENTIFICATION_TYPE       : GLOBAL SHARED


SQL>

Now you can add more users to AD group and just connect to DB like above.

So CMU configuration with shared schema is DONE!