Security Information and Review

Collection of security information and review

Hardening MS SQL Server Security with Database Roles

Author : Admin

MS SQL Server divides it’s authorization architecture into statement permission and object permission. In the MS SQL Server there are three types of permissions : GRANT, REVOKE AND DENY. With statement permissions allow user to create, alter and drop objects in the database, such as CREATE DATABASE, CREATE FUNCTION, CREATE PROCEDURE and others. In the object permission, allow user to perform operations that use the objects in the dataset such as SELECT, INSERT, DELETE and others.

In the MS SQL Server, each database has a set of ten built-in roles : public, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_backupoperator, db_datareader, db_datawriter, db_dennydatareader, db_denydatawriter.

The public role closely resembles the Everyone local group in Windows NT/2000/XP. Every user account in sysusers that has the hasdbaccess status bit set is automatically a member of public. User accounts that do not have that bit set are also technically members of the public role, but membership is irrelevant until they gain access to the database.

The member of db_owner role can perform any activity in the database but could not grant permissions on an object that he did not own. A member of db_accessadmin database role, it can add or remove Windows NT groups, user or SQL server users in the database.

The member of db_accessadmin role has limited set of permissions, it’s can only grant or revoke access to the database. Probably the best use of it is to make someone on the technical support or help desk staff a member so that he can grant access to the database to new users.

The member of db_securityadmin has a little more value than the db_accessadmin role in the day-to-day operation of a database. This role is allowed to grant, revoke, and deny permissions on every object in the database. It is also allowed to manage roles and role memberships.

By defining the appropriate role for a user can increase the security of MS SQL server and the object contained.
So to do, we can define user defined , we must be a member of the sysadmin and db_owner or db_securityadmin. The following code shows the stored procedures used for creating them and for managing their list of members :

1
2
3
4
5
6
7
 
sp_addrole [@rolename =] 'role' [,[@ownername =] 'owner']
sp_droprole [ @rolename = ] 'role'
sp_helprole [[@rolename =] 'role']
sp_addrolemember [@rolename =] 'role',   [@membername =] 'security_account'
sp_droprolemember [@rolename =] 'role',   [@membername =] 'security_account'
sp_helprolemember [[@rolename =] 'role']

Mohamad Widodo

Mohamad Widodo

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • del.icio.us
  • Netvouz
  • DZone
  • ThisNext
  • MisterWong
  • Wists
  • Technorati
  • YahooMyWeb
  • Slashdot
  • StumbleUpon
Categories: SQL Server Security
Great blog post, precisely what I had been on the search for.
5 November 10 at 10:43
If you like this posts, please leave messages / comments.