Security Enhancements in SQL Server 2005: Schema

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]

Default Schema

Although the best practice is to access database objects by specifying the fully qualified name, most of the time users access objects by providing simple select statements. In addition, most of the time users may not access several schemas. For example, accounts people will use the accounts schema while human resources people will use the HR schema. Therefore, it would be useless to specify a schema all the time. To avoid having to do this, there is an attribute called default schema for each user.

As the screenshot above indicates, HumanResources is the default schema for user dinesh. This means that when dinesh accesses database objects in the above schema, he does not have to specify the schema explicitly. However, for better performances and for good practice it is advised to specify the schema explicitly.

The following T-SQL script will assign HumanResources as the default schema for user dinesh.

ALTER USER dinesh WITH DEFAULT_SCHEMA=HumanResources

Sys Schema

The schema that you'll use most frequently is the sys schema. All the system tables, views, and stored procedures belong to this schema. To access system objects, a particular user should have permission on sys schema. For example, just as you can get users from the sysusers system table in SQL Server, you can get schema details from the sys.schema catalog view.

Upgrading from SQL Server 2000

So what will happen when you upgrade databases from SQL Server 2000 to SQL Server 2005?

Let us assume that you have upgraded a database by means of a backup and restore. You have a SQL Server 2000 database with user User2000 and this user owns a few objects. After the upgrade, you'll see a schema named User2000 whose owner is User2000. All the objects that belonged to the user have been put into the schema User2000. Because of this, you do not have to change your previous codes if you have used fully qualified texts.

Another important factor you'll need to remember is that you will not be able to delete a user after upgrading to SQL Server 2005 because that particular user is now the owner of a schema. To delete the user, you will first have to delete that user's schema or change its owner.

Conclusion

Schema is the new security enhancement in SQL Server 2005. For developers and administrators, it also reduces the hassles associated with deleting users.