chap8_4.html

来自「Oracle资料大集合」· HTML 代码 · 共 382 行 · 第 1/2 页

<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.operatingsystems.html"><font face="verdana,helvetica" size="1" color="000000"><b>Operating Systems</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.userinterfaces.html"><font face="verdana,helvetica" size="1" color="000000"><b>User Interfaces</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware &amp; Collaboration</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>

<b><font size="+1"><B>	8.4	How do I匛nable and disable roles at runtime?</B></font></b>
<p>
<B>Problem</B>
<p>
Within my application, I control the information the users can view. With the availability of easy-to-use ad hoc query tools, the users of my applications can query and modify data from the database, but I cannot remove their user accounts because they are valid users of the application. How do I restrict access by enabling and disabling roles at runtime?
<p>
<B>Technique</B>
<p>
Password-protected roles are the key to restricting the access of ad hoc query tools. Access can be restricted at two levels. Ad hoc tools can be restricted from modifying data or totally restricted from viewing data. Applications using the tables must embed a SET ROLE statement or call the DBMS_SESSION.SET_ROLE procedure after connecting to the database. The password of the role must also be kept secret from the users of the application. Without the password of the role and the method for enabling it, users will have restricted access to the data.
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. Run the CHP8_12.SQL script, shown in <A HREF="image/08oht12.gif" TARGET="_blank">Figure 8.12</A>, to create a sample table and users that will be restricted by the technique presented in this How-To.
<p>
<B>2.</B>	Create the roles that control access to the data by running CHP8_13.SQL, as shown in <A HREF="image/08oht13.gif" TARGET="_blank">Figure 8.13</A>.
<p>
<B>3.</B>	The CHP8_13.SQL script assumes that ad hoc query tools are only restricted from modifying data, and so it creates two roles. The first is a default role with SELECT privileges in which users can query information without enabling additional roles. A second role provides all the privileges required to use the approved applications. The second role is a non-default, password-protected role, which must be enabled by the application to be used. If ad hoc query tools are totally restricted from the data, only create the password-protected role.
<p>
<B>4.</B>	Grant privileges on the database objects to the roles by running CHP8_14.SQL, as shown in <A HREF="image/08oht14.gif" TARGET="_blank">Figure 8.14</A>.
<p>
<B>5.</B>	Now, grant the role privileges to the users of the application. In the example, MARY is the only user of the application. If two roles are created in Step 2, grant both roles to the users.

<p>
<code><b>SQL>  GRANT SELECT_ROLE, UPDATE_ROLE TO MARY;

<p>

Grant succeeded.</b></code>

<p>
<B>6.</B>	Remove the role with all privileges from the user account抯 default roles by running CHP8_15.SQL. This forces the user to enable the role in order to use it.
<p>
<code><b>SQL>  GET CHP8_15.sql
<p>
1  ALTER USER MARY
<p>
DEFAULT ROLE ALL EXCEPT UPDATE_ROLE;
<p>
User altered.
<p>
SQL></b></code>
<p>
<B>7.</B>	Now insert program code to enable the role into applications capable of modifying data. The technique used to enable password-protected roles at runtime is to call the DBMS_SESSION.SET_ROLE procedure, whose syntax is shown in <A HREF="image/08oht15.gif" TARGET="_blank">Figure 8.15</A>.
<p>
To enable the user to update the DEPT8 table from within a program, for example, insert the code from CHP8_16.SQL, as shown in <A HREF="image/08oht16.gif" TARGET="_blank">Figure 8.16</A>. The SET ROLE procedure in the DBMS_SESSION package enables PL/SQL statements to execute a SET ROLE statement. Executing the SET ROLE statement directly in PL/SQL causes an error.
<p>
<B>How It Works</B>
<p>
Step 1 creates a sample user account and table used throughout this How-To. Step 2 creates the roles used to restrict access to the database. Step 3 grants the privileges required on the database objects to the roles created in Step 2. Step 4 grants the users of the system the role privileges. Step 5 removes the password-protected role as the default from system users. Step 6 is where code would be inserted into applications that need to use the restricted table. The DBMS_SESSION.SET_ROLE procedure is used within applications to enable password-protected roles.
<p>
<B>Comments</B>
<p>
Password-protected roles can be used to restrict access from ad hoc query tools. They can be used to disable INSERT, UPDATE, and DELETE privileges or to disable all privileges. When SELECT privileges are restricted through password-protected roles, side effects can occur. You must be very careful how you develop your applications when restricting the SELECT privilege through roles. Some development tools look at the database before running their first statement.
<p>
For example, Oracle Reports interprets the code in PL/SQL formatting triggers before executing the first statement of code. The SET ROLE procedure must be run before this occurs, but unfortunately, it cannot. If you are restricting SELECT privileges with the roles, you cannot use PL/SQL format triggers in Oracle Reports. You must also specify all the roles that should be enabled within a single SET ROLE statement. Otherwise, just the roles specified are enabled, and no others.

<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">
<AREA SHAPE=RECT COORDS="62,0,135,26" HREF="http://www.developer.com/about/">
<AREA SHAPE=RECT COORDS="135,0,199,26" HREF="http://www.itlibrary.com/search/">
<AREA SHAPE=RECT COORDS="200,0,274,26" HREF="http://www.itlibrary.com/subscribe/">
<AREA SHAPE=RECT COORDS="275,0,335,25" HREF="http://www.developer.com/contact/adinfo.html">
<AREA SHAPE=RECT COORDS="335,0,417,25" HREF="http://www.developer.com/contact/">
<AREA SHAPE=RECT COORDS="418,0,467,26" HREF="http://www.developer.com/about/faq.html">
</MAP>

<P>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<TD WIDTH="130"></TD>
<td width="468">
<IMG SRC="../../../images/footer/footerfile.gif" ALT="footer nav" width="467" height="26" BORDER="0" usemap="#footer" ismap>
</td>
</tr>
<tr>
<TD WIDTH="130"></TD>
<td width="468"> 
<font face=arial,helvetica size="1"> Use of this site is subject certain <a href="http://www.developer.com/legal/">Terms &amp; Conditions.</a><br>
Copyright (c) 1996-1999 <A HREF="http://www.earthweb.com/">EarthWeb, Inc.</A>.  All rights reserved.  Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
<a href="http://www.earthweb.com/corporate/privacy.html">Please read our privacy policy for details.</a>
</td>
</tr>
</table>

</BODY>
</HTML>