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.

4
  • 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
    Commented Oct 7, 2021 at 18:56
  • Don't set a password for the SQL Server, and have the user set it himself.
    – ThoriumBR
    Commented Oct 7, 2021 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. Commented Oct 8, 2021 at 5:11
  • @Ajedi32 Good question, thanks. I added "within the business intranet" to the question. Commented Oct 10, 2021 at 16:24

2 Answers 2

4

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.

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.

3
  • 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
    Commented Oct 8, 2021 at 5: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
    Commented Oct 8, 2021 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
    Commented Oct 10, 2021 at 15:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .