Comparison between SQL Server and Oracle: permission management (2) some interesting metaphors

Document directory

• SQL Server permission management
• Role)
• Grant Permissions
• Oracle world
• The world of SQL Server

SQL Server permission management

 

Differences between login and user

I am used to the concept of login and user when I first started playing SQL Server. this is because there is no such distinction in Oracle. A user is logged in, and the tables and other objects created by the user are saved under the user. other users need additional permissions to access the tables under your users. You need additional permissions to access the tables under other users. so everything is clear and clear. of course, it's easy for you to understand it. however, it will become inflexible in data permission control operations. for example, if you want to access a part of a table of another user, you can only grant permissions to one table. If you have one hundred tables, you must use 100 SQL statements to grant permissions. SQL
Because the user and Schema are completely separated from each other in the server, you can put those tables under a schema so that a user can access the schema.

When you use Oracle, you must first create a user. then you can log on. run SQL Server and you will find that you cannot create a user and then give a password. because login can only be used to log on to SQL Server. and the separate login cannot be used. It is empty when you log in.

 

The database is a large courtyard, and the schema is a small room. login can have different keys for the large courtyard, and the user has different keys for the small room.

When creating a login, we can map multiple databases to it. A database is equivalent to a large courtyard (I would have liked to use a warehouse as a metaphor, but I can see other people describing it like this and using it as a copy). When you create a login, you map a lot of Databse, it is equivalent to giving you a lot of keys to the yard door. when you are idle, you can open the door one by one. however, the property (data) is stored in the courtyard room, and you do not have the room key. you look at the door. of course, some Schema (room), if the owner specifies to be public, that is, public property. this kind of house is not locked. You can just go in and get it. only the legendary communist society can make property public. this is not a good situation. so most of the time the room is locked.

With such a pile of nonsense, you may wonder how can I know the lock in the room? You need to create a user. The user here does not need to create a password. specify the owned shcemas of the user. it is equivalent to giving the user a key for one or several rooms. when creating a user, you must specify the login to which the user belongs. therefore, login has a one-to-many relationship with users. A login can have many users. for example. login is like a man, while user is like a woman. A man can have many wives. this is true in some countries. if you are in China, you will be able to get two milk and three milk. of course, if a login corresponds to multiple users, those users cannot be in the same database, that is, they cannot be in the same yard. for example, if you are looking for a second milk, you don't dare to let them live together. you must be sure to stay away from Tibet and put it in another big courtyard. In fact, you can also look at the big courtyard as a big villa.

Most of the time, they are men. therefore, you can use login to log on to the database. of course, the metaphor I mentioned is not necessarily appropriate. You may think that it can also be a female, a male, or a user. if a girl is romantic enough, she can have a lot of male. well, it's just a metaphor.

So much idle, this will wait for the actual point, depending on the specific operation.

Create login boy with Password = 'sb123' use mydb; -- create a login name Boy and specify a Databse mydb to give it to him. It is equivalent to giving the key of mydb to the big yard.

Then how to create a user?

First log on to the database mydb, and then create user girl for login boy.

However, it is quite troublesome to specify some schemas for the user. simply use the graphical interface tool management studio.

Database is mydb (this is mydb created by myself, you can also specify other ). then you need to find the database on the graphic interface, and then you will see the users option under the tree structure. right-click and select new user. you will find that the system will create a user with the same name by default when creating login and specifying a database. You right-click User boy and view properties. in the owned schemas option, you want to give the schemas to the user (give a lot of room keys to the user)

 

After such a big circle, I finally built a user. Although the trouble was troublesome, it would be a bit of a global fine-grained permission management.

 

Role)

We know that Oracle has the role concept. SQL server also has the role concept, and the usage is not much different, not as big as the difference between the above User Creation.

In SQL server, it is difficult to specify the schema you own when creating a user. you can create a role to specify the schema of the role. then assign the role to the user when creating the user. of course, what I am talking about now is the concept of the right to use a logical bucket such as database and schema. in fact, the Operation permissions for other objects such as tables have not been discussed yet.

 

Grant Permissions

For example, you need to grant the table creation permission to the user girl under database mydb. note that users in different databases are different. assume that there is a user named girl under mydb. there is a user under testdb called girl. they all correspond to login boy. granting permissions to user girls under different databases does not affect other user girls.

So first log on to the database mydb. Then grant create table to girl; -- OK, and grant the user girl the permission.

 

Comparison between Oracle and SQL Server

In fact, we need to distinguish between Oracle and SQL Server the concept of database and schema, and the concept of confusion between login and user. Let's look at a few metaphors.

Oracle world

It can be understood in this way. after you have installed a server in Oracle, there is only one database, which is equivalent to a large courtyard, and there are many small rooms in the yard, that is, schema. each user can have only one schema ). if the man died, his room would be torn down. since only a few users, such as sys, Scott, and system, are allowed to create databases by default, there are only sys, Scott, and system rooms in the yard (database), while the others are open spaces. after you create a user, if it is the user Arwen, you can create a room in the yard and the name of the room is Arwen. users can create new rooms without interruption. let's assume that the user's comrade Arwen was killed in an unexpected event. For example, the user was killed by a fallen coconut under a coconut tree. (In this case, it's a bit embarrassing to die .), then the user Arwen is deleted from the database, and the schema of the big courtyard is also split. all the things in the room are burned.

 

The world of SQL Server

After an SQL Server is installed, you can set up multiple Databse in it, and it is clearly called Databse, there are many. unlike Oracle. (Of course, database is actually just a logical concept. there are several data files physically. for example, a database in Oracle can have many data files ). if there is only one database in SQL Server, it is similar to Oracle. database is a large courtyard, but when there is no user, you can also create many rooms (schema) first, and then when you create a user, If you create a user Arwen, one or several rooms can be allocated to Arwen. if Arwen is playing soy sauce every day, it will be very flattering and appreciated by the leaders. so I was promoted to the official website. then you can allocate several more rooms (schema) to Arwen.
Let's say Comrade Arwen plug-in again one day. this time, if I ran to the top of the building to play, I fell into the meat sauce. as a result, many people think that Comrade Arwen is too hard-working and under too much pressure to commit suicide. so I sighed for a while. the leaders deleted the Arwen name. but his room is still there and his relics are still there. A few days later, weiwen, buddy. and takes the course of Arwen. as a result, the leaders distributed a few of the previous Arwen rooms to weiwen. of course, the relics are still there.

Regardless of Arwen or weiwen, it is just a little Luo, soy sauce. when the meeting was held, the audience only applauded the leaders. in general, it can only be represented by leaders. the user can only use one large courtyard (a small room in the database), and the leaders can have different rooms in the courtyard. assume that a leader named Sb has two employees, SS and BB. the two employees are in different places. assume that the SS has a room in db1, while BB is in db2. there are two rooms. the leader Sb has three rooms for ownership. because the property of his staff is his. it can be recycled at any time.

 

 

As a result, we found that the world of Oracle is fair. Everyone has a room. you are dead, and you are buried with your room. although it is a waste of time, it is quite human. SQL Server is not so fair and humane. when you get down to the room, someone else will take it for use.