Security Administration

Create user
Kill user
Product profile

Implement basic security features first, like those listed below. Advanced security mechanisms such as network encryption clearly help protect data, but they do not take the place of basic, effective change-control mechanisms and a secure production environment.

Gaurd your backups and development environments.

The most successful attempts to break into databases oftem exploit 2 areas in which data is held unsecured: backups and development databases. If I can acquire a copy of the backup then I can re-create the database on my own server.
Tape Backups: Checking tapes out from the tape library should include steps to authorize and document who has the tape, how long for and where its going.
Disk Backups: These should be protected from people reading them.
Development Databases: In many cases these are populated with production data. Anybody in the company often then has access to this data.

Know your default user and application accounts

Depending on the options selected Oracle 8.1.5 may create up to 12 database accounts. Check all accounts, remove those that are unnecessary and change the passwords on the others.
DBSNMP: This user is used by the intelligent agent, delete it if you are not using the intelligent agent. If you keep it, change its password remembering to update this in the snmp.ora file and protect the snmp.ora file from unauthorized reads.
Many application accounts have far more privileges than they need.
Make sure passwords are not visible by file searches ( such as UNIX grep).

Control the distribution of database names and locations

Net8 lets you use service names and aliases to mask the physical location of a database only to those who must know. If you use tnsnames.ora file for managing access to databases then remember that every user with a copy can read it, possibly gaining valuable information about sensitive databases. Keeping multiple copies of tnsnames files only allowing users to have access to information that they require is a way round this. Oracle names avoids the tnsnames problem but make sure that the central names.ora file is protected.

Make Password changes mandatory

With Oracle8 you can force passwords to expire (be careful thet sys, system etc. are not in the same profile). Make this as easy as possible, perhaps via a screen or procedure.

Isolate your production database

Revoke operating system access for developers, implement a standard change-control process.
Never publicize the name of the database and server.
Forbid the use of the production database for development or testing.
Receive privileges via roles, rather than direct grants. They can then be revoked quickly.

Change all operating system passwords regularly

Do not keep hard copies of passwords and change them all regularly (including development ones). Protect any password files.

Create User Syntax

 CREATE USER name IDENTIFIED BY password
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
QUOTA UNLIMITED ON tablespace
PROFILE profile;

Kill a User

 To identify users :

select sid, serial#, status
from v$session
where username = 'NAME';

sid serial# status
7 ...15 ....active

alter system kill session '7,15';


Product User Profile

 Within SQL*Plus, an additional level of security is provided - individual commands may be disabled for specific users. It is useful for revoking host or connect.
The script for creating the required tables is pupbld.sql, it is found in sqlplus/admin and should be run under SYSTEM.