Security: Authorization

This shall be my last blog post for the year 2009 and what better to end security month than the actual post that started the security month idea. Before continuing, I suggest reading the previous security posts this month: Understanding Server Security Principals, Introducing Database Users and Roles and Schemas.
Two years ago I read an interesting white paper : SQL Server 2005 Security Best Practices - Operational and Administrative Tasks. The paper highlighted the definition of authorization as the process of granting permissions on securables and also stated that a best practice for authorization is to encapsulate access through modules (e.g. stored procedures, functions). This paper prompted the idea of allowing application users to only access or modify some database objects (e.g. tables) via procedural code. For example, if user 'Dave' needs to update the Customer table then a stored procedure to update the table can be created and he can be given EXECUTE access to that procedure rather than granting him UPDATE access to the Customers table.
While this is a great method for a DBA to secure the instance lets consider a scenario where a database has more than a hundred stored procedures and due to upgrades it is possible that new stored procedures will be added, how will you give a user EXECUTE access to each stored procedure? It is not feasible to grant access to each stored procedure one by one and remembering to grant access to each new one when they are created is troublesome. The answer to the question is schemas. Once all objects are part of the same schema then it just a matter of granting permissions at the schema level in order for a user to have permissions to all current objects and any new objects created in the schema. So in the scenario presented earlier, if the stored procedures are part of the dbo schema then the user has to be granted EXECUTE permission on the dbo schema and then the user will be able to execute all current procedures and any new stored procedures created in the dbo schema.

The syntax to grant permission on a schema is:
GRANT [Permission] ON SCHEMA::[schema_name] TO [Database User/Role]

e.g. GRANT EXECUTE ON SCHEMA::dbo to Dave

If the database user didn’t exist then use the following syntax to create a database user with a default schema :
CREATE USER [Username] FOR LOGIN [login_name] WITH DEFAULT_SCHEMA=[schema_name] 

e.g.
CREATE USER Appuser FOR LOGIN Dave WITH DEFAULT_SCHEMA=work
GRANT EXECUTE ON SCHEMA:: work TO Appuser
Continue Reading →

Security: Schemas

In the database world, the word 'Schema' is associated with the description of the tables and the relations in the database. However when Microsoft launched SQL Server 2005, they introduced a securable called Schema which created another meaning for the word 'Schema' in the database world. A Schema is a container of database objects (e.g. tables, views, stored procedures etc) and it is used to define the namespace for the objects. It is used to make management of objects easier for a database users. Schemas and users are separate entities and a schema can be owned by any user or database role.

In previous versions of SQL Server prior to SQL Server 2005, the four-part name for database objects included the owner name where as SQL Server 2005 and 2008 has schema name replacing owner name in the naming scheme.

Scenario 1 - SQL Server 2000
     A user named Fred created objects in the 'Product' database. Each object has the following four-part name :
          [Server Name]. Product.Fred.[Object Name]
Scenario 2 - SQL Server 2005
     Objects need to be part of a schema and since Fred does not want to use the 'dbo' schema he created a schema called 'work'. Each object has the following four-part name :
          [Server Name]. Product.work.[Object Name]

This is beneficial since changing user names or removing users does not require manually transferring each object the user owned to another user. In Scenario 1,if the Fred user is deleted then no one will be able to access any of the objects that he created unless each object is transferred to another user. However in scenario 2, the user Fred can be deleted once the ownership of the schema is transferred to a next user.

A default schema is assigned to each database user for each database that the user can access. This allows users to access objects in the default schema without having to specify the schema name. For example, Fred who has a default schema of 'work' can select data from the table work.Company by specifying Customer in his query instead of work.Company. If you need to access another schema, a two-part identifier (schema_name.object_name) will be required.

Syntax to create a schema:
     CREATE SCHEMA [schema_name] AUTHORIZATION [owner_name]
     e.g. CREATE SCHEMA work AUTHORIZATION Fred

The ALTER SCHEMA is used to transfer a securable between schemas. Syntax:
     ALTER SCHEMA [schema_name] TRANSFER [securable_name]
     e.g. ALTER SCHEMA work TRANSFER dbo.customers
     Note: the securable_name can be one-part or two-part named

Syntax to remove a schema:
     DROP SCHEMA [schema_name]
Continue Reading →

Security: Introducing Database Users and Roles

Securables are the entities that security principals are allowed or disallowed to access based on the permissions assigned to principals. Some securables contain other securables, which create nested hierarchies referred to as scopes. In SQL Server the three (3) main scopes are server, database and schema. In this post the securables that I will be focusing on are Users and Roles from the database scope. For a list of the securables in each scope visit MSDN: Secuarbles.

In the last blog I highlighted security principals and how to create server logins, however logins cannot access specific databases unless they are given appropriate database access or they are members of the sysadmin server role. This database access is achieved by creating a database user for each login that needs access to the database. This user creation and mapping can be done using SQL Server Management Studio (SSMS) or the following syntax:

     CREATE USER [Username] FOR LOGIN [login_name]

The [Username] does not have to be the same as the [login_name] but the [login_name] has to be a valid SQL server login. If a [login_name] isn't specified then SQL Server will try to create a user mapped to a login with the same name which means the user won’t be created if the [Username] is not the same as a valid SQL server login.
i.e.
     CREATE USER Tester FOR LOGIN Test - This syntax would work only if Test is an actual SQL server login.
     CREATE USER Tester - This syntax would work if Tester is an actual SQL server login because no [login_name] was specified.

Visit MSDN: Creating Database Users for more information about creating users via SSMS.

Just like how logins can become members of server-level roles to assist with management of logins is the same way users can become members of database roles to easily manage database users. Each database has a set of fixed database roles that can be used to group database users. The list of fixed database roles can be found on MSDN: Database-Level Roles. Unlike server-level roles, database roles can be created by users by using the syntax:

     CREATE ROLE [role_name]

Adding a user to a role can be done via SSMS by modifying the user's properties or the role's properties or by using the stored procedure sp_addrolemember.
Continue Reading →

Security: Understanding Server Security Principals

Due to a lack of blogging in November I decided to do something special in December, so this month shall be SQL Server Security month. Why security you ask? While I was writing about a topic, I realized that some basic security information may be necessary for someone new to SQL Server to understand the post so I decided to blog about the basic topics before I publish my almost completed topic. So to start the security month I will begin with Server Security Principals.

Security principals are entities such as individuals, groups and processes that request SQL Server resources. They are managed at three levels: Windows and SQL Server levels which have an instance wide scope and Database levels which have a scope within a specific database.

When accessing SQL Server a user must specify an authentication mode and credentials. Currently there are two modes for authenticating access to the database engine - Windows authentication and SQL Server authentication. When installing SQL Server 2000, 2005 and 2008 you must specify the Authentication Mode for the instance by choosing either Windows Authentication Mode or Mixed Mode Authentication. The Mix Mode Authentication allows both Windows authentication and SQL Server authentication. It should be noted that your Authentication Mode can be changed after installation via the security page on the server properties dialog box. Your change in mode is implemented after restarting the SQL Server instance.

There are also two types of logins that let you manage access to the instance - Windows login and SQL Server login. Windows logins are logins that are mapped to Windows user accounts which allows the authentication process to be done by the Windows OS. SQL Server logins are not mapped to Windows user accounts and are created and stored in SQL Server.

Windows Authentication allows server access to only authenticated Windows users, hence only windows login can gain access to the instance. On the other hand, Mix Mode Authentication allows both Windows login and SQL Server login access to the instance.

Basic CREATE LOGIN syntax
Windows Login :
     CREATE LOGIN [Domain\User] from Windows

SQL Server Login :
     CREATE LOGIN [username] with PASSWORD='[password]'

Check MSDN: Create login for more information about the various options that are available when creating logins

SQL Server also has a set of Server-Level roles (also known as fixed server roles) available to assist with the management of these logins. You can simply assign and manage privileges by adding logins as a member of a roles for example, a Windows login that is added as a member of sysadmin can perform any activity in the instance. Check MSDN: Server-Level Roles for more information about the various roles.
Continue Reading →

Community Links