I was perusing (yes, “perusing”) the My Oracle Support Community Database Administration posts recently when I came across this one: “How to hide oracle database account password in a create user sql script“. The poster had a problem that every database administrator has had to confront at one time or another: namely, how to embed a password into an automation script in a way that is secure.
The original poster had three scenarios she was trying to deal with:
1. shell scripts run by oracle cron — which can be taken care of by SEPS
Only DBAs know the password in this category.
2. database creation scripts — which includes the DBA accounts (sys/oracle/c##oracle) creation and password file creation — via identified by xxx.
Only DBAs know the password in this category.
3. schema account creation & dblink creation — which includes the application schemas and database links creation scripts — via identified by xxx.
Only in this scenario, the passwords are known to the application (application admin needs to have the password and store them in the app. configuration file)
But the DBAs have to create the accounts first in the database — that is why the create user scripts must be used.
She was concerned that a security auditor would have a problem with plain text passwords appearing in her automation scripts, which they very well might. A variety of suggestions were made, of course, some better than others as you might expect. The first scenario is actually pretty easy to deal with using Oracle Wallets (aka Secure External Password Store, or SEPS), which can be used to secure credentials for an Oracle Client when you are actually connecting to the database. I’ve written previously on how to configure a Wallet to use Smart Card PKI certificates for authentication.
The trickier problem is how to take a normal, ASCII text SQL or shell script that needs to include a password as part of the content and keep it away from prying eyes. You could attempt to pull some sort of trickery with shell scripts and encrypt your automation scripts, but you’d have to have the encryption key available on the same system and decrypt the script before you could use it, which would largely defeat the purpose. So how do you go about this?
The most important thing to remember is this: at the end of the day, security is all really about managing risk, to the best of your ability. So what’s your biggest risk? Are you most concerned with protecting against insider threats (like rogue admins) or external threats (like hackers)? Your passwords could be intercepted on the network, the script files could be physically stolen, or even just viewed by the wrong person logged in to the server. Breaking that down, I see the following
- Risk 1: As previously mentioned, login credentials for actual database connections (i.e. running scripts, applications, etc.) can be protected handled by using the wallet, as well as using network encryption for all of your database communications. Since wallets essentially enable operating system authentication from the client, this requires that the system or account that uses the wallet is independently secured through good policy.
- Risk 2: Physical theft of the server or storage media, while hopefully unlikely, should be handled by using storage media-level encryption or Oracle’s Transparent Data Encryption for data files. Remember that media-level (whole disk) encryption doesn’t protect your files from being electronically copied off of the server and read, only from physical theft of the storage device itself. To protect against physical and electronic theft, use TDE and make sure to encrypt any export dumps using DataPump’s built in encryption features. Also make sure that backup sets of your file system (with the scripts) are also encrypted on whatever storage they wind up on using whatever backup software protects your system. Note: generally you should use media encryption or TDE, not media encryption and TDE.
- Risk 3: Exposure of scripts with embedded passwords, is the trickiest. The only sure way to protect this is to never have embedded passwords. That is pretty much always a bad idea, but if there’s no way around it then you mitigate the risk as much as you can. The original poster in the above thread stated that, “No one else other than the DBAs and System admin has the access to the database server nor file system to be able to read the scripts,” which is a good start. Then she’s dealing only with users who would already have direct access to the database without needing to use those account passwords at all. If you can’t trust those people, you are definitely in trouble already.
If you’re still going to get hassled by the auditor, even if you feel the risk is minimal or otherwise mitigated, or if you’re just uncomfortable with the idea of clear text, embedded passwords in general, then good for you! In my opinion, the best approach suggested in the MOSC thread was that of replacing the hard-coded passwords with bind variables and providing the passwords only at runtime. This is the most secure because there are no passwords to find if someone unauthorized does gain access to your scripts. In that case I would recommend using a SQL substitution prompt as part of the script; do not put input parameters (especially passwords!) on the command line or they could be exposed to other users on the system through commands line “ps” or “top”, or through history files like .bash_history.
You could even do a partial replacement of the password, so multiple passwords would potentially have a common prefix (or some seed value) provided at runtime, but have unique hard-coded suffixes to prevent them from being completely identical. Maybe even salt it with something else unique to the local system, like part of the hostname, too. Then you’d only have to complete a single input prompt, no matter how many passwords were potentially involved in your script. Each password would contain something random (provided by you at runtime), something specific to the local host (like hostname, or some common ENV parameter), and something hard-coded to add uniqueness.
An example of such a script might look something like this:
-- Set password values DEFINE password_tail1 = '_app1'; DEFINE password_tail2 = '_app2'; ACCEPT password_seed CHAR PROMPT 'Enter a password seed > '; -- Create app_user1 CREATE USER APP_USER1 IDENTIFIED BY &&password_seed&&password_tail1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE TO APP_USER1; -- Create app_user2 CREATE USER APP_USER2 IDENTIFIED BY &&password_seed&&password_tail2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE TO APP_USER1;
I would also recommend – in general – that you make that common prefix as long as possible: at least 15 characters, and the suffix another 5 or more. That makes processing a bit more complicated. This is one way to handle it if you use a single script.
SET SERVEROUTPUT ON; SET FEEDBACK OFF; SET VERIFY OFF; -- Define bind variables VARIABLE full_password VARCHAR2(30); -- Set password values DEFINE password_salt1 = '_app1'; DEFINE password_salt2 = '_app2'; ACCEPT password_seed CHAR PROMPT 'Enter a password seed > '; -- Create App Users BEGIN DBMS_OUTPUT.ENABLE; -- Confirm seed length is at least 15 characters IF LENGTH('&&password_seed') < 15 THEN DBMS_OUTPUT.PUT_LINE('Error: Password seed must be at least 15 characters long.'); ELSE -- Create App User1 :full_password := '&&password_seed&&password_salt1'; EXECUTE IMMEDIATE ('CREATE USER APP_USER1 IDENTIFIED BY '||:full_password||' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP'); EXECUTE IMMEDIATE ('GRANT CONNECT, RESOURCE TO APP_USER1'); -- Create App User2 :full_password := '&&password_seed&&password_salt2'; EXECUTE IMMEDIATE ('CREATE USER APP_USER2 IDENTIFIED BY '||:full_password||' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP'); EXECUTE IMMEDIATE ('GRANT CONNECT, RESOURCE TO APP_USER2'); DBMS_OUTPUT.PUT_LINE('Users created.'); END IF; END; / SET SERVEROUTPUT OFF; SET FEEDBACK ON; SET VERIFY ON;
Running that script would look like the following:
SQL> @create-users Enter a password seed > seed Error: Password seed must be at least 15 characters long. SQL> @create-users Enter a password seed > thisismyseed12345 Users created. SQL> select username from dba_users where username like 'APP_USER%'; USERNAME -------------------------------------------------------------------------------- APP_USER1 APP_USER2 2 rows selected.
In the hopefully unlikely event that a password is compromised, other mitigations to protect against that exposure could include:
- Limiting access to the database itself on the network. Use firewalls and/or Oracle Connection Manager to proxy inbound connections so that only recognized, approved clients can connect to specific service names, for instance. In that way, even if a password were somehow to escape into the wild a potential attacker would still have to have access to a trusted client to be able use it.
- Using logon triggers and the SYS_CONTEXT to whitelist clients to specific accounts. This is more effective if you combine it with database proxy authentication, as proxy usernames in SYS_CONTEXT cannot be spoofed by particularly clever attackers.
- Monitoring your audit trail and listener logs 24×7 for all valid or invalid connections or logins.
- Create wallets used by applications on network mounted directories with the “-auto_login_local” option. This allows the application to connect to the database securely, protects the credentials from accidental exposure in the wallet, and allows you to store them physically on another server. The wallets could only be opened from the application server, not the file server, so both systems would have to be physically stolen for there to be an issue.
Cautionary Tale: I once did an evaluation of a product where a privileged database account was assigned a fixed password across all installations of the application. That password was also embedded in the source code, and that snippet of code was accidentally included in a post to a public forum – easily located with a Google search – by a developer seeking help with an unrelated problem. Dozens of customers and their systems were potentially compromised. All it would take is one snippet of your code or script with complete passwords to get into the wild, and your systems would be potentially compromised. Be careful to use bind or substitution variables in your scripts, and if you must store a password with your application at run time, use a wallet!