For Kerberos authentication, first, we need to configure Kerberos in the Oracle Database that will be integrated with Microsoft Active Directory (AD).
Centrally Managed Users (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 set up 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 the 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 the below PowerShell command on the 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 the 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 key table 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-1120Now 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>exitObtain a 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 the Active Directory group and just connect to DB like above.
So CMU configuration with shared schema is DONE!