oracle10g系统管理之权限管理

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">权限（</span><span lang="EN-US"><span style="font-family: Times New Roman;">Privilege</span></span><span style="">）是执行特定类型</span><span lang="EN-US"><span style="font-family: Times New Roman;">SQL</span></span><span style="">命令或访问其它方案对象的权利。权限包括系统权限和对象权限两种类型。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">系统权限（</span><span lang="EN-US"><span style="font-family: Times New Roman;">System Privilege</span></span><span style="">）是执行特定类型</span><span lang="EN-US"><span style="font-family: Times New Roman;">SQL</span></span><span style="">命令的权利。它用于控制用户可以执行的一个或一组数据库操作，例如，当用户具有</span><span lang="EN-US"><span style="font-family: Times New Roman;">CREATE TABLE</span></span><span style="">权限时，可以在其方案中建表；当用户具有</span><span lang="EN-US"><span style="font-family: Times New Roman;">CREATE ANY TABLE</span></span><span style="">时，可以在任何方案中建表。</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">提供了</span><span lang="EN-US"><span style="font-family: Times New Roman;">100</span></span><span style="">多种系统权限。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">常用的系统权限：</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;"></span></span></p>
<table class="MsoTableGrid" style="margin: auto auto auto 32.4pt; width: 378pt; border-collapse: collapse;" border="1" cellspacing="0" cellpadding="0" width="504"><tbody>
<tr style="">
<td style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">系统权限</span></span></p>
</td>
<td style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">作用</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE SESSION</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">连接到数据库</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE TABLE</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建表</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE VIEW</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建立视图</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE PUBLIC SYNONYM</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建立同义词</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE SEQUENCE</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建立序列</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE PROCEDURE</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建立存储过程</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE TRIGGER</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建立触发器</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE CLUSTER</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建立簇</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE TYPE</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建立对象类型</span></span></p>
</td>
</tr>
<tr style="">
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; width: 153pt; padding-top: 0cm; background-color: transparent;" width="204" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE DATABASE LINK</span></span></p>
</td>
<td style="padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 225pt; padding-top: 0cm; background-color: transparent;" width="300" valign="top">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style=""><span style="font-size: small;">建立数据库链</span></span></p>
</td>
</tr>
</tbody></table>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;"></span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">另外</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">还提供了一类</span><span lang="EN-US"><span style="font-family: Times New Roman;">ANY</span></span><span style="">系统权限，当用户具有该类系统权限时，可以在任何方案中执行相应操作。例如，如果用户具有</span><span lang="EN-US"><span style="font-family: Times New Roman;">SELECT ANY TABLE</span></span><span style="">系统权限，那么用户可以查询任何方案的表。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">4.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">对象权限是指访问其它方案对象的权利，它用户控制用户对其他方案的对象的访问。如果用户</span><span lang="EN-US"><span style="font-family: Times New Roman;">SMITH</span></span><span style="">要访问</span><span lang="EN-US"><span style="font-family: Times New Roman;">SCOTT.EMP</span></span><span style="">表（</span><span lang="EN-US"><span style="font-family: Times New Roman;">SCOTT</span></span><span style="">：方案，</span><span lang="EN-US"><span style="font-family: Times New Roman;">EMP</span></span><span style="">：表），则必须在</span><span lang="EN-US"><span style="font-family: Times New Roman;">SCOTT.EMP</span></span><span style="">表上具有对象权限。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">5.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">管理系统权限</span><span style="font-family: Times New Roman;"> </span><span style="">系统权限时执行特定类型</span><span lang="EN-US"><span style="font-family: Times New Roman;">SQL</span></span><span style="">语句的权限。需要注意的是</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">数据库没有</span><span lang="EN-US"><span style="font-family: Times New Roman;">CREATE INDEX</span></span><span style="">、修改数据库对象、删除数据库对象等系统权限。言外之意就是如果用户可以创建数据库对象，用户就可以修改数据库对象。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">6.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">授予系统权限</span><span style="font-family: Times New Roman;"> </span><span style="">一般而言授予系统权限是由</span><span lang="EN-US"><span style="font-family: Times New Roman;">DBA</span></span><span style="">完成的。如果其他用户要授予系统权限，则要求该用户必须具有</span><span lang="EN-US"><span style="font-family: Times New Roman;">GRANT ANY PRIVILEGE</span></span><span style="">系统权限。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">7.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">授予用户</span><span lang="EN-US"><span style="font-family: Times New Roman;">blake</span></span><span style="">系统权限</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">以</span><span lang="EN-US"><span style="font-family: Times New Roman;">DBA</span></span><span style="">用户身份登录，为</span><span lang="EN-US"><span style="font-family: Times New Roman;">BLAKE</span></span><span style="">用户授予</span><span lang="EN-US"><span style="font-family: Times New Roman;">CREATE SESSION</span></span><span style="">，</span><span lang="EN-US"><span style="font-family: Times New Roman;">CREATE TABLE</span></span><span style="">和</span><span lang="EN-US"><span style="font-family: Times New Roman;">CREATE VIEW </span></span><span style="">权限，并且前两个权限带有</span><span lang="EN-US"><span style="font-family: Times New Roman;">WITH ADMIN OPTION</span></span><span style="">选项（可以将该权限授予其他角色或者用户）。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span lang="EN-US"><span style="font-family: Times New Roman;">GRANT CREATE SESSION</span></span><span style="">，</span><span lang="EN-US"><span style="font-family: Times New Roman;">CREATE TABLE TO blake</span></span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">WITH ADMIN OPTION</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">GRANT CREATE VIEW TO blake</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">8.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">管理对象权限</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">对象权限是指访问其他用户方案对象的权限。在</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">数据库中，用户可以直接访问其方案对象，但如果要访问其他用户的方案对象时，必须具有相应的对象权限。对象权限通常包括：</span><span lang="EN-US"><span style="font-family: Times New Roman;">ALTER<span style=""> </span>DELETE<span style=""> </span>INDEX<span style=""> </span>INSERT<span style=""> </span>REFERENCES<span style=""> </span>SELECT<span style=""> </span>UPDATE</span></span><span style="">等</span><span lang="EN-US"><span style="font-family: Times New Roman;">8</span></span><span style="">种。默认情况下，直接授予用户权限时，将访问所有列的权限都授予用户。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style=""><span style="font-size: small;">默认情况下，如果授予另外一个用户对象权限，这该用户默认拥有该对象所有列的权限。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">9.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">授予</span><span lang="EN-US"><span style="font-family: Times New Roman;">ALTER</span></span><span style="">权限</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">如果用户在其他方案的表和序列上执行</span><span lang="EN-US"><span style="font-family: Times New Roman;">ALTER TABLE</span></span><span style="">或</span><span lang="EN-US"><span style="font-family: Times New Roman;">ALTER SEQUENCE</span></span><span style="">命令，必须具有</span><span lang="EN-US"><span style="font-family: Times New Roman;">ALTER</span></span><span style="">对象权限。例如，如果用户</span><span lang="EN-US"><span style="font-family: Times New Roman;">BLAKE</span></span><span style="">需要修改表</span><span lang="EN-US"><span style="font-family: Times New Roman;">SCOTT.EMP</span></span><span style="">的结构，则必须为其授予</span><span lang="EN-US"><span style="font-family: Times New Roman;">ALTER</span></span><span style="">对象权限。示例如下：</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span lang="EN-US"><span style="font-family: Times New Roman;">GRANT ALTER ON emp TO blake</span></span><span style="">；</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">10.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">授予</span><span lang="EN-US"><span style="font-family: Times New Roman;">EXECUTE</span></span><span style="">权限</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">如果用户要执行其他方案的包、过程和函数，必须在包、过程和函数上具有</span><span lang="EN-US"><span style="font-family: Times New Roman;">EXECUTE</span></span><span style="">对象权限。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">11.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">授予列权限</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">列权限用于控制用户对其他方案对象列的访问，只能在</span><span lang="EN-US"><span style="font-family: Times New Roman;">INSERT</span></span><span style="">、</span><span lang="EN-US"><span style="font-family: Times New Roman;">UPDATE</span></span><span style="">和</span><span lang="EN-US"><span style="font-family: Times New Roman;">REFERENCES</span></span><span style="">上授予列权限。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span lang="EN-US"><span style="font-family: Times New Roman;">GRANT UPDATE</span></span><span style="">（</span><span lang="EN-US"><span style="font-family: Times New Roman;">sal</span></span><span style="">）</span><span lang="EN-US"><span style="font-family: Times New Roman;">ON emp TO blake</span></span><span style="">；</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">12.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">使用</span><span lang="EN-US"><span style="font-family: Times New Roman;">WITH GRANT OPTION</span></span><span style="">选项。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span lang="EN-US"><span style="font-family: Times New Roman;">WITH GRANT OPTION</span></span><span style="">选项用于转授对象权限，该选项只能用于用户，不能授予角色。</span></span></p>