oracledatabase12g

Essential Oracle Security Internal For DBA(V1.0) 刘相兵(Maclean Liu)liu.maclean@gmail.com www.oracledatabase12g.com

介绍允许或禁止Oracle DB中的用户行为，包括其中的对象通过以下实现：登录身份验证(Authentication)，连接到数据库访问控制，访问模式对象和数据(access control) 审计，记录用户行为(audit) www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

基础身份验证 数据库管理员(以SYSDBA/SYSOPER)身份在DB之外被身份验证操作系统身份验证密码文件身份验证举例来说 sqlplus “/ as sysdba” 登录，OS用户在Unix上为DBA组用户，在Windows上是ORADBA组用户普通数据库用户只能在数据库启动(alter database open)后身份验证并等登录也可以采用OS 身份验证例如: create user maclean identified externally . www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

基础身份验证 数据库身份认证例如：　create user maclean identified by oracle; 可以通过数据字典视图来查看用户信息 DBA_USERS describes all users of the database. ALL_USERS Lists users visible to the current user, but does not describe them USER_TS_QUOTAS Describes tablespace quotas for users V$SESSION Lists session information for each current session, includes user name PROXY_USERS Describes users who can assume the identity of other users V$PWFILE_USERS lists users granted SYSDBA and SYSOPER privileges as derived from the password file www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

访问控制 对象级别的安全（最小权限原则） • 通过对象权限 • 通过角色 • 数据级别的安全(细粒度访问控制) • -通过RLS(Row Level Security) www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

对象级别的安全控制 • 将自身拥有对象的权限显示地授权给其他用户，包括查询和修改数据 • 举例来说： CONN MACLEAN/ORACLE • GRANT SELECT ON wallet to hanna; • 角色(roles)是一组已被命名的权限，可以直接授权给用户或者其他角色: • 举例来说： CREATE ROLE developer; • GRANT SELECT ON wallet1 to developer; • GRANT INSERT ON wallet1 to developer; • GRANT role1 to hanna; www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

对象级别的安全控制 • 内核函数Kzpchkbu()负责完成为给定用户检查某个对象上权限的任务。该函数可能被多种路径调用，以检查对象上的必要权限。 • 大致的算法如下： • If 检查需要被授权的用户是否对象的拥有者 • 则返回授权验证成功(表示不需要做权限检查) • Else 该对象权限是否被授予了 PUBLIC • 若是，则返回授权验证成功 • Else 检查该用户是否被显示地授予了该对象权限或角色 • 若是，则返回授权验证成功 • Else 检查该用户是否被显示地授予了对应的系统权限 • 若是，则返回授权验证成功 • 否则报错，ORA_01031,ORA-00942 www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

对象级别的安全控制 • 普通用户访问 SYS schema下的对象？ (越来越困难！) • 从9i开始，’ANY’权限无法访问SYS用户对象 • 默认O7_DICTIONARY_ACCESSIBILITY=false ，设置为TRUE可以让’ANY’权限访问SYS对象 • 否则普通用户必须显示地拥有SYS对象的权限。 www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

对象级别的安全控制 • 常用数据字典视图，帮助了解对象和系统权限的信息： • - DBA_SYS_PRIVS describes system privileges granted to users and roles (USER_SYS_PRIVS for connected user). • - SESSION_PRIVS lists the privileges that are currently available to the user. • - SESSION_ROLES lists the roles that are currently enabled to the user. • - DBA_TAB_PRIVS describes all object grants in the database. (USER_TAB_PRIVS for connected user). www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

数据级别的安全(RLS/VPD) Virtual Private Database(VPD)有时候也叫做Fine Grained Access Control (FGAC)，亦即Row Level Security (RLS)，在 Oracle 8i中被引入；由于该特性是基于实际的数据内容而非数据库对象，因此被叫做RLS。仅在discretionary access control (DAC) 满足的情况下RLS生效，例如user1尝试访问user2所拥有的存在RLS policy的表，前提是在user2的表上有SELECT权限其内部工作原理是透明地将SQL语句修改成基于预定义准则的临时视图。在运行时，谓词会被附加到原查询上以便过滤查询所能看到的数据 www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

数据级别的安全(RLS/VPD) 通过Oracle提供的标准 DBMS_RLSPackage的过程来将表/视图/同义词等对象和策略关联起来 RLS策略包含一个PL/SQL函数以返回谓词串，这个谓词串会被在语句被执行前被加入到查询条件中例如： : CONNECT scott/tiger create table t1 (c1 int); insert into t1 values (10); insert into t1 values (10); insert into t1 values (20); insert into t1 values (30); commit; www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

数据级别的安全(RLS/VPD) CREATE OR REPLACE FUNCTION func1 (schema_name VARCHAR2, table_name VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN 'c1 = 10'; END; / SQL> EXEC DBMS_RLS.ADD_POLICY ('scott','t1','pol1','scott','func1'); PL/SQL procedure successfully completed. SQL> select * from t1; C1 ---------- 10 10 www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

数据级别的安全(RLS/VPD) 内核函数kzrtevw()完成为存在RLS policy的表/视图/同义词创建临时视图的工作在语义解析阶段，从数据字典层kkmfcblo()调用kzrtevw() 一个查询语句” select * from maclean” 在语义解析阶段被装换为 Select * from (select * from maclean where t1=10); ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^  临时视图 kzrtevw()生成的临时视图会再次被硬解析hard parse www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

数据级别的安全(RLS/VPD) 若存在参考完整性约束例如一张启用了RLS Policy的子表上有外键约束，RLS机制会检查相关的父表上是否有RLS Policy以判断是否真的可以从父表上读取数据以验证约束。这通过内核函数kzrtppg() 完成，若无法从父表读取到数据，则报错ORA-28117。 [oracle@vrh8 ~]$ oerr ora 28117 28117, 00000, "integrity constraint violated - parent record not found" // *Cause: try to update/insert a child record with new foreign key // values, but the corresponding parent row is not visible // because of fine-grained security in the parent. // *Action: make sure that the updated foreign key values must also visible in the parent www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

数据级别的安全(RLS/VPD) SYS对任何行级安全策略(RLS)均享有豁免权可以通过系统权限 “EXEMPT ACCESS POLICY”让普通用户也对RLS Policy豁免 RLS policies相关的一些有用字典视图： ALL_POLICIES describes the security policies on the synonyms, tables, and views accessible to the current user. DBA_POLICIES describes all security policies in the database. USER_POLICIES describes the security policies on the synonyms, tables, and views owned by the current user. www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 在部署安全措施后仍有发生恶意数据库行为的可能性审计和记录用户行为可以发现各种可疑的或伪装的恶意行为有助于进一步加强安全措施 www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 Audit审计的种类强制审计：为每一次实例启动写出审计记录到OS文件，shutdown以及权限登录的记录存放在$ORACLE_HOME/rdbms/audit 目录下(注意定期清理哦，亲！) SYS审计:记录SYSDBA/SYSOPER等权限用户的操作，审计记录存放在OS 文件，SYSLOG中。标准审计：记录用户针对数据库对象、语句、权限级别的行为。审计记录可以存放在OS文件、XML文件或数据库中(AUD$基表) • 对象级别审计 • 权限级别审计 • 语句级别审计细粒度控制：基于用户访问的数据记录用户行为。审计记录存放在数据库内(FGA_LOG$)或者XML文件中。 www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 示例审计文件: Audit file /s01/admin/G10R25/adump/g10r25_ora_3724_1.aud Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /s01/oracle/product/10.2.0.5/db_1 System name: Linux Node name: vrh8.oracle.com Release: 2.6.32-200.13.1.el5uek Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011 Machine: x86_64 Instance name: G10R25 Redo thread mounted by this instance: 1 Oracle process number: 15 Unix process pid: 3724, image: oracle@vrh8.oracle.com (TNS V1-V3) Sat Jul 7 02:29:41 2012 LENGTH : '160' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2652277393' Sat Jul 7 02:29:42 2012 LENGTH : '173' ACTION :[19] 'ALTER DATABASE OPEN' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2652277393' Sat Jul 7 02:29:46 2012 LENGTH : '172' ACTION :[18] 'select * from dual' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2652277393' www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 内核函数Kzasydmp()为强制的SYSDBA/SYSOPER审计写出审计记录到OS 文件、SYSLOG或者XML文件在windows系统上，打印审计记录到EventLog(DB_User, OS_Privilege, Client_User, Client_Termninal, Status, SQL_Text) 在Unix平台上若设置了AUDIT_SYSLOG_LEVEL，审计记录发送给syslog这个后台服务否则生成一个审计文件<program_code>_<OS_processid>.aud www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 对象级别的审计例如： AUDIT SELECT ON MACLEAN.TEST; 语句级别的审计例如：AUDIT CREATE TABLE BY MACLEAN; 权限级别的审计例如：AUDIT SELECT ANY TABLE BY MACLEAN; www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 部分标准审计选项： AUDIT BY SESSION—针对用户和会话例如:AUDIT SELECT ON MACLEAN.TAB BY SESSION; AUDIT BY ACCESS—针对每一个可审计的操作例如： AUDIT SELECT ON MACLEAN.TAB BY ACCESS; AUDIT WHENEVER SUCCESSFUL—仅审计执行成功的操作例如： AUDIT CONNECT WHENEVER SUCCESSFUL; Audit WHENEVER NOT SUCCESSFUL—仅审计执行失败的操作例如： AUDIT CONNECT WHENEVER NOT SUCCESSFUL www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 细粒度审计Fine Grained Auditing (FGA) FGA 策略通过DBMS_FGA包与表/视图/同义词关联起来例如：begin DBMS_FGA.ADD_POLICY(object_schema => 'scott', object_name => 'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column => 'comm,sal', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types => 'INSERT, UPDATE', audit_trail => DBMS_FGA.XML + DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS); end; www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 标准审计： audsucc()/audfail()是审计的主要入口，针对成功/不成功的审计操作会进一步调用auddft() 例如 maclean用户下的test表为成功操作审计 … -> opiexe() -> audsucc() -> auddft() -> audsel() -> audfro() … auddft()判断行为代码决定合适的审计路径 audsel()调用audfro()，记录审计链上的信息 audfro()首先设置已使用的对象权限，进一步检查该对象相关的审计选项，例如到底这个对象是audit by access 还是by session。 By access 调用audins()， By session调用audses() www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

Audit审计记录用户行为 启动审计必要的Init.ora实例初始化参数 AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }. AUDIT_SYS_OPERATIONS Oracle 9i以后版本中通过设置该参数为TURE可以记录不限于CONNECT,STARTUP,SHUTDOWN的以 SYSDBA或SYSOPER进行的操作。 AUDIT_FILE_DEST 指定审计目录(默认为$ORACLE_BASE/admin/$SID/adump) 一些有用的字典视图： DBA_AUDIT_POLICIES – Lists FGA policies in the database. DBA_AUDIT_TRAIL – Lists all audit trail entries. DBA_AUDIT_OBJECT - Lists audit trail records for all objects in the database. DBA_FGA_AUDIT_TRAIL - Lists all audit records for fine-grained auditing. DBA_COMMON_AUDIT_TRAIL - Lists all standard and fine-grained audit trail entries, mandatory and SYS audit records written in XML format. www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com www.oracledatabase12g.com

oracledatabase12g

oracledatabase12g

Presentation Transcript

oracledatabase12g