SQL Server vs. Oracle Learning: Rights Management (ii) some interesting metaphors

http://blog.csdn.net/weiwenhp/article/details/8094739

Directory (?) [-]

1. SQL Server Rights Management
2. The difference between login and user
1. Role roles
2. granting permissions
3. Compare Oracle vs. SQL Server with a metaphor
1. Oracle's World
2. The world of SQL Server

SQL Server Rights Management

The difference between login and user

I'm used to it. Oracle has just started to play SQL Server is a bit confused by the concept of login and user. Because there is no such distinction in Oracle. Just one user logged in, And then you create the table and so on the object is saved under the user. Other people want to access the table under your user needs additional permissions, you want to access other users of the table below also need additional permissions. So everything is clear, well-organized and at a glance. Of course it's easy for your users to understand, It's cool. But it also becomes inflexible in the operation of data rights control. For example, you need access to some other user's table permissions, only one table to assign permissions, if you have 100 tables you have to use 100 SQL to give permission. and SQL There are two concepts in server that are completely detached from the user and schema, and you can put those tables under a schema and then let a user have access to the schema.

When you start with Oracle, you must first create a user. And then you can log in. results run SQL Server inside a look, you simply can't create a user and then give a password. Because SQL In fact, the server can only log in with login. And the single login can not be used, you go in the inside is empty, nothing.

Database is the big yard, the schema is one of the small rooms. Login can have different big yard keys, user has different small room keys

We can map multiple database to it when we create login. A database is equivalent to a large courtyard (I would have liked to use a warehouse analogy, but see other people to describe, also copied to use), you create login when mapping a lot of databse, The equivalent of giving you the keys to many of the yard gates. You have nothing to do. You can open the door to look down. But the property (data) is kept in the courtyard room, you do not have the key to the room. You are the one who looks at the gate. Of course, some schemas (rooms), if owner is indicated as public, That is, public property. The house is not locked, so you can go in and get it. Only the legendary Communist Society of Communism will be the property of the public. It's not a good thing in normal circumstances. So most of the time the room is locked.

With all this crap, you might think, how do I know the lock in the room? You need to create a user, and the user here doesn't need you to create the password again. Then specify the user's owned Shcemas. A key that is equivalent to giving the user one or several rooms. You also specify which login the user belongs to when creating the user. So login is a one-to-many relationship with user, A login can have a lot of user. Make an inappropriate analogy. Login is like a man, and user is like a woman. A man can have a lot of wives. Some countries are like this. If it is China, you can make mistress three milk. If a login corresponds to more than one user, Those user cannot be under the same databse, that is, cannot be in the same yard. For example, you want to find Mistress words also dare not blatant let them live together. Must Jinwucangjiao, hide far point, put another big yard to go, actually also can the big yard as big villa.

And most of the time it's a man's decision. So log in to the database with login. Of course, I said the analogy is not necessarily appropriate, you may feel that in fact can also be a female is login, male is user. A woman who is merry enough to make a lot of men. Hey, it's a metaphor anyway.

Idle chatter so much, this will wait for a bit of practical, see specific how to operate.

Create login boy with password = ' sb123 ' use mydb; --Create a login name boy, and then assign a databse mydb to him. Equivalent to giving him the key to the big yard mydb.

So how do you create the user next?

Log in to database MyDB first, then create user girl for login boy.

However, it is troublesome to specify some schema for user. Or simply use the tools of Management Studio, the graphical interface.

Database is MyDB (this is my own creation of the mydb, you can also specify the other). Then you need to find the database from the graphical interface, and then you will see the Users option under the tree structure. You right click on the new User. You will find that when you create a login and specify a database, the system creates a user with the same name by default, you right-click the user Boy, and then you view the properties. In the owned. Schemas options you want to give the schemas to this user (give a lot of room keys to the user)

Around such a big circle, and finally put a user to build Ah. Trouble is trouble. But that would be more of a global fine-grained authority management.

Roles (role)

We know the concept of roles in Oracle. There are also in SQL Server. And the usage is not very big, not the above create user difference.

When you create a user in SQL Server, you specify the schema that it owns, and it is cumbersome to specify it. You can create a role first, Specifies which schema this role has. Then create the user and assign the roles to the user. Of course, now I'm talking about Database,schema. The concept of the right to use the storage space for such logic. Actually, there are other permissions on objects such as tables that have not been addressed.

granting permissions

For example, the database mydb below the user girl to give the permission to build the table. One thing to note here is that Different databases The following user is a different concept. If there is a user named Girl under the MyDB. There is a user named Girl under TestDB. They all correspond to login boy. So the user under the different database Girl permissions are not affected by the additional user girl.

So first login to database MyDB. Then grant create table to girl; --ok, gives the user girl to give good authority.

Compare Oracle vs. SQL Server with a metaphor

In fact, to distinguish between Oracle and SQL Server on database and schema, login and user such confusion concept. Let's make some metaphors.

Oracle's World

Can be understood in this way. After you have installed a server in Oracle, you have only one database, which is the equivalent of a large yard, and then there are a lot of small rooms in the yard, which is the schema. Each person (user) can have only one room (schema). One by one corresponding, If the man dies, his room will be torn down. Since the database was just built with only the default Sys,scott,system and so on a few few users, so the yard (database) only sys,scott,system These rooms, the other is open space. When you create a user , if the user Arwen, it is built in the courtyard of a room, the name of the room is called Arwen. Keep building new users will not stop building rooms. If the user Arwen comrade because of what accidental heroic sacrifice, such as walking coconut tree under the fall of a coconut killed. (This kind of death is a bit of a death injustice Ah.), the database will delete the user Arwen, and then the big yard called Arwen's Room (schema) has also been demolished. All the belongings in the room were burnt down.

The world of SQL Server

In fact, after the installation of a SQL Server, you can complete a lot of databse inside, and it is so blatant called databse, there are many. Not like Oracle. (Of course, the database is actually just a logical concept.) is physically a few data files. A database in Oracle can have many data files as well. If the SQL There's only one database in the server that looks like Oracle. Database is a large yard, but you can build many rooms first (schema) without user, and then when you create a user Arwen, Can be divided into one or a few rooms to Arwen. If Arwen although every day in soy sauce but very will kiss up, very much appreciated by the leadership. He was promoted. Then you can give the Arwen a few more rooms (schema). If one day comrade Arwen was hung up by accident. This time if the egg ran the roof to play, the result of a slip fell down into a meat sauce. As a result many people think that comrade Arwen work too hard, too much pressure, Bothered killed himself. So mourned and sighed for a while. The leaders deleted Arwen's name. But his room was still there, The relics are still there. After a few days, my friend called Weiwen. and Arwen's class. The leaders then gave Weiwen a few points in the room that they had arwen before. Of course, the relics in it are still there.

And no matter Arwen or Weiwen are just small ro-ro, dozen soy sauce. When you are a listener, give the leaders a big hand. What you mean is that you can only be led to represent. User can only use a large yard (small room in database), And the big leaders can have different rooms in the courtyard. If there is a leader (login) named SB, he has two staff SS and BB. These two staff are in different places. If the SS in the compound sub-db1, there is a room, and BB in another large yard db2. There are two rooms. So lead SB to have three rooms of ownership. Because his staff's property is his. Can be recycled at any time.

So we found that Oracle's world was fairer, and everyone was in a room. You're dead, and you've got your room with you. Although a waste of some, but more human ah. Not so fair and impersonal in SQL Server.

SQL Server vs. Oracle Learning: Rights Management (b) some interesting metaphors