SQL Server vs. Oracle Learning: Rights Management (i)

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

We find that our lives are all about passwords, and you have to remember all kinds of passwords. such as bank cards, mail, QQ, Weibo, games, various website members.

The use of the database is no exception, the entire user name and password to log in to use the data ah. Although there is also what Windows authentication does not need you to lose the password, but that is actually required you login WINDWOS user name and password.

Oracle Rights Management Create users

Create user Arwenidentifiedby ABC; --created a user named Arwen password for ABC

If you want to change the code,

Alter Userarwenidentifiedby abc123;--change the password to abc123

Shcema and table Space

Of course, do the above operation you generally need to use a more privileged users to do. It is generally just beginning with SYS or the system user to create some new users. When creating a new user, a schema is created by default, It's equivalent to giving you a space in a tablespace. Shcema is equivalent to a piece of space. But because the user and schema are one by one correspondence and completely bound together do not separate, it can also be completely equal. No tablespace was specified when the user was created. This is handled by the system. When a user is found to be created without specifying a tablespace, it provides a default tablespace, which is typically the system table space. But you can also modify the following statement

Alter Databasedefaulttablespaceusers;

The default allocated tablespace for the user Arwen created earlier will be users. Of course you can also create a tablespace that explicitly specifies Arwen

Create user Arwenidentifiedby Abc123defaulttablespacesystem;

or explicitly change the tablespace after it's created

Alter User Arwendefaulttablespace MySpace;

When the above user is built, you can see that it is not possible to log on. It's not quite common sense. It's not like I've ever seen one before. The user is not able to log in after successful creation. We are generally able to go directly into the Most of the time when the first login was forced to change the password. So you're just starting to wonder if the newly created user is locked. As soon as you install Oracle, some users, such as Scott, are locked. You can unlock it like this.

Alter user Scott Accountunlock;

If you want to lock Scott, it's natural that alter user Scott Accountlock;

However, the new user at this time is not in this case, or is not a permission. Just connecting to the database requires a separate permission. (There is no such permission in SQL Server, and the user is able to connect directly to the database.) and then we'll discuss why Oracle wants to do this alone.

Granting system permissions

To connect to a database, you have the Create session permission. Each connection in Oracle is called a session. Assign permissions with the following statement

Grant Createsessiontoarwen;

But you can log in at this time, but log in to find nothing in it, you can not do anything, you can not find other users of the table or create a table. Of course, there's no such thing as confidential information.

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

Well, if you want to do something else, you have to continue to be empowered. So there's almost nothing in Oracle that you can do. You have to have permission to do it. The partitioning of permissions in Oracle is very fine. Although this is cumbersome, it greatly guarantees security.

There are generally two ways to give permissions

1. Use the GRANT statement to assign a segmented permission to a user, as above.

Obviously if you want to give users hundreds of permissions, but also to create hundreds of users, you knock each other to knock your hands weak, so there is another way

2. Create a role and assign permissions to the role. Then you can assign the role to him when you create the user. This allows the new user to have permissions for all roles.

In fact, this is a bit like object-oriented inheritance, the subclass inherits the parent class inherits the property of the parent class. The role is like a parent class, and the user is like a subclass.

Create a role

Create role father;

Grant CREATE table to father;

Grant father to Arwen;

First create a role father, then give the role to set the table permissions, and then assign the role to the user Arwen. At this point, Arwen also has the permissions to build the table. We know that the parent class in object-oriented can inherit the parent class. Then the character can be given a role, For example, the default role in Oracle is resource

Grant resource to Father;

Assign object permissions (object privileges)

Most of the time we refer to the authority is the system permissions, generally refers to a wide range. For example, with the establishment of tables, query the permissions of the table. And object permissions are from a finer range. For example, you have permission to manipulate an object (a specific table or view, etc.).

For example, there is a table TMP. The grant SELECT on the TMP to Arwen. Indicates that Arwen has permission to access the table TMP, but does not have access to any other tables.

Permissions granted to Permissions

System permissions (with ADMIN option) granted by the associated class

Looking at so many ways to empower, you might ask a super-user to do something like Sys. How tired it is. You can also give other users permission to give to others.

First through the SYS user, grant CREATE table to Arwen with admin option;--Plus with admin Option This modification means that Arwen can also give other users permission to set up the table. This is a transitive way of empowering. And if Arwen gives the user Weiwen permission to build the table through grant CREATE table to Weiwen, If Arwen's own permission to create a table is recycled. The Weiwen permissions are still

Object permissions are assigned to the associated class (with GRANT Option)

There is also grant select on EMP-Arwen with GRANT option; --as in the above, Arwen can also give other users the permission to check the table tmp

Arwen via Grant Select on EMP-Weiwen with GRANT option; --Unlike system permissions, if Arwen's permissions are recycled, Weiwen's check-list permissions are gone.

If Weiwen again via grant Select on the EMP to test; --empower test. When either Arwen or Weiwen is removed, it is also canceled. Just like an inheritance hierarchy. The previous permission was canceled and the back one was gone.

Reclaim Permissions

We know that giving permission is sure to be recycled,

Very simply, granting permission is grant ...

Recycling is revoke ... from ...

For example revoke CREATE table from Arwen;

Why do I have create session permissions

There is a lot of controversy about this privilege. Because without this permission is actually possible, and it is easy to mislead people. For example, creating a user should not be able to login a bit of a common sense. There is no such permission in SQL Server. What does it do in Oracle?

I think the create session has a pretty important role to play the role of lock. For example, we want to lock a user in some situations. Like a user account is found to be abnormal or the user because of an employee left to temporarily lock. You cannot connect if you recycle the Create session permission. is exactly the same as lock. And you can reclaim the Create session permission and lock at the same time. Double insurance.

In addition, if you want to lock the user, you do not lock its permissions when you can only reclaim the create session.

SQL Server vs. Oracle Learning: Rights Management (i)