Security has become more and more important in today’s business environment. From the database point of view, DBAs and system administrators need an improved security model. SQL Server 2005 provides an improved security feature. It is claimed that SQL Server 2005 is secure by default. In SQL Server 2005, the security model is divided into three areas namely authentication, authorization, and encryption.
Authentication is the process of identifying the logon information of a user who is connecting to and accessing databases on the SQL server.
Authorization is the access rights to database objects that are given to a user after the logon process is successfully completed (authenticated).
Encryption, which is entirely new in SQL Server 2005, is the transmitting of valuable information in formats that an unauthorized user cannot easily access. Although there were undocumented stored procedures available for encryption in SQL Server 2000, they were hardly popular among the database community.
Breaking with the tradition of starting with authentication, this article will discuss the key features of authorization in SQL Server 2005. Among the several authorization features, schema is the most valuable and confusing special feature.
Schema Separation
Schema is a new addition in SQL Server 2005 and a bit confusing to many database developers. Before we look into it, let’s first revisit SQL Server 2000. This is a fully qualified query name to access a table in SQL Server 2000:
Select * from [DBServer].[DBName].[ObjectOwner].[Table]
I assume that you are aware of the difficulties that will arise when you try to drop a user who owns database objects. Before dropping the user, you need to assign all the objects belonging to that user to another user by using sp_changeobjectowner. If you do not reassign the objects first, you will have to drop all the objects belonging to the user before you can drop the user.
In SQL Server 2000, objects are tightly linked to users. This means that two users can have objects with the same names, which can lead to confusion in the development environment.
SQL Server 2005 provides a solution for this issue: a method called a schema. Think of a schema as a container that has boundaries. This container holds objects. Instead of accessing the table by object owner, as you did in SQL Server 2000, you can access it by schema:
Select * from [DBServer].[DBName].[Schema].[Table]
In general, schema is a replacement for database owner in SQL Server 2000. You might be wondering now how users fit into the picture. Users can create schemas and can own and belong to schemas. The benefits of SQL Server 2005 schemas are as follows:
- Dropping database users is greatly simplified.
- Multiple users can own a single schema via membership in roles or Windows groups. This familiar functionality is extended to allow roles and groups to own objects.
- Multiple users can share a single default schema for uniform name resolution.
- Shared default schemas allow developers to store shared objects in a schema created specifically for a specific application, rather than in the DBO schema, which was the general practice in SQL Server 2000.
- Permissions on schemas and objects contained in schemas can be managed with a higher degree of granularity than in earlier releases of SQL Server.
Let’s see how we can create schemas and how we can perform operations on them.
You can find schemas in the Security node of the Microsoft SQL Server Management Studio. By right clicking it and selecting new, you can create a new schema.
As I mentioned earlier, a schema has an owner who can modify its properties. Unlike in SQL Server 2000, ownership can be transferred easily.
CREATE SCHEMA [Accounts] AUTHORIZATION [db_accessadmin] is the T-SQL equlent for the above screenshot.
Users should be attached to schemas to access objects. Then you can add users to the schema and set the permissions for each user accordingly.
However, you need to remember that you will not be able to drop a user if that user belongs to any schemas. You may be wondering what the difference is between this and dropping a user in SQL Server 2000. Changing the owner of a schema is not as difficult as changing the owner of objects. In addition, you can adopt a practice that will make things even easier: Assign all schemas to ownership by user dbo and allocate appropriate permissions to the relevant users. In this way, you will be able to drop a user at will and the effect on your developments will be minimal.
The following script will create a user called dinesh with password wordPa$s and with SELECT permission on schema Accounts.
CREATE LOGIN dinesh WITH PASSWORD=’wordPa$s’
GO
USE AdventureWorks
GO
CREATE USER dinesh FROM LOGIN dinesh
GRANT SELECT ON SCHEMA::[Accounts] TO [dinesh]