邪恶八进制信息安全团队技术讨论组's Archiver

ring04h 2006-6-27 17:33

[转载]深入了解Oracle数据字典

<p>信息来源: 邪恶八进制信息安全团队</p><p>首先，Oracle的字典表和视图基本上可以分为三个层次。 </p><p /><p /><div id="2"> <strong>1.1 X$表</strong></div><p /><p /><div id="3"> 这一部分表是Oracle数据库的运行基础，在数据库启动时由Oracle应用程序动态创建。</div><p /><p /><div id="4"> 这部分表对数据库来说至关重要，所以Oracle不允许SYSDBA之外的用户直接访问，显示授权不被允许。</div><p /><p /><div id="5"> 如果显示授权你会收到如下错误:</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="6">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">grant</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> x$ksppi </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> eygle;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">grant</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> x$ksppi </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> eygle
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ERROR at line </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">:
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ORA</span><span style="COLOR: #808080">-</span><span style="FONT-WEIGHT: bold; COLOR: #800000">02030</span><span style="COLOR: #000000">: can </span><span style="COLOR: #0000ff">only</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> fixed tables</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">views </span></div>
</div></pre></div><p /><div id="7"> <strong>1.2 GV$和V$视图</strong></div><p /><p /><div id="8"> 从Oracle8开始，GV$视图开始被引入，其含义为Global V$.</div><p /><p /><div id="9"> 除了一些特例以外，每个V$视图都有一个对应的GV$视图存在。</div><p /><p /><div id="10"> GV$视图的产生是为了满足OPS环境的需要，在OPS环境中，查询GV$视图返回所有实例信息，而每个V$视图基于GV$视图，增加了INST_ID列判断后建立，只包含当前连接实例信息。</div><p /><p /><div id="11"> 注意，每个V$视图都包含类似语句：</div><p /><p /><div id="12"> where inst_id = USERENV(’Instance’) </div><p /><p /><div id="13"> 用于限制返回当前实例信息。</div><p /><p /><div id="14"> 我们从GV$FIXED_TABLE和V$FIXED_TABLE开始</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="15">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">   SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> view_definition </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> v_$fixed_view_definition </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"> view_name</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">’V$FIXED_TABLE’;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    VIEW_DEFINITION
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">----------------------------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> NAME , </span><span style="COLOR: #ff00ff">OBJECT_ID</span><span style="COLOR: #000000"> , TYPE , TABLE_NUM </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> GV$FIXED_TABLE </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"> inst_id </span><span style="COLOR: #808080">=</span><span style="COLOR: #000000"> USERENV(’Instance’) </span></div>
</div></pre></div><p /><div id="16"> 这里我们看到V$FIXED_TABLE基于GV$FIXED_TABLE创建。</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="17">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">    SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> view_definition </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> v_$fixed_view_definition </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"> view_name</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">’GV$FIXED_TABLE’;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    VIEW_DEFINITION
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">----------------------------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> inst_id,kqftanam, kqftaobj, ’</span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000">’, indx </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> x$kqfta
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">union</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">all</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> inst_id,kqfvinam, kqfviobj, ’</span><span style="COLOR: #0000ff">VIEW</span><span style="COLOR: #000000">’, </span><span style="FONT-WEIGHT: bold; COLOR: #800000">65537</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> x$kqfvi
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">union</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">all</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> inst_id,kqfdtnam, kqfdtobj, ’</span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000">’, </span><span style="FONT-WEIGHT: bold; COLOR: #800000">65537</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> x$kqfdt </span></div>
</div></pre></div><p /><div id="18"> 这样我们找到了GV$FIXED_TABLE视图的创建语句，该视图基于X$表创建。</div><p /><p /><div id="19"> <strong>1.3 GV_$,V_$视图和V$,GV$同义词</strong></div><p /><p /><div id="20"> 这些视图是通过catalog.ql创建。</div><p /><p /><div id="21"> 当catalog.sql运行时:</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="22">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">create</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">or</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">replace</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">view</span><span style="COLOR: #000000"> v_$fixed_table </span><span style="COLOR: #0000ff">as</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> v$fixed_table;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">create</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">or</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">replace</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">public</span><span style="COLOR: #000000"> synonym v$fixed_table </span><span style="COLOR: #0000ff">for</span><span style="COLOR: #000000"> v_$fixed_table;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">create</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">or</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">replace</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">view</span><span style="COLOR: #000000"> gv_$fixed_table </span><span style="COLOR: #0000ff">as</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> gv$fixed_table;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">create</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">or</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">replace</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">public</span><span style="COLOR: #000000"> synonym gv$fixed_table </span><span style="COLOR: #0000ff">for</span><span style="COLOR: #000000"> gv_$fixed_table; </span></div>
</div></pre></div><p /><div id="23"> 我们注意到，第一个视图V_$和GV_$首先被创建，v_$和gv_$两个视图。</div><p /><p /><div id="24"> 然后基于V_$视图的同义词被创建。</div><p /><p /><div id="25"> 所以，实际上通常我们访问的V$视图，其实是指向V_$视图的同义词。</div><p /><p /><div id="26"> 而V_$视图是基于真正的V$视图(这个视图是基于X$表建立的)。</div><p /><p /><div id="27"> 而v$fixed_view_definition视图是我们研究Oracle对象关系的一个入口，仔细理解Oracle的数据字典机制，有助于深入了解和学习Oracle数据库知识。 </div>

页: [1]
© 1999-2008 EvilOctal Security Team