Comparison between SQL Server and Oracle: permission management (1)

Document directory

• Shcema and tablespace
• Grant System Permissions
• Create a role
• Object privileges)
• Permission granted

We found that passwords are everywhere in our lives. You need to remember various passwords, such as bank cards, emails, QQ, Weibo, games, and various website Members.

The use of the database is no exception. The user name and password must be used before the data can be logged in. although you do not need to enter the password for Windows verification, it is actually the username and password you need to log on to windwos.

Oracle permission Management User Creation

 

Create user arwenidentifiedby
ABC; -- creates a user whose username is Arwen and whose password is ABC.

If you want to change the password

Alter
Userarwenidentifiedby
ABC123; -- change the password to ABC123

 

Shcema and tablespace

Of course, you must use a user with more permissions to perform the above operations. in the beginning, sys or system users are used to create new users. when creating a new user, a schema is created by default, which is equivalent to dividing a space for you in the tablespace. shcema is equivalent to a space. however, because the user and Schema are one-to-one and completely bound together, they can also be completely equivalent. the table space is not specified when the user is created. The system will handle this situation. when a user is created without a specified tablespace, a default tablespace is provided, which is generally a system tablespace. however, you can use the following statement to modify

Alter databasedefaulttablespaceusers;

In this way, the default allocated tablespace of the user Arwen created earlier is users. Of course, you can also explicitly specify the tablespace of Arwen during creation.

Create user arwenidentifiedby
Abc123defaulttablespacesystem;

Alternatively, you can explicitly change the tablespace after creating the table.

Alter user arwendefaulttablespace
MySpace;

 

After the above user is created, you can see that it cannot be accessed. this is unreasonable. I have never encountered a user that cannot be logged on after it is successfully created. generally, we can log in directly. At most, when we log on for the first time, we are forced to change the password first. at the beginning, you may wonder if the newly created user is locked. after oracle is installed, some users such as Scott are locked by default. you can unlock it like this.

Alter
User
Scott accountunlock;

If you want to lock Scott, it will naturally be alter user.
Scott accountlock;

However, the new user does not belong to this situation or has no permission. only connecting to the database requires a separate permission. (This permission is not available in SQL Server, and users can directly connect to the database after being created. I will discuss why Oracle needs to grant this permission separately)

 

Grant System Permissions

To connect to the database, you must have the create session permission. Each connection in Oracle is called a session. Use the following statement to grant permissions.

Grant createsessiontoarwen;

 

However, you can log on to the console, but you can find that there is nothing in it, and you can't do anything. You can't find other users' tables or create tables by yourself. of course, you can still see some confidential information that does not exist, such

Select * from
V $ version; -- View version information

If you want to do anything else, you must continue to grant permissions. so there is almost no such operation in Oracle. you must have permissions one by one before you can proceed. the permissions in Oracle are very detailed. although this is quite troublesome, it greatly guarantees security.

 

There are two methods to grant permissions

1. Grant the sub-division permissions to a user using the grant statement, as shown above.

Obviously, if you want to grant users hundreds of permissions and create hundreds of users at the same time, you have to make your hands soft one by one, so there is another method.

2. Create a role and grant all permissions to the role. Then, you can grant the role to the new user when creating the user. In this way, the new user has permissions for all roles.

In fact, this is a bit like inheritance in object-oriented systems. When a subclass inherits the parent class, it inherits the property of the parent class. A role is like a parent class, and a user is like a subclass.

 

Create a role

Create role father;

Grant create table to father;

Grant father to Arwen;

Create a role father first, grant the role table creation permission, and then assign the role to Arwen. at this time, Arwen also has the table creation permission. we know that the parent class can inherit the parent class in the object-oriented system. in this case, the role can also be assigned a role. For example, the Oracle database has a role resource by default.

Grant resource to father;

 

 

Object privileges)

Most of the time, we refer to system permissions, which generally refer to a wide range. for example, you have the permission to create and query tables. the object permission is detailed in scope. for example, you have operation permissions on an object (a specific table or view ).

For example, if the TMP table exists, grant select on TMP to Arwen. This indicates that Arwen has the permission to access table TMP, but has no permission to access any other table.

 

Permission granted

Cascade system permissions (with admin option)

After reading so many ways of granting permissions, you may ask if super users like sys are required to grant permissions. so tired. naturally not. you can also grant permissions to other users. example

Grant create table to Arwen with admin option through sys user first; -- add with admin option to indicate that Arwen can also grant the table creation permission to other users. this is a transfer-based authorization method. in addition, if Arwen uses grant create table to weiwen to grant weiwen the permission to create a table, Arwen's table creation permission will be revoked. weiwen's table creation permission is still in

 

With grant option)

In addition, grant select on EMP to Arwen with grant option; -- As shown above, Arwen can also grant TMP permission to other users.

Arwen uses grant select on EMP to weiwen with grant option; -- unlike the system permission, if Arwen's permission is revoked, weiwen's query table permission is also lost.

If weiwen again uses grant select on EMP to test; -- grant permissions to test. when either Arwen or weiwen is revoked, it is also canceled. just like an inheritance level. the preceding permissions are canceled, and the subsequent permissions are not.

 

Revoke permissions

We know that permissions granted can be recycled,

Grant permissions......

Recycling is revoke... from...

For example, revoke create table from Arwen;

 

 

Why is the create session permission available?

This permission is controversial. this permission is also acceptable, and it is easy to mislead people. for example, it is not common sense that a user cannot log on after being created. this permission is not available in SQL Server. what is the role of Oracle in the end?

I think a very important role of the create session is to play the lock role. for example, we want to lock a user in some situations. it seems that the user account is abnormal or the user is temporarily locked because an employee leaves the company. after you revoke the create session permission, you cannot connect. It works exactly the same as lock. in addition, you can revoke the create session permission and lock permission at the same time. double insurance.

In addition, if you want to lock a user, you can only recycle the create session if you do not lock it.