Neil Chandler's DB Blog

October 2024 – Oracle 19C, 23ai

So a while ago I spoke about passwords but forgot to mention a key element of account control in the blog. I was giving a talk on Security a few days ago and told an audience member that this information was in my blog. Well it was not and I apologise – I thought it was – so I’m putting that right now!

Hardcoding passwords in scripts…

How many of you have scripts on your database server or elsewhere like this:

sqlplus system/manager <<EOF
SELECT info FROM table;
EOF

Yes, everyone does. I’ve seen this at practically every site I have ever been to. It’s dangerous – that lovely clear-text password, there for anyone with server access to read.

And of course, that also means that you must not be versioning your software code as you cannot put something like that into git, or some other versioning system as that just breaks all of the rules. Anyone with access to git now has access to your passwords.

So what can we do to get around this? The answer is simple – put the password in a wallet and reference the wallet! So, how do I do this? It’s remarkably simple.

---

create the network files

First we need a wallet, and our tns configuration

mkdir /home/oracle/password-wallet
mkdir /home/oracle/tns

# And point to the tns location using TNS_ADMIN
export TNS_ADMIN=/home/oracle/tns

We also need to create TNSNAMES.ORA entries to connect to our database. Here I have a generic entry, and 2 other entries. These are the entries that I will add to the wallet to be able to connect as the relevant user without knowing or having the password.

First of all, we need the sqlnet.ora file, which will point to the WALLET_LOCATION and also incorporate SQLNET.WALLET_OVERRIDE=TRUE (to allow the wallet to override other O/S authentication methods). Note that this one also has Oracle Native Network Encryption enabled

oracle@ora19-rh79-1*UTF8 $ cat sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = FILE)
                            (METHOD_DATA = 
                            (DIRECTORY = /home/oracle/password-wallet)
                            )
                  )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_VERSION = 1

SQLNET.ENCRYPTION_SERVER      = REQUESTED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUESTED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA384)

IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE

Each wallet entry is unique, so we need a unique TNSNAMES.ORA entry for each wallet entry

oracle@ora19-rh79-1*UTF8 $ cat tnsnames.ora
# We are using Native Network Encryption, not TLS here...

UTF8PDB1      = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
                                          (HOST = ora19-rh79-1)(PORT = 1521))
                               (CONNECT_DATA = (SERVER = DEDICATED)
                                               (SERVICE_NAME = UTF8PDB1))
                )

UTF8PDB1_NEIL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
                                          (HOST = ora19-rh79-1)(PORT = 1521))
                               (CONNECT_DATA = (SERVER = DEDICATED)
                                               (SERVICE_NAME = UTF8PDB1))
                )

UTF8PDB1_SYST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
                                          (HOST = ora19-rh79-1)(PORT = 1521))
                               (CONNECT_DATA = (SERVER = DEDICATED)
                                               (SERVICE_NAME = UTF8PDB1))
                )

So now we can connect to the database, to the same service (UTF8PDB1), using @UTF8PDB1, @UTF8PDB1_NEIL or @UTF8PDB1_SYST – they all go to the same place

create the store and wallet

You need a nice long and complex password held in a safe somewhere, so here I’m using oracle1234

NOTE: mkstore is deprecated (but still usable) from Oracle 23ai, and has been incorporated into orapki. Thank you Pete Finnegan (if you care about your Oracle security, check out Pete. The best expert Oracle security guy out there).

$ mkstore -wrl "/home/oracle/password-wallet" -create
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Enter password: oracle1234
Enter password again: oracle1234

$ orapki wallet create -wallet "/home/oracle/password-wallet" -pwd oracle1234 -auto_login_local
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

$ ls -l password-wallet/
total 8
-rw-------. 1 oracle oinstall 270 Aug 26 15:07 cwallet.sso
-rw-------. 1 oracle oinstall   0 Aug 26 15:04 cwallet.sso.lck
-rw-------. 1 oracle oinstall 225 Aug 26 15:04 ewallet.p12
-rw-------. 1 oracle oinstall   0 Aug 26 15:04 ewallet.p12.lck

note the “-auto_login_local” switch. This means the wallet is dedicated to this user on this machine. Unless you are logged in on this machine as this user, it is of no use to you. However, as this user on this machine, you don’t need a password to read the wallet

Now that we have somewhere to store our user credentials, lets add them:

Note that we are passing in 3 parameters after the -createCredential switch; the unique TNSNAMES.ORA alias, the username, and the password. As you can see, there is a 1-2-1 relationship between the credentials entry in the wallet and the tnsnames.ora file. One userid and password to one alias.

$ mkstore -wrl "/home/oracle/password-wallet" -createCredential UTF8PDB1_NEIL neil oracle
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: oracle1234

$ mkstore -wrl "/home/oracle/password-wallet" -createCredential UTF8PDB1_SYST system manager
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: oracle1234

And we can check the credentials are safely in there:

$ mkstore -wrl "/home/oracle/password-wallet" -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

List credential (index: connect_string username)
2: UTF8PDB1_SYST system
1: UTF8PDB1_NEIL neil

and we can use the stored credentials to access the database, using either SQLcl or sqlplus, or even datapump (not shown):

$ sql /@UTF8PDB1_NEIL
SQLcl: Release 21.4 Production on Mon Aug 26 15:17:59 2024

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

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

15:18:03 15:18:03 NEIL @ UTF8PDB1_NEIL > show user
USER is "NEIL"

---

$ sqlplus /@UTF8PDB1_SYST

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 26 15:20:00 2024
Version 19.22.0.0.0

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

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

15:20:01 SYSTEM @ UTF8PDB1_SYST > show user
USER is "SYSTEM"

So there you have it! You scripts can now read like this, with no embedded passwords to worry about, and no security issues either locally or with source control

sqlplus /@UTF8PDB1_SYST <<EOF
SELECT info FROM table;
EOF

Enjoy!