Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server 2005 Secures Your Data Like Never Before : Page 4

Right out of the box, SQL Server 2005 does not install many of its services (such as SQL Server Reporting Services) or does not have features turned on by default (.NET integration), thereby reducing the attack vectors that hackers could use to compromise your data security.




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

Separating User from Schema
One of the strange things about how SQL Server 2000 and its predecessors work is demonstrated in the code below. Assume that User1 is logged in.

CREATE TABLE table1 (tID int) GO SELECT * FROM dbo.table1 -- Error! SELECT * FROM table1 -- User1 can run SELECT * FROM User1.table1 -- Runs

Assuming that User1 is not the database owner or a member of the System Administrators role, a table called User1.table1 is created by the first line of code. Trying to select data and referring to dbo.table1 doesn't work, since no table by that name exists. Only User1 can select from the table using just the name table1 since SQL Server helpfully tries to prepend the current user's name as one of the options it tries. For any other user, table1 isn't found. Finally, anyone with the proper permissions can select from User1.table1, since that is an unambiguous object name in the database.

If the current user is the database owner or a member of System Administrators, the name automatically becomes dbo.table1 unless you explicitly create the table as User1.table1.

The strange thing about this is that a specific user's name is part of the qualified name of the object. This causes all kinds of problems when you have to change ownership of an object. Say that Bob originally created the table so that its name is Bob, making the object name Bob.MyTable. During his time on this project Bob creates hundreds of other objects, including other tables, stored procedures, views, and so on.

Now it's time for Bob to go on to greater things (management, no doubt), and Floyd steps into Bob's role on the project. The problem is that the system admins want to remove all permissions in the database and remove the user Bob from access, in case Bob later gets mad at the company and vandalizes its databases to inflict revenge. But until every one of those hundreds of objects is assigned to a new owner—presumably Floyd or, if the admins have learned their lesson, a database role—Bob can't be dropped as a user. Even with an automated scripting tool, that can take some time. And any references to Bob.MyTable and all his other object references in applications distributed around the company and its customers will also have to be updated. In short, this is a nightmare. This is another reason why Microsoft has long recommended that all objects be owned by dbo and security implemented in other ways.

Figure 4: SQL Server 2000's Unity of Users and Schemas. In SQL Server 2000 the user Carol and the schema Carol were essentially one and the same.
The reason for this mess is that SQL Server 2000 and earlier blurred the distinction between users and schemas. More specifically, a user and a schema are one and the same thing. Consider Figure 4, which shows the situation in SQL Server 2000. When Carol logs into the database and creates an object as a non-admin and non-owner, the object name is prefixed with Carol. Conceptually SQL Server created a schema that hid behind the user, but you really couldn't get at the schema and modify it independent of the user. As a result, changing ownership requires touching every object and all the code that uses those names.

As defined in the SQL-99 specification, a schema is essentially a container for objects in the database. It then may, in turn, be owned by a principal, as shown in Figure 5. Now when Carol leaves the company, admins only have to change the ownership of any schemas Carol owns, each of which could have thousands of objects. (The schema name is unlikely to have the user name in it, but you can name them any valid SQL Server object name.) This approach is much cleaner, much easier, and much more secure.

Figure 5: SQL Server 2005 Separates Users and Schemas. In SQL Server 2005, the user Carol owns a schema CarolSchema, illustrating how users and schemas have been separated.
SQL Server 2005 no longer automatically creates a schema with the same name as the user when you create a user. Instead, you have to explicitly create a schema, assign ownership to a user, and then you can create and add objects as part of that schema. You can (and usually should) assign a default schema to a user so that all objects the user creates and doesn't explicitly assign to another schema become part of the default schema.

The following code shows how this works.

-- Log in using an admin account initially USE pubs CREATE LOGIN carol WITH PASSWORD = 'carolPWD' CREATE USER carol FOR LOGIN carol GRANT CREATE TABLE TO carol EXECUTE AS LOGIN = 'carol' GO CREATE TABLE table1 (tID int) -- Error: Don't have permission to create -- object in dbo schema. Revert to admin REVERT -- Create schema and link to login CREATE SCHEMA CarolSchema AUTHORIZATION carol EXECUTE AS LOGIN = 'carol' GO CREATE TABLE table1 (tID int) -- Still an error. Just because there is a -- schema, doesn't mean it's the default CREATE TABLE CarolSchema.table1 (tID int) -- Success at last! REVERT

After changing to the pubs database (which isn't included as one of the SQL Server 2005 sample databases, but you can easily add it), the code creates the login carol, maps it to the user carol in the pubs database, and grants it the ability to create tables. It then changes the execution context to the new user carol.


The code next attempts to create table1. But when the code created carol it didn't assign a default schema. SQL Server attempts to use the dbo schema, but Carol doesn't have ownership rights in the database so the CREATE TABLE statement fails.

CREATE TABLE table1 (tID int)

After reverting to the original admin login that started this session, the code creates a schema and gives ownership to user carol. You'll see the AUTHORIZATION clause a lot in SQL Server 2005 because it lets you assign ownership in the same statement that creates or alters an object.


The code then once again changes the execution context to carol and attempts again to create table1.

CREATE TABLE table1 (tID int)

But, damn, it fails again! The problem now is that just because a user owns a schema doesn't mean that it's the user's default schema. A user could own hundreds of schemas and SQL Server shouldn't be responsible for picking one to be the default. But what does finally work is creating the table to be explicitly contained within the schema.

CREATE TABLE CarolSchema.table1 (tID int)

Success at last! Listing 1 shows the complete code, including the execution context switches, to make this all happen.

The second attempt to create the table, once CarolSchema existed, would have succeeded had the code assigned a default schema, either when initially creating the user or by later altering it.


By the way, don't be confused by the CREATE SCHEMA statement in SQL Server 2000. That statement simply provided a streamlined means of creating tables and views owned by a specific user and giving the user permissions on the objects. It hinted at the blurring of users and schemas but is not the same thing at all.

Like many other new statements in SQL Server 2005, the CREATE and ALTER USER statements supplant system stored procedures that do similar things. In this case, the statements replace procedures such as sp_adduser and sp_addapprole. These procedures are still around for backward compatibility but have been modified somewhat to support the user/schema separation. They create a schema with the same name as the user and make it the default schema so as to imitate the behavior of SQL Server 2000. I recommend that you use the new statements and make it all explicit, however, rather than hiding schema creation.

The separation of users and schemas in SQL Server 2005 is yet another way that you can keep tighter control over the security architecture of your databases and applications. It most certainly makes it easier to administer a database and SQL Server. You shouldn't need to have dbo own everything anymore.

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