Contents

Controlling Usernames

Auditing

Backup

Recovery

Controlling Usernames

When creating and altering usernames, you need to be aware of three areas:

  1. Default tablespace
  2. Temporary tablespace
  3. Resource settings

Tablespaces

If a user’s default and temporary tablespaces are not set, then the database will default to the SYSTEM tablespace. This is not good because you do not want to store user and temporary objects within the SYSTEM tablespace. The SYSTEM tablespace is used to store the Data Dictionary information.

Resource Settings

Resource limits are important because they control how much space and processor usage is allocated to a user. User profiles are used to limit resources. "To use resource limits within your database, you must first turn them on, by setting the initialization parameter resource_limit to TRUE within the init.ora file for the database." (Fieldhouse)

To check which profile has been assigned to each user, use the command:

SQL> select username, profile

from dba_users

where username not in (‘SYS’, ‘SYSTEM’);

To check the resource settings for a certain profile, such as sam_user,

use the command:

SQL> select resource_name, resource_type, limit

from dba_profiles

where profile = ‘sam_user’;

To check the setting for the initialization parameter resource_limits,

We will query a system table, as shown below:

SQL> select name, value

from v_$parameter

where name = ‘resource limit’;

This example shows that the resource limiting is not turned on.

 

Return to the top

Auditing

Auditing is used to track what user changed a piece of data and when the data was changed. Auditing is important to use in a multi user environment.

To enable or disable auditing you must first set the audit_trail parameter in the init.ora file. This parameter has 3 possible values:

NONE – Auditing is turned off. This is the default.

DB – "Auditing is logged to the SYS.AUD$ table within the database."

OS – "Auditing is logged to an operating system file. This is not possible on Personal Oracle8 running Windows 95." (Fieldhouse)

If the audit_trail parameter is set to DB, then it is a good idea to store the audit table in a separate tablespace because it can get very large.

To enable auditing:

  1. Set the audit_trail parameter to DB or OS.
  2. Use the AUDIT command.

To turn auditing off:

  1. Set the audit_trail parameter to NONE.
  2. Use the NOAUDIT command.

A user must have the AUDIT_ANY privilege to use audit commands.

There are three types of auditing:

  1. Access
  2. Object
  3. Database

Access Auditing

You can see how many successful and unsuccessful log in attempts a user has made. If you choose to set the audit_trail parameter to DB, then the auditing data is stored in the SYS.AUD$ table, which is in the SYS schema.

To view the SYS.AUD$ table:
  1. Log in to the database as SYSTEM.
  2. Issue the command:

SQL> desc sys.aud$

To begin auditing sessions:
  1. Issue the command:

SQL> audit session;

Audit succeeded.

Log in to the database as Sam, Sally, and System.

SQL> connect

Enter user-name: sam

Enter password: ***

Connected.

SQL> connect

Enter user-name: sally

Enter password: ****

Connected.

SQL> connect

Enter user-name: system

Enter password: *******

Connected.

To query the sys.aud$ table for auditing information:

  1. Log in to the database as the system manager.

    2. SQL> connect

    Enter user-name: system

    Enter password: *******

    Connected.
  2. Issue the command:

SQL> select userid,

to_char(timestamp#, ‘DD/MM/YY HH:MI:SS’) time_on,

to_char(logoff$time, ‘DD/MM/YY HH:MI:SS’) time_off

from sys.aud$;

This table will show you all successful and unsuccessful log in attempts to the database.

Object Auditing

You can audit any changes made to a table, such as inserts, updates, and deletes, by using the AUDIT INSERT, UPDATE, DELETE command. To audit all actions performed on an object, use the AUDIT ALL command.

Example of auditing changes made to a table:

  1. Connect as the SYSTEM user.

    2. SQL> connect

    Enter user-name: system

    Enter password: *******

    Connected.
  2. Issue the command:

    3. SQL> audit insert, update, delete on sam.coach;

    Audit succeeded.

  3. Log in to the database as Sam.

    4. SQL> connect

    Enter user-name: sam

    Enter password: ***

    Connected.
  4. Insert a row of data into the player table, as shown below.

    5. SQL> insert into player

    values (15, ‘Sara’, ‘12/01/99’, 1, 01);

    1 row created.

  5. Connect as the SYSTEM user.

    6. SQL> connect

    Enter user-name: system

    Enter password: *******

    Connected.
  6. Query the dba_audit_object table to see the auditing information, as shown below.

SQL> select username, obj_name, owner

From dba_audit_object;

USERNAME OBJ_NAME OWNER

SAM COACH SAM

 

Database Auditing

You can audit "a set of actions" (Fieldhouse). For example, you can audit the ROLE command, which would audit the following commands:

CREATE ROLE, ALTER ROLE, SET ROLE, DROP ROLE

This would show you what use has done something to ROLES.

 

Return to the top

Backup

You should perform regular backups of your data. Backups are essential to recover your data if the database crashes or becomes corrupted. There are two ways to backup your data: the Backup Manager and the export utility.

Export utility

"Export is a utility that extracts data and object definitions from your database." (Fieldhouse) You can then import (copy) the data and object definitions to another database. The export utility only should be used with the import utility. Using the export utility you can decide to export the entire database or just certain parts of the database. There are two ways to use the export utility: through the Navigator or from the DOS command line.

The Navigator

To export data from your database:
  1. Open the Navigator.
  2. Click on the Projects folder.
  3. Click on the local database icon to start the database.
  4. Right click on the local database icon and select Export.
  5. This opens the Export – Save as window. Here you will specify where you want to store your export file. Type in your file name and click Save.

After you have clicked Save, Oracle "initiates a DOS session in the background that exports all of your data to the specified file." (Fieldhouse)

Using the Navigator your entire database is automatically exported, you do not have a choice which data to export. Once Oracle is finished exporting your data, it will ask you if you want to view the log file. It is a good to view the log file to check whether the export was successful or not.

DOS command line

This method allows you to specify which data you want to export. For example, you can choose to export:

There are three modes in which you can export data from your database:

  1. Table mode: "Exports all table information for the table(s) specified. Administrative users can export tables owned by other users; normally, you will be able to export only a table you own – i.e., in your own schema."
  2. User mode: "Exports all user objects, such as tables, indexes and views. This works on a per-user basis – i.e., you tell it which user to export. Administrative users can export the objects for any user."
  3. Full Database mode: "Exports all of the database information for all users. This also exports all of the system definitions, including tablespaces, with the exception of any objects owned by SYS. Only administrative users can export in full database mode." (Fieldhouse)

 

To export data from your database using the full database mode:

To get help at any time, use the command EXP80 help = y

  1. Open a DOS window.
  2. Issue the command the following command at the command line prompt:

EXP80 system/manager FILE=fulldb.dmp FULL=Y

This command would export the entire database "to a file called fulldb.dmp in the current directory." (Fieldhouse)

There are several parameters that can be used following the EXP80 command. I have listed a few important ones below. Additional information about the export utility commands can be found in Personal Oracle8 Explorer, written by Richard Fieldhouse.

Common parameters:

log – You can set the log parameter to log the output of the export utility. Use the log parameter with the file name and directory to log the output to.

owner – When you want to export the data in user mode, use the owner parameter. "This parameter can be supplied as a comma-separated list if more than one user is to be exported." (Fieldhouse)

tables – When you want to export specific tables from the database, use the tables parameter. "This parameter can be followed by a list of tables in parentheses and separated by commas." (Fieldhouse)

 

 Return to the top

Recovery

A recovery is performed to recover your data after your database has crashed.

There are two ways to perform recovery: through the Recovery Manager or the import utility.

Import utility

The import utility uses a previously created backup file made by the export utility. Using this method, you can specify if you want to import the entire database or just parts of it. There are two methods available when performing export or import: The Navigator or the DOS command line.

The Navigator

To export data from your database:

  1. Open the Navigator.
  2. Click on the Projects folder.
  3. Click on the local database icon to start the database.
  4. Right click on the local database icon and select Import.
  5. This opens up the Import window. This window asks you where the file to import is located. Type in the file name and location and click Open.

After you have clicked Open, Oracle "initiates a DOS session in the background, importing all of your data to the database." (Fieldhouse)

When using the Navigator, you do not get choice of what data to import. After the import has finished, you will prompted to view the log file. It is a good idea to view the log file, to check whether the import was successful or not.

DOS command line

This method allows you to specify which data you want to export. For example, you can choose to export:

  • A single user
  • A single table
  • The entire database

There are three modes in which you can import data into your database:

  1. Table mode: "Imports all table information for the table(s) specified."
  2. User mode: "Imports all user objects, such as tables, indexes and views. This works on a per-user basis – i.e., you tell it which user to import. Administrative users can import the objects for any user."

3. Full Database mode: "Imports all of the database information for all users. This also imports all of the system definitions, including tablespaces, which were exported from the database using the export utility." (Fieldhouse)

 

To import data from your database using the full database mode:

To get help at any time, use the command EXP80 help = y
  1. Open a DOS window.
  2. Issue the command the following command at the command line prompt:

IMP80 system/manager FILE=fulldb.dmp FULL=Y

This command would import the entire database to your local database.

There are several parameters that can be used following the IMP80 command. I have listed a few important ones below. For additional information about the import utility commands refer to Richard Fieldhouse’s Personal Oracle8 Explorer.

Explanations of a few im