SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Principle of Least Privilege – Skill #3

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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Clive Chinery on 7 September 2011

The only thing I can add to such an excellent article is that where there is a series of applications each with its own database on a common database server, there should never be a common account across all the databases that the applications use. Thus if ever one application is hacked, only one not all databases is accessed.

I have been advocating Least Privilege for years.

Posted by Mike Dougherty-384281 on 7 September 2011

A version of this is the creation of a user named after an application.  Web applications with anonymous users still need access to the database so we create a user for each application.  Rights are granted based on what that app is expected to do.  If the web server is ever compromised an  intruder is only granted the level of access that the app would have given anyway. (Business rules in SP rather than application code helps ensure this too because the app is only granted execute rights on procedures)

Posted by David.Poole on 16 September 2011

I remember creating mayhem when I designed a database with db roles that were explicit to the manager, reviewer, user.

I set up a whole bunch of grant and deny permissions so if someone was in the manager role and user role they only got the permissions appropriate for the user.

It meant that someone had to think carefully about the business role that the employee was supposed to fulfill.  It also meant that simply clicking permissions wouldn't work.

I sometimes think that if someone checks db_owner, db_reader and db_writer then they should be denied the extra privileges that db_owner gives them.

Posted by Derek Melber, MVP on 21 September 2011

I love to see that the least privilege needs are moving beyond the desktop and general server use. I have been really pushing least privilege and tools like BeyondTrust PowerBroker, which can help reduce security risk on desktops ans servers (including service accounts).

Great article.


Leave a Comment

Please register or log in to leave a comment.