Nrthugu

I'm writing a Python script which uses a MySQL database, which is locally hosted. The program will be delivered as source code. As a result, the MySQL password will be visible to bare eyes. Is there a good way to protect this?

The idea is to prevent some naughty people from looking at the source code, gaining direct access to MySQL, and doing something ... well, naughty.

Short answer

You can't.

If the password is stored in the artifact that's shipped to the end-user you must consider it compromised! Even if the artifact is a compiled binary, there are always (more or less complicated) ways to get at the password.

The only way to protect your resources is by exposing only a limited API to the end-user. Either build a programmatic API (REST, WS+SOAP, RMI, JavaEE+Servlets, ...) or only expose certain functionalities in your DB via SPROCs (see below).

Some things first...

The question here should not be how to hide the password, but how to secure the database. Remember that passwords only are often a very weak protection and should not be considered the sole mechanism of protecting the DB. Are you using SSL? No? Well, then even if you manage to hide the password in the application code, it's still easy to sniff it on the network!

You have multiple options. All with varying degrees of security:

"Application Role"

Create one database-user for the application. Apply authorization for this role. A very common setup is to only allow CRUD ops.

Pros

• 
  very easy to set-up


• Prevents DROP queries (f.ex. in SQL injections?)

Cons

• Everybody seeing the password has access to all the data in the database. Even if that data is normally hidden in the application.


• If the password is compromised, the user can run UPDATE and DELETE queries without criteria (i.e.: delete/update a whole table at once).

Atomic auth&auth

Create one database user per application-/end-user. This allows you to define atomic access rights even on a per-column basis. For example: User X can only select columns far and baz from table foo. And nothing else. But user Y can SELECT everything, but no updates, while user Z has full CRUD (select, insert, update, delete) access.

Some databases allow you to reuse the OS-level credentials. This makes authentication to the user transparent (only needs to log-in to the workstation, that identity is then forwarded to the DB). This works easiest in a full MS-stack (OS=Windows, Auth=ActiveDirectory, DB=MSSQL) but is - as far as I am aware - also possible to achieve in other DBs.

Pros

• Fairly easy to set up.


• Very atomic authorization scheme

Cons

• Can be tedious to set up all the access rights in the DB.


• Users with UPDATE and DELETE rights can still accidentally (or intentionally?) delete/update without criteria. You risk losing all the data in a table.

Stored Procedures with atomic auth&auth

Write no SQL queries in your application. Run everything through SPROCs. Then create db-accounts for each user and assign privileges to the SPROCs only.

Pros

• Most effective protection mechanism.


• SPROCs can force users to pass criteria to every query (including DELETE and UPDATE)

Cons

• not sure if this works with MySQL (my knowledge in that area is flaky).


• complex development cycle: Everything you want to do, must first be defined in a SPROC.

Final thoughts

You should never allow database administrative tasks to the application. Most of the time, the only operations an application needs are SELECT, INSERT, DELETE and UPDATE. If you follow this guideline, there is hardly a risk involved by users discovering the password. Except the points mentioned above.

In any case, keep backups. I assume you want to project you database against accidental deletes or updates. But accidents happen... keep that in mind ;)

In this case, I create a new section in my .my.cnf, like

[files]

host=127.0.0.1

port=3307

database=files

default-character-set=utf8

password=foobar

and use it on DB initialization with

d=MySQLdb.connect(

    read_default_group='files',

    port=0,  # read from .my.cnf

    db='files',

    cursorclass=cursors.DictCursor,

    # amongst other stuff

)

Similar unanswered question here: https://stackoverflow.com/questions/2850710/connect-to-a-db-with-an-encrypted-password-with-django The python DBAPI (PEP 249) has no interface for connecting to the database with an encrypted/hashed password in lieu of a plaintext password.

While this feature in other languages is comforting, it does not provide any real additional security: the hash of the password is as good as the password. You still have to control access to the database resources as exhuma describes.

MySQL itself does not provide any additional options, regardless of python bindings or not. You can read their guidance in the MySQL User Manual section on Password Security. The recommended option for protecting access to the password is to store it in an option file and protect the file, as described by glglgl.

From that page:

The methods you can use to specify your password when you run client
programs are listed here, along with an assessment of the risks of
each method. In short, the safest methods are to have the client
program prompt for the password or to specify the password in a
properly protected option file.

Either use simple passwor like root.Else Don't use password.