Access Control, Authorization, and Privilege
Access Control
指允许和禁止用户访问资源。通常与ACL结合。
Authorization
用户的安全策略与实际权限间的关联/映射。
Privilege
允许执行的操作。
Oracle数据库提供对象，系统权限，这是最主要的，还有一个intra-object权限。
系统权限是针对数据库的权限，可进一步细分为ANY 和 ADMINISTRATIVE。
ANY系统权限和特定的schema无关，可分为以下几类：
- SELECT
- DML
- DDL
- 系统控制，如alter system, alter database
- 会话控制，alter session，set role
- 事务控制，如commit, rollback
19c中的ANY系统权限为：
SQL> set pages 0
SELECT name
FROM system_privilege_map
WHERE name LIKE '%ANY%'
ORDER BY name;
ADMINISTER ANY SQL TUNING SET
ALTER ANY ANALYTIC VIEW
ALTER ANY ASSEMBLY
ALTER ANY ATTRIBUTE DIMENSION
ALTER ANY CLUSTER
ALTER ANY CUBE
ALTER ANY CUBE BUILD PROCESS
ALTER ANY CUBE DIMENSION
ALTER ANY DIMENSION
ALTER ANY EDITION
ALTER ANY EVALUATION CONTEXT
ALTER ANY HIERARCHY
ALTER ANY INDEX
ALTER ANY INDEXTYPE
ALTER ANY LIBRARY
ALTER ANY MATERIALIZED VIEW
ALTER ANY MEASURE FOLDER
ALTER ANY MINING MODEL
ALTER ANY OPERATOR
ALTER ANY OUTLINE
ALTER ANY PROCEDURE
ALTER ANY ROLE
ALTER ANY RULE
ALTER ANY RULE SET
ALTER ANY SEQUENCE
ALTER ANY SQL PROFILE
ALTER ANY SQL TRANSLATION PROFILE
ALTER ANY TABLE
ALTER ANY TRIGGER
ALTER ANY TYPE
ANALYZE ANY
ANALYZE ANY DICTIONARY
AUDIT ANY
BACKUP ANY TABLE
COMMENT ANY MINING MODEL
COMMENT ANY TABLE
CREATE ANY ANALYTIC VIEW
CREATE ANY ASSEMBLY
CREATE ANY ATTRIBUTE DIMENSION
CREATE ANY CLUSTER
CREATE ANY CONTEXT
CREATE ANY CREDENTIAL
CREATE ANY CUBE
CREATE ANY CUBE BUILD PROCESS
CREATE ANY CUBE DIMENSION
CREATE ANY DIMENSION
CREATE ANY DIRECTORY
CREATE ANY EDITION
CREATE ANY EVALUATION CONTEXT
CREATE ANY HIERARCHY
CREATE ANY INDEX
CREATE ANY INDEXTYPE
CREATE ANY JOB
CREATE ANY LIBRARY
CREATE ANY MATERIALIZED VIEW
CREATE ANY MEASURE FOLDER
CREATE ANY MINING MODEL
CREATE ANY OPERATOR
CREATE ANY OUTLINE
CREATE ANY PROCEDURE
CREATE ANY RULE
CREATE ANY RULE SET
CREATE ANY SEQUENCE
CREATE ANY SQL PROFILE
CREATE ANY SQL TRANSLATION PROFILE
CREATE ANY SYNONYM
CREATE ANY TABLE
CREATE ANY TRIGGER
CREATE ANY TYPE
CREATE ANY VIEW
DEBUG ANY PROCEDURE
DEBUG CONNECT ANY
DELETE ANY CUBE DIMENSION
DELETE ANY MEASURE FOLDER
DELETE ANY TABLE
DEQUEUE ANY QUEUE
DROP ANY ANALYTIC VIEW
DROP ANY ASSEMBLY
DROP ANY ATTRIBUTE DIMENSION
DROP ANY CLUSTER
DROP ANY CONTEXT
DROP ANY CUBE
DROP ANY CUBE BUILD PROCESS
DROP ANY CUBE DIMENSION
DROP ANY DIMENSION
DROP ANY DIRECTORY
DROP ANY EDITION
DROP ANY EVALUATION CONTEXT
DROP ANY HIERARCHY
DROP ANY INDEX
DROP ANY INDEXTYPE
DROP ANY LIBRARY
DROP ANY MATERIALIZED VIEW
DROP ANY MEASURE FOLDER
DROP ANY MINING MODEL
DROP ANY OPERATOR
DROP ANY OUTLINE
DROP ANY PROCEDURE
DROP ANY ROLE
DROP ANY RULE
DROP ANY RULE SET
DROP ANY SEQUENCE
DROP ANY SQL PROFILE
DROP ANY SQL TRANSLATION PROFILE
DROP ANY SYNONYM
DROP ANY TABLE
DROP ANY TRIGGER
DROP ANY TYPE
DROP ANY VIEW
ENQUEUE ANY QUEUE
EXECUTE ANY ASSEMBLY
EXECUTE ANY CLASS
EXECUTE ANY EVALUATION CONTEXT
EXECUTE ANY INDEXTYPE
EXECUTE ANY LIBRARY
EXECUTE ANY OPERATOR
EXECUTE ANY PROCEDURE
EXECUTE ANY PROGRAM
EXECUTE ANY RULE
EXECUTE ANY RULE SET
EXECUTE ANY TYPE
FLASHBACK ANY TABLE
FORCE ANY TRANSACTION
GRANT ANY OBJECT PRIVILEGE
GRANT ANY PRIVILEGE
GRANT ANY ROLE
INHERIT ANY PRIVILEGES
INHERIT ANY REMOTE PRIVILEGES
INSERT ANY CUBE DIMENSION
INSERT ANY MEASURE FOLDER
INSERT ANY TABLE
LOCK ANY TABLE
MANAGE ANY FILE GROUP
MANAGE ANY QUEUE
MERGE ANY VIEW
READ ANY ANALYTIC VIEW CACHE
READ ANY FILE GROUP
READ ANY TABLE
REDEFINE ANY TABLE
SELECT ANY CUBE
SELECT ANY CUBE BUILD PROCESS
SELECT ANY CUBE DIMENSION
SELECT ANY DICTIONARY
SELECT ANY MEASURE FOLDER
SELECT ANY MINING MODEL
SELECT ANY SEQUENCE
SELECT ANY TABLE
SELECT ANY TRANSACTION
TRANSLATE ANY SQL
UNDER ANY TABLE
UNDER ANY TYPE
UNDER ANY VIEW
UPDATE ANY CUBE
UPDATE ANY CUBE BUILD PROCESS
UPDATE ANY CUBE DIMENSION
UPDATE ANY TABLE
USE ANY JOB RESOURCE
USE ANY SQL TRANSLATION PROFILE
WRITE ANY ANALYTIC VIEW CACHE
159 rows selected.
查看哪些用户和角色赋予了SELECT ANY TABLE权限：
SELECT grantee
FROM dba_sys_privs
WHERE privilege = 'SELECT ANY TABLE'
ORDER BY grantee;
GRANTEE
--------------------------------------------------------------------------------
DATAPUMP_IMP_FULL_DATABASE
DBA
DV_REALM_OWNER
EXP_FULL_DATABASE
GGSYS
GSMADMIN_INTERNAL
IMP_FULL_DATABASE
OLAP_DBA
SYS
SYSTEM
WMSYS
11 rows selected.
查看当前用户的权限和角色：
SQL>
SELECT *
FROM session_privs
ORDER BY privilege;
PRIVILEGE
----------------------------------------
ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
UNLIMITED TABLESPACE
14 rows selected.
SQL>
SELECT *
FROM session_roles
ORDER BY role ;
ROLE
--------------------------------------------------------------------------------
RESOURCE
SODA_APP
Object Privileges
对象权限针对schema中的对象，对象属主拥有对象的所有权限。
SQL>
SELECT grantee ROLE, privilege,
table_schema||'.'||table_name OBJECT_NAME, type
FROM all_tab_privs
WHERE grantee IN (SELECT * FROM session_roles)
UNION
SELECT DECODE(grantee, UPPER(USER), 'DIRECT', grantee) ROLE, privilege,
table_schema||'.'||table_name OBJECT_NAME, TYPE
FROM all_tab_privs
WHERE grantee = UPPER(USER)
ORDER by role, privilege, object_name;
ROLE PRIVILEGE OBJECT_NAME TYPE
-------------------- -------------------- --------------------------------------------- --------------------
DIRECT EXECUTE SYS.DBMS_STATS PACKAGE
SODA_APP EXECUTE XDB.DBMS_SODA_ADMIN PACKAGE
SODA_APP EXECUTE XDB.DBMS_SODA_USER_ADMIN PACKAGE
SODA_APP READ XDB.JSON$USER_COLLECTION_METADATA VIEW
Column Privileges
可控制单独的列。例如:
grant update(salary) on employees to user01;
grant insert(col1, col2) on table02 to user02;
Synonyms
public synonym表示用户可以访问此synonym，但最终的对象仍需要赋权。
CREATE or replace PUBLIC SYNONYM sales_regions FOR sregions;
赋权给synonym等同于赋权给基础对象。删除同义词并不取消对基础对象的权限。
System and Object Privileges Together
收回对基础对象的授权并不影响系统权限，系统权限仍有效。
Privilege Conveyance and Retraction
如果赋权给用户时，指定了WITH GRANT OPTION选项，则此用户可以将此权限赋予其他人。
Roles
类似于OS中的group，用于批量赋权。
修改role的权限时，所以属于此role的用户自动得到新权限。
SQL> show parameter roles
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_roles boolean FALSE
remote_os_roles boolean FALSE
Role and Privilege Immediacy
Privilege是立即生效的，而role是连接时引入。换句话说，当赋予用户新role时，在下次登录时才生效。
Roles and Container Databases
和user一样，role也分为common和local两类。
Public and Default Database Roles
PUBLIC是role，但并不显示在SESSION_ROLES中。
Role Hierarchies
role可以嵌套，也即可以将role赋予role。当尽量少用，因带来复杂性，很难确定赋予了哪些权限。
Object Privileges Through Roles and PL/SQL
PL/SQL程序中不能使用通过role获取的权限，必须直接赋权。
Selective Privilege Enablement
role和Privilege的区别在于，role可以选择性的启用。
grant app_role to user01;
ALTER USER user01 DEFAULT ROLE ALL EXCEPT app_role;
connect user01@service
select * from session_roles;
set role app_role;
select * from session_roles;
Selective Privilege Use Cases
- 仅通过应用访问时具有权限
- 根据应用和用户赋权，如web应用和报表应用
- 根据应用，用户，时间和地点
所有以上，都不是仅根据用户身份确定权限，都是根据上下文。这种需求可以用Selective Privilege实现，即有条件的启用或关闭权限(role)。
因此，role需要保护，而技术则是口令保护和Secure Application Role。
Password-Protected Roles
create role app_role identified by password;
set role app_role identified by password;
-- or
dbms_session.set_role
口令保护的role不能 用于proxy authentication。不常用。
Secure Application Roles
实现了口令保护role的功能，但更强大。只能由PL/SQL程序激活：
create role ... identified using proc
这里的PL/SQL程序必须以invoker rights创建，另一个是definer rights。
所谓invoker rights，是指以调用者的权限执行过程中代码，而非过程定义者的权限。
Secure Application Role Example
创建scott用户，赋予app_role，可以查询表hr.region，但默认不开启此角色。
connect sys/Welcome1@orclpdb1 as sysdba
create role app_role identified using priv_mgr_proc;
grant create session to scott identified by Welcome1;
grant app_role to scott;
grant select on hr.regions to app_role;
grant connect, resource to scott;
ALTER USER scott DEFAULT ROLE CONNECT, RESOURCE;
SYS用户创建过程：
CREATE OR REPLACE PROCEDURE PRIV_MGR_PROC
AUTHID CURRENT_USER
AS
BEGIN
IF (SYS_CONTEXT ('CONNECTION', 'LOCAL_OR_REMOTE') = 'LOCAL') THEN
DBMS_SESSION.SET_ROLE ('app_role');
END IF;
END;
/
GRANT EXECUTE ON priv_mgr_proc TO scott;
这里的CONNECTION namespace是应用自己设置的，你可以改为USERENV namespace中的其它属性。
SYS_CONTEXT的设置和读取参考这里
scott用户连接后，无法直接启用role，而必须通过过程启用，而过程又会检查其是否具备条件。简而言之，PL/SQL就是你的安全策略：
SQL> connect scott/Welcome1@orclpdb1
Connected.
SQL> select role from session_roles;
ROLE
--------------------------------------------------------------------------------
CONNECT
RESOURCE
SODA_APP
SQL> set role app_role;
set role app_role
*
ERROR at line 1:
ORA-28201: invalid command to enable secure application role 'APP_ROLE'
SQL> exec sys.priv_mgr_proc;
PL/SQL procedure successfully completed.
SQL> select role from session_roles;
ROLE
--------------------------------------------------------------------------------
CONNECT
RESOURCE
SODA_APP
这句说得好：
security model is only as good as the code that is written.
显然，过程是可以带参数的，但参数是可以伪造的，因此可考虑多用非参数，如时间，认证模式，环境变量等。
Global Roles and Enterprise Roles
Global Role和LDAP中的enterprise role对应。LDAP(Oracle EUS)可实现集中式的认证和授权。
Creating and Assigning Global and Enterprise Roles
global role在数据库中创建，但不能直接赋予用户和角色，而需要OEMCC和命令行实现：
SQL> CREATE ROLE app_a_global IDENTIFIED GLOBALLY;
Role created.
SQL> GRANT SELECT ON hr.regions TO app_a_global;
Grant succeeded.
SQL> GRANT app_a_global TO scott;
GRANT app_a_global TO scott
*
ERROR at line 1:
ORA-28021: cannot grant global roles
SQL> GRANT app_a_global TO DBA;
GRANT app_a_global TO DBA
*
ERROR at line 1:
ORA-28021: cannot grant global roles
Combining Standard and Global Roles
LDAP不知道数据库中授予global role的权限信息；数据库不知道end user的用户名。
需要几个步骤将这些信息关联起来：
- 确定哪些权限被赋予global role
- 确定LDAP中的enterprise role与global role的对应关系
- 确定哪些enterprise user关联到enterprise role
Oracle数据库提供ldapsearch可实现以上步骤。
Using Roles Wisely
Too Many Roles
不要将role赋予role，否则很难跟踪。
role是消耗内存的。
Naming
role的名称是全局的，因此也不能和用户名重。
role的名称要有意义，顺便也可以保持全局唯一性，如APP_X_DEVELOPER 和 APP_Y_ADMINISTRATOR。
Dependencies
role是SYS用户拥有的schema对象，因此删除用户，并不会删除role; role是存放在数据字典中的，而非用户的schema。
删除role只能通过drop role。
Summary
本章主要介绍role，Oracle中的role分为standard role, password enabled role, secure application role, global role。
role的好处在于简单，节省管理开销；以及可实现选择性权限启用。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
