By Franck Pachot

.
When you create a DBaaS on the Oracle Cloud services you have to provide an administration password in the database configuration form. You do not need a password to connect to the VM. You use SSH key for it: on creation you provide the public key that will allow you to connect as the oracle user or the opc user (which can ‘sudo su’). But for the database you need to provide a password which will be the password for SYS and SYSTEM. This password can be used by yourself, or by the DBaaS management. Let’s see how it is stored and how to change it.

I’m talking about managed DBaaS here, not virtual image, because in virtual image you create and administrate the database yourself.

CapturePass0

You have to define the ‘administrator password’ which will be used for SYS ans SYSTEM (the -sysPassword and -systemPassword parameters of DBCA). This password must obey the

CapturePass1

Where is the password?

When I provide a password, I don’t want it to be exposed, even within the system that is protected by this password. I may use the same password, or same pattern, for different systems and I don’t want any user to see my password in clear. In the database, the password is not stored. It is immediately hashed and password verification is done with the hashed value. In the orapw file, it is encrypted. If it has to be used by some programs, I expect to use a wallet.

I want to be sure that the password I’ve provided is not stored anywhere, let’s check:


[oracle@CDB-dg01 ~]$ grep -R Ach1z0 /var/opt/oracle 2>/dev/null
/var/opt/oracle/dg/observer.sh:connect sys/Ach1z0#d
/var/opt/oracle/ocde/assistants/dg/tmp/18267.odgda.json:{"Standby":[{"dg":{"fsfo_enabled":null,"vmpresent":"yes","spfile":{"db_unique_name":"CDB_02"},"protection_mode":null},"dborch":{"db_sid":"CDB","vm_sshkeys":"/home/oracle/.ssh/id_rsa","vm_name":"CDB-dg02-nat"}}],"Primary":{"dg":{"fsfo_enabled":null,"vmpresent":"yes","spfile":{"db_unique_name":"CDB_01"},"protection_mode":null},"dborch":{"db_version":"12102","db_sid":"CDB","vm_sshkeys":"/home/oracle/.ssh/id_rsa","vm_name":"CDB-dg01-nat","db_passwd":"Ach1z0#d"}},"tool_defaults":{"octl_cmd":"","tmpdir":"/var/opt/oracle/ocde/assistants/dg/tmp/","dborch_cmd":"","tools_dir":".."}}
/var/opt/oracle/log/dgcc/dgcc.log:                                       'db_passwd' => 'Ach1z0#d',
/var/opt/oracle/log/dgcc/dgcc_2016-05-20_06:49:28.log:                                       'db_passwd' => 'Ach1z0#d',
/var/opt/oracle/log/ocde/ocde-cmd.log:                                       'db_passwd' => 'Ach1z0#d',
/var/opt/oracle/log/ocde/ocde_2016-05-20_06:31:45.log:                                       'db_passwd' => 'Ach1z0#d',
/var/opt/oracle/log/ocde/ocde.log:                                       'db_passwd' => 'Ach1z0#d',
/var/opt/oracle/log/ocde/ocde-cmd_2016-05-20_06:31:45.log:                                       'db_passwd' => 'Ach1z0#d',
/var/opt/oracle/log/creg/creg.ini.CDB-dg02-nat:passwd=Ach1z0#d
/var/opt/oracle/creg.ini:passwd=Ach1z0#d

SYS password is exposed to everybody on the server!

That’s bad. Really bad. The most important database password is exposed in configuration files, script files and log files, and some of them are readable by everybody:


[oracle@CDB-dg01 ~]$ ls -l $(grep -lR Ach1z /var/opt/oracle 2>/dev/null)
-rw------- 1 oracle oinstall   2381 May 20 21:35 /var/opt/oracle/creg.ini
-rwxr-xr-- 1 oracle oinstall    139 May 20 07:03 /var/opt/oracle/dg/observer.sh
-rw------- 1 oracle oinstall   2353 May 20 06:49 /var/opt/oracle/log/creg/creg.ini.CDB-dg02-nat
-rw-r--r-- 1 oracle oinstall   3395 May 20 07:07 /var/opt/oracle/log/dgcc/dgcc_2016-05-20_06:49:28.log
lrwxrwxrwx 1 oracle oinstall     53 May 20 06:49 /var/opt/oracle/log/dgcc/dgcc.log -> /var/opt/oracle/log/dgcc/dgcc_2016-05-20_06:49:28.log
-rw-r--r-- 1 oracle oinstall 152448 May 20 07:11 /var/opt/oracle/log/ocde/ocde_2016-05-20_06:31:45.log
-rw-r--r-- 1 oracle oinstall  82856 May 20 07:11 /var/opt/oracle/log/ocde/ocde-cmd_2016-05-20_06:31:45.log
lrwxrwxrwx 1 oracle oinstall     57 May 20 06:34 /var/opt/oracle/log/ocde/ocde-cmd.log -> /var/opt/oracle/log/ocde/ocde-cmd_2016-05-20_06:31:45.log
lrwxrwxrwx 1 oracle oinstall     53 May 20 06:31 /var/opt/oracle/log/ocde/ocde.log -> /var/opt/oracle/log/ocde/ocde_2016-05-20_06:31:45.log
-rw-r--r-- 1 oracle oinstall    581 May 20 06:49 /var/opt/oracle/ocde/assistants/dg/tmp/18267.odgda.json

The scripts and logs used at creation can be removed. The script that starts observer should use a wallet. But more worrying is that creg.ini because this is where are all our service attributes. And if we change or remove the password there, the service is broken. Here is an example when I initiate a switchover from the CLOUD My Services interface after having removed the password there:

CapturePass2

Of course, you can still do a switchover from DGMGRL command line, but this defeats the whole purpose of a managed service.

Changing SYS password

When you want to change the SYS password, you must use the DBaaS tool. You must run ‘dbaascli database changepassword’. In Data Guard, you must run it from the primary site and it takes care of everything, including the copy of orapw file, because in 12.1 (not talking about Next Generation of Oracle Database here) you have to do it manually.


[oracle@CDB-dg02 ~]$ dbaascli database changepassword
DBAAS CLI version 1.0.0
Executing command database changepassword
Enter username whose password change is required: SYS
Enter new  password:
Re-enter new password:
Unable to change password for sys

Humm.. let’s try with SYS in lowercase…


[oracle@CDB-dg02 ~]$ dbaascli database changepassword
DBAAS CLI version 1.0.0
Executing command database changepassword
Enter username whose password change is required: sys
Enter new  password:
Re-enter new password:
Dataguard is enabled
Warning: Permanently added 'cdb-dg02-nat,140.86.4.51' (RSA) to the list of known hosts.
Dataguard is enabled
Dataguard is enabled
Dataguard is enabled
Successfully changed the password for user sys/system

Okay, users must be lower case here… Good to know… And from the message SYS and SYSTEM have been changed…

Wallet

You wonder why the password is in clear text in the configuration file? So do I. I would expect the use of external password file here. But wait… there is one:


[oracle@CDB-dg01 ~]$ grep WALLET $ORACLE_HOME/network/admin/sqlnet.ora
ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/CDB/tde_wallet)))
SQLNET.WALLET_OVERRIDE = FALSE
WALLET_LOCATION = (SOURCE=(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/CDB/db_wallet))(METHOD=FILE))

and this wallet has credentials for SYS:


[oracle@CDB-dg01 db_wallet]$ mkstore -wrl /u01/app/oracle/admin/CDB/db_wallet -listCredential
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
List credential (index: connect_string username)
3: CDB_02 sys
2: CDB_01 sys
1: CDB sys

and those are entries to connect as SYS to each site:


[oracle@CDB-dg01 db_wallet]$ for i in CDB CDB_01 CDB_02 ; do tnsping $i ; done | grep DESC
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = CDB-dg01) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB.may.oraclecloud.internal)))
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = CDB-dg01-nat) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = CDB)))
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = CDB-dg02-nat) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = CDB)))

So this is a nice way to connect without providing the password, and without the password being visible… or is it?


[oracle@CDB-dg01 db_wallet]$ mkstore -wrl /u01/app/oracle/admin/CDB/db_wallet -viewEntry oracle.security.client.password1
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
oracle.security.client.password1 = Ach1z0#d
[oracle@CDB-dg01 db_wallet]$ mkstore -wrl /u01/app/oracle/admin/CDB/db_wallet -viewEntry oracle.security.client.password2
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
oracle.security.client.password2 = Ach1z0#d

Arghhh… password plain visible here from the oracle user.
When I create a wallet, I provide a password. And without the password we can use the credential but not display it. But here it seems that the wallet was created as ‘auto login’ with the ‘-createALO’ option. Want to know more about that option? You can see it in online help:


[oracle@CDB-dg01 db_wallet]$ mkstore
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
mkstore [-wrl wrl] [-create] [-createSSO] [-createLSSO] [-createALO] [-delete] [-deleteSSO] [-list] [-createEntry alias secret] [-viewEntry alias] [-modifyEntry alias secret] [-deleteEntry alias] [-createCredential connect_string username password] [-listCredential] [-modifyCredential connect_string username password] [-deleteCredential connect_string] [-help] [-nologo]

But if you want more information about -createALO you have to check documentation. Well, is it documented?
You need to wait that Bug 21152979 : PROVIDE EXPLANATION FOR EACH OPTION OF MKSTORE UTILITY is fixed…

Conclusion

Ok, better to stop here. Lot of interesting things here about automation, but it is still far from what we did with the dbi services Database Management Kit for years.

With DBaaS it is easy to configure a database in Data Guard configuration with a few click. No doubt. But what is done here is very far from the best practices we follow when we setup a Data Guard configuration. Very good for a lab or test where security is not that important.

Do not put sensible passwords for the DBaaS configuration because they are exposed to everybody that can read files on the VM created. You may be tempted to open the database to developers, giving them high privileges because it’s an isolated environment in the Cloud, so no risk. But then you expose the SYS password, which may give some clue about the passwords patterns you use elsewhere.