This is not a security discussion
Where to store the database credentials for an app is a long-running discussion that depends on many things. What app server is being used, what OS, how sensitive is the data and of course how much time and money is available to invest (bad reason to skip security).
In my opinion, if a hacker can get far enough into your systems to pull down your files (application code, config files or others) they will be able to find the DB credentials for the app.
It comes down to the point, that the information is accessible to your application somehow. If they breach far enough to get your code or config files, they will be able to find and attack that method.
There are many solutions out there and some are quite good so I’m not going to get into the best way to secure a system.
Using environment variables is relatively simple.
Example
Setting environment variables on Linux
|
1 |
export db_connect=user/pw@db |
or on Windows
|
1 |
set db_connect=user/pw@db |
Then in Python using the os package
|
1 2 3 4 |
import cx_Oracle import os connectString = os.getenv('db_connect') con = cx_Oracle.connect(connectString) |
Or in Ruby using ENV
|
1 2 3 |
require 'oci8' connectString = ENV['db_connect'] con = cx_Oracle.connect(connectString) |
Setting the variables on the command line like this is temporary for the current session. Consult your OS instructions for a more permanent method if needed.
Why?
There are a couple reasons I prefer this method over hard-coding or config files.
Accidental commits
|
1 2 |
connectString = os.getenv('db_connect') con = cx_Oracle.connect(connectString) |
|
1 |
con = cx_Oracle.connect('user/pw@db') |
I could expand on this but I think that covers it.
Different environments
They are called environment variables for a reason. On your development machine, you may have a DB running in a VirtualBox instance. Your test servers will probably have their own databases. And only certain people should even know the credentials for the Production server.
Using environment variables, there is no need for maintaining multiple versions of config files or worse yet, source code. Each environment is configured independently of the application.
Quick switching
This would be more of a development or test thing. But, if you need to run your code against multiple different databases you would simply change the environment variable and not any config files or source code. Remember, every time you modify the source code, no matter how small of a change, there’s a chance for a mistype to bring it all down.
Deploying to a Platform as a Service (PaaS)
Most PaaS systems will spin resources up and down as needed, including your database. If your application is using a database provided by the PaaS the process of spinning up the database would include creating secure credentials. To simplify the process the PaaS may simply set the environment variables for the credentials and your application never needs to change.
For a bit of extra security, they may automatically change the credentials whenever the system is restarted. You would be able to log onto your server and get the current credentials if you need them, otherwise, they just work.
Leave me a comment if you have any questions or suggestions.
Question on Twitter from @s_oravec.
If you have installed dbmsutil.sql you can get environment variables using dbms_system.get_env().
set serveroutput on
declare
retval varchar2(4000);
begin
dbms_system.get_env('someEnvVar', retval);
dbms_output.put_line(retval);
end;
/
Getting environment variables from the local system using SQL Plus takes a little extra help. From the answer at askTom we could do the following.
Linux example
at the prompt:
export envTest1=itWorks1
export envTest2=itWorks2
export envTest3=itWorks3
Make a script, I called mine setVars.sql
set serveroutput on
define test_var1='&1'
define test_var2='&2'
define test_var3='&3'
prompt &test_var1
prompt &test_var2
prompt &test_var3
begin
dbms_output.put_line('&test_var1');
dbms_output.put_line('&test_var2');
dbms_output.put_line('&test_var3');
end;
/
Then when you run sqlplus
sqlplus user/pw @/home/oracle/setVars.sql $envTest1 $envTest2 $envTest3
You could modify the shortcut you launch sqlplus with to include the environment vars you typically want available.
It’s a bit of a work around but it gives access to the vars you want to use.
Appreciate the recommendation. Wiill try it out.
Hi there! I could have sworn I�ve visited your blog before but
after going through some of the posts I realized it�s new to me.
Anyways, I�m certainly pleased I came across it and I�ll be bookmarking it and checking
back often!
Nice Article. How it help to developer in terms of balance the day to day life.
Thanks for the great article this is very useful info thanks for the wonderful post.