3 Security

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)

3.1 Logins and Users

Two separate security accounts exist in SQL Server, rather than the single username in oracle:

1. Logins – A login account is for an entire instance.
2. Users – A user account is for a particular database within an instance

Accounts must have both a login account for the instance and a user account for a database or they will not be able to connect.

In addition there are Windows accounts, typically handled through multi-server domains and maintained by system administrators rather than DBAs.

There are two methods of authentication for logins: Windows authentication (equivalent to an ops$ login in oracle) and mixed mode authentication, which is a username/password separate from the operating system.

Service access requests would involve four stages:

1. Create login account
2. Create user account – one for each database for which access is required
3. Assign login account to above user accounts
4. Assign permissions and roles to the above user accounts

Although the whole process is simplified by using the wizards.

The sa login is the system administrator – equivalent to sys in oracle. It should not be used outside the DBA team. It often has its login rights disabled for security.

The guest account also is often dropped for security reasons.

Lab 3.1 – add then revoke logins and users.

Prior to SQL Server 2008 Release 2, accounts which had Windows local admin rights on a server (similar to sudo root rights in linux) also automatically had SQL Server system admin rights login. That has now been changed, so that these accounts cannot now even connect to SQL Server unless explicitly granted login rights.

3.2 Permissions and Roles

Roles exist in SQL Server as in oracle. Roles are either Server level or Database level.

There are 7 fixed server level roles (sysadmin; dbcreator; diskadmin; processadmin; serveradmin; setupadmin; securityadmin, bulkadmin), 9 Fixed Database roles, and as many User-defined Database roles as you want to create.

The sysadmin server level role has DBA+ privileges, and so should not be granted outside the DBA team. Dbcreator is occasionally given to non-DBA users. The other fixed server level roles are in practice never used.

Three of the database level roles are commonly used:

• db_datareader – select any table
• db_datawriter – insert/update/delete any table
• db_owner – full power in that database, like an oracle schema owner

Object Permissions are granted to user accounts or database roles through the SQL Server Management Studio GUI. As in oracle, permissions can be implemented at column level. Roles can be assigned passwords.

Three separate object permissions exist in SQL Server:

1. Grant – can perform action
2. Deny – cannot perform action (strong). This applies even if the user account is a member of a role which has been granted the permission.
3. Revoke – cannot perform action (weak). This will be overridden by a grant to a role which the user account is a member of.

So Grant and Revoke are the same as in oracle. The additional Deny command is an extra strong form of Revoke.

Lab 3.2 – create a role and add users, then grant statement and object permissions to users and/or role.

3.3 Database Owner

Users can be granted the db_owner role – but there is also a “dbo” user account for each database. This is associated with one particular login, the “database owner”. This can be seen in database properties in the “General” tab, and can be easily changed in (counterintuitively) the “Files” tab also in database properties. It can be good practice to enforce use of a single login for database owner, either a secure domain account or “sa”.

Database owner is similar to schema owner in Oracle. However SQL Server also has the (rarely used) concept of distinct schemas within a database. By default, objects are created in the “dbo” schema, which is mapped to the database owner. When database owner is changed, the objects remain unchanged in the dbo schema.

3.4 Surface Area Configuration Facet

SQL Server disables most additional features by default as a security precaution, including database mail. To review/modify these, right click on instance name in SQL Server Management Console, choose “Facets”, select “Surface Area Configuration” from the drop down list.

The most important of these features are:

• Database mail – required to send emails for alerts, jobs, user reports.
• XP Cmd Shell – allow user procedures and jobs to directly run OS commands like copy and delete of files.

3.5 SQL Agent Proxies and Credentials

It is possible to store username/password details and assign those to particular operations of SQL Agent (cron) jobs. This is done with Credentials and Proxies, controlled through SQL Server Management Console from:

• Username/password store – expand instance “Security” > expand “Credentials” / right-click and select “New Credential”
• Assign those to an operation – expand “SQL Server Agent” > expand “Proxies” > / right-click and select “New Proxy”

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)