ch12.htm

Learn SQL in an easy way...

<H4><FONT COLOR="#000077">Table 12.1. System privileges in Oracle7.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>System Privilege</B></TD>
		<TD ALIGN="LEFT"><B>Operations Permitted</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>ALTER ANY INDEX</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to alter any index in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>ALTER ANY PROCEDURE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to alter any stored procedure, function, or package in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>ALTER ANY ROLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to alter any role in the database.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>ALTER ANY TABLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to alter any table or view in the schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>ALTER ANY TRIGGER</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to enable, disable, or compile any database trigger in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>ALTER DATABASE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to alter the database.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>ALTER USER</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to alter any user. This privilege authorizes the grantee to change
			another user's password or authentication method, assign quotas on any tablespace,
			set default and temporary tablespaces, and assign a profile and default roles.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE ANY INDEX</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create an index on any table in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE ANY PROCEDURE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create stored procedures, functions, and packages in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE ANY TABLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create tables in any schema. The owner of the schema containing
			the table must have space quota on the tablespace to contain the table.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE ANY TRIGGER</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create a database trigger in any schema associated with a
			table in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE ANY VIEW</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create views in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE PROCEDURE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create stored procedures, functions, and packages in their
			own schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE PROFILE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create profiles.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE ROLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create roles.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE SYNONYM</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create synonyms in their own schemas.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE TABLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create tables in their own schemas. To create a table, the
			grantees must also have space quota on the tablespace to contain the table.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE TRIGGER</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create a database trigger in their own schemas.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE USER</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create users. This privilege also allows the creator to assign
			quotas on any tablespace, set default and temporary tablespaces, and assign a profile
			as part of a <TT>CREATE USER</TT> statement.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>CREATE VIEW</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to create views in their own schemas.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DELETE ANY TABLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to delete rows from tables or views in any schema or truncate
			tables in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DROP ANY INDEX</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to drop indexes in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DROP ANY PROCEDURE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to drop stored procedures, functions, or packages in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DROP ANY ROLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to drop roles.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DROP ANY SYNONYM</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to drop private synonyms in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DROP ANY TABLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to drop tables in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DROP ANY TRIGGER</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to drop database triggers in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DROP ANY VIEW</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to drop views in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>DROP USER</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to drop users.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>EXECUTE ANY PROCEDURE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to execute procedures or functions (standalone or packaged) or
			reference public package variables in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>GRANT ANY PRIVILEGE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to grant any system privilege.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>GRANT ANY ROLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to grant any role in the database.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>INSERT ANY TABLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to insert rows into tables and views in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>LOCK ANY TABLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to lock tables and views in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>SELECT ANY SEQUENCE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to reference sequences in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>SELECT ANY TABLE</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to query tables, views, or snapshots in any schema.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>UPDATE ANY ROWS</TT></TD>
		<TD ALIGN="LEFT" VALIGN="TOP">Allows the grantees to update rows in tables.</TD>
	</TR>
</TABLE>
</P>
<P>Object privileges are privileges that can be used against specific database objects.
Table 12.2 lists the object privileges in Oracle7.
<H4><FONT COLOR="#000077">Table 12.2. Object privileges enabled under Oracle7.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><TT>ALL</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<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&gt; <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&gt; <B>create user Jack identified by Jack;</B>

User created.

SQL&gt; <B>create user Jill identified by Jill;</B>

User created.

SQL&gt; <B>grant connect to Jack;
</B>
Grant succeeded.

SQL&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt;<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&gt; <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