|
 Oracle
7 DBA
Oracle
7 Architecture
- SGA: (System
Global Area, sometimes call the Shared Global Area)
is a group of shared memory structures. The SGA
is made up of the following structures:
| 1) The Shared Pool - Comprised of the Library
Cache (also know as the Shared SQL Area)
and the Row Cache (also know as the Dictionary
Cache): |
- Library Cache:
Contains the text, parsed form,
and execution plan for SQL and PL/SQL
statements.
- Row Cache:
Contains Oracle data dictionary tables,
including the system catalog and privileges of all users.
| 2) Database Buffer Cache - Contains x amount of
database buffers as specified by the DB_BLOCK_BUFFERS
initialization parameter in the size of DB_BLOCK_SIZE.
Also contains the dirty buffer write queue, which
holds dirty block buffers (i.e., already written to) until
they can be written to disk. |
| 3) Redo Log Buffer - Circular buffer containing
information about changes made to the database.
Records are written from the redo log buffer to the online
redo log files by the LGWR
process. |
Note: User
session info is stored in Shared Pool when Oracle
running in MTS (Multithreaded Server) mode. Otherwise
user info stored in the PGA.
- PGA (Program Global
Area):
- Stores real variables
instead of bind variables for executing SQL
statements.
- The stack space
is memory allocated to hold session variables and arrays.
- The user session
data is additional memory for a users session.
- The PGA is
writeable and non-shared.
- Oracle Background
Processes:
There are four mandatory
processes. If any of these processes fail the instance will
crash and have to be restarted!
1) PMON (Process
Monitor)
- Cleans up abnormally
terminated user sessions.
- Rolls back any uncommitted
transactions.
- Releases locks held by a
terminated process.
- Frees SGA
resources allocated to the failed process.
- Restarts failed shared server
and dispatcher processes.
- Identifies deadlocks.
2) SMON (System Monitor)
- Performs automatic instance
recovery.
- Reclaims space used by
temporary segments no longer in use.
- Merges contiguous areas of
free space in the datafiles. Known as defragging or coalescing.
3) DBWR (Database
Writer)
- Writes all changed (i.e.,
dirty) buffers to datafiles.
- Uses a LRU
(least recently used) algorithm to keep most recently used blocks
in memory.
- Defers writes for I/O
optimization.
Will write dirty blocks when:
- The dirty list
reaches a threshold
- A process scans a specified
number of buffers in the LRU list without finding a free buffer
- A time-out
occurs
- A checkpoint
occurs
4) LGWR (Log Writer)
- There is only one redo
log writer per instance.
- A commit
confirmation is not issued until the transaction has been
recorded in the redo log file.
- Commits performed by other
users before LGWR flushes the buffer on behalf of
a user's commit are piggybacked to achieve an average of less than
one I/O per commit.
- During very long
transactions, the redo log buffer pool can become
more than one-third full before LGWR writes it to
the redo log file.
Will write redo log buffer
entries to the redo log files when:
- A commit
occurs.
- The redo log buffer
pool becomes one-third full.
- The DBWR
completes cleaning the buffer blocks as during a checkpoint.
- A LGWR time-out
occurs.
Here are the remaining Oracle
background processes:
5) CKPT (Checkpoint
Process) improves the performance of databases with many database
files. It is mandatory starting with Oracle 8. Good idea
to run regardless.
- Is enabled by setting the
intialization parameter CHECKPOINT_PROCESS equal
to TRUE. The default is FALSE.
- Takes over LGWR's
tasks of updating files at a checkpoint.
- Headers of
datafiles and control files are updated at the end of a
checkpoint.
- More frequent checkpoints
will reduce the time required for instance recovery,
but potentially at the cost of reduced system performance.
6) ARCH (Archiver
Process) copies online redo log files to a designated
storage device when LGWR switches to a new group.
- Copies redo log files to
tape or disk for media failure recovery.
- Operates only when a
log switch occurs.
- Is only needed when the
database is running in ARCHIVELOG mode.
7) RECO (Recover
Process) resolves failure involving distributed transactions.
8) LCKn (Lock
Process) performs inter-instance locking in a parallel server system.
9) Pnnn (Parallel
Query Process) performs: parallel query, parallel index
creation, parallel data loading, and
parallel 'CREATE TABLE AS SELECT...'.
10) SNPn
(Snapshot Process) automatically refreshes snapshots
(read only replicated tables). It is also responsible for the
server job queues and replication queues.
11) S000..S999 (Server
Processes)
- Parses and executes SQL
statements.
- Reads data blocks from disk
into the database buffer cache of the SGA.
- Returns the results of SQL
statements to the user process.
- When a server
process needs to read a data block from disk into the
buffer cache it will:
- Search the LRU
list.
- Look for a free buffer.
- Move dirty buffers to the dirty
buffer list.
12. D001..D999
(Dispatcher Process)
- Used with MTS.
- SQL*Net Listener
determines if each user process can use a shared
server process.
- SQL*Net Listener gives the
user process the address of a dispatcher process.
- The dispatcher process
connects a user process to a shared server process.
- When the user process
requests it, the listener will create a dedicated server
process and connects the user process to it.
- Redo Logs:
- Record all changes made to
the database and are used for data recovery.
- Written in a circular
fashion.
- There must be at least 2 redo
log groups.
- Each redo log group should
contain the same number of members.
Related info:
- Checkpoints
should occur more frequently than log switches.
- If redo logs are big,
increase checkpoints.
- 'ALTER SYSTEM SWITCH
LOGFILE' forces a checkpoint.
- 'ALTER SYSTEM
CHECKPOINT' performs a fast checkpoint.
Related initialization
parmeters:
LOG_CHECKPOINT_INTERVAL:
Number of blocks LGWR should write to redo
log after a log switch and before a
checkpoint. Volume based.
LOG_CHECKPOINT_TIMEOUT:
Number of seconds transpired before a checkpoint. Time based.
For checkpoints to occur as
frequently as log switches:
set LOG_CHECKPOINT_INTERVAL
> size of redo log file.
For checkpoints to occur more
frequently as log switches:
set LOG_CHECKPOINT_INTERVAL
= (((size of redo log file) / (db_block_size)) / desired frequency of
checkpoint between log switch) * number of operating system blocks in
one Oracle block.
- Control Files:
- A binary file that describes
the structure of the database.
- Required to mount, open, and
access the database.
- Synchronization info needed
for recovery is stored in the control file.
- Recommended configuration is
a minimum of 2 control files on separate disks.
- Alert File:
- Logs all internal (ORA-600),
block corruption (ORA-1578), and deadlock
(ORA-60) errors.
- Logs all DDL
and Server Manager commands such as STARTUP, SHUTDOWN,
ARCHIVE LOG and RECOVER.
- Logs the values of all
non-default initialization parameters at the time of database and
instance startup.
- Located in destination
specified by BACKGROUND_DUMP_DEST.
- Should be checked at least
daily.
- Trace Files:
- Contains internal errors
detected by server or background processes.
- If dumped by a background
process the file is located in destination specified by
initialization parameter BACKGROUND_DUMP_DEST.
- If dumped by server
process the file located in destination specified by
initialization parameter USER_DUMP_DEST.
- Are created if SQL_TRACE
parameter is TRUE.
- Are created if
SQL_TRACE is enabled for a session.
- Parameter File:
- Used to size the SGA
and locate the control files at instance start up.
- Contains all the database
initialization parameters.
- Password File:
- Stores passwords for users
with admin privileges.
- Used only with database
authentication, not OS level authentication.
- V$PWFILE_USERS
tells what users are in the password file.
- Naming convention =
orapw<sid>.pwd
Command Syntax: ORAPWD
file=... password =.. entries= ...
REMOTE_LOGIN_PASSWORDFILE
parameter values:
- NONE - no
unsecured privileged sessions because no password file exists.
- EXCLUSIVE -
Password file used for that instance only. Needed for
remote-nonsecure connections.
- SHARED -
password file only allows access for admin option by SYS
and the DBA using CONNECT INTERNAL.
If a secure connection can be
obtained you can use OS authentication. If no secure connection
is possible you must use Oracle method.
Start Up and Shut Down an
Instance
- Database Startup
Modes:
- NOMOUNT -
Starts the instance and that's it. Used for database
creation.
- MOUNT -
Control file opened for this instance. Used for DBA
maintenance activities including full database recovery. The
DBA can alter the control file in this mode.
- OPEN - All
files opened as described by the control file for this instance.
Used for database access for all users.
- RESTRICT -
Restricted access for DBA. User must have RESTRICTED
SESSION privilege to access the database.
- RECOVER -
For complete and incomplete recovery at instance start time.
- FORCE -
Forces the database to open. Similar to SHUTDOWN
ABORT. Should always issue SHUTDOWN NORMAL
and STARTUP NORMAL afterwards.
Accessing and Updating Data
- SQL Execution Cycle:
1) OPEN SQL
Statement
a) Obtain cursor or memory address in
library cache of shared pool, this is
where the SQL statement will execute. Cursor also
holds the status info for each
sql statement. |
| b) Obtain private sql area in PGA where
statement return values will be stored. |
2) PARSE SQL
Statement
| a) Check syntax |
| b) Create execution plan (also called a parse tree)
and put into the shared pool. |
3) Create BIND
variables
This step is only performed
for SELECTs. Not for INSERT, UPDATE,
or DELETEs.
4) EXECUTE the
statement.
| a) Applies parse tree to data buffers. |
| b) Performs physical reads. |
| c) Performs constraint checking. |
| d) Changes data if needed. |
5) FETCH
For SELECT
only. Returns dataset into BIND variables.
- Criteria for reuse of
execution plan:
- Current SQL statement must
be identical to statement already parsed, including upper/lower
case, white space, and commas.
- Referenced objects are
identical.
- The types and names of
variables are identical.
- The parsed execution plan
must be in the shared pool at the same time the current sql
statement, trying to reuse it, is looking for it.
- The SELECT
operation:
- If the block is in memory,
perform a logical read.
- If the block is not in
memory, perform a physical read.
- If an index exists the
server process reads in index blocks and the actual data blocks
are read into the database buffer using the ROWID.
ROWID format = BRF Block(8)
ROWID(3) DATAFILE ID(4).
- If no index exists, full
table scan is performed. Every block of a table is
read into buffer cache until required row is found.
- Blocks read into db buffer
cache using an index stay in cache longer than those from a full
table scan. This is called the modified LRU
(least recently used) algorithm.
- The UPDATE
operation:
- Acquire data blocks into the
db buffer cache.
- Acquire rollback blocks into
the database buffer cache.
- Place exclusive row locks on
rows that are about to change.
- Store record in the redo log
buffer for before and after image.
- Save rollback data into a
rollback segment block buffer.
- Apply changes to the
database block buffer.
Manage the Database Structure
- Some ALTER TABLESPACE
options:
- NORMAL -
performs a checkpoint for all datafiles in the tablespace.
- TEMPORARY -
performs a checkpoint for just the online datafiles in the
tablespace.
- IMMEDIATE -
does not ensure that tablespace files are available and does not
perform a checkpoint.
- BEGIN BACKUP
- prepares the tablespace for an online backup.
- END BACKUP
- returns a backed up tablespace to normal status.
- READ WRITE
- allows objects to be created, modified, and dropped.
- READ ONLY -
prevents changes.
- Segment Types:
- Rollback -
Holds rollback data for rollback, read-consistency, or recovery.
- Table &
Cluster - Holds all the data for a table or
cluster.
- Index -
Holds all of the date for a specific index.
- Temporary -
Holds data belonging to temporary objects.
- Cache -
- Contains dictionary
definitions for dictionary tables that are loaded when the
database is opened.
- Is self administered and
requires no attention on the part of the DBA.
- Also referred to as the bootstrap
segment.
- Is inaccessible to users.
- Resides In the SYSTEM
tablespace and is owned by user SYS.
Manage Storage Allocation
- Extent Allocation Parameters:
- INITIAL -
Default is the equivalent of 5 data blocks. Size in bytes of
the first extent.
- NEXT -
Default is the equivalent of 5 data blocks. Size in bytes of
a subsequent extent allocation.
- MAXEXTENTS
- Minimum value = 1. Default value varies upon DB_BLOCK_SIZE
and the operating system. Specifies the maximum number of
extents, including the first extent.
- MINEXTENTS
- Default value = 1, except for rollback segments
which = 2.
- PCTINCREASE
- Default value = 50. Amount by which each extent grows over
the last extent size. Do not use for rollback segments.
- OPTIMAL -
Default value = NULL. Specifies optimal size for rollback
segments.
- FREELISTS -
Default value = 1. Number of lists of free blocks kept for
inserts into a tables.
- FREELIST GROUPS
- Default value = 1. Number of separate freelists for use by
different instances of a parallel server.
- Parts of a Database Block:
- Header -
general block info such as block address and segment type.
Grows from the top down.
- Fixed block header
= 24 bytes. Variable transaction header =
24 bytes * initrans(1 for tables 2 for clusters).
- Table Directory
- Stores info about the tables in the block for clustered
tables. Takes 4 bytes.
- Row Directory
- Contains row info about the actual rows in the block.
Requires 2 bytes of overhead per row.
- Free Space
- Set of bytes in the block available for inserts, updates or
additional transaction entries.
- Row Data -
Stores table or index data. Has 3 components: 1) Row
header = 3bytes. 2) Column data,
of varying size. 3) Length bytes if cols < 250
=1, cols >= 250 = 3.
- Index Block Header
= 161 bytes.
- Index Row Header
= 2, rather than 3 as in table row header.
- Datatype NUMBER
takes up a maximum of 21 bytes.
- Datatype DATE
is 7 bytes.
- Datatype ROWID
is 6 bytes.
- Space Mgmt Parameters.
Used to control free space in a data segment:
- PCTFREE -
Default = 10% of spaces in each block reserved for future updates
to a table's rows. When used on index segments, specifies an
amount of space reserved for additional index entries. Grows
from the top down.
- PCTUSED -
Default = 40% of used space for each data block. A block
becomes a candidate for row insertion when its used space falls
below PCTUSED. Grows from the bottom up.
- INITRANS -
Default usually 1, 2 for clustered tables. Number of
transaction entries allocated initially in the block header.
- MAXTRANS -
Default usually 255. Maximum number of transactions that can
access the block concurrently.
- PCTFREE:
- After PCTFREE
is met the block is considered full and is not available for
inserts of new rows.
- PCTFREE can
also be specified for indexes.
- Free space is filled by
inserting new rows, growth of existing rows, and by growth of data
block header.
- A block is removed from the
free list when free spaces drops down below the PCTFREE
threshold.
- When specified for indexes, PCTFREE
reserves space for both inserts and updates.
- Free space is calculated as
a percent of DB_BLOCK_SIZE minus the header.
- PCTUSED:
- When the percentage of a
block being used falls below PCTUSED, either
through row deletion or updates reducing column storage, the block
is again available for insertion of new rows.
- Oracle adds the block to a
free list when it finds that a block has dropped below its PCTUSED
quota.
- Setting a lower
PCTFREE:
- Allows inserts to fill
blocks more completely.
- May require fewer blocks to
store data.
- Can increase processing
costs if Oracle Server must frequently re-organize blocks.
- Can cause row migration.
- Use if rows will not be
updated much.
- Setting a higher
PCTFREE:
- Reserves more room for
future updates.
- May require more blocks to
store data.
- Lowers processing costs
because of less block re-organization.
- Reduces the need to chain
rows.
- Setting a lower
PCTUSED:
- Reduces processing costs
because blocks are not often free.
- Increases unused space.
- Use with high insert and
delete activity.
- Setting a higher
PCTUSED:
- Increases processing costs
because blocks may often become free.
- Improves space usage.
Manage Rollback Segments
- Rollback Segments:
- Store uncommitted and
"pre-change" data to support read and transaction
consistency.
- At instance startup Oracle
requires at least one rollback segment in the SYSTEM
tablespace.
- If a user is going to create
tables in other tablespaces besides SYSTEM at
least one more rollback segment is required.
- All rollback segments should
be the same size. The PCTINCREASE storage
parameter value is not allowed.
- Requires a minimum of 2
extents of the same size.
- wait ratio =
(waits/gets) * 100. Evaluate V$ROLLNAME and V$ROLLSTAT.
If wait ratio is > 1 this is not good.
- Rule of 4:
(Total number of active transactions / 4) = the number of rollback
segments to create for a database. If total < 8, round up to
nearest multiple of 4. If over 50 use 50. Sort of a dumb
rule but you will be quizzed on it.
- SCN (System
Change Number): Each entry in a rollback segment is associated with an
SCN. Allows rollback segment entries to be associated
with a particular transaction.
- Related init parms:
- TRANSACTIONS
- Set to average number of active transactions at any given time.
- TRANSACTIONS_PER_ROLLBACK_SEGMENT
- Number of transactions to allocate to any given rollback
segment.
- Types of rollback segments:
- Private -
Must be included in the parm ROLLBACK_SEGMENTS in
the init<sid>.ora file to be recognized by
the system.
- Public -
Forms a pool of rollback segments that can be used by any
instance. Only useful with the Parallel Server
option. Private rollback segments are usually still
preferred.
- Deferred -
Created when a tablespace goes offline in such a manner that
transactions cannot be rolled back immediately and always created
in the SYSTEM tablespace.
-Public and private
rollback segments are the only ones that can be created by a DBA.
-Need CREATE ROLLBACK
SEGMENT system privilege to create rollback segments.
- The calculation (TRANSACTIONS
/ TRANSACTIONS_PER_ROLLBACK_SEGMENT) determines the
number of rollback segments to be taken from the public pool.
Manage Cluster Segments
Cluster Segments:
- Index Cluster:
Rows from one or more tables stored together based on the value of a
column in the tables.
- Hash Cluster:
Rows from a table stored by way of a hashing algorithm.
- Cluster defines a storage
structure, each cluster contains one or more table definitions.
- Hash cluster may contain more
than one table but this is rare.
- Hash cluster can increase I/O
performance and reduce storage overhead.
- Hash cluster can reduce
insert, update, delete, and full table scan performance.
- Cluster key
is the columns the clustered tables share.
- Cluster index
is an index created on the cluster key columns. 16 columns max.
- Storing a clustered table
requires more space than storing one table alone.
- Cluster table candidates:
- Primarily queried and not
frequently updated.
- Contain redundant values
within a column.
- Often joined tables.
- Good choices for cluster
key columns:
- Containing a wide range of
values.
- Used in joins of multiple
tables.
- Bad choices for cluster key
columns:
- Few distinct values.
- Frequently updated.
- If too many rows for cluster
key value, rows are stored in chained blocks.
- If too few rows for cluster
key value, space will be wasted in data block. Set SIZE
parameter to small value.
- Steps to create a cluster:
- Create the cluster itself.
- Create table and add to
cluster.
- Create cluster key index.
- PCTFREE and PCTUSED
apply to the cluster and not to the individual tables!
- SIZE
parameter:
- Determines the maximum
number of cluster keys that can be stored in a data block.
- Is an optional parameter.
- Specifies estimated number
of bytes needed to store the average cluster key and its rows.
- Limits the number of cluster
keys stored in a data block.
- If too big will cause wasted
space.
- ALTER CLUSTER
allows you to change:
- Storage parameters.
- Data block space usage
parameters (PCTFREE and PCTUSED).
PCTFREE and PCTUSED apply to all
cluster blocks, current and subsequent.
- Average cluster key size (SIZE).
SIZE applies to all cluster blocks, current and
subsequent.
- Transaction entry settings (INITRANS
and MAXTRANS). INITRANS
affects only subsequent blocks. MAXTRANS affects
all data blocks.
Hash Clusters:
- Apply a hash function
to the rows of a table. Stores rows according to the result of
the hash function.
- Use hash clusters
when:
- Tables generally do not vary
in size.
- Optimizing queries are prime
concern.
- Queries are always equality
queries on the hashed column.
- The hash key
is well distributed.
- To hash a table it must be
clustered first. A hash cluster must exist before creating a table to
use the hash algorithm.
- Oracle rounds the HASHKEYS
value to the nearest prime number.
- The minimum number of hash
keys = 2.
- SIZE
parameter controls the number of hash keys assigned to a data block.
- Overestimating the value of SIZE
increases the amount of unused space in the cluster.
- If average hash key size is
small, many hash keys can be assigned to each data block.
- If average hash key size is
large, only a few hash keys can be assigned to each data block.
- Frequent collisions
cause overflow blocks to be created.
HASH IS column:
The HASH IS
column must meet these conditions:
- It must be the only column
in the cluster key.
- The cluster must be a single
column with a datatype of NUMBER.
- The cluster key contains
non-negative integers only.
- Can contain a user-defined
hash function.
If these conditions are not
met, you must use the internal hash function or specify a user-defined
hash function.
-The SIZE, HASHKEYS
and HASH IS parameters cannot
be altered with the ALTER CLUSTER command. To change
these parameters you must recreate the cluster and copy the data from
the original cluster. SIZE can not be changed
for a hash cluster.
Manage Users
- SYS and SYSTEM
are reserved user names in Oracle.
- A CREATE USER
statement can have multiple QUOTA clauses, each for
different tablespaces.
- A quota on a
tablespace is required to create database objects in it.
Inserts, updates and deletes are controlled by privileges, not quotas.
- Only the owner of an object
needs a usage quota, and even then only when the object is created or
grows.
- Users accessing objects owned
by others do not need usage quotas.
- Be sure SYSTEM
is not default or temporary tablespace of any user!!!
- When creating a user in
Server Manager, the CONNECT role is automatically
granted.
- You can assign a default profile
at user creation time with the CREATE USER command.
- The ALTER USER
system privilege is required to issue the 'ALTER USER'
SQL command. This includes assigning a profile or default role to an
existing user.
- If you set a quota of zero on
an existing tablespace, the objects owned by users remain in the
revoked tablespace. However, the objects cannot be allocated any
new space.
- The 'ALTER USER'
command can change user profile and roles.
- The ALTER SYSTEM
privilege is required to kill an active user's session.
Manage Resource Usage
- With Oracle profiles
you can manage:
- CPU time
- I/O operations
- idle time
- connect time
- memory space (private SQL
area/MTS only)
- concurrent sessions
- Can be enforced at the session
level, call level, or both. Can be explicit (specific
resource limit) or composite (sum).
- RESOURCE_LIMIT
initialization parameter:
- TRUE
enables enforcement.
- FALSE
disables enforcement (default).
- Use this method to enable
enforcement when the database can be shutdown.
- ALTER SYSTEM
command:
- Setting made with this
command stays in effect until altered again or the database is
shutdown.
- Use this method to enable
enforcement when the database can not be shutdown.
- The CREATE PROFILE
system privilege is needed to create a profile. Oracle
creates a default profile when the database is created.
- Use the ALTER PROFILE
command to change system limitations. Changes to profiles
do not affect the current session. The ALTER PROFILE
system privilege is required to alter a profile.
- 'DROP PROFILE CASCADE'
must be used if profile has been assigned to a user.
- Default profile:
- Assigned as default to user
if profile is not explicitly assigned.
- All unspecified limits for
any profile have the default profile value.
- Initially, all defaults are
unlimited.
- Can be altered so that no
user can have unlimited use of resources by default.
- Use ALTER USER
command to assign a profile to an existing user.
- Composite limit:
Weighted sum of CPU_PER_SESSION, CONNECT_TIME,
PRIVATE_SGA, and LOGICAL_READS_PER_SESSION.
Use to limit resources for a session.
- Resource costs:
- Used to weight CPU_PER_SESSION,
CONNECT_TIME, PRIVATE_SGA, and LOGICAL_READS_PER_SESSION.
- Applied only to the
composite limit of a profile.
- Do not apply to explicitly
set individual resource limits.
- Use ALTER RESOURCE
COST command to specify weights for each session resource
limit.
- Data dictionary views related
to profiles:
- DBA_USERS
- USER_RESOURCE_LIMITS
- DBA_PROFILES
- RESOURCE_COST
Manage Database Access
System Privileges:
- Used to control the creation
of all database objects.
- Includes the ANALYZE
and AUDIT capabilities.
- Not specific to a named
schema, object or structure. Specific to a particular operation
or class of operations.
- SELECT ANY TABLE
gives the user the right to query any table in the database.
- Can be granted with the WITH
ADMIN OPTION. This allows the person receiving the
privilege or role to grant it to another user or role. The grantee can
also alter or drop the role if granted WITH ADMIN OPTION.
- A role is a
named group of privileges that are granted to users and other roles.
- Removal of system privileges
(by using REVOKE) does not cascade.
- System privileges SYSDBA
and SYSOPER cannot: 1) be granted WITH ADMIN
OPTION and 2) be granted to roles.
- DBA_SYS_PRIVS
view displays all system privileges granted to roles and users.
- REVOKE x
FROM PUBLIC revokes the system privilege or role from all
users.
Object Privileges:
- Allows users to perform a
particular action on a specific table, view, sequence, or stored
procedure.
- SELECT FROM
object (table, view, snapshot)
- UPDATE
object (table or view)
- INSERT INTO
object (table or view)
- ALTER
object (table or seq)
- CREATE TRIGGER ON
object (tables only)
- DELETE FROM
object (table or view)
- TRUNCATE
object (tables only)
- EXECUTE OBJECT
(procedure or function)
- CREATE INDEX ON
object (tables only)
- REFERENCES
- CREATE or ALTER TABLE defining
a foreign key on a table only.
- Can be granted with the WITH
GRANT OPTION. Allow grantee to grant to other users and
roles.
- WITH GRANT OPTION
itself can not be granted to a role.
- Object privileges granted WITH
GRANT OPTION are revoked when the grantors privilege is
revoked.
- Related data dictionary
views:
- DBA_TAB_PRIVS
- all privileges on objects
- DBA_COL_PRIVS
- all privileges on columns
- USER_TAB_PRIVS
- user is owner, grantor, or grantee
- USER_TAB_PRIVS_MADE
- user is the owner
- USER_TAB_PRIVS_RECD
- user is the grantee
- USER_COL_PRIVS
- user is owner, grantor, or grantee
- USER_COL_PRIVS_MADE
- user is the owner
- USER_COL_PRIVS_RECD
- user is the grantee
- Grantors can revoke
privileges from only those to whom they have granted privileges.
- You must use the CASCADE
CONSTRAINTS option to revoke REFERENCES
privilege.
Manage Roles
- The CREATE ROLE
privilege is required to create a role.
- Role creation options:
- NOT IDENTIFIED:
users granted the role need not be verified by oracle to
enable it.
- IDENTIFED:
users granted the role need to be verified by oracle to
enable it.
- BY
password: user must specify the password to enable the role.
- EXTERNALLY:
Oracle verifies access to role via OS system utility.
- First, create the role.
Second, grant privileges to the role. Finally, grant the role to
the user.
- Use the 'ALTER ROLE'
command to change authorization needed to enable the role.
- The 'SET ROLE'
command enables roles. Any roles not specified are disabled for
the session.
- SET ROLE ALL:
- Enables all roles granted to
a user except those listed in the EXCEPT clause.
- Roles listed in the
EXCEPT clause must be granted directly to you, not
through another role.
- Can not use this option to
enable roles with passwords that have been granted directly to
you.
- Works when all roles
involved have either no password or are authorized externally (by
the OS).
- Establish default role with
the ALTER USER command.
- Related data dictionary
views:
- ROLE_SYS_PRIVS:
system privs granted to roles
- ROLE_TAB_PRIVS:
object privs granted to roles
- ROLE_ROLE_PRIVS:
roles granted to other roles
- SESSION_ROLES:
roles user has currently enabled
- USER_ROLE_PRIVS:
roles granted to the user
- DBA_SYS_PRIVS:
description of sys-privs granted to users and roles
- DBA_ROLES:
all roles that exist in the db
- OSOPER/SYSOPER
roles:
STARTUP, SHUTDOWN,
ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP CONTROLFILE, ALTER
TABLESPACE BEGIN/END BACKUP, ARCHIVE LOG AND RECOVER. Also
contains the RESTRICTED SESSION privilege.
- OSDBA/SYSDBA
roles:
- All system privileges WITH
ADMIN OPTION and the OSOPER role.
- Permits CREATE
DATABASE and time-based recovery. OSOPER/SYSOPER
does not.
- To set up operating system
role management:
- Create roles in the Oracle
Server.
- Set OS_RULES
parameter equal to TRUE in init<sid>.ora.
- Give each user operating
system rights to indicate is or her database roles. The
format is OS specific.
Audit the Database
- Oracle database auditing
consists of statement, privilege,
and object level auditing.
- All auditing information is
stored in the audit trail.
- Set the AUDIT_TRAIL
parameter in init<sid>.ora to write the audit
trail. Possible values are:
- DB: enables
auditing and directs all audit records to the database (SYS.AUD$
view)
- OS: enables
auditing and directs all audit records to the operating system
audit trail (if permitted).
- NONE:
disables auditing. This is the default value.
- Run cataudit.sql
and catnoaudit.sql to create and remove the data
dictionary tables and views for auditing.
- Instance startup, shutdown,
and connections to the database with administrator privilege is ALWAYS
audited, no matter what the AUDIT_TRAIL parameter
value is.
- Auditing is not carried out
if you CONNECT INTERNAL, or connect as user SYS.
- Statement and privilege
auditing options specified by the audit command apply only to
subsequent sessions, not the current one.
- The AUDIT SYSTEM
privilege is required to execute AUDIT and NOAUDIT
commands.
- If you omit the WHENEVER
clause, both successful and unsuccessful attempts will be audited.
- When auditing unsuccessful
actions, no audit record is generated for invalid statements such as
syntax errors.
- AUDIT
command options:
- user
- session
- access
- whenever successful
- whenever not successful
Disable with the NOAUDIT
command.
Object AUDIT:
- Object audit options always
set for all users. Can not be specified for specific users.
- The object must be in your
own schema or you must have the AUDIT ANY
privilege.
- Set defaults for schema
objects that have yet to be created.
- The AUDIT SYSTEM
privilege is required to set DEFAULT audit
options on objects.
- The audit trail stores
records generated by statement, privilege,
and object auditing.
- The audit trail is located in
the SYS.AUD$ data dictionary table.
- Audit records are written
during the execute phase of statement execution.
- Auditing by session
writes only one record per session to the audit trail for a given
database object.
- The maximum size of the audit
trail is determined at database creation time in the sql.bsq
script.
- A user must own an object or
have AUDIT ANY privilege to audit an object.
- Be sure the security
manager is the only user with AUDIT ANY
privilege and/or do not allow any other actual users to own their
objects.
- Records can be deleted from
the audit trail by anyone with the DELETE ANY TABLE
privilege.
- Related audit trail data
dictionary views:
- STMT_AUDIT_OPTION_MAP:
maps audit option number to its name
- AUDIT_ACTIONS:
maps an action number to its name
- ALL_DEF_AUDIT_OPTS:
default audit options
- DBA_STNT_AUDIT_OPTS:
system auditing options
- DBA_PRIV_AUDIT_OPTS:
system privilege auditing options
- DBA_OBJ_AUDIT_OPTS:
auditing options on objects
- USER_OBJ_AUDIT_OPTS:
auditing options on objects
- DBA_AUDIT_TRAIL:
all audit trail entries
- USER_AUDIT_TRAIL:
user related entries in the audit trail
- DBA_AUDIT_SESSION:
all connect and disconnect entries
- USER_AUDIT_SESSION:
user’s connect and disconnect entries
- DBA_AUDIT_STATEMENT:
all records concerning dba system auditing
- USER_AUDIT_STATEMENT:
as above but for the current user
- DBA_AUDIT_OBJECT:
all records concerning objects
- USER_AUDIT_OBJECT:
as above but for the current user
- DBA_AUDIT_EXISTS:
all records for EXISTS/NOT EXISTS
|
