Practice:Demonstrating Oracle AUDIT Concepts and Procedures

2005/07/10 by Leave a Comment

This practice uses common UNIX and NT Oracle utilities to practice enabling AUDIT on a database.  You will:

  • See the procedure to enable and disable Oracle AUDIT on a database.
  • Understand the SQL commands used to audit a specific user schema object.
  • Investigate how to configure Audit to extend auditing into modified or new schema objects..

ASSUMPTIONS

  • This practice will reference SQL commands that function equally on UNIX operating systems, and the NT operating system, using SQL*Plus.
  • Results may vary slightly according to your Oracle environment.
  • Utilize the RealPlayer Demonstration in conjunction with this Practice, to further illustrate and guide this activity.
  • Login to your sqlplus session using the SYSTEM user object and the current password.

INSTRUCTIONS

1.

UNIX: Open a shell, login, locate and edit the initSID.ora file for your database:

NT: Use Window’ File Manager to locate the initSID.ora file for your database.

#audit_trail = true        # save,  original line

audit_trail = true         #activated for demonstration, rjm

 

Locate the line above, make a full copy of the line in the next newline, then uncomment (remove the # symbol) from the line.  Edit comments to reflect your reasons for the change.  Save the modified file, then shutdown/startup the instance.  Audit is now active on your database instance.

2.

UNIX: Open a shell, login, create a SQLPlus session with the SYSTEM connection.

NT: Create an SQLPlus session with SYSTEM connection.

sqlplus /nolog

 

SQL> audit select any table by scott;

 

Audit succeeded.

 

SQL> noaudit select any table by scott;

 

Noaudit succeeded.

 

SQL> audit all by scott;

 

Audit succeeded.

 

SQL> noaudit all by scott;

 

Noaudit succeeded.

 

SQL>

Now, all SELECT activity by the user Scott will be recorded in the audit trail, for our review later.

The NOAUDIT command following disables this selective monitoring once we have accumulated sufficient data to analyze.  The next commands will begin monitoring on ALL database activity for the user Scott, and then disables that same type of monitoring.

3.

UNIX: Open a shell, login, create a SQLPlus session with SYSTEM connection:

NT: Create a SQLPlus session with SYSTEM connection:

sqlplus /nolog

 

SQL> audit insert on default;

 

Audit succeeded.

 

SQL> audit delete on default;

 

Audit succeeded.

 

SQL> audit update on default;

 

Audit succeeded.

 

SQL> noaudit insert on default;

 

Noaudit succeeded.

 

SQL> noaudit delete on default;

 

Noaudit succeeded.

 

SQL> noaudit update on default;

 

Noaudit succeeded.

 

SQL>

These commands will extend INSERT, DELETE, UPDATE auditing to include future new or modified schema objects.

The second set of NOAUDIT commands disable those same audit actions.

Filed Under: Oracle Tagged With:

Practice:Demonstrating Database User Objects, Roles and Permissions

2005/07/10 by Leave a Comment

This practice uses common UNIX and Windows NT Oracle utilities to review database user objects’ key roles and  permissions.  You will:

1.        See default user objects and their default passwords.

2.        Understand V$PWFILE_USER data dictionary view, and how to use this view to inspect SYSDBA or SYSOPER privileges assigned to user objects.

3.        Examine two key data dictionary views that reveal information about an important privilege for Oracle Audit.

4.        Investigate how to find user objects that are assigned unlimited tablespace in your active Oracle instances.

5.        Review the secure method for invoking SQLPlus, utilizing the /nolog command line switch.

ASSUMPTIONS

1.        This practice references SQL commands that function equivalently on the UNIX and NT operating systems.

2.        Results may vary slightly according to your Oracle environment.

3.        To further illustrate and guide this activity, view  this topic’s  Demo in conjunction with this Practice.

4.        Login to your sqlplus session using the SYSTEM user object and the current password.

INSTRUCTIONS

1.

UNIX: Open a shell, login, and type the following command:

NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

 sqlplus system/<password>

                       

SQL> connect system/manager

Connected.

SQL> connect sys/change_on_install

Connected.

SQL> connect scott/tiger

Connected.

SQL>

Each connect command shows if the default password is in use with that particular default database user object.   Those objects using the default passwords should have their passwords changed.

2.

UNIX:  Open a shell, login, and type the following command

NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

sqlplus system/<password> 

SQL> select * from v$pwfile_users;

 

USERNAME                       SYSDB SYSOP

------------------------------       -----       -----

INTERNAL                       TRUE      TRUE

SYS                            TRUE      TRUE

ROD                            TRUE      FALSE

 

SQL>

It is normal for INTERNAL or SYS to have the SYSOPER and/or SYSDBA privilege.   Any other user objects are suspect and should be scrutinized.

3.

UNIX: Open a shell, login, and type the following command:

NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

 

sqlplus system/<current password> 

SQL> select role,privilege from role_sys_privs

       2  where role='DELETE_ANY_CATALOG';

no rows selected

 

SQL> select role,privilege from role_sys_privs

       2  where role='DELETE_ANY_CATALOG';

no rows selected

 

Any user objects that have the DELETE_ANY_CATALOG privilege should be scrutinized closely.  Verify the need for this object to have this privilege.  If in doubt, revoke the privilege.

4.

UNIX: Open a shell, login, and type this command

NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

sqlplus system/<current password> 

SQL> select username,max_bytes,max_blocks from dba_ts_quotas;

 

USERNAME                        MAX_BYTES MAX_BLOCKS

------------------------------ ---------- ----------

RODOEMREPOS                            -1         -1

RODOEMREPOS                            -1         -1

 

SQL>

 

The numeric “-1” is the indication of unlimited tablespace assignments, whether in a MAX_BYTES or MAY_BLOCKS column.   Examine those usernames that display the “-1” value.   A username with unlimited tablespace privilege could accidentally or intentionally use 100% of that tablespace.

5.

UNIX: open a shell, login, and type the following command:

NT: no practice for this concept on NT.

sqlplus system/<current password> 

SQL> host ps -ef|grep sqlplus

 

The results of this UNIX command within sqlplus will display the sqlplus session with the SYSTEM user object’s password in plain text.  This is a security breach, and can be avoided with utilizing sqlplus in the NOLOG mode displayed next.

6.

UNIX: Open a shell, login, and type the following command:

NT: No practice for this concept on NT.

sqlplus system /nolog 

SQL> connect system/manager

Connected.

 

SQL> host ps -ef|grep sqlplus

 

The results of this UNIX command within sqlplus will display the sqlplus session with the SYSTEM user object’s password hidden.  The session now displays only the “/nolog” in the UNIX process table.   This keeps unauthorized users from gaining passwords by watching active sessions using the UNIX command “ps”.

Filed Under: Oracle Tagged With: , ,

Script: Computing Table Size

2005/07/07 by Leave a Comment

This script calculates the average row size for all tables in a schema.
It generates a script (getAvgCol.sql) from USER_TABLES and then runs it.
The following type of SELECT is generated for each table in USER_TABLES:

SELECT round(avg(nvl(vsize(COL1),0)) +
round(avg(nvl(vsize(COL2),0)) + … +
round(avg(nvl(vsize(COLn),0))

Where n=# of cols. on the table

Tables with LONG and LOB columns will not report row size properly. Also
tables with object types will throw the following error and will also not
report row size properly:

ORA-00932: inconsistent datatypes

=============
Sample Output
=============
ACCOUNTS 6
ACCTS 39
ACCT_ADDRS 38
BAD_DATA 116
BASE1 6
BONUS
CEG1 11
CHESS_SAVE
CHESS_SAVE_PLAYER
CITIES 36
COMPANY_SUMMARY 60
CR_FILES 113

Script:

SET ECHO off
REM NAME:   ROWSZ.SQL

drop table column_counts;
create table column_counts
        (
        table_name,
        column_count
        )
        as
        (
        select table_name, max(column_id)
        from user_tab_columns
        where data_type not like 'LONG%' AND table_name in
        (select table_name from user_tables)
        group by table_name
        )
        ;
set pages 0
set tab on
set trim on
set verify off
set feedback off
set termout off
set head off
set lines 100
set recsep off
set embedded on
spool getavgcol.sql
prompt column TB format A30
prompt set head off recsep off
prompt set lines 80 feedback off pages 0
prompt spool getavgcol
REM
column select_line format A8
column end_line format A1
column from_stmt format A34 word_wrap
column col_nm format A100
column col_val format A32
column tnm1 noprint
column tnmprint format A37
column column_id noprint
break on tnm1 skip 2
set null ''
clear breaks
select UTC.table_name tnm1,
        decode(column_id,1,'select ' || chr(39) || UTC.table_name || chr(39) ||
                ' TB, ', '        ') ||
        'round(avg(nvl(vsize('||column_name||'),0)),0)' ||
        decode(column_id,column_count, ' row_size from ' || UTC.table_name
             || ';'|| chr(10)||chr(10),
                ' +') col_nm
from user_tab_columns UTC, column_counts CC
where UTC.data_type not like 'LONG%' AND UTC.table_name = CC.table_name
order by UTC.table_name, UTC.column_id;
prompt spool off
prompt exit
spool off
drop table column_counts;
exit
Filed Under: Oracle, Oracle脚本script Tagged With: , , , ,

Data Block Cache Header Format Changes (Oracle8 Physical layout)

2005/01/11 by Leave a Comment 
Oracle8 has introduced a change with the data block cache header format.  The
basic idea is that incarnation and sequence numbers stored in the cache header
have been replaced with an SCN number and sequence number.  The size of the
cache header has remained 20 bytes.  The size of the block trailer is still 4
bytes.  Only the format of the cache header and the trailer has changed.

Oracle7 Implementation
~~~~~~~~~~~~~~~~~~~~~~

The current Oracle7 implementation stores the incarnation and sequence number
in the cache header of each data block to determine the current version of the
block.  A compressed version of the incarnation/sequence is maintained at the
end of the data block.  The incarnation and sequence numbers are each 4 byte
values.  The low-order 2 bytes of each value are stored as the last 4 bytes of
the data block.  This information is used to detect media corruption when
reading the block for normal operations or during recovery, or when validating
the block.  It allows for a consistency check to ensure the top of the block
is in sync with the bottom of the block.

See [NOTE:33242.1] for more details on the Oracle7 format.

Oracle8 Implementation
~~~~~~~~~~~~~~~~~~~~~~

The basic idea is to store the current redo generating SCN in the cache header
when making a change to a block.  Since multiple changes to a block can be
made at the same SCN, a sequence number is also stored in the cache header to
differentiate between different changes at the same SCN.  The sequence number
is increased each time a change is made at the same SCN.  The sequence number
is reset to 1 when making a change at a higher SCN than the SCN currently in
the block.

Oracle8 data block layout
-------------------------

        |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |
        | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |10 |11 |12 |13 |14 |15 |

        +-------+-------+---------------+---------------+---------------+
        |Typ|Fmt|Filler |     RDBA      |    SCNBase    |SCNWrap|Seq|Flg|
        |-------+-------+---------------+---------------+---------------|
        |ChkVal |Filler | <------                                       |
        |-------+-------+                                               |
        :                       Body of                                 :
        :                        Data Block                             :
        :                                                               :
        |                                               +---------------|
        |                                    ---------> |     Tail      |
        +---------------------------------------------------------------+

        | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | A | B | C | D | E | F |
        |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |

    The bytes in the header are defined as:

        Typ     Block Type (defined in k.h).

        Fmt     Block format.  In Oracle8 this is 2, converted on-the-fly.

        Filler  Not used currently.

        RDBA    Relative database address of the block.

        SCNBase SCN Base

        SCNWrap SCN Wrap

        Seq     Sequence number.  Incremented for every change made to the
                block at the same SCN.

        Flg     Flag (defined in kcbh.h)

        ChkVal  Optional check value for the block.  Set so that an xor of all
                the ub2 values in the block equals zero.  This may require the
                value to be zero (unlike Oracle7).  ChkVal will be verified
                only if KCBHFCKV is set in the Flg field.
                   - always for file header blocks
                   - if the init.ora <parameter:DB_BLOCK_CHECKSUM> is set.

        Tail    Consistency information used to verify the beginning and the
                end of the block are of the same version.  Lower order 2 bytes
                of SCNBase, plus block Type, plus SCN Seq number.

Migration to the New Format
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Data block cache headers will be upgraded to the new Oracle8 format on-the-fly
when the block is read into the cache.  Oracle8 will do this automatically for
every block that is read into the cache during normal operation. The on-disk
copy is not updated unless the block becomes dirty.

Soft Corrupt in Oracle8
~~~~~~~~~~~~~~~~~~~~~~~

  Soft corrupt blocks in Oracle8 have:
     Seq# is 0xff
     flg  is 0x00
Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: ,

SCRIPT – to Tune the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters

2002/11/02 by 2 Comments

Script:

select
  'session_cached_cursors'  parameter,
  lpad(value, 5)  value,
  decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
from
  ( select
      max(s.value)  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name = 'session cursor cache count' and
      s.statistic# = n.statistic#
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'session_cached_cursors'
  )
union all
select
  'open_cursors',
  lpad(value, 5),
  to_char(100 * used / value,  '990') || '%'
from
  ( select
      max(sum(s.value))  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name in ('opened cursors current', 'session cursor cache count') and
      s.statistic# = n.statistic#
    group by
      s.sid
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'open_cursors'
  )
/

Sample Output:

PARAMETER              VALUE      USAGE
---------------------- ---------- -----
session_cached_cursors    20       100%
open_cursors             300        16%

select
  to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,
  to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,
  to_char(100 * hard / calls, '999990.00')||'%' hard_parses
from
  ( select sum(value) calls from v$sysstat
      where name in ('parse count (total)', 'parse count (failures)') ),
  ( select value hard from v$sysstat where name = 'parse count (hard)' ),
  ( select value sess from v$sysstat where name = 'session cursor cache hits' )
/

select
  to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,
  to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,
  to_char(100 * hard / calls, '999990.00')||'%' hard_parses
from
  ( select sum(value) calls from v$mystat m, v$statname s
    where m.statistic# = s.statistic# and
          name in ('parse count (total)', 'parse count (failures)') ),
  ( select value hard from v$mystat m, v$statname s
    where m.statistic# = s.statistic# and name = 'parse count (hard)' ),
  ( select value sess from v$mystat m, v$statname s
    where m.statistic# = s.statistic# and name = 'session cursor cache hits' )
/
Filed Under: Oracle Tagged With:

SCRIPT TO GENERATE SQL*LOADER CONTROL FILE

2002/01/23 by 3 Comments

This script prepares a SQL*Loader control file for a table already existing in the database. The script accepts
the table name and automatically creates a file with the table name and extension ‘ctl’.  This is specially
useful if you have the DDL statement to create a particular table and have a free-format ASCII-delimited file but
have not yet created a SQL*Loader control file for the loading operation.

Default choices for the file are as follows (alter to your needs):

Delimiter:              comma (‘,’)
INFILE file extension:  .dat
DATE format:            ‘MM/DD/YY’

You may define the Loader Data Types of the other Data Types by revising the DECODE function pertaining
to them.

Please note:
The name of the table to be unloaded needs to be provided when the script is executed as follows:

Script:

set echo off
set heading off
set verify off
set feedback off
set show off
set trim off
set pages 0
set concat on
set lines 300
set trimspool on
set trimout on

spool &1..ctl

select 'LOAD DATA'||chr (10)||
       'INFILE '''||lower (table_name)||'.dat'''||chr (10)||
       'INTO TABLE '||table_name||chr (10)||
       'FIELDS TERMINATED BY '','''||chr (10)||
       'TRAILING NULLCOLS'||chr (10)||'('
from   all_tables
where  table_name = upper ('&1');

select decode (rownum, 1, '   ', ' , ')||
       rpad (column_name, 33, ' ')||
       decode (data_type,
               'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
               'FLOAT',    'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
               'NUMBER',   decode (data_precision, 0,
                           'INTEGER EXTERNAL NULLIF ('||column_name||
                           '=BLANKS)', decode (data_scale, 0,
                           'INTEGER EXTERNAL NULLIF ('||
                           column_name||'=BLANKS)',
                           'DECIMAL EXTERNAL NULLIF ('||
                           column_name||'=BLANKS)')),
               'DATE',     'DATE "MM/DD/YY"  NULLIF ('||column_name||'=BLANKS)',
               null)
from   user_tab_columns
where  table_name = upper ('&1')
order  by column_id;

select ')'
from sys.dual;
spool off

Sample Output:

LOAD DATA
INFILE 'tv.dat'
INTO TABLE TV
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(  T1                               INTEGER EXTERNAL NULLIF (T1=BLANKS)
 , T2                               CHAR NULLIF (T2=BLANKS)
 , T3                               CHAR NULLIF (T3=BLANKS)
)
Filed Under: Oracle Tagged With: ,

沪ICP备14014813号-2

沪公网安备 31010802001379号