Browse DevX
Sign up for e-mail newsletters from DevX


The 'Secure by Default' Features of SQL Server 2005 : Page 2

SQL Server 2005 offers improvements in authentication and authorization, as well as native support for cryptography—features that enable more secure database application design and implementation than previous releases.




Building the Right Environment to Support AI, Machine Learning and Deep Learning


After a user is successfully authenticated against SQL Server, the authorization process starts to determine which rights the user has on the database. In this area, SQL Server 2005 has two new features:
  • Separation of users and schemas
  • Execution context

Figure 3. User and Schema Relationship in SQL Server 2000

Separation of Users and Schemas
A schema is a container where you can logically group database objects (tables, stored procedures, views, etc.). It is the same as a namespace in the .NET Framework base class library. Because a schema in SQL Server also can have an owner, you can dictate that each object in a given schema have the same owner. SQL Server 2000 doesn't have a good implementation of schemas: the name of the schema is the same as the name of the user. Therefore, a direct relationship exists between the schema and the owner of the database objects (see Figure 3).

The big problem in SQL Server 2000 is that objects in a given schema must be addressed with a fully qualified name (schema.objectname), but when you drop a user from the database, the fully qualified name of an object changes. Therefore, the changes you made in the database unfortunately also occurred in the queries on the client side, and such changes were sometimes costly. With SQL Server 2005, the schema is a standalone native object in the database with its own name and owner. Such a schema has no relationship to the user (see Figure 4).

Figure 4. User and Schema Separation in SQL Server 2005

Now you can delete and add new users to the database without making changes in the software (neither on the client side nor on the database side). Furthermore, you can assign rights to a schema to control access to the objects in that particular schema. Listing 2 shows schema usage in SQL Server 2005. This code is also available as a download in the file Schemas.sql.

Listing 2 USE master GO -- Create database logins CREATE LOGIN Paul WITH PASSWORD='p@ssw0rd1' CREATE LOGIN Mary WITH PASSWORD='p@ssw0rd1' USE TestDatabase GO CREATE USER Paul FOR LOGIN Paul CREATE USER Mary FOR LOGIN Mary -- Create a new schema CREATE SCHEMA SalesData -- Create a new table in the schema "SalesData" CREATE TABLE SalesData.SalesPromotion ( ID int, [Name] varchar(255) ) GRANT ALL ON SalesData TO Paul ALTER USER Paul WITH DEFAULT SCHEMA = SalesData GRANT SELECT ON SCHEMA::SalesData TO MARY

Execution Context
Another new feature in SQL Server 2005 allows you to change the execution context of a stored procedure, a user-defined function, or a trigger. You can control under which user these database objects execute by changing the execution context with the EXECUTE AS statement, which supports the following options:
  • EXECUTE AS USER='user name'

EXECUTE AS CALLER is the default option, through which a stored procedure is executed with the identity of the calling user. With EXECUTE AS USER, you specify the user under which the stored procedure should run. When you use EXECUTE AS SELF, the stored procedure executes with the identity of the creator. Finally, you can use EXECUTE AS OWNER to execute the stored procedure with the identity of the object owner.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date