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 →

To Store XML or To Not Store XML

In SQL Server 2005, XML was a new data type introduced in order to store XML documents and fragments in a SQL Server database. While this data type is most definitely useful, one of the main questions that you need to ask yourself before using it is "Should I store this data as XML?"
Based on what data should be stored and/or the processing that the data will participate in later, the shredding the XML data to store the data in a relational form (if it is possible) may be the better than storing it as XML to avoid possible storage and performance issues in the future. If the data is going to be used as XML for later processing then storing it as XML is beneficial to avoid extra processing once you consider the cost for storage as being minimal compared to the processing cost. If you are unsure of when or why to use the XML data type then I suggest you read Tim Anderson's interview with Dr Micheal Rys which highlights the thinking behind some features that were introduced and provides some guidance on how to use them. Please note that this article is based on SQL Server 2005, new and improved XML features for SQL Server 2008 will appear in a later post.

Continue Reading →

Data Types

As stated on MSDN, a data type is an attribute that specifies the type of data that the object can hold. Choosing an appropriate data type is one of the most important decisions that you will make when designing your database. Your decision can have a positive or negative impact on your database's performance and storage requirements.

If the data type is too restrictive it can cause an issue like restricting the growth of your database. For example using the 'tiny int' data type which has a maximum value of 255 instead of using the 'int' data type which has a maximum value of 2,147,483,647. If you choose a data type that is broad and allows anything to be stored then you may end up using excess server resources such as disk space and memory which will result in performance issues.

SQL Server 2005 and 2008 native data types are organized into seven categories: Exact numerics, Approximate numerics, Date and Time, Character strings, Unicode character strings, Binary strings and other data types (also called Special purpose). Some books include Unicode character strings as part of Character strings category and highlights Monetary as the 7th category. Check MSDN: Data Types for more detailed information about the categories and their data types, including storage details and restrictions. Ensure that you avoid using data types that are being deprecated to avoid future upgrade issues.

Continue Reading →

Microsoft Tech Boot Camp 2009: Business Intelligence Highlights

I had a fun time presenting on 'Business Intelligence with SQL Server 2008' at the Microsoft Tech Boot Camp 2009. The number of attendees was larger than I expected but the interaction during the session and the few comments that I got after the session made me feel that the presentation had a positive impact on them. I think that the information made people aware of the power of SQL Server and highlighted some of the advantages of using the components of SQL Server 2008.
As for the demos that I did, I learned them from videos that you can find at the following links:
SSIS Demo - Creating a Basic Package
SSRS Demo - Create a Basic Table Report with a Wizard

If you were at the session please feel free to leave feedback about the presentation. Constructive criticism is appreciated.

Continue Reading →

SQL Server 2008: Business Intelligence

I've got a presentation next week Thursday in The Microsoft Tech Boot Camp 2009. The presentation (Track 6) will give an overview of SQL Server 2008 and highlight some of the BI capabilities that the product offers. The Boot Camp offers information for both developers and administrators so anyone interested can use the information from the image below in order to register. Please note this is an event taking place in Trinidad.

Continue Reading →

Welcome: My First Post

My name is Nigel Sammy and I would like to welcome you to my blog, SQL Server: A DB Professional's Best Friend. In my company I deal with many database tasks such as administration, development, BI, consultation etc and I have created this blog to share the cool SQL Server and technical information that I encounter in my DB adventures. I have been trying to start this blog for almost 2 years now but after viewing Steve Jones 'Building a better blog' I finally got it started, so there will be some posts of information that I encountered in the past but wanted to still share . Feel free to leave comments and questions and please do enjoy.

Continue Reading →