一. 概述

本片文章主要介紹了：

• 用戶的創(chuàng)建，修改，刪除
• 權(quán)限的分類
• 系統(tǒng)權(quán)限和對象權(quán)限的賦予
• 角色的定義與使用
• 回收權(quán)限

二. 用戶

2.1 創(chuàng)建用戶格式

CREATE USER user_name IDENTIFIED BY pwssword;

user_name 是用戶名
password 是用戶密碼

• 【GRANT privileges TO user_name IDENTIFIED BY password】可以實現(xiàn)賦權(quán)與建用戶同時進(jìn)行;

2.2 創(chuàng)建用戶

• 創(chuàng)建一個名為damon的用戶密碼為damon

SYS@oradb> create user damon identified  by damon;

User created.

2.3 修改密碼

• 方法一：使用alter user

ALTER USER user_name identified by new_password；

• 方法二：psassword user

password user_name

• 示例：

SYS@oradb> alter user damon identified by oracle;

User altered.

SYS@oradb> PASSWORD damon
Changing password for damon
New password:
Retype new password:
Password changed

2.4 解鎖用戶

ALTER USER user_name IDENTIFIED BY password ACCOUNT UNLOCK;

2.5 切換用戶登錄

• 語法： conn user_name/password
• 使用新建用戶登錄：

SYS@oradb> conn damon/damon
ERROR:
ORA-01045: user DAMON lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
@> show user
USER is ""
@> conn / as sysdba
Connected.
SYS@oradb> !oerr ora 1045
01045, 00000, "user %s lacks CREATE SESSION privilege; logon denied"
// *Cause: A connect was attempted to a userid which does not have
//         create session privilege.
// *Action: Grant the user CREATE SESSION privilege.

發(fā)現(xiàn)鏈接失敗，是因為該用戶沒有賦予創(chuàng)建會話的權(quán)限。處理方法后續(xù)說明。

2.6 刪除用戶

• 語法： DROP USER user_name [CASCADE];
  當(dāng)用戶下還有其他對象的時候（表、索引、視圖等） ，直接使用DROP USER user_name 會報錯，需要加上【CASCADE】參數(shù)級聯(lián)刪除該用戶下的所有對象。

三. 權(quán)限

3.1 權(quán)限分類

• 權(quán)限的作用是為了保證數(shù)據(jù)庫的安全：包括系統(tǒng)安全和數(shù)據(jù)安全
• 權(quán)限可以分為系統(tǒng)權(quán)限和對象權(quán)限

系統(tǒng)權(quán)限：對于數(shù)據(jù)庫的權(quán)限，能夠在數(shù)據(jù)庫做什么操作（例如創(chuàng)建表）

對象權(quán)限：操作數(shù)據(jù)庫對象的權(quán)限，能夠?qū)χ付ǖ膶ο笞鍪裁床僮鳎ɡ鐚Ρ淼腄ML操作）

• schema(模式/方案）:某個用戶下所有對象的集合。模式與用戶是一 一對應(yīng)的，可以把其理解為用戶。
  示例： 我們查看scott模式下的emp表（emp表是用戶scott用戶下的一個表對象）

SYS@oradb> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

3.2 系統(tǒng)權(quán)限

3.2.1 用戶的系統(tǒng)權(quán)限

• 用戶創(chuàng)建之后，DBA可以賦予該用戶一些權(quán)限
  語法： GRANT privilege1[,privilege2,…] TO user1[,user1|role,PUBLIC…];

privilege一般可選：
-CREATE SESSION
-CREATE TABLE
-CREATE SEQUENCE
-CREATE VIEW
-CREATE PROCEDURE

• 示例：

SYS@oradb> GRANT create session,create table,create sequence,create view TO damon;

Grant succeeded.

• 連接damon用戶,此時能正常鏈接，因為賦予了damon用戶創(chuàng)建會話的

SYS@oradb> conn damon/damon
Connected.
DAMON@oradb>

• 查看用戶擁有的系統(tǒng)權(quán)限

DAMON@oradb> select * from user_sys_privs;

USERNAME             PRIVILEGE                                ADM COM INH
-------------------- ---------------------------------------- --- --- ---
DAMON                CREATE VIEW                              NO  NO  NO
DAMON                CREATE SEQUENCE                          NO  NO  NO
DAMON                CREATE TABLE                             NO  NO  NO
DAMON                CREATE SESSION                           NO  NO  NO

3.3 角色

3.3.1 角色的定義

角色可以理解為一些權(quán)限的集合。角色的作用是用來簡化權(quán)限的管理。例如，我們在3.2.1節(jié)中把四個系統(tǒng)權(quán)限都付與給了用戶damon，但是如果我們需要將好幾十個權(quán)限賦給damon，此時還需要將每個權(quán)限的名稱（create table）全部輸入，費力不討好。此時角色就能便于我們對用戶權(quán)限的管理。我們可以創(chuàng)建一個或多個角色，將這幾十個權(quán)限一并或者分組賦予給這些創(chuàng)建的角色，再把這一個或多個角色賦予用戶。此時該用戶就擁有它所有角色下的權(quán)限。圖解如下：

3.3.2 角色的使用

• 創(chuàng)建角色

CREATE ROLE role_name;

• 為角色賦予權(quán)限

GRANT PRIVILEGE1[,PRIVILEGE2,...] TO role_name;

• 將角色賦予用戶

GRANT role_name TO user_name;

• 查看當(dāng)前用戶下角色信息：

SELECT * FROM USER_ROLE_PRIVS;

• 查看某角色擁有的系統(tǒng)權(quán)限

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='role_name';

• 查看某角色擁有的對象權(quán)限

SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='role_name';

3.3.3 系統(tǒng)默認(rèn)角色

[CONNECT]、[RESOURCE]是兩個系統(tǒng)默認(rèn)的角色

• 查看這兩個默認(rèn)角色包含的系統(tǒng)權(quán)限

SYS@oradb> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE IN('CONNECT','RESOURCE');

ROLE                 PRIVILEGE                                ADM COM INH
-------------------- ---------------------------------------- --- --- ---
CONNECT              SET CONTAINER                            NO  YES YES
RESOURCE             CREATE SEQUENCE                          NO  YES YES
RESOURCE             CREATE TRIGGER                           NO  YES YES
RESOURCE             CREATE CLUSTER                           NO  YES YES
RESOURCE             CREATE PROCEDURE                         NO  YES YES
RESOURCE             CREATE TYPE                              NO  YES YES
CONNECT              CREATE SESSION                           NO  YES YES
RESOURCE             CREATE OPERATOR                          NO  YES YES
RESOURCE             CREATE TABLE                             NO  YES YES
RESOURCE             CREATE INDEXTYPE                         NO  YES YES

這兩個角色能給用戶能夠完成任務(wù)的最低標(biāo)準(zhǔn)

• 授予damon用戶這兩個角色

SYS@oradb> grant connect,resource to damon;

Grant succeeded.

SYS@oradb> conn damon/damon
Connected.
DAMON@oradb> select * from user_role_privs;

USERNAME             GRANTED_ROLE         ADM DEL DEF OS_ COM INH
-------------------- -------------------- --- --- --- --- --- ---
DAMON                CONNECT              NO  NO  YES NO  NO  NO
DAMON                RESOURCE             NO  NO  YES NO  NO  NO

3.4 對象權(quán)限

• 不同的對象擁有不同的對象權(quán)限

• 對象的擁有者擁有所有權(quán)限
  比如用戶創(chuàng)建了一個對象，該用戶擁有這個對象的所有權(quán)限
• 對象的擁有者可以向外分配權(quán)限
  示例：讓damon用戶能夠查看scott用戶擁有的表emp

# 登連接到表emp的擁有用戶
DAMON@oradb> conn scott/tiger
Connected.
# 使用scott用戶對用戶damon賦予查詢emp表的權(quán)限
SCOTT@oradb> grant select on emp to damon;

Grant succeeded.
# 查看damon用戶擁有的對象權(quán)限
DAMON@oradb> select * from  user_tab_privs;

GRANTEE    OWNER      TABLE_NAME           GRANTOR              PRIVILEGE       GRA HIE COM TYPE       INH
---------- ---------- -------------------- -------------------- --------------- --- --- --- ---------- ---
DAMON      SCOTT      EMP                  SCOTT                SELECT          NO  NO  NO  TABLE      NO

# 查詢scott用戶下的emp表
DAMON@oradb> select count(*) from scott.emp;

  COUNT(*)
----------
        14

• 對象的權(quán)限可以精確到對列做insert/update：grant update (dname,loc) on dept to damon;

3.4.1 語法

對于授予的對象權(quán)限語法可以表示為：

GRANT  object_priv[(columns)]
ON     object
TO     {user|role|PUBLIC}
[WITH GRANT OPTION]

3.4.2 [WITH GRANT OPTION]

這是賦權(quán)時候的可選項，它的作用是，讓被賦權(quán)的用戶能夠賦權(quán)給其他用戶或角色；例如，A把權(quán)限授予B，B還可以把這個權(quán)限賦予給其他用戶或者角色C；
示例：scott賦予damon查看emp的權(quán)限，damon賦予角色se查詢emp的權(quán)限；

SCOTT@oradb> grant select on emp to damon with grant option;
Grant succeeded.

DAMON@oradb> select * from  user_tab_privs;
GRANTEE    OWNER      TABLE_NAME           GRANTOR              PRIVILEGE       GRA HIE COM TYPE       INH
---------- ---------- -------------------- -------------------- --------------- --- --- --- ---------- ---
DAMON      SCOTT      EMP                  SCOTT                SELECT          YES NO  NO  TABLE      NO

DAMON@oradb> grant select on scott.emp to se;
Grant succeeded.
DAMON@oradb> select * from role_tab_privs where role='SE';
ROLE                 OWNER      TABLE_NAME           COLUMN_NAME          PRIVILEGE       GRA COM INH
-------------------- ---------- -------------------- -------------------- --------------- --- --- ---
SE                   SCOTT      EMP                                       SELECT          NO  NO  NO

3.4.3 [PUBLIC]

• PUBLIC也是一個默認(rèn)的角色，數(shù)據(jù)庫中任意一個角色都擁有public角色，授予給public的絕權(quán)限（grant…to public),會讓所有的用戶都擁有這個權(quán)限。
• 它的信息可以在user_tab_privs表中：

DAMON@oradb> select * FROM USER_tab_privs;

GRANTEE    OWNER      TABLE_NAME           GRANTOR              PRIVILEGE       GRA HIE COM TYPE       INH
---------- ---------- -------------------- -------------------- --------------- --- --- --- ---------- ---
DAMON      SCOTT      EMP                  SCOTT                SELECT          YES NO  NO  TABLE      NO
PUBLIC     SYS        DAMON                DAMON                INHERIT PRIVILE NO  NO  NO  USER       NO
                                                                GES

3.5 權(quán)限相關(guān)的數(shù)據(jù)字典視圖

數(shù)據(jù)字典視圖在前幾小節(jié)也有使用到，下面是總結(jié)的表格：

3.6 收回權(quán)限

• 使用【REVOKE】語句撤銷權(quán)限
• 使用WITH GRANT OPTION 子句分配的權(quán)限同樣被收回

3.6.1 語法

REVOKE {privilege1[,privilege2,...]|ALL}
ON object
FROM {user1[,user2...]|role|PUBLIC}
[CASCADE CONSTRAINTS] 
{[WITH GRANT OPTION]|[WITH ADMIN OPTION]};

CASCADE CONSTRAINTS:
收回外鍵約束權(quán)限的時候，先將外鍵約束刪除

WITH GRANT OPTION: 對象權(quán)限
假設(shè)權(quán)限：A -> B -> C -> D
對象權(quán)限是級聯(lián)的，A收回B，那么C和D的權(quán)限一并收回
但是A不能直接收回C的權(quán)限

WITH ADMIN OPTION: 系統(tǒng)權(quán)限
假設(shè)權(quán)限：A -> B -> C -> D
系統(tǒng)權(quán)限的回收不是級聯(lián)的：A回收B的權(quán)限，C和D的系統(tǒng)權(quán)限還存在
A可以直接回收C和D的權(quán)限，只要使用了該選項

3.7 [SELECT ANY TABLE]

• SYS用戶可以把[SELECT ANY TABLE]賦權(quán)給其他用戶，這樣這個用戶就可以訪問其他用戶所有的表，但是該用戶依舊無法查詢dba_users這樣的數(shù)據(jù)字典視圖。
• 如果想讓普通用戶就能查到dba_數(shù)據(jù)字典視圖，需要使用【SELECT ANY DICTIONARY】的權(quán)限。

四. 總結(jié)

五. 練習(xí)

5.1 使用 DBA 賬戶創(chuàng)建一個賬號為 temp,密碼為 temp 用戶,并授予會話創(chuàng)建權(quán)限

• 可以在賦權(quán)的同時創(chuàng)建角色

SYS@oradb> grant create session to "temp" identified by temp;

Grant succeeded.

SYS@oradb> conn "temp"/temp
Connected.

5.2 以 SCOTT 用戶登錄，將查詢 emp 表的權(quán)限授權(quán)給 temp 用戶.

temp@oradb> conn scott/tiger
Connected.
SCOTT@oradb> grant select on emp to "temp";

Grant succeeded.

temp@oradb> select * from user_tab_privs;

GRANTEE    OWNER      TABLE_NAME           GRANTOR              PRIVILEGE       GRA HIE COM TYPE       INH
---------- ---------- -------------------- -------------------- --------------- --- --- --- ---------- ---
temp       SCOTT      EMP                  SCOTT                SELECT          NO  NO  NO  TABLE      NO
PUBLIC     SYS        temp                 temp                 INHERIT PRIVILE NO  NO  NO  USER       NO

5.3使用 temp 用戶登錄。

• 嘗試查詢 emp 表中部門號為 20 的所有員工信息，是否成功?

temp@oradb> SELECT * FROM SCOTT.EMP WHERE DEPTNO=20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

• 嘗試刪除 emp 表中部門號為 20 的所有員工信息，是否成功?

temp@oradb> DELETE FROM SCOTT.EMP WHERE DEPTNO=20;
DELETE FROM SCOTT.EMP WHERE DEPTNO=20
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

沒有權(quán)限

5.4 以 DBA 用戶，創(chuàng)建用戶 temp2,密碼為 temp2,并授予會話創(chuàng)建權(quán)限。

SYS@oradb> grant create session to temp2 identified by temp2;

Grant succeeded.

5.5 以 DBA 用戶，創(chuàng)建角色 temp_role.

SYS@oradb> CREATE ROLE TEMP_ROLE;

Role created.

5.6 以 scott 用戶登錄，將查詢 scott.dept 表的權(quán)限授權(quán)給 temp_role

SCOTT@oradb> grant select on dept to temp_role;

Grant succeeded.

5.7 將角色 temp_role 授給用戶 temp 和 temp2

SYS@oradb> grant temp_role to "temp",temp2;

Grant succeeded.

5.8 用 temp2 用戶登錄，查詢表 scott.dept 的所有信息

TEMP2@oradb> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

5.9 刪除角色 temp_role。重復(fù)操作第5.8

SYS@oradb> DROP ROLE TEMP_ROLE;
Role dropped.

TEMP2@oradb> select * from scott.dept;
select * from scott.dept
                    *
ERROR at line 1:
ORA-00942: table or view does not exist