Oracle-3-: Super Beginner's entry level pen-user rights, set operator, advanced subquery

The content of the previous article in the second article here,

User rights: Create a user, create a role, use GRANT and REVOKE statements to give and reclaim permissions, create a database join

User created: Create user xxx identified by PASS:XXX for the newly created user username, pass for password

It is emphasized here that the Oracle 12c version must be created by the user in C # #开头, can also be changed, see Baidu, haha

But the newly created user does not have the Create session permission, see diagram, login is denied

　　　　

Grant user rights: Grant permission to User

Since he does not have the Create session permission, we can give it permission: Grant create session to C # #wangziqiang; You can log in right now.

　　　　　　　

Follow the required permissions to empower yourself, big God stickers

CREATE TABLE EMP (ID number), name VARCHAR2 (20)); You will be prompted for insufficient permissions.

　　　　　　　　

Permissions given to create TABLE: The grant CREATE table to C # #wangziqiang; Now it's time to create the table.

If you are prompted for a tablespace error, you can make a change to the tablespace: Alter user userName quota unlimited on users; this is an unlimited tablespace created for userName, and if you restrict tablespaces, you need to unlimit Ed to the value that you want to set for example: 5m is five megabytes in size

Change your password: Alter user C # #wangziqiang identified by 123;

Create a role: a role integrates one or more permissions, assigns a role to a user, and the user has all the permissions of that role, which is equivalent to a title,

Create role C # #myrole; created a character named Myrole

Grant Create table,create view ... to C # #myrole: Assigning Permissions to Roles

Grant C # #myroleto User: Assigning Roles to users

Assign the relevant permission of your own object to others: it is to give others the permission to change the table view, etc.

Wangziqiang user does not have permission to DVF user jobs table, when queried, the table or view does not exist

　　　　

Let's give the Wangziqiang user access to the jobs table of the DVF user,

Grant SELECT--related permissions, separated by commas
On Dvf.jobs--which user's table
to C # #wangziqiang; --to whom?

At this point, the information on the jobs table will appear again.

　　　　　　

When a grants b the user's own table the relevant permissions, the default is B can no longer assign a to B permissions to others. By the end, with GRANT option, to set the permissions that B can also assign to someone else's table

When a table requires everyone to have permission to this table, we can use public to assign the table permissions to all users: Grant SELECT--related permissions, separated by commas
On Dvf.jobs--which user's table
to public; --Assign to all users

　　　　　　

Command: SELECT * from USER_TAB_PRIVS_RECD; Under the Wangziqiang user, query the user-owned permissions on the Table object, for example, this user has the SELECT permission of the DVF user's jobs table

　　　　　　

Delete Related permissions: Revoke select on Dvf.jobs from C # #wangziqiang; Removing the SELECT permission on the DVF user's jobs table, from C # #wangziqiang这里收回, is the query permission from the C # #wangziqiang用户这收回dvf的jobs table,

Command run finished, and then check the wangziqing user under the owner of the Table object permissions, this time the relevant permissions have been canceled off

　　　　　　

　　　　

　　　　

Oracle-3-: Super Beginner's entry level pen-user rights, set operator, advanced subquery