2

Agenda

Database Overview Oracle Audit/Security/Control

3

Objectives

Your objectives Magic Disk

– Database Control Objectives– Frank W. Lyons– Entellusfl@aol.com– 407-774-8397

4

Terminology Database

– A set of data Tablespaces

– Logical division of a database Files

– datafile Instances

– Also known as a server Table, columns and datatypes

5

Columns

The characteristics of a column are made up of two parts: its datatype and its length.

For columns using the NUMBER datatype, the additional characteristics of precision and scale can be specified. Precision determines the number of significant digits and Scale determines the placement of the decimal point

6

Structure Query Language (SQL)•Data Definition Language

–DDL »Create, Drop, Alter

•Data Manipulation Language–DML

»Select, Insert, Update, Delete•Data Control Language

–DCL»Grant, Revoke

The Database Language

7

Obtain the Data Structure Diagram

Customer Table Warehouse Table

Order Table

The data structure diagram provides allthe tables and columns

Database Structure

8

Tables Tables owned by the user SYS are called the data

dictionary tables

Dictionary tables provide a system catalog that the database uses to manage itself

The database maintains the relationship between table by using referential integrity

9

Object Dependencies

Database

Tablespace

Table

Index Synonym

View

Synonym

This is the file name under theoperating system

Database Structure

10

Databases A database is a set of data.

Oracle provides the ability to store and access data in a manner consistent with a defined model known as the Relational Model.

11

Tablespaces A tablespace is a logical division of a database

Each tablespace is constituted of one or more files, called datafiles, on a disk. A datafile can belong to one and only one tablespace

12

To prevent users from creating objects in the SYSTEM tablespace, any quotas on SYSTEM, which could give a user the ability to create objects in the SYSTEM tablespace, must be revoked:

– alter user Frank quota 0 on SYSTEM

When you create a new user via the create user command, you can specify a default tablespace:

– create user Frank identified by excellence– default tablespace Human_Resources

Tablespaces

13

SYSTEM Data Dictionary DATA Standard-operation tables DATA_2 Static tables used during standard operation INDEXES Indexes for the standard operation tables INDEXES_2 Indexes for the static tables RBS Standard operation rollback segment RBS_2 Specialty rollback segments used for data loads TEMP Standard operation temporary segments TEMP_USER Temporary segments created by a user TOOLS RDBMS tools tables TOOLS_1 Indexes for RDBMS tools tables USERS User objects, in development databases

Tablespaces

14

Instance In order to access the data in the database, Oracle uses a set

of background processes that are shared by all users.

A database instance (also known as a server) is a set of memory structures and background processes that access a set of database files.

15

Views Views appear to be a table containing columns and is

queried in the same manner that a table is queried Views do not use physical storage to store data Views can not be indexed.

16

Typical Authentication Pyramid

Application Logon

Reporting Tool

Database

DataAccountability lessens as you move down the

pyramid

17

Where Are the Application Controls?

Direct access usually by-passes application controls– User profiling is normally only used within the

application– Views of reporting data could be incorporated – Summarized data could be used to reduce

sensitivity

18

Relational Data Storage

Employee Table

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

19

Views Based on User Profile

View of the Commercial Division

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Security is based on data value

20

Reduced Data Sensitivity

Employee Locator View

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Security is based on columns selected

21

Summarized Views

View Summarized by Division

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Division Total SalaryCorporate 164,830Commerical 356,550

22

Access Type Difference

Id and Passwords User Profiles Accountability and audit ability

23

Data Access

Application interfaces Reporting Tools Direct access

24

ID and Password Controls

Where is the ID and Password Stored for verification?

How is the password stored? Is the same user ID used for multiple

applications? Is the password stored in multiple locations?

25

User Profiles

Profiles allow access according to job responsibilities– Division– Position– Security Clearance

26

User Profiles Controls Who creates and modifies the user profile? What audit trails are in place for profile changes? What approvals are required for changes to the user

profile? What is the notification process for job responsibility

changes?

27

Accountability and Auditability Is the user id translated to a high powered id during

application access to the data? Does the application record read access? When data is inserted, modified, or deleted is the

change logged?– If there is a log who is reviewing the log?

28

ID Translation

Used to prevent users from accessing data directly Prevents authentication by the database management

system Creates tuning and monitoring challenges

29

Reporting Tools and Direct Access

Ad-hoc reporting– User flexibility– Less labor to support user reporting

requirements– Checks and balance to insure information

accuracy

30

Separate Reporting Database

Name Location Phone Division Salary

Order Date Customer Status

Customer Address Phone Last Order

Name Location Phone Division

Order Date Total Amount

Customer Address Phone Last Order

On-line Database

Reporting Database

Synchronization can be done real time or on intervals

31

Advantages of Separate Reporting Databases

Design for reporting efficiencies On-line environment not impacted by the reporting workload

Data can be summarized to reduce data sensitivity Multiple reporting databases can be defined to limit access

to sensitive information

32

Disadvantages of Separate Reporting Databases

Extra storage and processor resources are required

Extra labor resources are required to support replication process

Special controls needed for direct access authority Read activity needs to be logged for audit ability

33

Reporting Tools

Name Location Phone Division Salary

Order Date Customer Status

Customer Address Phone Last Order

Name Location Phone Division

Order Date Total Amount

Customer Address Phone Last Order

Reporting Database

Reporting Tool View of Data

Reporting tools can limit access by column, data value, or through summarization.

34

Web Access

Reporting Tools can push static or dynamic information Design should balance performance differences with

flexibility Security at the data level needs to be well understood so that

access is based on data sensitivity and job responsibilities

35

Application Recovery

Code is normally static Code changes should be installed on well defined

intervals Recovery usually requires reboot of application

server Corrupted application files can be restored from the

last copy

36

Database Recovery

Data is a constantly changing resource Rebooting the database server causes the database

system to recovery any in-flight units of work Restoring data from backup requires the database

logs to be applied to the data in order to restore data consistency

37

Database Logs

Data Identification (Record Header)

Before Image of Data

Kristine Smith,Director,Headquarters,x8839,Corportate,75330 Kristine Smith,Director,Headquarters,x8839,Corportate,75330

Kristine Smith,Director,Headquarters,x8839,Corportate,82100 Kristine Smith,Director,Headquarters,x8839,Corportate,82100

Dbid Tableid Rowid Dbid Tableid Rowid

After Image of Data

38

Log Contains All Data Modifications

Database Log

Database

User Activity

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

39

Backout Due to Abort or Abend

Database Log

Database

User Activity

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Aborted Unit of Work

Log Records are read to backout the changes to the data

40

Protecting Database Log

Critical Recovery Resource Contains Sensitive Information Needed for on-site and off-site recovery Log shipping often done for off-site recovery

41

Log Shipping

Database Log

Database

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Name Title Location Phone Division SalaryKristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

On-site

Database Log CopyName Title Location Phone Division Salary

Kristine Smith Director Headquarters Room 3400 x8839 Corporate 75,330John Thomas Legal Headquarters Room 3360 x7583 Corporate 89,500Suzie West Carpenter Building 120 Room 200 x3579 Commerical 32,100Phil Walker Administrator Bulding 120 Room 130 x3221 Commerical 35,000Mike Harris Plummer Building 120 Room 210 x4422 Commerical 34,200Sandy Morse Electrician Building 120 Room 170 x9576 Commerical 36,440Joe Campbell Technician Building 120 Room 140 x7473 Commerical 23,000Don Wagner Technician Building 120 Room 140 x5759 Commerical 27,810Renee Anderson Plummer Building 120 Room 210 x3589 Commerical 27,300Bob Williams Electrician Building 120 Room 170 x8305 Commerical 31,200Sam White Electrician Building 120 Room 170 x7362 Commerical 29,500Dale Thomas Carpenter Building 120 Room 200 x9973 Commerical 34,700Tom Woods Manager Building 120 Room 220 x6392 Commerical 45,300

Off-site

42

Criteria for Server Selection in the Database Environment

Stability Security Recoverability Performance

43

Key Points

Application access controls are by-passed through most reporting interfaces

Security design MUST be centered on the data and incorporated in each interface

Data and application recovery have key differences and need special considerations

44

Key Points Security should be designed around the DATA All interface points must be reviewed Data recoverability needs differ between application and

data Database logs contain sensitive information

Oracle

46

Identify Risks

Default Users Operating System configuration Database server configuration Listener process Privileges Database links Patches

47

Init.ora

Database startup file

This file is read during the instance startup and may be modified by the DBA. Any modifications to this file will not take affect until the next startup that uses this file

48

In the default directory configuration, the init.ora file is stored in a directory named

– /orasw/app/oracle/admin/instance_name/pfile The init.ora file does not list the names of the datafiles or

online redo log files for the database as these are stored in the data dictionary.

Init.ora does list the names of the control files for the database

Init.ora

49

Config.ora A second configuration file is typically used to store the

settings of variables that do not change after database creation

Such as the database block size

In order for the config.ora settings to be used, the file must be listed as an include file via the IFILE parameter in the instance’s init.ora file

50

Procedures

A procedure is a block of PL/SQL statements that is stored in the data dictionary and is called by applications

Stored procedures help to enforce data security

51

Functions Functions, like procedures are blocks of code that are

stored in the database.

Function are capable of returning values to the calling program

52

Packages Packages are used to arrange procedures and functions into

logical groupings Packages have a public and private elements Private elements may include procedures called by other

procedures within the package Source code for the functions, procedures, and packages are

stored in the data dictionary tables

53

Triggers Triggers are procedures that are executed when a

specified database event takes place against a specified table

Used as part of referential integrity Used to enforce additional security Used to enhance the available auditing options

54

Two types of triggers– Statement triggers

• Fire once for each triggering statement– Row triggers

• Fire once for each row in a table affected by the statement For each type a BEFORE trigger and AFTER trigger can be

created Triggering events include inserts, updates, and deletes

Triggers

55

Synonyms To completely identify a database object such as a table or

a view, the host machine name, the server instance name, the object’s owner, and the object’s name must be specified

Synonyms reduce this effort Public synonyms are shared by all users Synonyms can provide pointers for tables, views,

procedures, functions, packages, and sequences

56

Database Life Cycles Planning Creating Monitoring Tuning Securing Auditing

57

Operating system configuration•Use file system security to protect the DBMS software and data files (Only allow the Oracle user and Oracle group access to the files)

•Turn off all operating system functionality/services that are not required by the DATABASE SERVER (mail,ftp,telnet)

• Turn on O/S level auditing and review the audit log daily

•Secure the backup of the database

•Audit regularly

58

Database System ConfigurationRemove non-essential users and enforce password management (see appendix)

Change the default passwords on accounts

Do not allow development in a production database/server

Secure the development database (may contain production data)

Keep the software up to date and patched

59

Database System ConfigurationOracle Database - Physical Structures

•When a backup of a database occurs, the backup software is making copies of the physical structures of an Oracle instance.

•Physical Structures stored in the operating system

•Control File

•Online Redo Log

•Data File

•INIT.ORA

•SPFILE (Oracle9i)

•ORACLE_HOME (Oracle software)

•Backup destination

60

Database System ConfigurationOracle Database/Physical Structures

•Control File

•Contains information about the instance and all of it’s external files.

•Used by Oracle to know if a data file needs recovery.

•Audit view: V$CONTROLFILE

•Online Redo Log

•Keeps track of all the transactional activity that makes changes to the database.

•Audit view: V$LOGFILE

61

Database System ConfigurationOracle Database/Physical Structures

•Data File

•Associated with a single tablespace.

•Oracle server creates a data file for a tablespace by allocating the specified amount of disk storage + a small overhead.

•Can contain data segment (table), index segment (primary keys, unique constraints or tuning indexes), rollback and temporary segments.

•Audit view DBA_DATA_FILES

•INIT.ORA

•Used by the Oracle instance to configure how much of the OS resources will be used by the instance.

•Parameters can be placed here for optional processes

• Audit view V$PARAMETER

62

Database System ConfigurationOracle Database/Physical Structures

•Data File

•Audit view DBA_DATA_FILES

SQL> select file_name from dba_data_files

order by file_name

•Control File

•Audit view: V$CONTROLFILE

SQL> select * from v$controlfile;

•Online Redo Log

Audit view: V$LOGFILE

SQL> select * from v$logfile

order by group#

63

Database System ConfigurationOracle Database/Physical Structures

•INIT.ORA

•Audit view V$PARAMETER

SQL> select name,value from v$parameter

License/version

Audit view v$version

SQL> select * from v$version

64

Privileges

Third party application owner will typically have the ‘DBA’ role granted to them

This is needed for installs/upgrades of software, but typically can be removed for day to day activities

SELECT ANY TABLE privilege should not be allowed on any ‘end user’

65

Privileges

•SELECT ANY TABLE privilege gives users the ability to select from ANY table

–including SYS.USER$ and SYS.LINK$–These tables will show passwords

66

Privileges Audit Checklist

SQL> select privilege privilege_granted, grantee,admin_option from sys.dba_sys_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by privilege_granted,admin_option;

67

Privileges Audit Checklist

PRIVILEGE_GRANTED GRANTEE ADM------------------------------------------------- ---QUERY REWRITE AROLE NO QUERY REWRITE DBA YESRESTRICTED SESSION DBA YESSELECT ANY DICTIONARY OEM_MONITOR NO SELECT ANY DICTIONARY DBA YESSELECT ANY TABLE DBA YES

SELECT ANY TABLE IMP_FULL_DATABASE NO

68

Database Link

Used to connect one database to anotherThe Database link contains: 

•USER ACCOUNT TO CONNECT TO THE TARGET DATABASE

•LOCAL USERNAME AND PASSWORD (HARD CODED) or pass through authentication

•CONNECT STRING (Oracle / SQLNET/ NET8)

Audit view: dba_db_links

69

Oracle NET / NET8 /SQL*NET LISTENER Configuration

1

listener.oralistener.ora

ClientClient

ServerServer

Listener

On portsqlplus user/pw@DB1sqlplus user/pw@DB1

User

DB1DB1 tnsnames.ora/sqlnet.oratnsnames.ora/sqlnet.ora

2

3

4

Server

70

Oracle NET / NET8 /SQL*NetLISTENER

•Oracle NET enables communications between partners in a distributed transaction

•Applies to client/server or server/server environment.

•During the life of a connection, resolves all differences between the internal data representations and/or character sets of the computer.

•Net8 has been renamed to Oracle NET for 9i

71

Harden the Listener Process

The configuration file for the listener is LISTENER.ORA.

LISTENER.ORA resides on the server and defines…

• Listener name, a database can have multiple listeners

• The network listener address

• The SID of the database for which it listens

• Parameters that influence the network listener’s behavior, including tracing, timeout and logging and password

72

Harden the Listener Process

Prevent unauthorized administration of the Oracle Listener by:

ADMIN_RESTRICTIONS_listener_name=ON

•Use a password on all listeners•At the lsnrctl prompt enter change_password

73

Oracle NET / Net8 /SQL*NETConfiguration

•Oracle Names

•Network service that provides centralized name resolution to Oracle clients and servers.

•Consists of one or more administrative regions, each having a single installation of the Oracle Network Manager tool.

•Oracle Network Manager enables the administrator to administer the following in it’s administrative region…

•All database listeners

•Global database links

•Clients, interchanges and Names Servers.

74

Oracle NET/ NET8 / SQL*Net Configuration

•Oracle Names (continued)

•Clients do not need a TNSNAMES.ORA file if an Oracle Names Server is used.

•If a TNSNAMES.ORA file is created, the client may use it to resolve the service name before resolving it through the Names Server.

•Clients have a SQLNET.ORA file that identifies Oracle Names Server. This file can reference a file on a server so that it doesn’t have to change if the Names Server changes.

75

Oracle NET / NET8 /SQL*NETConfiguration

ServerServer

Names ServerNames Server

1

2

3

4

ClientClient

76

Oracle NET/ NET8 /SQL*Net Audit checklist

• Listener password protected

• Is ADMIN_RESTRICTION turned on

• Is a protocol.ora in place for node checking

• How is tnsnames protected

• ONAMES is used, this adds an additional server to be audited

77

Oracle listenerAudit checklist

•Set up the listener with password

•Set up a strong password

•Protect the listener .ora file (this is where the password is kept)

78

Patches Audit checklist

Verify patches using the V$VERSION view

Keep informed on the latest security patches

http://otn.oracle.com/deploy/security/alerts.htm

Have a policy on how quickly a critical security patch should be applied

Oracle rates severity of patches one is most severe

79

PatchesAlerts (Subscribe to security alerts  )

PDFBuffer Overflows in EXTPROC of Oracle Database ServerAlert #57, Rev 2, 07August 2003PDFBuffer Overflow Vulnerability in Oracle E-Business SuiteAlert #56, Rev 1, 23 July 2003PDFUnauthorized Disclosure of Information in Oracle E-Business SuiteAlert #55, Rev 1, 23 July 2003PDFBuffer Overflow in Net Services for Oracle Database ServerAlert #54, Rev 2, 30 April 2003PDFReport Review Agent Vulnerability in Oracle E-Business SuiteAlert #53, Rev 1, 10 April 2003PDFTwo Security Vulnerabilities in Oracle9i Application ServerAlert #52, Rev 3, Updated 03 March 2003PDFBuffer Overflow in ORACLE executable of Oracle9i Database ServerAlert #51, Rev 6, Updated 18 April 2003PDFBuffer Overflow in Oracle9i Database ServerAlert #50, Rev 6, Updated 18 April 2003

80

Oracle's built-in toolsUSERS / SCHEMAS

Security DomainDefines the security settings thatApply to the user

Authentication MechanismDatabaseOperating systemNetwork

Privileges—Direct/Indirect (via roles)

81

Oracle's built-in toolsUSERS / Database SCHEMAS

Authentication Mechanism

Database– passwords are kept internally in a database table encrypted

Operating system—Passwords are kept in the operating system

82

Oracle's tools to purchaseAuthentication Mechanism usingOracle Advanced Security option

Network– Uses third party network authentication services (like Kerberos and SESAME)

Token Devices, one time passwords are used to authenticate

Biometrics Devices, use physical features of users to authenticate

Advanced Encryption Standard (AES)•AES is symmetric block cipher•AES-128,AES-192 and AES-256

83

Oracle's built-in toolsUSERS / Database SCHEMAS

Predefined users

Sys/change_on_install (super user in Oracle,schema for Oracle dictionary tables)

System/manager (Owner of internal tables used by Oracle tools)

Scott/tiger (created by demo files, sometimes left in production)

Note: 9i passwords are custom, unless you create the Database using the Database creation assistant in batch mode

Additional users defined later in the Oracle9i section

84

Oracle's built-in tools Password File to authenticate DBAs

A password file for DBAs is optional and can be setup using the ORAPWD password utility.

The password file will restrict administration privilege to only the users who know the password and have been granted a special role.

The roles are SYSDBA and SYSOPER.

85

Oracle's built-in toolsTwo special roles SYSOPER/SYSDBA

SYSOPER Permits you to perform

STARTUP, SHUTDOWN,ALTER DATABASE OPEN/MOUNT, ALTERDATABASE BACKUP, ARCHIVE LOG, andRECOVER, and includes the RESTRICTED SESSIONprivilege. 

86

Oracle's built-in tools

SYSDBAContains all system privileges with ADMIN OPTION, and the SYSOPER system privilege; CREATE DATABASE and time-based recovery.

Listing Password File Members

Audit view: V$PWFILE_USERS will show all users that have been granted SYSDBA and SYSOPER system privileges for a database.

87

Oracle's built-in toolsADMINISTERING PRIVILEGES

Two types of privileges:

System– Enables users to perform ADMIN type activities in the database

OBJECT– Enables users to access and manipulate objects

88

Oracle's built-in toolsSYSTEM PRIVILEGES

Administering privileges

System– 80 system privilegesThe ANY keyword means that the user has the privilege

for any schema.

The GRANT command is used to add a privilege to a user or group of users

GRANT CREATE SESSION to smith;GRANT CREATE SESSION to smith with ADMIN

OPTION;

The REVOKE command deletes the privilegeREVOKE CREATE SESSION to smith;

89

Oracle's built-in toolsSYSTEM PRIVILEGES

Administering privileges—Displaying System Privileges

DATABASE LEVEL SESSION LEVEL

DBA_SYS_PRIVS SESSION_PRIVSGrantee PrivilegePrivilegeAdmin option

90

Oracle's built-in toolsOBJECT PRIVILEGES

OBJECT SQL STATEMENT AllowedALTER   ALTER object (table or sequence)  DELETE DELETE FROM object (table or view)  EXECUTE EXECUTE object (procedure or function).

References to public package variables  INDEX CREATE INDEX ON object (tables only)  INSERT INSERT INTO object (table or view)  REFERENCES CREATE or ALTER TABLE statement

defining a FOREIGN KEY integrity constraint on object (tables only)  

SELECT   SELECT...FROM object (table, view, or snapshot). SQL statements using a

sequence  

91

Oracle's built-in toolsDISPLAYING OBJECT

PRIVILEGES

DBA_TAB_PRIVS DBA_COL_PRIVSGrantee GranteeOwner OwnerTable_name Table_nameGrantor Column_namePrivilege Grantorgrantable  Privilege

Grantable

92

Oracle's built-in toolsUSERS / Database SCHEMAS

Restriction privilege system by enabling

O7_DICTIONARY_ACCESSIBILITY=FALSE

This prevents users with the system ‘ANY’ from being able to execute against the SYS schema

The default is TRUE

This allows the user with the ‘ANY’ privilege to execute against the SYS schema

93

Oracle's built-in toolsAdministering privileges

A role is a database entity that is a named group of privileges. It is unique within the database and not owned by a user.

A role can be authenticated by a password.

Special role SELECT_CATALOG_ROLE, which enable access to the data dictionary

94

Oracle's built-in toolsPredefined roles

ROLENAME DESCRIPTION==============================================CONNECT Provided for backward RESOURCE compatibilityDBA All systems privileges

w/adminEXP_FULL_DATABASE Privileges to export the DBIMP_FULL_DATABASE Privileges to Import the DBDELETE_CATALOG_ROLE Delete privileges on dictionaryEXECUTE_CATALOG_ROLE Execute privilege on

dictionarySELECT_CATALOG_ROLE Select privilege dictionary tablesPUBLIC Role that all users have

95

Oracle's built-in toolsDisplaying information on roles

DATABASE ROLE VIEW DESCRIPTIONDBA_ROLES All roles which exist in the DBDBA_ROLE_PRIVS Roles granted to users and rolesROLE_ROLE_PRIVS Roles which are granted to rolesDBA_SYS_PRIVS System privileges grated to users

and rolesROLE_SYS_PRIVS System privileges granted to rolesROLE_TAB_PRIVS Table privileges granted to rolesSESSION_ROLES Roles which the user currently has

enabled

96

Oracle's built-in toolsPROFILES used for password

management

• A PROFILE is a named set of limits for passwords and system resources

• Are assigned to users

• Can become the default for all users

• Can be enabled or disabled

97

Oracle's built-in toolsPROFILES

A PROFILE is a named set of limits for passwordsAnd system resources

1. CPU time2. I/O operations3. IDLE time (inactive time measured in minutes)4. Connect time (measured in minutes)5. Memory space6. Concurrent sessions7. Passwords aging and expiration8. Password history9. Password complexity verification10. Account locking

98

Oracle's built-in toolsPROFILES management

Create a profileCREATE PROFILE end_user_prof LIMIT SESSIONS_PER_USER 1IDLE_TIME 60CONNECT TIME 600;

Modify a profileALTER PROFILE end_user_prof limitIDLE_TIME 10;

Remove a profileDROP PROFILE end_user_prof;DROP PROFILE end_user_prof CASCADE;

99

Oracle's built-in toolsPROFILES management

Associate a user to a profile

ALTER USER smithPROFILE end_user_prof;

Resource limits must be enable to enforce profile limits,This does not include password management.

Two ways to enable:1. ALTER SYSTEM SET RESOURCE_LIMIT=TRUE

1. Modify the init.ora file and set RESOURCE_LIMIT=TRUE,2. Restart the instance

100

Oracle's built-in toolsPROFILES displaying resource limits

DBA_USERSProfileusername

DBA_PROFILESProfileResource_nameResource_typeLimit

Join the views DBA_USERS and DBA_PROFILESTo display the resource limits

101

Oracle's built-in toolsPASSWORD MANAGEMENT

PARAMETER DESCRIPTION

FAILED_LOGIN_ATTEMPTS Number of failed login attempts before lockout

PASSWORD_LOCK_TIME Number of days password will remain locked upon password expiring

PASSWORD_LIFE_TIME Lifetime of password measured in days

PASSWORD_GRACE_TIME Grace period in days for changing the password, after it has expired

102

Oracle's built-in toolsPASSWORD MANAGEMENT

PARAMETER DESCRIPTION

PASSWORD_REUSE_TIME Number of days before a password can be reused

PASSWORD_REUSE_MAX Maximum number of times a password can be

reused

PASSWORD_VERIFY_FUNCTION PL/SQL package that makes a complexity

check before a password is assigned

Note: The script utlpwdmg.sql must be run in the SYS schema toenable

103

Oracle's built-in toolsPASSWORD MANAGEMENT

VERIFY_FUNCTION• Minimum length of four characters• Password not equal to user name• Password must have at least one alpha,

numeric,special character• Password must differ from the last password by three

characters

104

Oracle's built-in toolsPASSWORD MANAGEMENT

CREATE PROFILE passwd_mgtFAILED_LOGIN_ATTEMPTS 3PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_TIME 30PASSWORD_VERIFIY_FUNCTION verify_functionPASSWORD_GRACE_TIME 5;

105

Oracle's built-in toolsDisplaying PROFILES

information

DBA_PROFILES

Profile

Resource_name

Resource_type =PASSWORD

limit

106

Auditing the Database Audit a specific user Audit for a specific statement Audit for a specific statement on a schema

user Audit to know what happens on your

database Audit failed logon attempts Audit to know who changed what and when

107

Auditing the Database Audit by session or access Audit by successful or not successful status Audit with discretion

Audit actions:

Alter, audit,comment,create,delete,execute, grant,index,insert,lock,read,reference,rename,select,update,write

108

Auditing the Databasesyntax

AUDIT {statement|system_priv}BY userBY SESSION|ACCESSWHENEVER {NOT} SUCCESSFUL;

NOTE:BY SESSION will create only one audit recordPer sessionBY ACCESS will create a record for each auditable activity

109

Auditing the Database(cont)

Audit to know what happens on your database– High level of unsuccessful logins– Audit through the middle tier – Audit the user statement– Audit the audit table

110

Auditing the Database connect internal (Oracle8i)

– NT Systems:  On NT Systems you can see auditing for INTERNAL in the event viewer. The INTERNAL connections are written to the operating system audit trail .

–  Unix Systems:  On Unix Systems the INTERNAL connections are logged to special log files stored in the $Oracle_HOME/rdbms/audit directory.

111

Auditing the Database SYSDBA/SYSOPER

– Initialization parameter

AUDIT_SYS_OPERATIONS=TRUE

Will write all activities to an O/S audit log

112

Auditing the Database(cont) TIPS on auditing

– Run reports on a daily basis– Truncate the audit table on a daily basis– Use whenever not successful option (whenever you

can)– Use the by session clause– Auditing is now optimized (statements are parsed once

for execution and audit– Set default audit options– May need to create an alarm facility

113

Auditing the Database(cont)

TIPS on auditing– Oracle preserves the identify of the user on the middle tier

and can capture the user id of who logged into the database via the TP monitor

This means that oracle can audit the true user who initiated the transaction and the user who logged into the database (TP monitor)

Protect the audit trailAUDIT delete ON sys.aud$ BY ACCESSOnly the DBA or SECURITY personnel shouldHave the DELETE_CATALOG_ROLE

114

Auditing the DatabaseViews to the AUDIT Table

ALL_DEF_AUDIT_OPTSAUDIT_ACTIONSDBA_AUDIT_EXISTSDBA_AUDIT_OBJECTDBA_AUDIT_SESSIONDBA_AUDIT_STATEMENTDBA_AUDIT_TRAILDBA_OBJ_AUDIT_OPTSDBA_PRIV_AUDIT_OPTSDBA_STMT_AUDIT_OPTS

115

Audit/Security Approach Account Security Object Privileges System Level Roles and Privileges

116

Create User– Username– Password– Default Tablespace– Temporary Tablespace– Quota (on Tablespaces)– Profile

Assigns a profile to the user, if none is specified, then the default profile is used. Profiles are used to restrict the usage of system resources and to enforce password management rules. The default is set to UNLIMITED resource consumption

Audit/Security Approach

117

Audit/Security Approach

System Level Privileges– ANY and PUBLIC are not synonymous. A PUBLIC object

is accessible to all users in a database; all other objects are privately owned. The ANY option allows you to create private objects in other users’ schemas

– There are eight system level roles provided with Oracle• Connect

EXP_FULL_DATABASE• Resource Select_Catalog_Role• DBA Execute_Catalog_Role• IMP_FULL_DATABASE Delete_Catalog_Role

118

Audit/Security Approach

User Profile– SESSION_PER_USER– CPU_PER_SESSION– CPU_PER_CALL– CONNECT_TIME– IDLE_TIME– LOGICAL_READS_PER_SESSION– LOGICAL_READS_PER_CALL– PRIVATE_SGA– COMPOSITE_LIMIT– FAILED_LOGIN_ATTEMPTS– PASSWORD_LIFE_TIME– PASSWORD_REUSE_TIME– PASSWORD_REUSE_MAX– PASSWORD_LOCK-TIME– PASSWORD_GRACE_TIME– PASSWORD_VERIFY_FUNCTION

119

Audit/Security Approach

Object Level Privileges– Grants– Grant with grant option– Privileges

• SELECT• INSERT• UPDATE• DELETE• ALTER• INDEX• REFERENCES• EXECUTE• READ

120

Audit/Security Approach

Dictionary Views– DBA_ROLES Names of roles and their password status

– DBA_ROLE_PRIVS Users who have been granted roles

– DBA_SYS_PRIVS Users who have been granted system privileges

– DBA_TAB_PRIVS Users who have been granted privileges on tables

– DBA_COL_PRIVS Users who have been granted privileges on columns

– ROLE_ROLE_PRIVS Roles that have been granted to other roles

– ROLE_SYS_PRIVS System privileges that have been granted to roles

– ROLE_TAB_PRIVS Table privileges that have been granted to roles

121

Audit/Security Approach

Password Security During Logins– When you connect to a database server from a client

machine, or from one database to another via a database link, Oracle transmits the password you enter in an unencrypted format unless you specify otherwise. As of Oracle8, you can set parameters that forces Oracle to encrypt the password values prior to transmitting them.

– To enable password encryption, set the following parameters:

• On the client set the ORA_ENCRYPT_LOGIN parameter in your sqlnet.ora file to TRUE

• On the server set the DBLINK_ENCRYPT_LOGIN parameter in your init.ora file to TRUE

• Shut down and restart the database

122

Password– Password specified for a user account or a role are stored

in an encrypted version in the data dictionary– Setting the same password for two different accounts will

result in different encryption– For all passwords, the encrypted value is 16 characters

long and contains numbers and capital letters.– When a password is entered during a user validation, that

password is encrypted, and the encryption that is generated is compared to the one in the data dictionary for that account, if they match, then the password is correct and the authorization succeeds.

Audit/Security Approach

123

Audit/Security Approach Passwords

– Knowing how the database stores passwords is important because it adds new options to account security.

– To query the Username and Password fields from DBA_USERS

• select– Username, /*Username*/– Password */Encrypted password*/– from DBA_USERS– where Username is (‘Lyons’)

124

Audit/Security Approach Becoming Another User

– Since the encrypted password can be set, you can temporarily take over any account and then set it back to its original password without ever knowing the account’s password. This capability allows you to become another user

– Query DBA_USERS to determine the current encrypted password to the account

– Generate the alter user command that will be needed to reset the encrypted password to its current value after you are done

– Spool the alter user command to a file– Change the user’s password– Access the user’s account – Run the file containing the alter user command to reset the user’s

encrypted password to its original value.

125

Database Determine Permission Levels

– Do not give direct table level permissions to an end user

• No - Select (Query from “Hell”), Update, Insert, Delete

– Instead, use stored procedures– Better yet, do not let user know how to sign on to

the database application• Authenticate the user and then supply a new

password that they do not even know about. In this way the user must first authenticate to the application and cannot go around this authentication process to access the database

126

Privileges and Roles Access to an object owned by another account Privilege must have been granted Privileges such as insert, select, update, and delete Privileges can be granted to users, groups, roles, or to

Public Roles are groups of privileges Use roles to grant system level privileges such as create

table

127

Role Definitions System Level Roles

– Connect– Resource– DBA

User Defined System Level Roles

128

Role Definitions Connect Role

– Gives users privileges beyond just creating sessions in the database. In addition to the Create Session system privilege, the Connect role gives the users the following system privileges

• Alter Session• Create Cluster• Create Database Link• Create Sequence• Create Synonym• Create Table• Create View

129

Role Definitions Resource Role

– The resource role has the following system privileges• Create Cluster• Create Index• Create Procedure• Create Sequence• Create Table• Create Trigger• Create Type

130

Role Definitions DBA Role

– The DBA role has all system privileges with admin option, which means that the DBA can grant the system privileges to any other user

131

Auditing The database has the ability to audit all actions that

take place within it. Audit records may be written to either the SYS.AUD$ or the operating system’s audit trail. The ability to use the operating system’s audit trail is operating system-dependent

Three different types of actions may be audited: Login attempts, Object accesses and Database actions The database’s default functionality is to record both

successful and unsuccessful commands To enable auditing in a database, the init.ora file for the

database must contain an entry for the AUDIT_TRAIL parameter.

132

Auditing The AUDIT_TRAIL values are:

• NONE - Disable auditing• DB - Enables auditing, writing to the SYS.AUD$ table• OS - Enables auditing, writing to the operating system’s audit

trails

133

Auditing Audit command

– Can be issued regardless of the setting of the AUDIT_TRAIL parameter. They will not be activated unless the database is started using an init.ora AUDIT_TRAIL value that enables auditing

– If you elect to store the audit records in the SYS.AUD$ table, then that table’s records should be periodically archived and the table should then be truncated. Since it is the data dictionary, this table is in the SYSTEM tablespace and may cause space problems if its records are not periodically cleaned out. You can grant DELETE_CATLOG_ROLE to a user to give the user the ability to delete from the SYS.AUD$ table.

– Every attempt to connect to the database can be audited. The command to begin auditing of login attempts is:

•audit session•audit session whenever successful•audit session whenever not successful

134

Establish minimum audit standards

Login/Logoffs• Adding of New Users• Adding Users to Groups• All Grants and Revokes

Remember that auditing takes up much storage and processing time

Audit Features

135

Trace Files Each of the background processes running in an instance

has a trace file associated with it. The trace file will contain information about significant events

encountered by the background process In addition, Oracle maintains a file called the alert log The alert log records the commands and command results of

major events. This includes, tablespace creations, redo log switches, recovery operations, and database startups

136

Alert Log The alert log is a vital source of information for day-

to-day operations Trace files are most useful when attempting to

discover the cause of a major failure.

137

Database Constraints A table can have constraints placed upon it A constraint is applied to a table and every row in the table

must satisfy the conditions specified in the constraint definition

138

Database

DBMS Journal Redo Logs SVRMGR program for Database Administration tasks

• Performs backup of the redo logs SQL COMMIT statements

• Used by application programs Backup is on-line

139

Backup/Recovery Backup and Recovery Options Export/Import Offline Backups Online Backups

140

Users A user account is not a physical structure in the database Users own the database objects The user SYS owns the data dictionary tables The user SYSTEM owns views that access the data

dictionary tables for use by the rest of the users in the database

141

Users User accounts can be connected to an operating

system This allows the user to enter only one password

to obtain access to the operating system and the database

142

Database Responsibility for the Security Officer

– Should Perform:• All Grants and Revokes• Review Security and Audit Logs• Maintain a copy of Audit Logs for the Auditors

143

Exposures

Clear text transmission over the network Connect ID and Access ID for application Direct connection to the database from the

desktop

144

Securing the SQL* Plus Tool

PRODUCT_PROFILE table to provide product-level

 

Security for oracle products and augment user-level  Security

With this table, you can enforce security on a per-user basis and restrict certain SQL and SQL*plus commands.

145

Securing the SQL* Plus Tool

 SQL*plus commands:EDIT EXECUTE EXIT GET HOST (or your operating system’s

alias for HOST, such as $ on VMS and ! on UNIX)

QUIT RUN SAVE SET (Spool start SQL commands:Alter analyze audit connect create deleteDrop grant insert lock NOAUDIT rename revokeSelect set role set transaction truncate update

 

146

Securing the SQL* Plus ToolRecommended commands to restrict

Commands reason 

HOST allows user access to a operating-system prompt 

SET ROLE allows a user to set a new security role 

GRANTallows a user to grant privileges 

NOAUDIT allows a user to turn off auditing You should restrict access to the PRODUCT_PROFILE

147

Oracle Security Checklist– Revoke unnecessary privileges from

PUBLIC ROLE• Revoke all unnecessary privileges and roles

from the database server• PUBLIC is the default role granted to every

user– Privileges include EXECUTE on various powerful

packages that may potentially be misused include:

UTL_SMTP• This package permits arbitrary mail messages

to be sent from one arbitrary user to another arbitrary user.

148

Oracle Security Checklist

– Revoke unnecessary privileges from PUBLIC ROLE

UTL_TCP• This package permits outgoing network

connections to be established by the database server to any receiving (or waiting) network service.

UTL_HTTP• This package allows the database server to

request and retrieve data via HTTP. Granting this package to PUBLIC may permit data to be sent via HTML forms to a malicious web site.

149

Oracle Security Checklist

– Revoke unnecessary privileges from PUBLIC ROLE

UTL_FILE• If configured improperly, this package

allows text level access to any file on the host operating system. Even when properly configured, this package does not distinguish between its calling applications with the result that one application with access to UTL_FILE may write arbitrary data into the same location that is written to by another application.

150

Oracle Security Checklist– Revoke unnecessary privileges from PUBLIC ROLE– DBMS_RANDOM

• Is used to encrypt stored data. • encrypted data may be non-recoverable if the keys are not

securely generated

Do not assign “all permissions” to any database server run-time facility

• Oracle Java Virtual Machine (OJVM)• Grant specific permissions to the explicit document root file

paths for such facilities that may execute files and packages outside the database server.

– call dbms_java.grant_permission('SCOTT', 'SYS:java.io.FilePermission','<<ALL FILES>>','read');

151

Oracle Security Checklist– Authenticate clients properly

• Remote authentication is a security feature provided by Oracle9i such that if turned on (TRUE), it defers authentication of users to the remote client connecting to an Oracle database.

• configuration parameter in the following manner: REMOTE_OS_AUTHENT = FALSE will be more secure

RESTRICT NETWORK ACCESS– Utilize a firewall– Keep the database server behind a firewall. Oracle9i’ s

network infrastructure, Oracle Net (formerly known as Net8 and

SQL*Net), offers support for a variety of firewalls from various vendors. Supported proxy-enabled firewalls

152

Oracle Tools Virtual Private Database/label security

– Attach security policies at the Table or View level, allows for easy integration to existing systems.

– One to many policies per Table.

– Different policies for different type of accesses (SELECT,UPDATE..).

153

Oracle Security Checklist

Virtual Private Database using application contexts.– User-definable can allow security based

on categories (Employee number, Cost Center). These contexts are used in the security policy function.

– Access to session primitives (information about a user session) using USERENV application context

154

Oracle Tools

– Oracle Enterprise Manger• Security Manager

Application Security, Inchttp://www.appsecinc.com/products/APPLICATION VULNERABILITY ASSESSMENT AppDetective™

Network-based vulnerability assessment tool that rates the security strength of applications within your network. Armed with a revolutionary security methodology together with an extensive knowledgebase of application vulnerabilities, AppDetective™ will locate, examine, report, and help fix your security holes and miss-configurations at your command.

155

Oracle Tools

DATABASE ENCRYPTION DbEncrypt™

Easy-to-use, affordable, and effective security solution for encrypting column and row data within the database. Provided with DbEncrypt™ are encryption algorithms, templates, and an intuitive point-and-click interface.

APPLICATION INTRUSION DETECTION AppRadar™

Intrusion detection solution strictly for application-specific attacks and malicious behavior. As a complementary solution to existing intrusion detection systems, AppRadar™ empowers organizations with a real-time security solution able to thwart attacks and malicious behavior against all of your mission-critical enterprise applications.

156

Oracle Security checklist

Important web siteshttp://otn.oracle.com/deploy/security/alerts.htmhttp://technet.oracle.com/deploy/security/alerts.htmhttp://www.nessus.org/http://www.insecure.org/http://www.atstake.com/research/http://www.securityfocus.com/

157

Summary

– Oracle was built to be secure– Audit at the database, network, and server level– Takes time and is complex– Limited tools available– Requires outages for patches

158

Checklist Predefined Users

USERNAME ACCOUNT_STATUS JONES EXPIRED & LOCKED LBACSYS EXPIRED & LOCKED MDSYS EXPIRED & LOCKED OE EXPIRED & LOCKED

159

Checklist Predefined Users

USERNAME ACCOUNT_STATUS OLAPDBA EXPIRED & LOCKED OLAPSVR EXPIRED & LOCKED OLAPSYS EXPIRED & LOCKED ORDPLUGINS EXPIRED & LOCKED ORDSYS EXPIRED & LOCKED OSE$HTTP$ADMIN OPEN OUTLN OPEN

160

Checklist Predefined Users

USERNAME ACCOUNT_STATUS PM EXPIRED & LOCKED QS EXPIRED & LOCKED QS_ADM EXPIRED & LOCKED QS_CB EXPIRED & LOCKED QS_CBADM EXPIRED & LOCKED QS_CS EXPIRED & LOCKED QS_ES EXPIRED & LOCKED QS_OS EXPIRED & LOCKED QS_WS EXPIRED & LOCKED SCOTT OPEN SH EXPIRED & LOCKED SYS OPEN SYSTEM OPEN

161

Checklist Predefined Users USERNAME ACCOUNT_STATUS ADAMS EXPIRED & LOCKED AURORA$JIS$UTILITY$ OPEN AURORA$ORB$UNAUTHENTICATED OPEN BLAKE EXPIRED & LOCKED CLARK EXPIRED & LOCKED CTXSYS EXPIRED & LOCKED DBSNMP OPEN HR EXPIRED & LOCKED