[20170329]还原用户口令信息.txt
--//上午在例行检查中发现发现sys.aud$增加异常.
col object_name format a40
col owner format a40
prompt 日期1 日期2 显示行数
SELECT *
FROM ( SELECT dhso.OWNER
,dhso.object_name
,object_type
,SUM (db_block_changes_delta)
FROM dba_hist_seg_stat dhss
,dba_hist_seg_stat_obj dhso
,dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN '&1' AND '&2'
GROUP BY dhso.OWNER, dhso.object_name, object_type
HAVING SUM (db_block_changes_delta) > 0
ORDER BY SUM (db_block_changes_delta) DESC)
WHERE ROWNUM <= NVL (&3, 50);
--//检查发现 dbsnmp用户 出现ora-1017错误.估计使用EM12c导致,我从来不用这个监测分析数据库.
--//两种解决方法:1.调整EM12c设置.2.重新设置口令原来的设置.我选择第2种方式:
select * from DBA_AUDIT_SESSION where username='DBSNMP' and returncode=1017 order by timestamp;
--//昏检查才发现2016/5/31 9:29:06就出现这个问题,真不知什么维护的.涉及一些安全问题,在测试环境演示使用scott用户.
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//正常情况要restore出问题前的备份数据文件,在这里我直接当前数据文件.
SYS@book> column SPARE4 format a62
SYS@book> select rowid,name,password,spare4 from sys.user$ where name='SCOTT';
ROWID NAME PASSWORD SPARE4
------------------ -------------------- ------------------------------ --------------------------------------------------------------
AAAAAKAABAAAADVAAC SCOTT 0EDE56329E1D82EA S:C17C48017316A8CB83FA3E8B747CA2106F44B17D9496E45C19D20ED261FC
SYS@book> @ &r/rowid AAAAAKAABAAAADVAAC
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
10 1 213 2 0x4000D5 1,213 alter system dump datafile 1 block 213 ;
--//OBJECT=10,也可以通过bootstrap$定位.
SYS@book> select * from bootstrap$ where obj#=10;
LINE# OBJ# SQL_TEXT
---------- ---------- ------------------------------------------------------------
10 10 CREATE CLUSTER C_USER#("USER#" NUMBER) PCTFREE 10 PCTUSED 40
INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K M
INEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 10 EXT
ENTS (FILE 1 BLOCK 208)) SIZE 372
2.转储块:
SYS@book> alter system dump datafile 1 block 213 ;
System altered.
tab 1, row 2, @0x65d
tl: 171 fb: -CH-FL-- lb: 0x0 cc: 22 cki: 0
col 0: [ 5] 53 43 4f 54 54
col 1: [ 2] c1 02
col 2: [16] 30 45 44 45 35 36 33 32 39 45 31 44 38 32 45 41
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col 3: [ 2] c1 05
col 4: [ 2] c1 04
col 5: [ 7] 78 71 08 18 0d 05 16
col 6: [ 7] 78 75 02 04 0b 01 19
col 7: [ 7] 78 73 0b 18 0a 0e 1c
col 8: [ 7] 78 73 0b 18 0a 0e 1c
col 9: [ 1] 80
col 10: *NULL*
col 11: [ 2] c1 02
col 12: *NULL*
col 13: *NULL*
col 14: [ 1] 80
col 15: [ 1] 80
col 16: [22]
44 45 46 41 55 4c 54 5f 43 4f 4e 53 55 4d 45 52 5f 47 52 4f 55 50
col 17: *NULL*
col 18: [ 1] 80
col 19: *NULL*
col 20: *NULL*
col 21: [62]
~~~~~~~~~~~~
53 3a 43 31 37 43 34 38 30 31 37 33 31 36 41 38 43 42 38 33 46 41 33 45 38
42 37 34 37 43 41 32 31 30 36 46 34 34 42 31 37 44 39 34 39 36 45 34 35 43
31 39 44 32 30 45 44 32 36 31 46 43
--//col 2 ,col 21 就是口令信息.转换字符串就ok了.
3.使用bbed查询信息:
SYS@book> @ &r/bbedcol sys user$
DISPLAY BBED EXAMINE(X) FORMAT
C80
-------------------------
ncncnnttttncnnnnnccnnncct
BBED> x /rncncnnttttncnnnnnccnnncct dba 1,213 *kdbr[2]
rowdata[6755] @7813
-------------
flag@7813: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7814: 0x00
cols@7815: 1
kref@7816: 1
mref@7818: 1
hrid@7820:0x004000d5.2
nrid@7826:0x004000d5.2
col 0[2] @7832: 85
--//这个表在CLUSTER SYS.C_USER#(USER#);不能这样查询
BBED> p kdbr
sb2 kdbr[0] @114 8074
sb2 kdbr[1] @116 7976
sb2 kdbr[2] @118 7721
sb2 kdbr[3] @120 7469
sb2 kdbr[4] @122 7217
sb2 kdbr[5] @124 6965
sb2 kdbr[6] @126 6713
sb2 kdbr[7] @128 2205
sb2 kdbr[8] @130 1607
sb2 kdbr[9] @132 1198
sb2 kdbr[10] @134 -1
sb2 kdbr[11] @136 1122
sb2 kdbr[12] @138 1629
sb2 kdbr[13] @140 5045
sb2 kdbr[14] @142 5214
sb2 kdbr[15] @144 5552
sb2 kdbr[16] @146 5383
sb2 kdbr[17] @148 5721
sb2 kdbr[18] @150 5890
sb2 kdbr[19] @152 1220
sb2 kdbr[20] @154 966
BBED> x /rcncnnttttncnnnnnccnnncct *kdbr[12]
rowdata[663] @1721
------------
flag@1721: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@1722: 0x00
cols@1723: 22
col 0[5] @1725: SCOTT
col 1[2] @1731: 1
col 2[16] @1734: 0EDE56329E1D82EA
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col 3[2] @1751: 4
col 4[2] @1754: 3
col 5[7] @1757: 2013-08-24 12:04:21
col 6[7] @1765: 2017-02-04 10:00:24
col 7[7] @1773: 2015-11-24 09:13:27
col 8[7] @1781: 2015-11-24 09:13:27
col 9[1] @1789: 0
col 10[0] @1791: *NULL*
col 11[2] @1792: 1
col 12[0] @1795: *NULL*
col 13[0] @1796: *NULL*
col 14[1] @1797: 0
col 15[1] @1799: 0
col 16[22] @1801: DEFAULT_CONSUMER_GROUP
col 17[0] @1824: *NULL*
col 18[1] @1825: 0
col 19[0] @1827: *NULL*
col 20[0] @1828: *NULL*
col 21[62] @1829: S:C17C48017316A8CB83FA3E8B747CA2106F44B17D9496E45C19D20ED261FC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//这样就获得口令的加密串.如何改写呢?
SYS@book> select dbms_metadata.get_ddl( 'USER', 'SCOTT' ) c120 from dual;
C120
-----------------------------------------------------------------------------------------------------------------------------
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:C17C48017316A8CB83FA3E8B747CA2106F44B17D9496E45C19D20ED261FC;0EDE56329E1D82EA'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
--//很明显把2个口令串使用分号连接起来就ok了.修改口令.
SYS@book> password scott
Changing password for scott
New password:
Retype new password:
Password changed
SYS@book> select rowid,name,password,spare4 from sys.user$ where name='SCOTT';
ROWID NAME PASSWORD SPARE4
------------------ -------------------- ------------------------------ --------------------------------------------------------------
AAAAAKAABAAAADVAAC SCOTT B1205594A73D0549 S:AEB2CE95EA6BA72BC8DBFE32E79EA587C1593410A6DF46B6BF4038AF1FE9
--//口令已经更改.
SYS@book> alter USER "SCOTT" IDENTIFIED BY VALUES 'S:C17C48017316A8CB83FA3E8B747CA2106F44B17D9496E45C19D20ED261FC;0EDE56329E1D82EA' ;
User altered.
SYS@book> select rowid,name,password,spare4 from sys.user$ where name='SCOTT';
ROWID NAME PASSWORD SPARE4
------------------ -------------------- ------------------------------ --------------------------------------------------------------
AAAAAKAABAAAADVAAC SCOTT 0EDE56329E1D82EA S:C17C48017316A8CB83FA3E8B747CA2106F44B17D9496E45C19D20ED261FC
--//通过查询旧备份的数据库文件,然后拼接在执行就可以还原后来的口令.
4.复习一下oracle口令如何加密的.
--//SPARE4最后10位是slot. slot=9496E45C19D20ED261FC.
SYS@book> set serverout on
SYS@book> exec dbms_output.put_line('S:'||dbms_crypto.hash(utl_raw.cast_to_raw('book')||'9496E45C19D20ED261FC',dbms_crypto.HASH_SH1)||'9496E45C19D20ED261FC');
S:C17C48017316A8CB83FA3E8B747CA2106F44B17D9496E45C19D20ED261FC
PL/SQL procedure successfully completed.
--//可以发现能对上spare4的信息.
--//如果使用openssl命令:
$ ((echo -n book | xxd -c 16| cut -c1-18) ;echo -n ' 9496 E45C 19D2 0ED2 61FC' )| tr -d '\n'| cut -c1-53 | xxd -c32 -r |openssl dgst -sha1 | tr 'a-z' 'A-Z'
C17C48017316A8CB83FA3E8B747CA2106F44B17D
--//前面加S:,后面加SLOT=9496E45C19D20ED261FC
S:C17C48017316A8CB83FA3E8B747CA2106F44B17D9496E45C19D20ED261FC
--//可以发现能对上spare4的信息.
