Friday, August 17, 2007

SQL 101 : SQL 2005 Schemas

‘User Schema Separation’ is allegedly an ANSI SQL 1999 standard but documentation on ansi standards is not freely available on the web.

2000 : [server].[database].[owner].object

2005 : [server].[database].[schema].object


Prior to SQL 2005, each database object had 1 owner.

In SQL Server terminology, the concept of a Schema was introduced in SQL 2005 to provide a logical way to group database objects, ease administration and address security.


Benefits of SQL 2005 Schema addition >

Database Structure

Organization

Subdivide database into areas of interest / business area by assigning tables with schema names.

For example in the AdventureWorks database >

HumanResources.Employee

Person.Address
Sales.CreditCard

This is especially helpful in databases with a large number of tables,

Easier Assignment of permissions

Logical Permissions Grouping

Permissions assigned at schema level make it easier to control relevant access to developers etc by subject area.

Reduced Administration

One command versus many when granting access to a set of objects when grouping them by schema.

Objects not reliant on users

Duplicate Objects

In SQL 2000, objects being assigned to users means that 2 users could create objects with the same name which is confusing to say the least.

Removing Users

Objects not being directly tied to user accounts means that dropping user accounts is much easier. It can be accomplished without either dropping all the owned objects or changing the object owner for any linked objects.

Users that OWN a schema now cannot be dropped however without changing the schema ownership.

Ease of teamworking

Multiple users can share a schema

Multiple users can OWN a schema through a role or group membership.

‘Default Schema’ setting at user level prevents users having to explicitly reference a schema, for example – if they always use they same one.

Security

The addition of schemas means that combined with object permissions, many more levels of security can be achieved.

Access to system objects can now be controlled by a user’s permissions on the SYS schema, unless of course their server role overrides this.

Script Order

Encompassing create statements for related objects inside the same schema makes scripting order unimportant. For example >

CREATE SCHEMA UserInfo
 
    CREATE TABLE Logins 
    (
        UserId INT NOT NULL 
            REFERENCES Users (UserId),
        LoginDate DATETIME
    )
 
    CREATE TABLE Users 
    (
        UserId INT NOT NULL PRIMARY KEY
    )
 
GO


SQL 2000 / 2005 Underlying tables >


2000 table

2005 tables

Server level

syslogins

sys.server_principals

Database level

sysusers

sys.database_principals

sys.schemas

No comments: