This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

Secure by Default

SQL Server doesn’t give logins or users any rights by default. That means when you add a login or user to the SQL Server instance, the user cannot access any of the data or objects in the instance until you grant rights.

That’s not the model that so many people have learned in many applications where once a user has access, they can view anything. This leads to many administrators and developers thinking something is wrong when they create a new login and data cannot be accessed.

So they start by granting one of two rights initially: sysadmin or db_owner.

That’s a huge mistake, and leads to security issues down the road if the database contains any type of sensitive information.

The Principle of Least Privilege

There’s a security tenet that is known as the principle of least privilege. This essentially means that any user is only allowed to the minimum amount of access needed to accomplish their job. A few examples of what this means in practice:

• If a user is supposed to only use the HR application to add new employees, they shouldn’t have administrator access.
• If a web application provides read only views of sales data, the account it uses to access SQL Server should only have read (SELECT) access, and no rights to change data (no INSERT/UPDATE/DELETE)
• A manager that only maintains an employee’s address information in a self service situation should have read/write access to the address data, but not the salary data, name data, or any other employee data.
• A developer that is allowed to back up a particular database from the production systems to restore this on the development server should not have system administrator access to production. They should have backup rights only for the database(s) the developer needs.
• An auditing application that writes to an audit table needs INSERT rights on the table, but not UPDATE, DELETE, or SELECT.

There are many more examples, but the basic idea is that you grant the rights needed, not every right.

In Practice

It feels like a lot of work to deal with roles, or think about the rights needed. It’s really not. Set up a role when someone needs access and grant the rights they need to that object. If they need more rights, grant more rights.

Filed under: Blog Tagged: security, sql server, syndicated