ch15.htm

来自「Teach yourself Oracle8 in 21 day」· HTM 代码 · 共 702 行 · 第 1/3 页

	or the interval between runs.
	<P>
	<LI><TT>INTERVAL</TT>--Alters the interval between runs.
	<P>
	<LI><TT>NEXT_DATE</TT>--Alters the next execution time of the job.
	<P>
	<LI><TT>REMOVE</TT>--Removes a job from the job queue.
	<P>
	<LI><TT>RUN</TT>--Forces a job to run.
	<P>
	<LI><TT>SUBMIT</TT>--Submits a job to the job queue.
	<P>
	<LI><TT>WHAT</TT>--Alters the job description.
</UL>

<P>Each of these options has its own parameters that call it. The following sections
outline the parameters for a few of them.
<H4><FONT COLOR="#000077">SUBMIT</FONT></H4>
<P>The <TT>SUBMIT</TT> function can be executed by calling the package with a syntax
such as</P>
<PRE><FONT COLOR="#0066FF">DBMS_JOB.SUBMIT(
Job
What
Next_date
Interval
No_parse )
</FONT></PRE>
<P>where Job is an output parameter and the others are input parameters.</P>
<P>The <TT>SUBMIT</TT> procedure returns the job number. Here is an example:</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; VARIABLE job number;
SQL&gt; begin
  2  DBMS_JOB.SUBMIT(
  3  :job,
  4  `DELETE FROM dogs2;',
  5  SYSDATE,
  6  NULL);
  7  COMMIT;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL&gt; PRINT job;

      JOB
---------

        1
</FONT></PRE>
<H4><FONT COLOR="#000077">REMOVE</FONT></H4>
<P>The <TT>REMOVE</TT> function can be executed by calling the package with syntax
such as</P>
<PRE><FONT COLOR="#0066FF">DBMS_JOB.REMOVE(Job)
</FONT></PRE>
<P>where Job is the job number.</P>
<P>An example of using <TT>DBMS_JOB.REMOVE</TT> is as follows:</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; begin
  2  DBMS_JOB.REMOVE(1);
  3  COMMIT;
  4  end;
  5  /

PL/SQL procedure successfully completed.
</FONT></PRE>
<P>If you don't know the job number, you can try to find it by using the following
query:</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; select job, next_date, next_sec from user_jobs;

      JOB NEXT_DATE NEXT_SEC
--------- --------- --------
        1 25-JUL-97 16:55:28
        2 25-JUL-97 17:07:51
</FONT></PRE>
<P>Other activities, such as <TT>DBMS_JOB.BROKEN</TT>, use a similar syntax. You
can find more details in the Oracle documentation.
<H2><FONT COLOR="#000077"><B>Using Oracle Auditing</B></FONT></H2>
<P>The Oracle auditing facility is used to log information about database operations
such as when they occurred and who performed them. Auditing is important primarily
if one or more incidents have occurred to make you suspicious of the activity of
one or more users. Through auditing, a record is made of this activity that can be
used to track down the party at fault.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>For auditing to be effective, it
	is necessary for each user (especially the DBAs) to have his own account and use
	it. It does no good to audit database activity and determine that the <TT>SYSTEM</TT>
	user is at fault if you have 15 DBAs who use the <TT>SYSTEM</TT> account. 
<HR>


</BLOCKQUOTE>

<P>Auditing is not only used when you think you have some sort of problem; it can
also give you useful information about the usage of your system that might help you
to determine a better system configuration in the future. By having information about
the activities that are going on within the Oracle RDBMS, you will be better able
to provide the proper services to the user community.</P>
<P>Auditing, although useful, is very expensive in terms of both CPU overhead and
disk usage. As such, you should use auditing very carefully and selectively. Let's
look at how to set up the auditing process.
<H3><FONT COLOR="#000077"><B>Developing an Auditing Strategy</B></FONT></H3>
<P>Before attempting to start auditing, you must first decide what needs to be audited.
This not only involves the activity that is to be audited, but the user accounts
that are to be audited as well. The first step in developing an auditing strategy
is to determine the purpose of auditing.</P>
<P>There may be one or more reasons why you want to audit. By determining these reasons,
you can better put together the audit plan. You might perform auditing for either
of the following reasons:

<UL>
	<LI>Informational purposes--It is common to use auditing to retain specific historical
	information about your system. This can provide valuable insights.
	<P>
	<LI>Suspicious behavior--It is more common to use the audit trail to investigate
	suspicious activities that have occurred in the database.
</UL>

<P>Depending on the reason for auditing, different guidelines should be followed.
In this section you will learn how these different types of audits are handled.
<H4><FONT COLOR="#000077"><B>Auditing for Informational Purposes</B></FONT></H4>
<P>If you are auditing for historical information, you should determine which database
activities will provide the most useful information for the audit. Because you are
auditing for information only, decide which events are of interest to your audit
and which events are not. Once you have compiled a list of pertinent activities,
only those activities should be audited. Occasionally, you should extract the important
information and purge the audit logs. In this way, you can keep down the size of
the logs and still get the information you want.
<H4><FONT COLOR="#000077"><B>Auditing for Suspicious Behavior</B></FONT></H4>
<P>When auditing suspicious behavior, it is usually necessary to audit most database
activity. If you limit the database activity that is being audited, you might miss
some vital clue that can help you solve the mystery of the suspicious behavior. If
you start out auditing all activity, you can reduce the auditing as you gather clues.
When you have determined more information about the suspicious behavior, you can
reduce the number of activities being audited.</P>
<P>Also, note that when auditing suspicious behavior it is necessary to protect the
audit logs. If this undesirable behavior is intentional, the perpetrator might try
to cover up his or her tracks by removing information from the audit trail. How to
protect the audit trail is described later today in the section titled &quot;Protecting
the Audit Trail.&quot;
<H3><FONT COLOR="#000077"><B>Creating the Audit Trail</B></FONT></H3>
<P>To enable the audit trail, first you must run the administration SQL script <TT>CATAUDIT.SQL</TT>.
This script is located in the directory <TT>Orant\Rdbms80\Admin</TT>. This administrative
SQL script will create a number of views into the audit tables; each view shows a
different perspective of the tables. The <TT>CATAUDIT.SQL</TT> script should be run
by the <TT>SYS</TT> user.</P>
<P>To remove the audit trail views when they are no longer needed, use the administrative
SQL script <TT>CATNOAUD.SQL</TT>. This will remove the views and disable auditing.
<H4><FONT COLOR="#000077"><B>The Format of the Audit Trail</B></FONT></H4>
<P>Each entry in the audit trail contains a wealth of information about the event
in question:

<UL>
	<LI>Username
	<P>
	<LI>Session identifier
	<P>
	<LI>Terminal identifier
	<P>
	<LI>Object being accessed
	<P>
	<LI>Operation performed or attempted
	<P>
	<LI>Completion code
	<P>
	<LI>Date and time stamp
</UL>

<P>For some auditing events, this might be all the information that is provided.
Other auditing events might provide more information than this.
<H4><FONT COLOR="#000077"><B>Enabling Auditing</B></FONT></H4>
<P>The audit trail contains a variety of information depending on how the system
is configured. Auditing of particular functions and users is enabled with the <TT>AUDIT</TT>
statement. Auditing is disabled using the <TT>NOAUDIT</TT> statement. By default,
some operations are automatically audited. These operations include

<UL>
	<LI>Instance startup
	<P>
	<LI>Instance shutdown
	<P>
	<LI>All connections to Oracle with <TT>SYSOPER</TT> or <TT>SYSDBA</TT> privileges
</UL>

<P>All other auditing information is enabled by specifying them individually using
the <TT>AUDIT</TT> command. Auditing can be set on three different levels:

<UL>
	<LI>Statements--Audit on particular SQL statements.
	<P>
	<LI>Privileges--Audit on SQL statements that require a particular system privilege.
	<P>
	<LI>Objects--Audit on statements that involve a particular table or other schema
	object.
</UL>

<P><FONT COLOR="#000077"><B>Auditing Statements and Privileges</B></FONT></P>
<P>To audit SQL statements you should use the following syntax with the <TT>AUDIT</TT>
SQL statement:</P>
<PRE><FONT COLOR="#0066FF">AUDIT
SQL_STATEMENT_OPTIONS or PRIVILEGE
[, SQL_STATEMENT_OPTIONS or PRIVILEGE ...]
[BY user_name [, user_name]...]
[BY SESSION or BY ACCESS]
[WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL]
</FONT></PRE>
<P>This statement is all that is necessary to enable auditing. The explanations of
the parameters are as follows:

<UL>
	<LI>SQL_STATEMENT_OPTIONS--With the audit command there are certain keywords, called
	<I>statement options</I>, that are used to indicate auditing of associated SQL statements.
	For example, the statement option <TT>CLUSTER</TT> indicates auditing on the <TT>CREATE
	CLUSTER</TT>, <TT>ALTER CLUSTER</TT>, <TT>DROP CLUSTER</TT>, and <TT>TRUNCATE CLUSTER</TT>
	SQL statements. A complete list of the statement options is given in the Oracle documentation.
	<P>
	<LI>PRIVILEGE--Indicates that SQL statements authorized by this system privilege
	are to be audited. A complete list of system privileges is given in Appendix C, &quot;Oracle
	Roles and Privileges.&quot;
	<P>
	<LI><TT>BY</TT>user_name--Indicates that the SQL statements issued by this user or
	users will be audited. If omitted, the SQL statements for all users will be audited.
	<P>
	<LI><TT>BY SESSION</TT>--Causes Oracle auditing to write only one record for each
	SQL statement issued in a session, so duplicate SQL statements are not logged.
	<P>
	<LI><TT>BY ACCESS</TT>--The opposite of <TT>BY SESSION</TT>. Causes an audit record
	to be written for each SQL statement issued.
	<P>
	<LI><TT>WHENEVERE SUCCESSFU</TT>--Causes an audit record to be written only when
	the SQL statement was successful. If omitted, all SQL statements are audited regardless
	of success.