Oracle Rights Management

A) 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 A10;
Col Account_status for A10;
Set pagesize 100;
Select Username,account_status from Dba_users;

Query which users are in Oracle
SELECT * from All_users;

II) 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;

"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;

"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;

III) 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

IV) 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

(v) 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;

"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;

"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没有权限创建表

"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

"Log in with SYS, revoke C # #tiger的create table permissions
Sqlplus/as Sysdba;
Revoke CREATE TABLE from C # #tiger;

VI) 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

"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 Rights Management