Setting database and access control permissions

Question

Hi.

I am part of a team creating a web application using PHP and MySQL. The application will have multiple users with different roles. The application will also be used in a geographically distributed manner.

Accordingly we need to create an access control system that controls user permissions for specific database records i.e. modifies database queries so that only specific records are displayed. For example, for a user at the city level, only those records should be displayed that relate to the user's particular city, while for a user at the national level, records for ALL CITIES in the country should be displayed.

I need help on designing a system that can handle this type of information retrieval without hardcoding the information in the SQL queries.

Any help would be appreciated.

Thanks in advance

Answer 1

Will the users have direct access to the database engine? Avoid it if you can.

Make the application connect to the database with a dedicated user account that provides it with just the permissions that the WHOLE application needs. Do the rest of the security INSIDE the application based on application-defined users and roles and a simple custom API.

If these users may need to access and use multiple such applications, consider storing the users in a separate shared database and have the applications consult it using the shared custom API.

Answer 2

The design of the application will vary based on the required security needs.

For example, is information stored across multiple databases or in a single database? Is it important to present each TYPE of user with a schema of the database that represents their particular permissions? Obviously the answers to these questions suggests whether you need a more secure system for database access or not.

If you do, then create a database user for each type of user that will exist in the system. Provide each user with permissions to the appropriate tables, views, procedures, etc. When a user authenticates to the system, you'll have to create a conditional database connection based on the user type for that person.

To take the furthest extreme, it can also be appropriate to control access to the database strictly through stored procedures. Then each database user would only have access to their needed set of stored procedures.

Implementing security in the database can be pain and increases the complexity of the application as well as the cost of maintaining it and does decrease overall cohesion in the application IMO but it also presents a very tight security wall to anyone who might want to hack or otherwise abuse the system.

Regardless of what you implement within the database for security purposes, you will also obviously need to use a session mechanism to store the currently authenticated user and their type. An easy way to change functionality for a particular user is to encapsulate each user's available actions (read functions or methods) and their attributes (or properties) within a class to represent the specific user. That way if two people hit Dashboard.php, they will see precisely the data that they have access to.

Lastly, you can control some of the access controls (and likely will have to) through information stored about the user in the database such as what cities they need to see information for or what user's they supervise. Then use that information to create collections of objects that can be accessed from your user-specific domain objects.