</FONT></PRE><H5>ANALYSIS:</H5><P>Once again, Jill tried to go around the privileges that she had been given. Naturally,Oracle caught this error and corrected her quickly.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE Bryan.SALARIES</B>  2  <B>SET SALARY = 35000</B>  3  <B>WHERE NAME = 'JOHN';</B>1 row updated.SQL&gt; <B>SELECT *</B>  2 <B> FROM Bryan.SALARIES;</B>NAME                              SALARY       AGE------------------------------ --------- ---------JACK                               35000        29JILL                               48000        42JOHN                               35000        55</FONT></PRE><H5>ANALYSIS:</H5><P>You can see now that the update works as long as Jill abides by the privilegesshe has been given.<H3><FONT COLOR="#000077">Using Views for Security Purposes</FONT></H3><P>As we mentioned on Day 10, &quot;Creating Views and Indexes,&quot; views are virtualtables that you can use to present a view of data that is different from the wayit physically exists in the database. Today you will learn more about how to useviews to implement security measures. First, however, we explain how views can simplifySQL statements.</P><P>Earlier you learned that when a user must access a table or database object thatanother user owns, that object must be referenced with a username. As you can imagine,this procedure can get wordy if you have to write writing several SQL queries ina row. More important, novice users would be required to determine the owner of atable before they could select the contents of a table, which is not something youwant all your users to do. One simple solution is shown in the following paragraph.<H4><FONT COLOR="#000077">A Solution to Qualifying a Table or View</FONT></H4><P>Assume that you are logged on as Jack, your friend from earlier examples. Youlearned that for Jack to look at the contents of the <TT>SALARIES</TT> table, hemust use the following statement:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>  2  <B>FROM Bryan.SALARIES;</B></FONT></PRE><H5><FONT SIZE="2" COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">NAME                              SALARY       AGE------------------------------ --------- ---------JACK                               35000        29JILL                               48000        42JOHN                               35000        55</FONT></PRE><P>If you were to create a view named <TT>SALARY_VIEW</TT>, a user could simply selectfrom that view.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW SALARY_VIEW</B>  2  <B>AS SELECT *</B>  3 <B> FROM Bryan.SALARIES;</B>View created.SQL&gt; <B>SELECT * FROM SALARY_VIEW;</B>NAME                              SALARY       AGE------------------------------ --------- ---------JACK                               35000        29JILL                               48000        42JOHN                               35000        55</FONT></PRE><H5>ANALYSIS:</H5><P>The preceding query returned the same values as the records returned from <TT>Bryan.SALARIES</TT>.<H3><FONT COLOR="#000077">Using Synonyms in Place of Views</FONT></H3><P>SQL also provides an object known as a synonym. A synonym provides an alias fora table to simplify or minimize keystrokes when using a table in an SQL statement.There are two types of synonyms: private and public. Any user with the resource rolecan create a private synonym. On the other hand, only a user with the DBA role cancreate a public synonym.</P><P>The syntax for a public synonym follows.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">CREATE [PUBLIC] SYNONYM [schema.]synonymFOR [schema.]object[@dblink] </FONT></PRE><P>In the preceding example, you could have issued the following command to achievethe same results:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE PUBLIC SYNONYM SALARY FOR SALARIES</B></FONT></PRE><PRE><FONT COLOR="#0066FF">Synonym created.</FONT></PRE><P>Then log back on to Jack and type this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM SALARY;</B>NAME                              SALARY       AGE------------------------------ --------- ---------JACK                               35000        29JILL                               48000        42JOHN                               35000        55</FONT></PRE><H4><FONT COLOR="#000077">Using Views to Solve Security Problems</FONT></H4><P>Suppose you changed your mind about Jack and Jill and decided that neither ofthem should be able to look at the <TT>SALARIES</TT> table completely. You can useviews to change this situation and allow them to examine only their own information.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW JACK_SALARY AS</B>  2  <B>SELECT * FROM BRYAN.SALARIES</B>  3  <B>WHERE NAME = 'JACK';</B>View created.</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW JILL_SALARY AS</B>  2  <B>SELECT * FROM BRYAN.SALARIES</B>  3  <B>WHERE NAME = 'JILL';</B>View created.</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>GRANT SELECT ON JACK_SALARY</B>  2  <B>TO JACK;</B>Grant succeeded.</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>GRANT SELECT ON JILL_SALARY</B>  2  <B>TO JILL;</B>Grant succeeded.</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; R<B>EVOKE SELECT ON SALARIES FROM JACK;</B>Revoke succeeded.</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>REVOKE SELECT ON SALARIES FROM JILL;</B>Revoke succeeded.</FONT></PRE><P>Now log on as Jack and test out the view you created for him.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM Bryan.JACK_SALARY;</B>NAME       SALARY     AGE---------- ---------- ----Jack       35000      29</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM PERKINS.SALARIES;</B>SELECT * FROM PERKINS.SALARIES                      *</FONT></PRE><PRE><FONT COLOR="#0066FF">ERROR at line 1:ORA-00942: table or view does not exist</FONT></PRE><P>Log out of Jack's account and test Jill's:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM Bryan.JILL_SALARY;</B>NAME               SALARY        AGE------------------ ------------- ----Jill               48000         42</FONT></PRE><H5>ANALYSIS:</H5><P>You can see that access to the <TT>SALARIES</TT> table was completely controlledusing views. SQL enables you to create these views as you like and then assign permissionsto other users. This technique allows a great deal of flexibility.</P><P>The syntax to drop a synonym is</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; drop [public] synonym synonym_name;</FONT></PRE><BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>By now, you should understand the	importance of keeping to a minimum the number of people with DBA roles. A user with	this access level can have complete access to all commands and operations within	the database. Note, however, that with Oracle and Sybase you must have DBA-level	access (or SA-level in Sybase) to import or export data on the database. <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">Using the WITH GRANT OPTION Clause</FONT></H3><P>What do you think would happen if Jill attempted to pass her <TT>UPDATE</TT> privilegeon to Jack? At first glance you might think that Jill, because she was entrustedwith the <TT>UPDATE</TT> privilege, should be able to pass it on to other users whoare allowed that privilege. However, using the <TT>GRANT</TT> statement as you didearlier, Jill cannot pass her privileges on to others:</P><PRE><FONT COLOR="#0066FF">SQL&gt; <B>GRANT SELECT, UPDATE(SALARY) ON Bryan.SALARIES TO Jill;</B></FONT></PRE><P>Here is the syntax for the <TT>GRANT</TT> statement that was introduced earliertoday:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">GRANT {object_priv | ALL [PRIVILEGES]} [ (column[, column]...) ][, {object_priv | ALL [PRIVILEGES]} [ (column[, column] ...) ] ] ...ON [schema.]objectTO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...[WITH GRANT OPTION]</FONT></PRE><P>What you are looking for is the <TT>WITH GRANT OPTION</TT> clause at the end ofthe <TT>GRANT</TT> statement. When object privileges are granted and <TT>WITH GRANTOPTION</TT> is used, these privileges can be passed on to others. So if you wantto allow Jill to pass on this privilege to Jack, you would do the following:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>GRANT SELECT, UPDATE(SALARY)</B>  2  <B>ON Bryan.SALARIES TO JILL</B>  3  <B>WITH GRANT OPTION;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">Grant succeeded.</FONT></PRE><P>Jill could then log on and issue the following command:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>GRANT SELECT, UPDATE(SALARY)</B>  2 <B> ON Bryan.SALARIES TO JACK;</B>Grant succeeded.</FONT></PRE><H2><FONT COLOR="#000077">Summary</FONT></H2><P>Security is an often-overlooked topic that can cause many problems if not properlythought out and administered. Fortunately, SQL provides several useful commands forimplementing security on a database.</P><P>Users are originally created using the <TT>CREATE USER</TT> command, which setsup a username and password for a user. After the user account has been set up, thisuser must be assigned to a role in order to accomplish any work. The three rolesavailable within Oracle7 are Connect, Resource, and DBA. Each role has differentlevels of access to the database, with Connect being the simplest and DBA havingaccess to everything.</P><P>The <TT>GRANT</TT> command gives a permission or privilege to a user. The <TT>REVOKE</TT>command can take that permission or privilege away from the user. The two types ofprivileges are object privileges and system privileges. The system privileges shouldbe monitored closely and should not be granted to inexperienced users. Giving inexperiencedusers access to commands allows them to (inadvertently perhaps) destroy data or databasesyou have painstakingly set up. Object privileges can be granted to give users accessto individual objects existing in the owner's database schema.</P><P>All these techniques and SQL statements provide the SQL user with a broad rangeof tools to use when setting up system security. Although we focused on the securityfeatures of Oracle7, you can apply much of this information to the database systemat your site. Just remember that no matter what product you are using, it is importantto enforce some level of database security.<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2><DL>	<DD><B>Q I understand the need for security, but doesn't Oracle carry it a bit too	far?</B>	<P><B>A </B>No, especially in larger applications where there are multiple users.	Because different users will be doing different types of work in the database, you'll	want to limit what users can and can't do. Users should have only the necessary roles	and privileges they need to do their work.</P>	<P><B>Q It appears that there is a security problem when the DBA that created my	ID also knows the password. Is this true?</B></P>	<P><B>A </B>Yes it is true. The DBA creates the IDs and passwords. Therefore, users	should use the <TT>ALTER USER</TT> command to change their ID and password immediately	after receiving them.</DL><H2><FONT COLOR="#000077">Workshop</FONT></H2><P>The Workshop provides quiz questions to help solidify your understanding of thematerial covered, as well as exercises to provide you with experience in using whatyou have learned. Try to answer the quiz and exercise questions before checking theanswers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;<H3><FONT COLOR="#000077">Quiz</FONT></H3><DL>	<DD><B>1. </B>What is wrong with the following statement?</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>GRANT CONNECTION TO DAVID;</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>2. </B>True or False (and why): Dropping a user will cause all objects owned	by that user to be dropped as well.	<P><B>3.</B> What would happen if you created a table and granted select privileges	on the table to <TT>public</TT>?</P>	<P><B>4.</B> Is the following SQL statement correct?</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>create user RON</B>     <B>identified by RON;</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>5.</B> Is the following SQL statement correct?</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>alter RON     identified by RON;</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>6.</B> Is the following SQL statement correct?</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>grant connect, resource to RON;</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>7.</B> If you own a table, who can select from that table?</DL><H3><FONT COLOR="#000077">Exercise</FONT></H3><DL>	<DD><B>1.</B> Experiment with your database system's security by creating a table	and then by creating a user. Give this user various privileges and then take them	away.</DL><H1></H1><CENTER><P><HR><A HREF="../ch11/ch11.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch13/ch13.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR><BR><BR><IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"BORDER="0"></P><P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. Allrights reserved.</CENTER></BODY></HTML>