024 - Invoker Rights for Dynamic SQL <p><script> </script><span></span></p> <table width="100%" cellpadding="0" cellspacing="0" border="0"><tr valign="top"></tr></table> <table width="100%" height="20" border="0" cellpadding="0" cellspacing="1"><tr></tr></table> <table width="100%" border="0" cellspacing="0" cellpadding="0"><tr valign="top"><td align="center"><table width="95%"><tr><td align="left"> <table width="100%" border="0" cellspacing="0" cellpadding="2"> <tr><td valign="top" height="5"><img src="/books/2/409/1/html/2/images/pixel.gif" width="1" height="5" alt="" border="0"></td></tr> <tr> <td><b><font size="1" color ="#0000FF">Team-Fly<img border="0" src="/books/2/409/1/html/2/Fly-Logo.gif" width="81" height="25"></font></b></td> <td valign="top" align="right"> </td> </tr> </table> <hr size="1"> <table width="100%" border="0" cellspacing="0" cellpadding="5"> <tr> <td valign="top" width="76" rowspan="4"><img src="/books/2/409/1/html/2/images/1565926757/ornewfeatures_xs.gif" width="76" height="100" border="0"></td> <td valign="top">Oracle PL/SQL Programming Guide to Oracle 8<span>i</span> Features<br>By Steven Feuerstein<br> </td> </tr> <tr><td>Table of Contents</td></tr> <tr><td></td></tr> <tr><td valign="bottom"> Chapter 3. Invoker Rights: Your Schema or Mine? </td></tr> </table> <hr size ="1"> <br><table width="100%" border="0" cellspacing="0" cellpadding ="0"><tr><td valign="top"> <h3 id="141540-931">3.3 One Program, Multiple Schemas</h3> <p> You have the syntax down and we've covered some rules. Let's try out the new AUTHID clause in a more elaborate example.</p> <p>A couple of years ago, a group of people started the Stolen Lives Project. The objective of this project is to document the lives "stolen" from families and communities when people are killed by law enforcement officers. I will use this project as the basis for my example in this section.</p> <p></p> <table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><td><table bgcolor ="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr> <td width="60" valign="top"><img src="/books/2/409/1/html/2/images/tip_yellow.gif" width="50" height="54"></td> <td valign="top"> <p>Neither the Stolen Lives Project nor I believe that all law enforcement officers commit acts of brutality. I also recognize that some killings by officers are justifiable, and that many, if not the vast majority of, officers are committed to improving the lives and guaranteeing the safety of <span>all</span> the citizens in their jurisdiction.</p> <p>You can get lots more information about the Stolen Lives Project (hereafter referred to as SLP) at the following URL:</p> <p>http://www.unstoppable.com/22/english/stolenlivesPROJECT</p> </td> </tr></table></td></tr></table> <p>Suppose then that there is a national headquarters for the SLP, and that Headquarters maintains an Oracle database with a separate schema for each city and town in which information is being collected by local law enforcement brutality activists. Each schema has its own stolen_life table, but all schemas perform the same analyses on this information.</p> <p>The DBA/developer for SLP, Salinda, has upgraded recently to 8.1 and sees an immediate opportunity for the AUTHID feature. She would like to maintain all the code in one place, but when activists in a given city run that code for their locale, they see their data and analyses. Figure 3.4 shows the architecture Salinda wants to implement.</p> <center> <h5>Figure 3.4. One program analyzes data in different tables</h5> <img border="0" width="502" height="213" src="/books/2/409/1/html/2/images/1565926757/figs/o8if.0304.gif" alt="figs/o8if.0304.gif"> </center> <p></p> <table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr> <td width="60" valign="top"><img src="/books/2/409/1/html/2/images/tip_yellow.gif" width="50" height="54"></td> <td valign="top"> <p>I do not show all the statements needed to set up the various schemas in Oracle; you will find them, however, in the <i>authid.ins</i> file on the companion disk.</p> </td> </tr></table></td></tr></table> <p>Salinda connects to the HQ schema and creates the following objects. First, a stolen_life table that contains just one row, indicating that you shouldn't look here for data:</p> <pre>/* Filename on companion disk: authid.hql */ CREATE TABLE stolen_life ( dod DATE, ethnicity VARCHAR2(100), victim VARCHAR2(100), age NUMBER, description VARCHAR2(2000) ); INSERT INTO stolen_life (dod, ethnicity, victim, age, description) VALUES ( SYSDATE, 'N/A', 'HQ Table', 0, 'All information is stored in city tables.');</pre> <p>As you will see, Salinda needs to have this table in the schema so that she can compile the stored program units that will be shared throughout all the local schemas.</p> <p>Next, she creates two programs, only the second of which specifies invoker rights. First, a program to display information about a life stolen:</p> <pre>/* Create a display program, run as DEFINER. */ CREATE OR REPLACE PROCEDURE show_victim ( stolen_life IN stolen_life%ROWTYPE ) AS BEGIN DBMS_OUTPUT.PUT_LINE (stolen_life.victim); DBMS_OUTPUT.PUT_LINE (''); DBMS_OUTPUT.PUT_LINE (stolen_life.description); DBMS_OUTPUT.PUT_LINE (''); END; /</pre> <p>And then the analysis program (which, in this case, simply displays the victims for a location):</p> <pre>CREATE OR REPLACE PROCEDURE show_descriptions AUTHID CURRENT_USER AS BEGIN FOR lifestolen IN (SELECT * FROM stolen_life) LOOP show_victim (lifestolen); END LOOP; END; /</pre> <p>Both of these programs compile by resolving the reference to stolen_life against the all-but-empty HQ table. Since these programs are to be used by all, Salinda then performs the necessary grants:</p> <pre>GRANT EXECUTE ON show_descriptions TO PUBLIC; DROP PUBLIC SYNONYM show_descriptions; CREATE PUBLIC SYNONYM show_descriptions FOR show_descriptions;</pre> <p>All of the above statements can be found, by the way, in the <span>authid.hq1</span> file.</p> <p>Once the centralized objects are in place, Salinda can now get everything defined in the local (city/town) schemas. Remember that the objective in this architecture is to store all of the code in one schema; the only step Salinda should have to take in her local schemas, therefore, is to create the stolen_life table specific to that locale. Here are the steps for Chicago. For these steps see <i>authid.chi</i>and note that all data shown is taken from the web site and reflects real-world tragedy:</p> <pre>/* Filename on companion disk: authid.chi */</pre> <pre>DROP TABLE stolen_life; CREATE TABLE stolen_life ( dod DATE, ethnicity VARCHAR2(100), victim VARCHAR2(100), age NUMBER, description VARCHAR2(2000), moreinfoat VARCHAR(200) DEFAULT 'http://www.unstoppable.com/22/english/stolenlivesPROJECT' );</pre> <p>Now that the table is created, Salinda populates it with just a tiny fragment of all the broken lives you will find on the web site. I will show just a single entry in the text:</p> <pre>INSERT INTO stolen_life (dod, ethnicity , victim, age, description) VALUES ( '23-OCT-96', 'Puerto Rican', 'Angel Castro, Jr.', 15, 'After being beaten, abused with racial epithets and told by police that he would be killed if he did not move, Angel Castro's family moved. Angel returned to the neighborhood for a friend's birthday party. After leaving the party, a police car rammed him as he rode his bike. As Angel tried to get on his knees, the police shot and killed him');</pre> <p>Salinda also performs the same steps for New York City; see the <i>authid.ny</i> file for all the details. Now let's see how well it all works.</p> <p>I connect to the NY schema and show the stolen lives:</p> <pre>SQL> CONNECT newyork/newyork SQL> set serveroutput on size 1000000 format wrapped SQL> exec show_descriptions Amadou Diallo Shot 19 times by four police officers outside his Bronx apartment. Diallo was a devout Muslim working 12 hour days selling CDs and tapes to earn money to finish his bachelor's degree. He was unarmed. ...</pre> <p>And when I connect to CHICAGO, I see different information:</p> <pre>SQL> CONNECT chicago/chicago SQL> set serveroutput on size 1000000 format wrapped SQL> exec show_descriptions Angel Castro, Jr. After being beaten, abused with racial epithets and told by police that he would be killed if he did not move, Angel Castro's family moved. Angel returned to the neighborhood for a friend's birthday party. After leaving the party, a police car rammed him as he rode his bike. As Angel tried to get on his knees, the police shot and killed him. ...</pre> <p>Perfectly abominable behavior, but perfect implementation of shared code and nonshared data!</p> <p>Let's just verify that Salinda did need the AUTHID clause to get things to work right. Suppose she goes back to the HQ account and recompiles the show_descriptions procedure, this time with the default rights model:</p> <pre>CREATE OR REPLACE PROCEDURE show_descriptions AUTHID DEFINER -- The default AS BEGIN FOR lifestolen IN (SELECT * FROM stolen_life) LOOP show_victim (lifestolen); END LOOP; END; /</pre> <p>Then when activists connect to the CHICAGO and NY schemas to see their data, this is all they see:</p> <pre>SQL> exec show_descriptions HQ Table All information is stored in city tables.</pre> <p>Clearly, without the help of the invoker rights model, Salinda could not achieve her design objectives. With AUTHID CURRENT_USER, though, it will be easier for the Stolen Lives Project to inform the American public about this issue.</p> <p></p> <table cellspacing="0" width="90%" border="1"><tr><td> <h2>Invoker Rights for Dynamic SQL</h2> <p>I have written hundreds of programs using dynamic SQL, and prior to Oracle 8.1, I always had to worry about schema issues: where is the program running? Who is running the program? What will happen when someone runs the program? These are scary questions to ask about your own code!</p> <p>Now with the invoker rights model, those issues fall away. You should as a rule always include the AUTHID CURRENT_USER clause for any stored program unit that uses either the built-in package DBMS_SQL for dynamic SQL or the new 8.1 native dynamic SQL implementation (described in Chapter 4).</p> <p>Once you take this step, you can rest assured that no matter where the program is compiled and no matter which schema runs the program, it will always act on the currently connected schema. </p> </td></tr></table> </td></tr></table> <hr size="1"> <table width="100%" border="0" cellspacing="0" cellpadding="2"> <tr><td valign="top" height="5"><img src="/books/2/409/1/html/2/images/pixel.gif" width="1" height="5" alt="" border="0"></td></tr> <tr> <td><b><font size="1" color="#0000FF">Team-Fly<img border="0" src="/books/2/409/1/html/2/Fly-Logo.gif" width="81" height="25"></font></b></td> <td valign="top" align="right"> </td> </tr> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top" align="right">Top</td></tr></table> </td></tr></table></td></tr></table>
