Oracle之用户、特权和角色

本文主要包括：

   · 介绍创建用户

   · 了解如何使用特权来确保用户能够在数据库中执行任务

   · 介绍两种特权类型：系统特权和对象特权

   · 介绍系统特权怎样允许执行操作，例如执行 DDL 语句

   · 介绍对象特权怎样允许执行操作，例如执行 DML 语句

   · 介绍如何将特权组合在一起形成角色

一、 用户

数据库使用表空间来存储对象，其中可以包含表、类型和 PL/SQL 代码等

表空间存储在数据文件 (datafile) 中 

1. 创建用户

语法：

CREATE USER user_name IDENTIFIED BY password

[DEFAULT TABLESPACE default_tablespace]

[TEMPORARY TABLESPACE temporary_tablespace];

可以从 user_users 查询默认表空间和默认临时表空间

2. 修改用户密码

ALTER USER user_name IDENTIFIED BY password;

PASSWORD 可以用来修改当前登录用户的密码

3. 删除用户

DROP USER user_name;

注：如果要删除的用户模式中包含任何表或其他项，就必须在 DROP USER

语句中，在要删除的用户名后面加上关键字 CASCADE

二、 系统特权

系统特权（system privilege）允许用户在数据库中执行特定的操作，比如执行 DDL 语句

常用的系统特权如下表

特权组合在一起形成角色（role），授予用户的两种有用的角色是 CONNECT 和 RESOURCE

CONNECT 允许用户连接到数据库，RESOURCE 允许用户创建各种数据库对象，如表、序列等

1. 向用户授予系统特权

GRANT CREATE SESSION,CREATE USER,CREATE TABLE TO user_name;

注：使用 WITH ADMIN OPTION 选项，这样被授予的用户就可以把这种特权再授予其它用户

GRANT EXECUTE ANY PROCEDURE TO user_nameWITH ADMIN OPTION;

注：通过将一种特权授予 PUBLIC，就可以将该特权授予所有用户

CONNECT / AS SYSDBA

GRANT EXECUTE ANY PROCEDURE TO PUBLIC;

2. 检查授予用户的系统特权

通过查询 user_sys_privilege 可以检查某个用户具有哪些系统特权

3. 使用系统特权

用户被授予系统特权之后，就可以使用这种特权来执行特定的任务，如创建用户等

但执行没有被授予的系统特权，操作会失败

4. 撤销用户的系统特权

可以使用 REVOKE 语句撤销某个用户的系统特权

CONN / AS SYSDBA

REVOKE CREATE TABLE FROM user_name;

三、 对象特权

对象特权（object privilege）允许用户对数据库对象执行特定的操作，例如对表执行 DML 语句

1. 向用户授予对象特权

可以使用 GRANT 语句向用户授予对象特权

范例：以 store 用户的身份连接到数据库，并向 steve 用户授予以下特权：

对 products 表的 SELECT、INSERT、UPDATE对象特权以及对表 employees 的 SELECT 特权

CONN store/store_password

GRANT SELECT,INSERT,UPDATE ON store_products TO steve;

GRANT SELECT ON store.employees TO steve;

注：可以使用 GRANT 选项，这样被授予的用户就可以把这种特权再授予其他用户

GRANT SELECT ON store.customers TO steve WITH GRANT OPTION;

2. 检查已授予的对象特权

通过查询 user_tab_privs_made 可以检查某个用户对哪些表向其他用户开放了哪些对象特权

范例：检索 table_name 为 PRODUCTS 的行

SELECT grantee,table_name,grantor,privilege,grantable,hierarchy

FROM user_tab_privs_made

WHERE table_name='PRODUCTS';

通过查询 user_col_privs_made 可以检查某个用户对哪些列对象开放了哪些特权

范例：对 user_col_privs_made 进行查询

SELECT grantee,table_name,column_name,grantor,privilege,grantable

FROM user_col_privs_made

ORDER BY column_name;

注：

   · 系统特权的传递使用 WITH ADMIN OPTION

   · 对象特权的传递使用 WITH GRANT OPTION

3. 检查已接受的对象特权

通过查询 user_tab_privs_recd表可以检查某个用户被授予了哪些表上的哪些对象特权

范例：以 steve 用户的身份连接到数据库，并对 user_tab_privs_recd 进行查询

SELECT owner,table_name,grantor,privilege,grantable,hierarchy

FROM user_tab_privs_recd

ORDER BY table_name,privilege;

通过查询 user_col_privs_recd可以检查某个用户被授予了哪些列的对象特权

范例：对 user_col_privs_recd 进行查询

SELECT owner,table_name,column_name,grantor,privilege,grantable

FROM user_col_privs_recd;

4. 使用对象特权

用户被授予对象特权之后，就可以使用这种特权来执行特定的任务，

例如， steve 用户对 store.customers 具有 SELECT 特权

CONNECT steve/button

SELECT * FROM store.customers;

注：如果 steve 试图对 purchases 表 (steve 对该表没有任何权限) 进行检索，数据库就会报错

SELECT * FROM store.purchases;

5. 创建同义词

范例：以 system 用户的身份连接到数据库，并将 CREATE SYNONYM 系统特权授予 steve 用户

CONN system/oracle

GRANT CREATE SYNONYM TO steve;

范例：以 steve 身份连接到数据库，并执行一条 CREATE SYNONYM 语句，为 store.customers 表创建同义词

CONN steve/button

CREATE SYNONYM customers FOR store.customers;

6. 创建公共同义词

可以为表创建公共同义词 (public synonym) ，在创建公共同义词之后，所有的用户都可以

看到这个同义词，下面这4条语句执行以下任务：

   · 以 system 用户的身份连接到数据库

   · 将 CREATE PUBLIC SYNONYM 系统特权授予 store 用户

   · 以 store 用户的身份连接到数据库

   · 为 store.products 创建公共同义词

CONNECT system/oracle

GRANT CREATE PUBLIC SYNONYM TO store;

CONN store/store_password

CREATE PUBLIC SYNNONYM products FOR store.products;

7. 撤销用户的对象特权

可以使用 REVOKE 语句撤销某个用户的对象特权

范例：以 store 用户的身份连接到数据库，并撤销 steve 用户对 products 表的 INSERT 特权

CONN store/store_password

REVOKE INSER ON products FROM steve;

注：

   · 系统特权在撤销时，该用户授予其它用户的特权不会消失   

   · 对象特权再撤销时，该用户授予其它用户的特权也会消失    

四、 角色

角色就是一组特权，可以分配给用户或其它角色，角色的优点可以总结为如下几点：

   · 并不是一次一个地将特权直接授予用户，而是先创建角色，向角色授予一些特权，

      然后再将角色授予多个用户和角色

   · 在增加或删除角色的某种特权时，被授予该角色的所有用户和角色都会自动获得

      新增加的特权或自动失去这种特权

   · 可以将多个角色授予用户或角色

   · 可以为角色设置密码

1. 创建角色

要创建角色，必须具有 CREATE ROLE 系统特权

2. 为角色授权 

3. 将角色授予用户

4. 检查授予用户的角色

通过查询 user_role_privs 可以检查已经授予用户哪些角色

注：

   · 密码保护的角色是禁用的，必须输入密码才能使用该角色

   · 创建角色的那个用户也会被授予该角色

5. 检查授予角色的系统特权

通过查询 role_sys_privs 可以检查已经授予角色哪些系统特权

6. 检查授予角色的对象特权

通过查询 role_tab_privs 可以检查已经授予角色哪些对象特权

7. 使用已授予角色的特权

对于无密码保护的角色，通过角色将特权授予用户之后，用户连接到数据库后就

可以立即使用这种特权了；

对于密码保护的角色，用户必须输入角色密码才能使用这个角色

SET ROLE role_name IDENTIFIED BY role_password;

8. 启用和禁用角色

可以禁用角色，ALTER ROLE 语句可以用来对角色进行修改，使其变为非默认角色

范例：以 system 用户身份连接到数据库，并对 john 用户进行修改，使 hr_manager 不再是默认角色

CONN system/oracle

ALTER USER johnDEFAULT ROLE ALL EXCEPT hr_manager;

范例：使用 SET ROLE 命令启用 hr_manager 角色

SET ROLE hr_manager;

范例：将 hr_manager 角色设置为默认角色，它在退出登录后仍然保留

CONN system/oracle

ALTER USER johnDEFAULT ROLE hr_manager;

范例：可以将角色设置为 NONE ，这表示没有角色

CONN john/brown

SET ROLE NONE;

范例：将角色设置为除 hr_manager 之外的任何角色

SET ROLE ALL EXCEPT hr_manager;

9. 撤销角色

REVOKE 语句可以用来撤销角色

REVOKE role_nameFROM user_name;

10. 从角色中撤销特权

REVOKE 语句可以用来从角色中撤销某种特权

REVOKE ALL ON table_name FROM role_name;

11. 删除角色

DROP ROLE 语句可以用来删除角色

DROP ROLE role_name;