Fixed database roles

1. Fixed database roles Date :12-4-2011 Palestinian Land Authority

2. Fixed Database-Level Roles • db_ddladmin • db_securityadmin • db_accessadmin • db_backupoperator • db_datawriter • db_datareader • db_denydatawriter. • db_denydatareader Palestinian Land Authority

3. The db_owner role • Like the sysadmin fixed server role, the db_owner role has complete control, except in this case it is control of the database instead of the whole server. • CREATE AUTHORIZATION ON DATABASE::database_name TO valid_login • The db_owner can assign permissions, • backup and restore the database, • create and destroy objects, • manage user accounts and roles without any restrictions. Like with the db_ddladmin role, • DBAs should exercise caution before giving out the db_owner role, even in development. Give developers only what rights they need and no more. • Caution: It is a best practice to ensure rights, regardless of system, are the minimum required to do the job and nothing greater. This is the Principle of Least Privilege. Even if a user has good track record of doing only what they are supposed to do, Palestinian Land Authority

4. The db_ddladmin role • A user with the db_ddladmin fixed database role has rights to issue (DDL) statements in order to CREATE, DROP, or ALTER objects in the database. • The ability to set permissions such as with DENY, GRANT, or REVOKE, however, is not given to the db_ddladmin role. • list the rights of this role: • All DDL but GRANT, REVOKE, DENY • REFERENCES permission on any table • sp_changeobjectowner • dbcc cleantable : Reclaims space from dropped variable-length columns in tables or indexed views • dbcc show_statistics :displays current query optimization statistics for a table or indexed view • dbcc showcontig :Displays fragmentation information for the data and indexes of the specified table or view. • sp_fulltext_column :Specifies whether or not a particular column of a table participates in full-text indexing. • sp_fulltext_table: Marks or unmarks a table for full-text indexing. • sp_recompile : Causes stored procedures and triggers to be recompiled the next time that they are run • sp_rename :Changes the name of a user-created object in the current database • sp_tableoption: Sets option values for user-defined tables • TRUNCATE TABLE :Removes all rows from a table without logging the individual row deletes. Palestinian Land Authority

5. The db_ddladmin role • sp_changeobjectownerpermissions has an additional requirement. In order for a member of the db_ddladmin role to be able to successfully call sp_changeobjectowner, the member must also be a member of the db_securityadmin database role. When an object’s owner is changed, permissions are changed as well. • As a result, there is a check built into the system stored procedure to verify that a calling user is either a db_owner or the combination of a db_ddladmin and db_securityadmin: • DBAs should exercise great caution with this database role. • The db_ddladmin role has the ability to create objects under the context of any valid user. This means the role can create objects belonging to dbo simply by specifying the owner in the CREATE statement. • Note: Because of the ability to create an object with any owner, I would not recommend any user being granted this right in a production environment except in the rarest of circumstances. • Even in development you should carefully weigh its use for personnel outside of the DBA team. This is a good security permission for a junior DBA who is only assigned development tasks, however. Palestinian Land Authority

6. The db_securityadmin role • db_securityadmin Members can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation. • The list includes the DENY, GRANT, and REVOKE commands along with all the store procedures for managing roles. • The full list is: • DENY • GRANT • REVOKE • sp_addapprole • sp_addgroup • sp_addrole • sp_addrolemember • sp_approlepassword • sp_changegroup • sp_changeobjectowner • sp_dropapprole • sp_dropgroup • sp_droprole • sp_droprolemember Palestinian Land Authority

7. The db_securityadmin role • sp_changeobjectowner. Recall from my discussion of the db_ddladmin security role that sp_changeobjectowner requires either db_owner or the combination of db_ddladmin and db_securityadmin role membership. This is because the stored procedure makes a schema and a permission change. • This example changes the owner of the authors table to TESTuser., valid owneruser or role EXEC sp_changeobjectowner 'authors', ‘testuser' • Surprisingly enough ,the db_securityadmin role cannot add users to the fixed database roles. • Only db_owner role members can add users to a fixed database role and this is by design in SQL Server Palestinian Land Authority

8. The db_accessadmin role • Members can add or remove access for Windows logins, Windows groups, and SQL Server logins. • It has the ability to add and remove user access to the database just as the securityadmin has the ability to add and remove logins to the server. • The db_accessadmin role does not, however, have the ability to create or remove database roles, nor does it have the ability to manage permissions. • sp_dropalias :Removes an alias that links a user in the current databaseto a SQL Server login. • sp_dropuser :Removes a database user from the current database • sp_grantdbaccess :Adds a database user to the current database. • sp_revokedbaccess: Removes a database user from the current database. • sp_addalias : Maps a login to a user in a database: • CREATE USER test FOR LOGIN test • Tip: To give a user the ability to add users to the database AND manage roles and permissions, make the user a member of both the db_accessadmin role and the db_securityadmin role. • sp_dropalias,sp_addalias , sp_dropuser, sp_grantdbaccess, sp_revokedbaccess: This feature will be removed in the next version of Microsoft SQL Server • Use Create user, Drop user, Palestinian Land Authority

9. The db_backupoperator role • db_backupoperator Members can backup the database. • The whole purpose of the db_backupoperator is to have sufficient rights to create backups of a database. • Restore permissions, however, are not granted to the db_backupoperator role. • BACKUP DATABASE • BACKUP LOG • CHECKPOINT :Writes all dirty pages for the current database to disk • I will say that I don’t use this role a whole lot. Most of the backup jobs I run are controlled by SQL Server Agent or an external job scheduler and write backups to disk. • These backups are then grabbed by a third-party product backup product and written to tape. Since the SQL Server Agent or job scheduler has sysadmin level access, Palestinian Land Authority

10. The db_datareader role • db_datareader Members can run a SELECT statement against any table or view in the database • db_datareader SELECT permission on any object • This role is often used in reporting databases where users would be coming in via a third-party reporting tool and building their own ad-hoc queries. • If you need to restrict a user to only be able to read from certain tables, the db_datareader is not the right choice as it would have to be combined with the explicit use of DENY permissions on tables the user shouldn’t be able to access. • A better practice would be to create a user-defined database role with the proper permissions • One key point about the db_datareader role is that it always has the right to read all tables and views. That means if you create a new table in the database, a member of the db_datareader role has access immediately. • This differs greatly from a user-defined role with which you must explicitly grant each permission. Therefore, unless you give user-defined role permission to access a database object such as a table or view, that role can’t do so. • Not only does it have access to user tables but also system tables. • That means a member of the db_datareader role can execute a SELECT query against a system table even you decide to revoke public access to SELECT against these tables. • (keep in mind that revoking default permissions would result in an unsupported configuration so far as Microsoft is concerned). Palestinian Land Authority

11. The db_datawriter role • db_datawriter Members can add, delete, or change data in all user tables. • DELETE, INSERT ,UPDATE permission on any object • The db_datawriter role has the ability to modify via INSERT, UPDATE, or DELETE data in any table or view in the database. • the db_datawriter role has permission to write to all tables and views, regardless of when they get created. So if you assign a user to the db_datawriter role and then create a table in the database, the user has access to modify the table immediately. • if specific permissions are needed, I recommend a user-defined database role as a better option.  Palestinian Land Authority

12. The db_denydatareader role • db_denydatareader Members cannot read any data in the user tables within a database. • db_denydatareader No SELECT permission on any object • The db_denydatareader role is the exact opposite of the db_datareader role: instead of granting SELECT permissions on any database object, the db_denydatareader denies SELECT permissions. • If access to the database can be controlled completely via stored procedures (with no dynamic SQL queries), it may be a good idea to make standard users a member of this role. • By placing a user into the db_denydatareader role, the user is unable to connect to the database and generate a SELECT query against any table. • If the same user is granted execute rights to a stored procedure that controls how a particular table should be accessed, the user can only view data based on how the stored procedure is coded, meaning data access can be carefully controlled. • GRANT EXECUTE ON dbo.usp_ControlSELECT TO testuserGO • Keep in mind that the public role has SELECT permissions against all tables in the default Northwind database configuration. Simply adding testuser to db_denydatareader has ensure the user cannot access the Customers table directly, even with the wide-open access granted to the public role. Palestinian Land Authority

13. The db_denydatawriter role • db_denydatawriter Members cannot add, modify, or delete any data in the user tables within a database. • No DELETE, No INSERT, No UPDATE permission on any object • Like db_denydatareader, the db_denydatawriter role serves to restrict permissions on a given database. • the user is preventing from modifying the data on any data via an INSERT, UPDATE, or DELETE statement. • if access can be completely controlled by stored procedures, this is a good role to put a standard user into. This will insure the user only accesses the data in the means intended. • Tip: If data access can be controlled via stored procedures while maintaining ownership chains and completely avoiding dynamic SQL, consider using the db_denydatareader and db_denydatawriter role for regular users to restrict data access to the stored procedures only. Palestinian Land Authority

14. The public role • So if all current and future users who have rights to a database should have the ability to access a particular stored procedure, rights could be assigned to the public role, such as in the following example: • GRANT EXECUTE ON dbo.EveryoneStoredProc TO public • I assign no permissions to the public role and I recommend strongly that you don’t either. • If I have a case where all users should access a particular object, I’ll create a user-defined role. • My reason is simple: while everyone currently requires access to that particular object, this may not always be the case. If, in the future, a single user does not require permissions, I’ll be forced to uncouple the rights to the public role, create a new role, and assign the permissions to it. I’ll then be adding all the users except for the one in question to the new role. It’s much simpler if I go ahead and do this up front, rather than try and do it later after additional complexity may have been added. Palestinian Land Authority

15. dbo Versus db_owner • In every database there is a special user, dbo. • While dbo has the effective permissions of a database owner, it is not one and the same as the db_owner role. • Any user account can be assigned to the db_owner role, giving that user complete control of the database. • The dbo user account is a special account which all members of the sysadmin role are implicitly mapped to. • Also, the system table sysdatabases (located in master) stores who is the owner of the database (and is assigned the dbo user account) in the sid field. • So not only are sysadmins mapped to dbo, but so is the database owner. • The dbo user will always have db_owner rights to a given database, but it is more than just a default user account. Palestinian Land Authority

16. dbo Versus db_owner • The reason the sid of the database owner is important is in the case of a damaged database. Normally, a db_owner role member could restore a database, • but the information on who belongs to the db_owner role is stored within the database itself. If the database is damaged, SQL Server won’t be able to determine who are members of the db_owner role • The only logins it knows for certain have the appropriate rights is the db_owner (by virtue of the information stored in sysdatabases) and those who are assigned to the sysadmin fixed server role (since they have all rights anyway). • Any time SQL Server cannot obtain the information about the db_owner database role because the database is unavailable, the actual owner (apart from the sysadmins) will be the only one who can perform such functions. • Another difference deals with permissions. If a user is a member of the db_owner role but not the dbo, DENY permissions still apply. In other words, if I issue a DENY to prevent a particular user from executing a stored procedure (or selecting data from a table, etc.), the user will be unable to issue the execute statement (or SELECT, etc.) as SQL Server will prohibit the user from doing so. Palestinian Land Authority

17. Concluding Comments • Some roles, such as db_securityadmin, are included to assist DBAs with delegation of administration responsibilities for a given database. • Other roles, such as db_datareader, are used to provide a simple method of assigning blanket permissions for end users. With that said. • there is no role for executing all stored procedures in a given database. If you want something like this, you'll have to create it manually.  • Certain roles, such as db_ddladmin and db_owner, have very broad permissions that typically aren't assigned in a production environment. • Other roles, such as db_securityadmin, have limitations on the full scope of permissions they are supposed to have. Palestinian Land Authority

18. Thank you Palestinian Land Authority