Understanding of schemas in SQL SERVER 2005/2008 (ii)

The understanding of schemas in SQL SERVER 2005/2008 is presented in this article (i)

The role of architecture and examples

By separating the user from the schema, the objects in the database are no longer tied to a user account, and you can solve the "user leaving the company" issue in SQL SERVER 2000 and previous versions, that is, when the user who owns the object leaves the company, or leaves the job, It doesn't have to be a big hassle to change all of the user's objects to belong to the new user. In addition, it allows DBAs to install a package software, the database objects used to set up the package software belong to a specific schema, easy to distinguish. In other words, within a single database, objects of different departments or purposes can differentiate between different object naming principles and permissions through the schema.

In SQL Server 2005/2008, schemas exist independently of the database user who created them. You can transfer ownership of a schema without changing the schema name. You can also create an object with a user-friendly name in the schema that explicitly indicates the functionality of the object. For example, you can create a schema named Cus.app.manifest.customEntry, in addition to Cus.app.entry.customEntry. Because "manifest" is not a user, you do not have to change this name after you remove the user from the database. This simplifies the work of database administrators and developers.

SQL Server 2005/2008 also introduces the concept of "default schema", which resolves the names of objects that are not referenced with their fully qualified names. In SQL Server 2000, you first check the schema that is owned by the calling database user, and then the schema owned by the DBO. In SQL Server 2005/2008, each user has a default schema that specifies the first schema that the server will search for when resolving the name of the object. You can use the Default_schema option of the CREATE user and alter user to set and change the default schema. If Default_schema is not defined, the database user will use DBO as its default schema.

The following diagram showing the SQL Server permissions hierarchy may give us an intuitive understanding of:

SQL Server 2005/2008 Database Engine manages a hierarchical collection of entities that can be protected by permissions. These entities are called "securable objects." In securable objects, the most prominent are servers and databases, but discrete permissions can be set at a finer level. SQL Server controls the actions that the principal performs on securable objects by verifying that the principal has the appropriate permissions.

Security object relationships such as:

Here's a concrete example of how the architecture works.

--command to manipulate the schema use mastergosetusergo--to create a test database creation Schtestgo Create login df with password= ' sj1234 ', default_ Database=schtestcreate login Xhl with password= ' sj1245 ', Default_database=schtestgouse Schtestgo-- No schema was specified when creating two users create user DF for login DF Create user XHL for login xhl--This table does not specify which schema belongs to the default dbo schema gocreate table TB1 (name varchar (8), sex char (2)) go--This table belongs to the SCH schema create schema schgocreate table SCH.TB2 (name varchar (8), sex char (2), age int) go-- Gives schtest this user permission to query objects in the Sche schema. Grant SELECT on Schema::sch to DF Gosetuser ' DF '--Switch User DF Select * FROM tb2--the report "Invalid object name" is because there is no schema specified for TB2, the system defaults to DBO, and our TB2 Belongs to the SCH schema. --With the schema name, you can query the goselect * from Sch.tb2gosetuser--switch to SA---switch to xhlsetuser ' XHL '--cannot be queried because there is no permission select * from SCH.TB2 goset User--Switch sa--to DF user Assign default schema alter user DF with Default_schema=schgosetuser ' DF '--switch df--you do not need to specify SCH at this time, if there are other objects in the schema, You can also query the select * from TB2 gosetuser--Switch sa--Create a third Test table, the same is the SCH schema under CREATE TABLE SCH.TB3 (ID int,uname varchar (8)) go--Switch User Dfsetu Ser ' DF '---can be queried for select * from TB3go---But unable to insert the data because there is no insert permission insert into TB3 values (1, ' ABCDE ')--Deny INSERT permission gosetuser--assign insertion permission on the grant insert on Schema::sch to df--Switch User dfsetuser ' DF ' Go---data can be inserted insert into TB3 values (1, ' ABCDE ')--ok! ---query result select * FROM Tb3gogrant alter on Schema::sch to DF--enables schtest this user to have the ability to change all architectures. --Error--cannot grant, deny, or revoke permissions to the SA, dbo, entity owner, INFORMATION_SCHEMA, SYS, or yourself. Gouse Master gogrant control server to DF--enables schtest this user to control the server. --Error--cannot grant, deny, or revoke permissions to the SA, dbo, entity owner, INFORMATION_SCHEMA, SYS, or yourself. Setusergouse schtestgo--Create Schema sch1create schema Sch1 go--Modify the schema of the Object TB2 table schema transferred from Sch to Sch1alter schema sch1 transfer sch.tb2go--Create a new user, specifying the default schema, default to dbo Create login yhy with password= ' sj1234 ', default_database=mastergouse Schtestgocreate user yhy for login yhy with Default_schema=sch--belongs to sch--switch user yhysetuser ' yhy '--query table, object name ' TB2 ' is invalid. SELECT * FROM tb2gosetuser--assign schema permission grant Select on Schema::sch1 to yhy--switch user yhysetuser ' yhy ' goselect * from TB2---or invalid because Not the same schemagosetusergoalter user yhy with Default_schema=sch1--Change yhyDefault schema go--because Yhy is not the current user, you cannot query select * FROM TB2-show current user goselect usergo--switch User yhysetuser ' yhy '-you can query now, if there are other objects in the schema, You can also query Goselect * from TB2-show current user select usergo--Switch user setuser--Switch user to Dfsetuser ' DF '-this time because the TB2 architecture is changed from Sch to Sch1, So DF can not query TB2 go--query Error SELECT * FROM TB2--Display the current user Select User Gosetusergouse mastergo---Delete users drop user dfdrop username Xhldrop u   Ser yhy---Delete logins drop login dfdrop login yhydrop login xhl--Delete database drop db schtest

Understanding of schemas in SQL SERVER 2005/2008 (ii)