📄 ch12.htm
字号:
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 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 of
them should be able to look at the <TT>SALARIES</TT> table completely. You can use
views to change this situation and allow them to examine only their own information.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <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> <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> <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> <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> 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> <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> <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> <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> <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 controlled
using views. SQL enables you to create these views as you like and then assign permissions
to 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> 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> privilege
on to Jack? At first glance you might think that Jill, because she was entrusted
with the <TT>UPDATE</TT> privilege, should be able to pass it on to other users who
are allowed that privilege. However, using the <TT>GRANT</TT> statement as you did
earlier, Jill cannot pass her privileges on to others:</P>
<PRE><FONT COLOR="#0066FF">SQL> <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 earlier
today:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">GRANT {object_priv | ALL [PRIVILEGES]} [ (column
[, column]...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column
[, column] ...) ] ] ...
ON [schema.]object
TO {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 of
the <TT>GRANT</TT> statement. When object privileges are granted and <TT>WITH GRANT
OPTION</TT> is used, these privileges can be passed on to others. So if you want
to allow Jill to pass on this privilege to Jack, you would do the following:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <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> <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 properly
thought out and administered. Fortunately, SQL provides several useful commands for
implementing security on a database.</P>
<P>Users are originally created using the <TT>CREATE USER</TT> command, which sets
up a username and password for a user. After the user account has been set up, this
user must be assigned to a role in order to accomplish any work. The three roles
available within Oracle7 are Connect, Resource, and DBA. Each role has different
levels of access to the database, with Connect being the simplest and DBA having
access 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 of
privileges are object privileges and system privileges. The system privileges should
be monitored closely and should not be granted to inexperienced users. Giving inexperienced
users access to commands allows them to (inadvertently perhaps) destroy data or databases
you have painstakingly set up. Object privileges can be granted to give users access
to individual objects existing in the owner's database schema.</P>
<P>All these techniques and SQL statements provide the SQL user with a broad range
of tools to use when setting up system security. Although we focused on the security
features of Oracle7, you can apply much of this information to the database system
at your site. Just remember that no matter what product you are using, it is important
to enforce some level of database security.
<H2><FONT COLOR="#000077">Q&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 the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises."
<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> <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> <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> <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> <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.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch11.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch13.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch13.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>
<P>© <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -