Applications and Systems
One of the worst things you can do with a system is let everyone have access to every part of it. If you do, people tend to mess around and “investigate” parts of the system they shouldn’t. They may accidentally delete something they shouldn’t. They may be tempted to access data they have no business going near, and by doing any of this they may make the company liable for any number of data-related legal problems.
Faced with this problem, you might decide to implement a system of permissions on your system, where each user is granted access to the parts of the system they need to access for their job.
For example, say you have a member of staff, John, who handles invoicing. So you set him up with permissions to access the appropriate parts of your office management system. He can create, edit and view invoices, enter and edit new client details, and send reminders to clients. As time passes, his job expands to take him into other areas of the system, and you adjust his permissions to allow him to do this.
Eventually John decides to get a better job and leaves, so your company gets a replacement, Jane, Obviously she needs the same permissions that John had, and that’s where the problems start. The permissions are implemented for each user so you need some way of copying them from John’s account.
You could reconstruct John’s permissions by copying them from his account, assuming you get there before the scheduled job deletes it. If the account is gone, you’ll probably work from memory and add all of the rights you can think of. To finish the job you’ll probably have to sit next to Jane when she starts using the system, adding permissions every time the system comes to a halt as she’s trying to do her job. This assumes that you have nothing better to do than sit next to someone who’s thinking you’re more and more incompetent with every passing minute. Calling in sick and hiding out at home for a couple of weeks sounds good at this point.
I’m labouring the point a little, but if you find yourself in this situation and you designed the system, you have nobody to blame but yourself. Think of it like this: in the real world, a landscaper needs access to your yard, a meter reader needs access to your porch or any cupboards in the house where there’s a meter. A grocery delivery service needs access to your porch, possibly the front hallway and, if they’re very helpful, to the cupboards in your kitchen.
It doesn’t matter WHICH landscaper, meter reader or delivery person the company sends, they each get access they need to depending on their jobs, or roles; no more, no less. (This is an example of the Principle of Least Privilege or PoLP).
Users should have access to systems depending upon their roles, not who they are. So John should have been given the role of “Finance Operator”, or something similar. Then when he left, you could have just assigned this role to his replacement.
Databases
When you’re designing access to a database, you should use the same principles as you’d use with applications: roles and the Principle of Least Privilege. Access doesn’t depend on the user, it depends on the user’s role. A DBA needs the ability to do pretty much anything in the whole database, but the system that prints picking lists for orders just needs the rights to access the data to produce its lists. It doesn’t need the ability to make any changes, unless there’s an audit trail, and even then there are only limited places it needs to add or change data. It shouldn’t be able to delete data, and it certainly shouldn’t have access to any of your customers’ personal data beyond names, delivery addresses and contact details. Ideally you should go so far as to abstract the database schema behind stored procedures so that the processes that access the database have no way to access data they shouldn’t.
As with other systems, as well as improving security, roles allow you to use the separation between users and permissions to simplify administration. For example, you could organize your roles so that each process has a role of its own:
Alternatively you can separate roles so that each area of the database is associated with a separate role. This is arguably more correct, as roles represent a purpose rather than an application:
Whichever way you decide to organize them, make sure roles only have access to the parts of the database that they need to have. When they’re first introduced to the idea of roles, some developers see them as more of an obstacle than a feature, and just set the permissions using something like this:
USE [MyDatabase];
CREATE ROLE MyNewRole AUTHORIZATION dbo;
GRANT EXECUTE TO MyNewRole;
GRANT SELECT TO MyNewRole;
GRANT INSERT TO MyNewRole;
GRANT DELETE TO MyNewRole;
GRANT UPDATE TO MyNewRole;
This is wrong. It grants the role access to do pretty much anything to the database. It’s like leaving your front door wide open; in both cases you’re eventually going to wonder where all your stuff went.
Summary
Roles exist to improve your system security and to simplify administration. Whether they’re in your applications, your database, and API or some other part of your system, it makes sense to use them. They may seem to make it more complicated when you’re setting up your system but bear in mind that even systems with one user don’t usually stay that way for long. When you have multiple users or processes, you’ll be pleased you used roles.
