Creating a different user for each concern of my application!

Question

I want to create my site and in the page have it so that the forum pages will use the forum mysql user having privileges on mydb.forum_table, mydb_forum_table2. and the profile page to use the profile user having access to mydb.users and mydb.profiefields and so on with the photogallery, blog, chat and... is this the right way to do it! I'm thinking of principle of least privileges but I wonder why I haven't seen other big known CMS do it!

Answer 1

One of the critical resources for a database is connections. Generally databases are configured with a maximum number of connections, an each time a process needs to make a query, it needs a connection to do so. Database connections are expensive objects to create -- they take time and memory, and most importantly, connections are established for a specific user. The generally accepted 'best practice' for web applications is for the application, when it needs a database connection, to check a pool for an available connection. If there's a free connection in the pool, the web app will pull that connection, use it as necessary, and then return it to the pool for reuse. If there are no free connections, the app will create a new one, use it, and then place it in the pool for reuse.

If you're dealing with an application that uses multiple database users (for privilege management) and you need to use connection pooling, your application will need to establish many pools (one for each user), which will usually result in your application acquiring at least one connection for each database user it is using. This is inefficient, error prone, and needlessly complex.

If you're truly intent on limiting your application's access to data, then you should probably investigate how much support your database has for views. If views are well-supported, then you can create a view (or views) that are customized to the needs any given portion of your application.

My recommendation would be to stick to a single database user, and then use the time you just freed up to do more debugging of your application. You'll get better results, and will aggravate fewer DBAs.

Answer 2

If I understand correctly, the question is about implementing module access control based on the permissions on the tables that are used by the module.

I think it would be complicated to maintain (the link between modules, and tables), and slow to have to check the permissions on each table accessed by the module.