Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpsg\tdpsg_privileges012.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <title>Step 5: Create the PL/SQL Procedure to Set the Secure Application Role</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1" /> <meta name="date" content="2009-06-02T18:13:3Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content="Step 5: Create the PL/SQL Procedure to Set the Secure Application Role" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E10575-01" /> <link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" /> <link rel="contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="prev" href="tdpsg_privileges011.htm" title="Previous" type="text/html" /> <link rel="next" href="tdpsg_privileges013.htm" title="Next" type="text/html" /> <script src="./callback.js" type="text/javascript"></script> <noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript> </head> <body> <div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr valign="bottom"> <td align="left"></td> <td align="center"><a href="tdpsg_privileges011.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpsg_privileges013.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CIHIIJCC" name="CIHIIJCC"></a><a id="TDPSG33238" name="TDPSG33238"></a></p> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h1>Step 5: Create the PL/SQL Procedure to Set the Secure Application Role</h1> <a name="BEGIN" id="BEGIN"></a> <p>Now, you are ready to create the secure application role procedure. In most cases, you create a package to hold the procedure, but because this is a simple tutorial that requires only one secure application role test (as defined in the procedure), you will create a procedure by itself. If you want to have a series of procedures to test for the role, create them in a package.</p> <p>A PL/SQL package defines a simple, clear interface to a set of related procedures and types that can be accessed by SQL statements. Packages also make code more reusable and easier to maintain. The advantage here for secure application roles is that you can create a group of security policies that used together present a solid security strategy designed to protect your applications. For users (or potential intruders) who fail the security policies, you can add auditing checks to the package to record the failure.</p> <p class="orderedlisttitle">To create the secure application role procedure: </p> <ol> <li> <p>In SQL*Plus, connect as user <code>sec_admin</code>.</p> <pre xml:space="preserve" class="oac_no_warn"> CONNECT sec_admin Enter password: <span class="italic">password</span> </pre></li> <li> <p>Enter the following <code>CREATE PROCEDURE</code> statement to create the secure application role procedure. (You can copy and paste this text by positioning the cursor at the start of <code>CREATE OR REPLACE</code>.)</p> <div class="inftblsimple"> <table class="Simple" title="secure application role example" summary="secure application role example, line numbers and code, listed left to right" dir="ltr" width="100%"> <col width="5%" /> <col width="5%" /> <col width="*" /> <tbody> <tr align="left" valign="top"> <td align="left"><br /></td> <td align="left"> <pre xml:space="preserve" class="oac_no_warn"> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 </pre></td> <td align="left"> <pre xml:space="preserve" class="oac_no_warn"> CREATE OR REPLACE procedure sec_roles AUTHID CURRENT_USER AS v_user varchar2(50); v_manager_id number :=1; BEGIN v_user := lower((sys_context ('userenv','session_user'))); SELECT manager_id INTO v_manager_id FROM hr.hr_verify WHERE lower(email)=v_user; IF v_manager_id = 100 THEN EXECUTE IMMEDIATE 'SET ROLE employee_role'; ELSE NULL; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_manager_id:=0; DBMS_OUTPUT.PUT_LINE(v_manager_id); END; / </pre></td> </tr> </tbody> </table> <br /></div> <!-- class="inftblsimple" --> <p>In this example:</p> <ul> <li> <p><span class="bold">Line 1</span>: Appends the <code>AUTHID CURRENT_USER</code> clause to the <code>CREATE PROCEDURE</code> statement, which creates the procedure using invoker's rights. <a id="sthref236" name="sthref236"></a><a id="sthref237" name="sthref237"></a><a id="sthref238" name="sthref238"></a>The <code>AUTHID CURRENT_USER</code> clause creates the package using invoker's rights, using the privileges of the current user.</p> <p>You <span class="italic">must</span> create the package using invoker's rights for the package to work. Invoker's rights allow the user to have <code>EXECUTE</code> privileges on all objects that the package accesses.</p> <p>Roles that are enabled inside an invoker's right procedure remain in effect even after the procedure exits, but after the user exits the session, he or she no longer has the privileges associated with the secure application role. In this case, you can have a dedicated procedure that enables the role for the rest of the session.</p> <p>Because users cannot change the security domain inside definer's rights procedures, secure application roles can only be enabled inside invoker's rights procedures.</p> <p>See <a href="tdpsg_privileges006.htm#BABCDIBA">"About Secure Application Roles"</a> for information about the importance of creating the procedure using invoker's rights.</p> </li> <li> <p><span class="bold">Line3</span>: Declares the <code>v_user</code> variable, which will store the user session information.</p> </li> <li> <p><span class="bold">Line 4</span>: Declares the <code>v_manager_id</code> variable, which will store the manager's ID of the <code>v_user</code> user.</p> </li> <li> <p><span class="bold">Line 6</span>: Retrieves the user session information for the user logging on, in this case, Matthew or Winston. To retrieve user session information, use the <code><a id="sthref239" name="sthref239"></a><a id="sthref240" name="sthref240"></a></code><code>SYS_CONTEXT</code> SQL function with the <code>USERENV</code> namespace attributes ('<code>userenv</code>', <code><span class="codeinlineitalic">session_attribute</span></code>), and the write this information to the <code>v_user</code> variable.</p> <p>The information returned by this function indicates the way in which the user was authenticated, the IP address of the client, and whether the user connected through a proxy. See <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF06117','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a> for more information about <code>SYS_CONTEXT</code>.</p> </li> <li> <p><span class="bold">Lines 7–8</span>: Get the manager's ID of the current user. The <code>SELECT</code> statement copies the manager ID into the <code>v_manager_id</code> variable, and then checking the <code>HR.HR_VERIFY</code> view for the manager ID of the current user. This example uses the employees' e-mail addresses because they are the same as their user names.</p> </li> <li> <p><span class="bold">Lines 9–13</span>: Use an <code>IF</code> condition to test whether the user should be granted the <code>sec_roles</code> role. In this case, the test condition is whether the user reports to Matthew's manager, Steven King, whose employee number is 100. If the user reports to King, as Matthew does, then the secure application role is granted to the user. Otherwise, the role is not granted.</p> <p>The result is that the secure application role will grant Matthew Weiss the role because he is a direct report of Steven King, but will deny the role to Winston, because he is not a direct report of Steven King.</p> </li> <li> <p><span class="bold">Lines 10–12</span>: Within the <code>IF</code> condition, the <code>THEN</code> condition grants the role by executing immediately the <code>SET ROLE</code> statement. Otherwise, its <code>ELSE</code> condition denies the grant.</p> </li> <li> <p><span class="bold">Lines 14–15</span>: Use an <a id="sthref241" name="sthref241"></a><a id="sthref242" name="sthref242"></a><code>EXCEPTION</code> statement to set <code>v_manager_id</code> to <code>0</code> if no data is found.</p> </li> <li> <p><span class="bold">Line 16</span>: Copies the manager's ID into a buffer so that it is readily available.</p> </li> </ul> <div class="helpinfonote"> <p><span class="bold">Tip: </span>If you have problems creating or running <a id="sthref243" name="sthref243"></a><a id="sthref244" name="sthref244"></a><a id="sthref245" name="sthref245"></a>PL/SQL code, check the Oracle Database trace files. The <code>USER_DUMP_DEST</code> initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing <code>SHOW PARAMETER USER_DUMP_DEST</code> in SQL*Plus. See <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=PFGRF010','newWindow').focus()"><span class="italic">Oracle Database Performance Tuning Guide</span></a> for more information about trace files.</p> </div> </li> </ol> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="tdpsg_privileges006.htm#BABCDIBA">About Secure Application Roles</a></p> </div> </div> <!-- class="sect3" --> <!-- Start Footer --> <div class="footer"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr> <td align="left"><span class="copyrightlogo">Copyright © 2006, 2009, Oracle and/or its affiliates. All rights reserved.</span><br /> <a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td> <td align="center"><a href="tdpsg_privileges011.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpsg_privileges013.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de