I have been working on a PHP application for my college that requires role based access control. The approach I followed was to create a separate MySQL user for each role. So every role had a separate db user. Each of these db users had privileges on a minimal set of tables that was required for the role.
Now, my question is whether this is infact a proper way to handle a role based application. The reason I have come into this doubt is because now when the time has come to get the system online (with GoDaddy hosting) I found out that they do not allow creating users with table specific privileges. Infact they don't even allow creating users directly through a SQL script(Role creation on the system involved creating users through a php script executing a SQL command).
Due to this I am now thinking if my approach was infact the correct approach or not. What is the standard way of implementing such role based systems?
This was my first live project so I don't really had much previous experience in terms of actually delivering a real project.
Unless you are not a hoster you probably don't want to create database users dynamically via scripts. Think about it:
So stick with one database for your application and use dedicated tables to build your ACL. Your database schema could look like this:
You basically have three things here:
That's the basic setup. The other tables are just helper tables to join pieces together.
Your code handles the rest. Setup a (or better more) class that do the heavy lifting. Then pass an instance of your ACL to your User class (other implementations are of course possible. See at the bottom of the post).
You should get the basic concept. The actual implementation is up to you. Things you may want to consider:
User
or rather a standalone component?Those question depend on what you like and on your architecture. Happy Coding!