📄 ch12.htm
字号:
<TD ALIGN="LEFT"><TT>ALTER</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>DELETE</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>EXECUTE</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>INDEX</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>INSERT</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>REFERENCES</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>SELECT</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>UPDATE</TT></TD>
</TR>
</TABLE>
<P>You can use the following form of the <TT>GRANT</TT> statement to give other users
access to your tables:</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>To remove the object privileges you have granted to someone, use the <TT>REVOKE</TT>
command with the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">REVOKE {object_priv | ALL [PRIVILEGES]}
[, {object_priv | ALL [PRIVILEGES]} ]
ON [schema.]object
FROM {user | role | PUBLIC} [, {user | role | PUBLIC}]
[CASCADE CONSTRAINTS]
</FONT></PRE>
<H4><FONT COLOR="#000077">From Creating a Table to Granting Roles</FONT></H4>
<P>Create a table named <TT>SALARIES</TT> with the following structure:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">NAME, CHAR(30)
SALARY, NUMBER
AGE, NUMBER
SQL> <B>CREATE TABLE SALARIES (</B>
2 <B> NAME CHAR(30),</B>
3 <B>SALARY NUMBER,</B>
4 <B>AGE NUMBER);</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">Table created.
</FONT></PRE>
<P>Now, create two users--Jack and Jill:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>create user Jack identified by Jack;</B>
User created.
SQL> <B>create user Jill identified by Jill;</B>
User created.
SQL> <B>grant connect to Jack;
</B>
Grant succeeded.
SQL> <B>grant resource to Jill;</B>
Grant succeeded.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>So far, you have created two users and granted each a different role. Therefore,
they will have different capabilities when working with the database. First create
the <TT>SALARIES</TT> table with the following information:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM SALARIES;</B>
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 61000 55
</FONT></PRE>
<P>You could then grant various privileges to this table based on some arbitrary
reasons for this example. We are assuming that you currently have DBA privileges
and can grant any system privilege. Even if you do not have DBA privileges, you can
still grant object privileges on the <TT>SALARIES</TT> table because you own it (assuming
you just created it).</P>
<P>Because Jack belongs only to the Connect role, you want him to have only <TT>SELECT</TT>
privileges.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>GRANT SELECT ON SALARIES TO JACK;</B>
Grant succeeded.
</FONT></PRE>
<P>Because Jill belongs to the Resource role, you allow her to select and insert
some data into the table. To liven things up a bit, allow Jill to update values only
in the <TT>SALARY</TT> field of the <TT>SALARIES</TT> table.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>GRANT SELECT, UPDATE(SALARY) ON SALARIES TO Jill;
</B>
Grant succeeded.
</FONT></PRE>
<P>Now that this table and these users have been created, you need to look at how
a user accesses a table that was created by another user. Both Jack and Jill have
been granted <TT>SELECT</TT> access on the <TT>SALARIES</TT> table. However, if Jack
tries to access the <TT>SALARIES</TT> table, he will be told that it does not exist
because Oracle requires the username or schema that owns the table to precede the
table name.
<H4><FONT COLOR="#000077">Qualifying a Table</FONT></H4>
<P>Make a note of the username you used to create the <TT>SALARIES</TT> table (mine
was Bryan). For Jack to select data out of the <TT>SALARIES</TT> table, he must address
the <TT>SALARIES</TT> table with that username.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM SALARIES;
SELECT * FROM SALARIES
*</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ERROR at line 1:
ORA-00942: table or view does not exist
</FONT></PRE>
<P>Here Jack was warned that the table did not exist. Now use the owner's username
to identify the table:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B>
2 <B>FROM Bryan.SALARIES;
</B>
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 61000 55
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You can see that now the query worked. Now test out Jill's access privileges.
First log out of Jack's logon and log on again as Jill (using the password <TT>Jill</TT>).</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *</B>
2 <B>FROM Bryan.SALARIES;</B>
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 61000 55
</FONT></PRE>
<P>That worked just fine. Now try to insert a new record into the table.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> INSERT INTO Bryan.SALARIES</B>
2 <B>VALUES('JOE',85000,38);</B>
<B>INSERT INTO Bryan.SALARIES
*</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">ERROR at line 1:
ORA-01031: insufficient privileges
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This operation did not work because Jill does not have <TT>INSERT</TT> privileges
on the <TT>SALARIES</TT> table.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>UPDATE Bryan.SALARIES</B>
2 <B>SET AGE = 42</B>
3 <B>WHERE NAME = 'JOHN';
UPDATE Bryan.SALARIES
*</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">ERROR at line 1:
ORA-01031: insufficient privileges
</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> <B>UPDATE Bryan.SALARIES</B>
2 <B>SET SALARY = 35000</B>
3 <B>WHERE NAME = 'JOHN';</B>
1 row updated.
SQL> <B>SELECT *</B>
2 <B> FROM Bryan.SALARIES;</B>
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 35000 55
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You can see now that the update works as long as Jill abides by the privileges
she has been given.
<H3><FONT COLOR="#000077">Using Views for Security Purposes</FONT></H3>
<P>As we mentioned on Day 10, "Creating Views and Indexes," views are virtual
tables that you can use to present a view of data that is different from the way
it physically exists in the database. Today you will learn more about how to use
views to implement security measures. First, however, we explain how views can simplify
SQL statements.</P>
<P>Earlier you learned that when a user must access a table or database object that
another 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 in
a row. More important, novice users would be required to determine the owner of a
table before they could select the contents of a table, which is not something you
want 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. You
learned that for Jack to look at the contents of the <TT>SALARIES</TT> table, he
must use the following statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <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 29
JILL 48000 42
JOHN 35000 55
</FONT></PRE>
<P>If you were to create a view named <TT>SALARY_VIEW</TT>, a user could simply select
from that view.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>CREATE VIEW SALARY_VIEW</B>
2 <B>AS SELECT *</B>
3 <B> FROM Bryan.SALARIES;</B>
View created.
SQL> <B>SELECT * FROM SALARY_VIEW;
</B>
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 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 for
a 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 role
can create a private synonym. On the other hand, only a user with the DBA role can
create a public synonym.</P>
<P>The syntax for a public synonym follows.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CREATE [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object[@dblink]
</FONT></PRE>
<P>In the preceding example, you could have issued the following command to achieve
the same results:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <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> <B>SELECT * FROM SALARY;</B>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -