 | |||
|
|
||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| SQL Server News: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
|||||||||||||||||||||||||||||||||||||||
Separation of users and schemas SQL-99 defines the concept of a database schema as a named group of data that is owned by a particular authorization ID. Schemas are scoped to the database (called "catalog" in SQL:1999), and one database can contain one or more schemas. Schema objects, such tables, views, and stored procedures, live in a schema, and the two-part name of a database object is actually schemaname.objectname. Prior to SQL Server 2005, the concept of a schema was tied to a particular user. Any objects created by a user were owned by that user, and SQL Server really defined the two-part name of a database object as ownername.objectname rather than schemaname.objectname. There was a CREATE SCHEMA DDL statement, but you did not have the option of naming your schema, only its owner. -- SQL Server 2000 create schema, no schema name CREATE SCHEMA AUTHORIZATION fred GRANT SELECT ON v1 TO public CREATE VIEW v1 AS SELECT au_id, au_lname FROM authors GO -- SQL Server 2005 create schema with name CREATE SCHEMA fredstuff AUTHORIZATION fred This pre–SQL Server 2005 CREATE SCHEMA statement actually was a convenient way to create objects that belonged to a specific user (like fred, in this case) and grant permissions to them in a single DDL statement batch. The problem of having database objects tied to a particular user was that in order to drop the user, the database administrator had to reassign or drop and re-create all of that user's database objects. SQL Server 2005 introduces the concept of named schemas as separate from users. When you use the new CREATE USER DDL to statement to create a user, you can assign a default schema for that user. If a default schema is not assigned, the DBO (database owner) schema is the default. -- user's default schema is uschema CREATE USER u1 FOR USER u1WITH, DEFAULT_SCHEMA = 'uschema' go -- user's default schema is dbo CREATE USER u2 FOR LOGIN u2 go A schema can be owned not only by a specific user (created with a SQL Server login or Windows login), but also by a Windows group, a database role, or an application role defined in that database. The new CREATE APPLICATION ROLE DDLstatement permits assignment of a default schema, but because many users can be assigned to a role (an ordinary role, not an application role), CREATE ROLE does not assign a default schema for the role. Note that the legacy procedures sp_adduser and sp_addapprole have been changed to first create a schema with the same name of the user or application role and then call the appropriate CREATE statement, specifying that schema as the default schema. Use of the new CREATE statements is preferred; the behavior of the stored procedures is kept only for backward compatibility. The owner of a schema (a single user or multiple users) can create database objects within that schema and also grant schema-level privileges to others. The schema owner does have to be granted permission to create the database objects, but the grant permission exists on a database level, not on a schema level. Here's an example of a user that has an associated schema and is also the owner of that schema. USE demo1 GO CREATE LOGIN alogin1 WITH password = 'password1', DEFAULT_DATABASE = demo1 GO -- default named schema CREATE USER auser1 FOR LOGIN alogin1 WITH DEFAULT_SCHEMA = aschema1 GO CREATE SCHEMA aschema1 AUTHORIZATION auser1 GO GRANT CREATE TABLE TO auser1 GO SETUSER 'auser1' GO -- this works and creates aschema1.table1 CREATE TABLE table1 (theid INTEGER) go In this case, if we did not set a default schema for the auser1 user, his default schema would be dbo. Because auser1 is not a member of the dbo database role, the CREATE TABLE statement would fail. What this means to the database administrator is that because schemas (and the objects they contain) can be owned by a role, an application role, or a Windows group, when a user is dropped from the database, the database objects she has have created do not have to be reassigned or dropped and re-created. Here's an example using a SQL Server role for a payroll system. We'll assume that a role called payroll has already been created. USE payrolldb GO CREATE LOGIN janet WITH PASSWORD = 'temppwd', DEFAULT_DATABASE = payrolldb GO — default named schema CREATE USER janet FOR LOGIN janet WITH DEFAULT_SCHEMA = prschema GO CREATE ROLE payroll -- if it does not exist GO sp_addrolemember 'payroll', 'janet' CREATE SCHEMA prschema AUTHORIZATION payroll GO GRANT CREATE TABLE TO janet GO Now, user janet can create tables, and they will be contained within the prschema schema. If Janet is reassigned, the user janet can be dropped from the database without affecting any of the tables she has created. Having named schemas affects the way database object names are resolved. If Janet issues the SQL statement SELECT * FROM benefits, SQL Server will attempt to resolve the table name benefits in this order: 1. prschema.benefits (using the default schema) One further special case needs to be mentioned. It is possible that a database user will have a default schema that she does not own (such as dbo), but will have the ability to create database objects in a different schema. In that case, the database object in the CREATE DDL statement must explicitly use the two-part name. For example, if user janet was defined without a default schema keyword, her default schema would be dbo, since she is not a member of the dbo role. — this statement would fail CREATE TABLE benefits2003 (empid INT) — other columns elided — this statement would succeed CREATE TABLE prschema.benefits2003 (empid INT) Schemas have their own sets of permissions. You can grant or deny permissions like SELECT, EXECUTE, or VIEW DEFINITION on a schema-wide basis. The following SQL statement prohibits the group public from seeing any database objects in the bob schema using the system views. DENY VIEW DEFINITION ON schema::bob TO public Synonyms SQL Server 2005 introduces support for a database object known as a synonym. A synonym is just an alternate name for an existing database object that keeps a database user (more likely, a database programmer) from having to use a multipart name for an object. Synonyms can be defined on a two-part, three-part, or four-part SQL Server object name. A synonym can be defined by the following database objects:
Although synonyms can be created on a multipart object name, they are scoped to the database that they are created in. Here are some examples of creating and using synonyms. USE pubs GO CREATE SYNONYM customers_east FOR eastserver.northwind.dbo.customers GO CREATE SYNONYM employees FOR payroll.employees GO -- these work SELECT * FROM customers_east SELECT * FROM employees GO USE northwind GO -- so does this SELECT * FROM pubs..customers_east Click for the next excerpt in this series: Specifying execution context for procedural code Click for the book excerpt series or visit here to obtain the complete book.
|
|
|
|
|||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||
The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005 for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete 
|
|
 |  |  | |  | |  | |  | |  | |  | |  | |  | |
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
