I have written regarding setting up Oracle Centrally Managed Users (CMU) and the various troubleshooting steps in my previous posts. You can take a look at those by clicking below links.
Oracle Tips and Techs: Oracle Centrally Managed Users (CMU) configuration (orabliss.blogspot.com)
Oracle Tips and Techs: Troubleshooting Kerberos and Oracle CMU configuration (orabliss.blogspot.com)
Oracle Tips and Techs: Troubleshooting Kerberos and Oracle CMU configuration (orabliss.blogspot.com)
Once we complete the setup, one of the most anticipated questions one will have is how many Service Principal Accounts (oserv - in our lab) do we need to create if we have databases on multiple servers. For example, if we have 10 servers, do we need 1 Service Principal Account (SPN) or 10 SPNs individual to each server.
I have raised this question to Oracle support on multiple SRs and the answers differ from each SR. One engineer says all we need is 1 SPN and another engineer says we need 10 SPNs if we have 10 servers. This is confusing. So, now we will test it ourselves. I'll be configuring Kerberos Authentication on another server (linux75.selvapc.com) running Oracle database 12.2.0.1
For this test, we are not going to change any of the steps from the PART A and PART B of the CMU configuration since that will complete the Kerberos authentication. My sqlnet.ora and krb5.conf will not have any changes from the other server.
[oracle@linux75 admin]$ more sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/db/122/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#Kerberos Parameters
SQLNET.AUTHENTICATION_SERVICES=(beq,kerberos5)
SQLNET.FALLBACK_AUTHENTICATION=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.KERBEROS5_CONF=/u01/db/122/network/admin/krb5.conf
SQLNET.KERBEROS5_CLOCKSKEW=6000
SQLNET.KERBEROS5_CONF_MIT=TRUE
#Following parameter is server-side only
SQLNET.KERBEROS5_KEYTAB=/u01/db/122/network/admin/linux75-database.keytab
[oracle@linux75 admin]$
[oracle@linux75 admin]$ more krb5.conf
[libdefaults]
default_realm = ORABLISS.COM
clockskew = 6000
passwd_check_s_address = false
noaddresses = true
forwardable = yes
[realms]
ORABLISS.COM = {
kdc = 192.168.56.99:88
}
[domain_realm]
ORABLISS.COM = ORABLISS.COM
.ORABLISS.COM = ORABLISS.COM
orabliss.com = ORABLISS.COM
.orabliss.com = ORABLISS.COM
[oracle@linux75 admin]$
You can notice, the only difference will be the keytab file specific to my linux75 server. The keytab file is generated as below
Windows PowerShell Copyright (C) Microsoft Corporation. All rights reserved. PS C:\Users\Administrator> ktpass -princ oracle/linux75.selvapc.com@ORABLISS.com -pass Qwerty123# -mapuser oserv@orabliss.com -crypto ALL -ptype KRB5_NT_PRINCIPAL -out Z:\linux75-database.keytab Targeting domain controller: DS2019.orabliss.com Successfully mapped oracle/linux75.selvapc.com to oserv. Password successfully set! Key created. Key created. Key created. Key created. Key created. Output keytab to Z:\linux75-database.keytab: Keytab version: 0x502 keysize 66 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x1 (DES-CBC-CRC) keylength 8 (0xfb5e5b384c467904) keysize 66 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x3 (DES-CBC-MD5) keylength 8 (0xfb5e5b384c467904) keysize 74 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x17 (RC4-HMAC) keylength 16 (0xc5c6bc2b1d924244af78c13df85a5eee) keysize 90 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x12 (AES256-SHA1) keylength 32 (0xc9e1bf8b841bd336c3ab4fca0c67f0037b21d5719b4c661606a0745cd823e7f7) keysize 74 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x11 (AES128-SHA1) keylength 16 (0x4e84b8fae0598b793cd76a09f5658f29) PS C:\Users\Administrator>You can now notice in the above command, I'm still using the -mapuser as oserv@orabliss.com which is our SPN. Now the account will look like below.
Notice the User logon name has changed from ORACLE/linux-8.selvapc.com to oracle/linux75.selvapc.com
Note: Please see the reason for small case usage for "oracle" in the troubleshooting post.
Now, we are ready for testing. Transfer the keytab file generated to database server linux75.selvapc.com and create the externally authenticated user as usual. Once all is done, we are ready to connect to the database.
SQL> create user cmuuser identified externally as 'snagulan@ORABLISS.COM';
grant create session to cmuuser;
grant select on v_$database to cmuuser;
User created.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@linux75 admin]$ okinit snagulan
Kerberos Utilities for Linux: Version 12.2.0.1.0 - Production on 29-JAN-2023 12:53:43
Copyright (c) 1996, 2016 Oracle. All rights reserved.
Configuration file : /u01/db/122/network/admin/krb5.conf.
Password for snagulan@ORABLISS.COM:
[oracle@linux75 admin]$
[oracle@linux75 admin]$ oklist
Kerberos Utilities for Linux: Version 12.2.0.1.0 - Production on 29-JAN-2023 12:53:50
Copyright (c) 1996, 2016 Oracle. All rights reserved.
Configuration file : /u01/db/122/network/admin/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: snagulan@ORABLISS.COM
Valid starting Expires Service principal
01/29/23 12:53:48 01/29/23 22:53:48 krbtgt/ORABLISS.COM@ORABLISS.COM
renew until 01/30/23 12:53:44
[oracle@linux75 admin]$ sqlplus /@orclpdb
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 29 12:53:58 2023
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set heading off
select 'DB_NAME (from v$database) : '||name,
'CONTAINER_NAME : '||sys_context('USERENV','CON_NAME'),
'SESSION_USER : '||sys_context('USERENV','SESSION_USER'),
'AUTHENTICATED_IDENTITY : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
'AUTHENTICATION_METHOD : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
SQL> 'IDENTIFICATION_TYPE : '||sys_context('USERENV','IDENTIFICATION_TYPE'),
2 3 4 5 6 7 --'LDAP_SERVER_TYPE : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
'ENTERPRISE_IDENTITY : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
from v$database; 8 9
DB_NAME (from v$database) : ORCL
CONTAINER_NAME : ORCLPDB
SESSION_USER : CMUUSER
AUTHENTICATED_IDENTITY : snagulan@ORABLISS.COM
AUTHENTICATION_METHOD : KERBEROS
IDENTIFICATION_TYPE : EXTERNAL
ENTERPRISE_IDENTITY : snagulan@ORABLISS.COM
SQL> !hostname
linux75.selvapc.com
SQL>
Ok, we have successfully tested the kerberos connection from the newly configured database server. What happens to the previously configured database server since we have now change the SPN User Logon name? Does the CMU connection to the server linux-8.selvapc.com still work or does it break after new server configuration? Let's test it.
[oracle@linux-8 ~]$ okinit snagulan
Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2023 23:31:46
Copyright (c) 1996, 2019 Oracle. All rights reserved.
Configuration file : /oracle/db/db19/network/admin/krb5.conf.
Password for snagulan@ORABLISS.COM:
[oracle@linux-8 ~]$ oklist
Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2023 23:31:53
Copyright (c) 1996, 2019 Oracle. All rights reserved.
Configuration file : /oracle/db/db19/network/admin/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: snagulan@ORABLISS.COM
Valid starting Expires Service principal
01/29/23 23:31:50 01/30/23 09:31:50 krbtgt/ORABLISS.COM@ORABLISS.COM
renew until 01/30/23 23:31:47
[oracle@linux-8 ~]$ sqlplus /@odb1
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 29 23:32:12 2023
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 28 2023 23:10:46 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> set heading off
select 'DB_NAME (from v$database) : '||name,
'CONTAINER_NAME : '||sys_context('USERENV','CON_NAME'),
'SESSION_USER : '||sys_context('USERENV','SESSION_USER'),
'AUTHENTICATED_IDENTITY : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
'AUTHENTICATION_METHOD : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
'IDENTIFICATION_TYPE : '||sys_context('USERENV','IDENTIFICATION_TYPE'),
'LDAP_SERVER_TYPE : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
'ENTERPRISE_IDENTITY : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
from v$database;SQL> 2 3 4 5 6 7 8 9
DB_NAME (from v$database) : CDB19
CONTAINER_NAME : ODB1
SESSION_USER : SELVA
AUTHENTICATED_IDENTITY : snagulan@ORABLISS.COM
AUTHENTICATION_METHOD : KERBEROS_GLOBAL
IDENTIFICATION_TYPE : GLOBAL EXCLUSIVE
LDAP_SERVER_TYPE : AD
ENTERPRISE_IDENTITY : cn=Selvakumar Nagulan,cn=Users,dc=orabliss,dc=com
SQL>
SQL> !hostname
linux-8.selvapc.com
SQL>
We can see the CMU connection still works without any issues. This concludes we just need 1 SPN (in our case, oserv) to configure kerberos or CMU connection for multiple oracle database servers. Happy CMUing...!!!
