I want to develop a database for project management (monitoring). I have three types of users: administrators, project management department (PMD), and scientist.
General case study:
- When the administrator logs on, he can edit records regarding the project management.
- When PMD logs on, it will display the read-only files or other forms.
- When the scientist logs, on he can see what project he has, but cannot edit area, and he can only view details in list view manner in VB.
The question is what queries should be made for log-in area, after all each user has different tasks and privileges? How can VB know which one to display at what login?
Generally you would establish a series of numeric 'Security Levels', for example a Scientist is a 1, a PMD is a 2, and an Administrator a 3. As you design and build your application, keep the security levels in mind and disallow anything that the user does not have a high enough security level for.
Once your system is in place, you can enhance this by creating database privileges that only allow for what the security level would allow, ensuring that someone who cannot edit data does not have the UPDATE, INSERT, and DELETE privileges.
As your application progresses you can also look at using very limited MySQL user accounts, and use stored procedures that check the user security level, bring security as close to the data as possible.
This all depends on your security needs. In some operations the data is not particular and the roles are just there to prevent average users from breaking things accidentally, in which case enforcement within the application is probably adequate.
There is a lot that can be done to manage the actions of your users, how far you wish to take things is up to you.
This was first published in January 2005