Using different users for select/insert in accessing MySQL

Question

I'm building a MySQL DB for a web app which will be written in Python. I wanted to segregate the users functions to provide better security and maybe improve performance. I'm thinking to do it in this way: 2 sets of tables:

• 1 Table for app user management
• 1 Set of table for storing the information

I want to create 4 mysql users to access and manipulate the data from the app:

• 1 user with Select rights for the Users Table
• 1 user with Insert/update rights for the Users table
• 1 user with Select rights for the set of tables storing the information
• 1 user with Insert/update rights for the set of tables storing the information

So each of the Python modules reading/writting each of the tables will use the required user.

My reasoning is that if a user or insert field gets compromised by whatever the reason, the damage and information that can be manipulated/obtained will be limited. It shouldn't complicate the app, as it will be only using the right connection to the DB and you only do it once per module.

Would that be overkill? Can it impact the performance? Any other thoughts?

Answer 1

Using the Principle of Least Privilege is ALWAYS a good idea.

Is it overkill? No, it's part of any good design.

Can it impact performance? No. A connection to the database is just a connection, the privileges associated with the account connecting to the database are irrelevant.

Answer 2

My reasoning is that if a user or insert field gets compromised by whatever the reason, the damage and information that can be manipulated/obtained will be limited. It shouldn't complicate the app, as it will be only using the right connection to the DB and you only do it once per module.

Yes, it is possible. You simply have to create one connection/session per user. I'd advise you to always use SQLAlchemy to work your ORM out, and it'd be as simple as declare as many instances as you have users in your database.

engine_select_only = create_engine(u'mysql://user1:[email protected]/mydb'
engine_insert_update = create_engine(u'mysql://user1:[email protected]/mydb'
engine_insert_update = create_engine(u'mysql://user1:[email protected]/mydb'
...

and then you map your engines to your tables, using the strategy that best fits your needs. I never tested that (because I'd never implement such a thing), but I see no reason SQLAlchemy couldn't map the same objects to different sessions.

Would that be overkill?

My personal opinion, is that yes it is overkill and utterly stupid. If you code correctly your application, there's no need of doing such a thing. You shall give your mysql connection the exact permissions you will need for the whole instance of your application, and you shall take a really good care of checking the user inputs. Thus you will protect your application, the system it is running on and your DBMS.

Can it impact the performance?

It can, each connection to the database has a cost, and your dbms has a limited pool of possible connections. The math is easy, if for every instance you open 4 connections, you divide the number of possible connections by that much. And that will not only impact your application, but every application that use your DBMS.

Any other thoughts?

Yes, just don't try to do that, and just have a good and secure design for your application :-)