0

Environment

I am creating a python application which writes to SQL.

It is compiled into an .exe and distributed to other computers in the business.

(Edit: The SQL server is hosted by our business, within our intranet. The password is also required to change on occasion.)

Question

What are some effective solutions for keeping the application's SQL password private?

My Ideas

  1. Use LDAP authentication when possible.
  2. Keep passwords in a sensitive_info.py file and do not commit to source control.
  3. ???

What are some other solutions or precautions I could consider?

References

How can one secure a password/key in source code

How to protect ftp account information in the source code of a program

Thanks!

Thanks @Alex and @Polynomial for the advice! Very educational! I would flag them both as answers if it would let me. I don't have the points to upvote yet, but I appreciate your help.

Jordan Wirth
  • 3
  • 2
  • 2
    What do you mean by "The SQL server is hosted locally"? Locally as in on the end-user's computer? Or locally as in within your business's internal network? – Ajedi32 Oct 07 '21 at 18:56
  • Don't set a password for the SQL Server, and have the user set it himself. – ThoriumBR Oct 07 '21 at 21:50
  • Note that if each user ends up with their own copy of the database (implied by "hosted locally"), then you don't need to _install_ a database, you'd normally use an in-process one, which would remove the need for credentials altogether. – Clockwork-Muse Oct 08 '21 at 05:11
  • @Ajedi32 Good question, thanks. I added "within the business intranet" to the question. – Jordan Wirth Oct 10 '21 at 16:24

2 Answers2

3

The moment you hardcore a password in your software, it's effectively exposed to anyone who can run and analyze this EXE file. It's a good idea to store it in a separate file and don't include this file in source code control, but there's not much you can do preventing it from being reverse engineered once the app is distributed. Not only can it be reverse engineered through static code analysis, but it can also be intercepted at the moment the initial DB connection is being made.

The only possible strong solution I see is actually implement an additional layer between the user and the password - for example, providing the user application from Citrix (without giving him full control over the application file), or (seems better) giving the user a web interface only, or connect from the application to some interim API server which will route the requests to DB without exposing the password to the user.

Alex
  • 261
  • 2
  • 7
1

If you're talking about distributing an application that connects to a SQL server hosted on the end-user system, then you just have the user set database credentials in a config file rather than putting it in the application itself.

If, instead, your application's architecture is built around having the applications running on the (untrusted) user systems sending direct SQL queries to a database over the network, your security model is broken. There's no way to properly secure this - anyone with access to the application can make arbitrary changes to the database contents. You're giving them free reign. The correct way to architect such a system is to have a central service that handles the business logic, and have the application talk to that instead of directly to the database. The service talks to a backend database to store the actual data, but the clients don't do any SQL stuff themselves. You could implement this in a number of ways, but a web service is a common approach.

Polynomial
  • 132,208
  • 43
  • 298
  • 379
  • That is only true of the target database doesn't have a robust security model. While definitely not the best security model, db that implement user access control properly (operation, row and column level access control) can in fact be secured for remote access as long as each user has a unique login – Stephane Oct 08 '21 at 05:46
  • @Stephane I've tested thousands of systems over my career, several dozen of which tried using the direct SQL model for thick clients. D'you know how many managed to resolve their security problems with row and column level access control? Zero. It's a great idea in theory, but row-level security in databases can only restrict access in the most simplistic way. You can't use it to enforce workflows or business logic, and it becomes incredibly cumbersome to manage if you need dynamic restrictions. It's certainly useful for enforcing minimum necessary privileges, but it's insufficient on its own. – Polynomial Oct 08 '21 at 15:56
  • With all due respect, you (and I) have no idea what the requirement for the OP security model are. And while you might never have encountered and instance where DB level security could be applied to end user access restriction, it does not mean it cannot be done. Indeed, for simple cases (like simple CRDU operations on disconnected tables), it can adequately replicate a 3-tier model without the additional costs. – Stephane Oct 10 '21 at 15:01