The relationship between user and schema in the database

If we want to know what the user and schema are like in the database, let's first look at what the user and schema are in the database.        in SQL Server2000, there is always a hidden relationship between user and schema due to the architectural reason. It is very rare to realize that the user and schema are two completely different concepts, but in SQL Server2005 the architecture is broken, and the user and schema are separated.              first I'll do a metaphor, what is database, what is schema, What is a table, what is column, what is row, what is user? We can think of database as a large warehouse, the warehouse has a lot of rooms, the schema is one of the rooms, a schema represents a room, table can be considered as a bed in each schema, table (bed) is placed in each room, Cannot be placed outside the room, that is not the night sleep homeless J. , and then the bed can be placed a lot of items, just like the table can be placed a lot of columns and rows, the database is stored in the basic unit of data is a table, in reality each warehouse placed the basic unit of the item is the bed, user is the owner of each schema, " So the schema contains the object, not the user ", in fact, the user is corresponding to the database (that is, the user is the owner of each corresponding database), since there is the right to operate the database (warehouse), there must be operational database of each schema (room) right, That is, each database mapping user has a key to each schema (room), in other words, if he is the owner of a warehouse, then the use of the warehouse and everything in the warehouse is his (including the room), he has the full operation right, can throw away the unused things from each room, You can also put some useful things into a room, and of course you can remove a room (remove Schema). Oh, and the reality is too similar to it. I (the Administrator of the warehouse) can also assign specific permissions to the user, that is, what he can do in a room, can only see (read-only), or can be like the owner has all the control (r/w), this depends on the role of the user corresponding roles, As to the question of assigning permissions, I will leave it in detail in a separate blog later. The analogy here, I believe everyone is clear. OK, weTopic continues!        in SQL Server2000, if we create a user Bosco in a database, press the background to create the default schema "Bosco" for us by default. The name of the schema is the same as the name of user, which is why we don't know the user or schema.        in SQL Server2005, for backwards compatibility, when you create a user with a sp_adduser stored procedure, the SQL SERVER2005 also created a schema with the same user name, but this stored procedure is reserved for backwards compatibility, and we should become familiar with the new DDL language create user and create schema to manipulate the database. In SQL Server2005, when creating a database user with create user, we can specify an existing schema as the default schema for that user, and if we do not specify it, the default schema for that user is the dbo schema,dbo The room (schema) is like a large public room, if you do some operations in a large warehouse, such as Create Tabe, if you do not specify a specific room (schema), if you do not have a default schema for the currently logged-on user. Then your belongings will have to be put into the public dbo room (SCHEMA). However, if the current logged on user has a default schema, then everything is done on the default schema (for example, the current logged on user is login1, the default schema for the user is login1, Then all of the operations are done on this login1 default schema. The experiment has proved to be true). Guess you're going to be a little dizzy at this point, why? I just said that dbo is a schema, but you can see in the database, dbo is also a user, dizzy it, hehe.        when creating a database in SQL Server2005, some default schemas are created, and the schema created by default is: Dbo,information_ Schema, Guest,sys, etc. (there are some character schemas, not mentioning, and dizzy).        I mentioned earlier, in SQL Server2005, when using a storedProcess Sp_adduser Create a user, and SQL Server2005 also created a default and user name of the same schema, this time the problem comes out when we create table A, if there is no specific schema prefix, Which schema was created on this a-table and which room was entered? The answer is:

1. If the user of the current operational database (which can be identified with select Current_User) has a default schema (specified when the user is created), then table A is created on the default schema.
2. If the user of the current operations database does not have a default schema (that is, it is empty by default when creating user), but there is a schema with the same name as the user name, table A is created on the DBO schema even if there is a schema with the same name as the user name. Since it is not the default schema for the user, the creation of the table is not considered, as a general schema to deal with, although the name is the same, but there is no relationship oh.
3. If you specify a specific schema to prefix when creating table A, then table A is created on the specified schema (is there permission?). ）

Now the problem is out, in the current operation of the database user (with select Current_User can see, re-emphasize) without the default Schema, when we use the CREATE TABLE a statement, a will look for the DBO schema, And try to create it on the DBO schema, but what if the user who creates the a table has only read-only permission to the DBO schema, without the Write permission? At this time the A-table is neither established nor successful, this is the relationship between Login,user, role and schema that I will refer to later. Here, in order to avoid confusing and increasing the speed of manipulating the database (in a small amount of data, it is almost impossible for us to see the difference for our naked eye), it is best to explicitly specify a particular schema prefix every time you manipulate a database object.

Now if the logged-on user is Sue, the user has a default Schema also for Sue, so if there is now a query statement for SELECT * FROM MyTable, what is the order of the search for each room (Schema)? The order is as follows:

1. First search sys.mytable (sys Schema)
2. Then search for sue.mytable (Default Schema)
3. Last Search dbo.mytable (dbo Schema)

Order of execution since everyone is clear, then in the Query database table data, it is better to specify a specific schema prefix, this way, the database does not have to scan the SYS schema, of course, can improve the speed of the query.

It is also necessary to note that, after each database is created, there are 4 schemas that are necessary (delete is not removed), the 4 schemas are: Dbo,guest,sys and Information_schema, the rest of the schema can be deleted.

It says in the book "MySQL Must Know":

"Tables have features that define how data is stored in a table, such as what data can be stored, how data is decomposed, how parts of information are named, and so on. This set of information describing a table is called a pattern, which can be used to describe a particular table in a database and the entire database (and its relationships with tables).

Schema (schema) information about the layout and characteristics of databases and tables. ”

Excerpt from: http://blog.csdn.net/yanjiangbo/article/details/1782576

http://blog.csdn.net/rui_dlut/article/details/6737046

Relationship of user and schema in database