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>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>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>
[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]$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]$[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]$[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]$[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]$[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]$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';
---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-1120SQL> 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[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>So CMU configuration with shared schema is DONE!
