Oracle series: (26) User Rights control

1. Users

Users in Oracle are divided into two major categories

1) when the Oracle database server is created, users that are automatically created by the system are called system users , such as Sys.

2) users created by the system users, called ordinary users , such as scott,hr,c# #tiger, Zhaojun,...

"Log in with SYS to query the name and status of existing users in the current Oracle database server

Username indicates the login name

Expired&locked indicates account expiration and lockout

Open indicates that the account is now available

Sqlplus/as Sysdba;  Col username for A30;  Col account_status for A30;  Set pagesize 100; Select Username,account_status from Dba_users;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/87/1A/wKioL1fUUNqDh6KPAACk_xZ5C0g763.jpg "title=" 001. JPG "alt=" wkiol1fuunqdh6kpaack_xz5c0g763.jpg "/>

Query which users are in Oracle

SELECT * from All_users;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/87/1C/wKiom1fUUR_hR2ECAACNkW8mk1Y192.jpg "title=" 002. JPG "alt=" wkiom1fuur_hr2ecaacnkw8mk1y192.jpg "/>

2, create and delete ordinary users

You can create a new normal user in Oracle and create a normal user command: Create user, which should be assigned a specific tablespace, usually called users, while creating a normal user.

"Log in with SYS to query what storage space is available in Oracle, and all ordinary users default to the users storage space

SELECT * from V$tablespace;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/87/1A/wKioL1fUUjTAUCPsAABNM_lDuCU614.jpg "title=" 003. JPG "alt=" wkiol1fuujtaucpsaabnm_lducu614.jpg "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/87/1C/wKiom1fUiPHQO4kaAAB97ebKk-k526.jpg "title=" 001. JPG "alt=" wkiom1fuiphqo4kaaab97ebkk-k526.jpg "/>

"Log in with SYS, create a normal user C # #tiger, the password is ABC, by default use the users storage space, that is, a dbf binary file on the corresponding hard disk

Sqlplus/as Sysdba; Create User C # #tiger identified by ABC default tablespace users;

"With SYS login, for C # #tiger分配users空间无限制使用, that is, the database dbf file can be infinitely increased, a dbf file is not enough, will create a second DBF file

Sqlplus/as Sysdba; Alter User C # #tiger quota unlimited on users;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/87/1A/wKioL1fUU1iwo_p6AABZXb3neWw012.jpg "title=" 004. JPG "alt=" wkiol1fuu1iwo_p6aabzxb3neww012.jpg "/>

"In C # #tiger登录, can go into ORCL database?"

Sqlplus C # #tiger/ABC

Can't get into the ORCL database

"Log in with sys, remove normal user C # #tiger

Sqlplus/as Sysdba; Drop User C # #tiger Cascade;

3. Understanding System Users

SYS is an important system user in Oracle, and SYS is the highest-privileged user in Oracle with a role of SYSDBA (System administrator)

Sqlplus/as SYSDBA

4. Permissions

Permissions end up acting on the user. That is, all user objects and executable actions within the database are restricted.

There are two main categories of permissions in Oracle:

1) System permissions

2) Object permissions

4.1. System permissions

Licenses for specific operations in the database, such as: let C # #tiger能登录到orcl数据库 to create tables in the ORCL database

"Log in with SYS to obtain information about system permissions, for example: Select any table represents SELECT permissions for all tables

Sqlplus/as Sysdba; Select distinct privilege from Dba_sys_privs;

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/87/1C/wKiom1fUVNORcB9XAACdZtdbQYM032.jpg "title=" 005. JPG "alt=" wkiom1fuvnorcb9xaacdztdbqym032.jpg "/>

"With SYS login, for C # #tiger分配create session with the database to establish the permissions, that is, allow the user to log on

Sqlplus/as Sysdba; Grant create session to C # #tiger;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/87/1A/wKioL1fUVTORkZRmAAAtGATTi1A472.jpg "title=" 006. JPG "alt=" wkiol1fuvtorkzrmaaatgatti1a472.jpg "/>

"In C # #tiger登录, can go into ORCL database?"

Sqlplus C # #tiger/ABC

can go in. ORCL Database

"In C # #tiger登录, create a tiger table, can you create it?

Sqlplus C # #tiger/ABC CREATE table Tiger (name VARCHAR2 (20));

This is the C # #tiger没有权限创建表

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/87/1C/wKiom1fUVZbDAbsLAAA-Y7g9qS8467.jpg "title=" 007. JPG "alt=" wkiom1fuvzbdabslaaa-y7g9qs8467.jpg "/>

"Log in with SYS, #tiger分配create table permission for C #, which allows tables to be created

Sqlplus/as Sysdba; Grant CREATE table to C # #tiger;

"In C # #tiger登录, create a tiger table, can you create it?

Sqlplus C # #tiger/ABC CREATE table Tiger (name VARCHAR2 (20));

You can create a C # #tiger表

"Log in with sys and query C # #tiger所拥有的系统权限

Sqlplus/as Sysdba;

Select Grantee,privilege from Dba_sys_privs where lower (grantee) = ' C # #tiger ';

Grantee represents a normal user name

Privilege Permission Name

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/87/1C/wKiom1fUWKjAGUukAABXu71D6C8553.jpg "title=" 008. JPG "alt=" wkiom1fuwkjaguukaabxu71d6c8553.jpg "/>

"Log in with SYS, revoke C # #tiger的create table permissions

Sqlplus/as Sysdba; Revoke CREATE TABLE from C # #tiger;

4.2. Object permissions

The user's permission to manipulate an existing object, including:

1) Select can be used for tables, views and sequences

2) Insert a new record into the table or view

3) Update the data in the table

4) Delete deletes data from the table

5) Execute function, execution of the procedure

6) index create indexes for table

7) References create external health for table

8) Alter modify the properties of the table or sequence

"Log in with sys and query C # #tiger所拥有的对象权限

Sqlplus/as Sysdba;  Col grantee for A10;  Col table_name for A10;  Col privilege for A20; Select Grantee,table_name,privilege from Dba_tab_privs where lower (grantee) = ' C # #tiger ';

"With SYS login, for C # #tiger分配对tiger表的所有权限, that is, adding and deleting the operation

Sqlplus/as Sysdba; Grant all on C # #tiger. Tiger to C # #tiger;

Note: C # #tiger表示空间名

Tiger indicates the name of the table under this space

C # #TIGER TIGER FLASHBACK

C # #TIGER TIGER DEBUG

C # #TIGER TIGER QUERY REWRITE

C # #TIGER TIGER on COMMIT REFRESH

C # #TIGER TIGER REFERENCES

C # #TIGER TIGER UPDATE

C # #TIGER TIGER SELECT

C # #TIGER TIGER INSERT

C # #TIGER TIGER INDEX

C # #TIGER TIGER DELETE

C # #TIGER TIGER ALTER

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/87/1A/wKioL1fUWbPRVqMdAAECDrrhpqI348.jpg "title=" 009. JPG "alt=" wkiol1fuwbprvqmdaaecdrrhpqi348.jpg "/>

"In C # #tiger登录, the Tiger Watch for the increase and deletion check operation

Sqlplus C # #tiger/ABC;  INSERT into Tiger (name) VALUES (' AA ');  Update Tiger Set name = ' BB ';  Delete from Tiger where rownum = 1; SELECT * from Tiger;

Oracle series: (26) User Rights control