Agenda

1
(No Transcript)
2
Agenda

• Database Overview
• Oracle Audit/Security/Control

3
Objectives

• Your objectives
• Magic Disk
• Database Control Objectives
• Frank W. Lyons
• Entellusfl_at_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

• The Database Language

• 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

7

Database Structure

• Obtain the Data Structure Diagram

Customer Table
Warehouse Table
Order Table
The data structure diagram provides all the
tables and columns
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

Database Structure

• Object Dependencies

Database
This is the file name under the operating system
Tablespace
Table
Index
Synonym
View
Synonym
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

Tablespaces

• 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

13

Tablespaces

• 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

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
Accountability lessens as you move down the
pyramid
Data
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
19
Views Based on User Profile
View of the Commercial Division
Security is based on data value
20
Reduced Data Sensitivity
Employee Locator View
Security is based on columns selected
21
Summarized Views
View Summarized by Division
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
Reporting Database
On-line 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
Reporting Tool View of Data
Reporting Database
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
After Image of Data
38
Log Contains All Data Modifications
User Activity
Database Log
Database
39
Backout Due to Abort or Abend
User Activity
Database Log
Database
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
Off-site
Database Log Copy
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

45

• 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

Init.ora

• 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

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 instances 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

Triggers

• 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

55
Synonyms

• To completely identify a database object such as
  a table or a view, the host machine name, the
  server instance name, the objects owner, and the
  objects 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/servic
  es 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 Configuration

• Remove 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 Configuration Oracle 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 Configuration Oracle
Database/Physical Structures

• Control File
• Contains information about the instance and all
  of its external files.
• Used by Oracle to know if a data file needs
  recovery.
• Audit view VCONTROLFILE
• Online Redo Log
• Keeps track of all the transactional activity
  that makes changes to the database.
• Audit view VLOGFILE

61
Database System Configuration Oracle
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 VPARAMETER

62
Database System Configuration Oracle
Database/Physical Structures

• Data File
• Audit view DBA_DATA_FILES
• SQLgt select file_name from dba_data_files
• order by file_name
• Control File
• Audit view VCONTROLFILE
• SQLgt select from vcontrolfile
• Online Redo Log
• Audit view VLOGFILE
• SQLgt select from vlogfile
• order by group

63
Database System Configuration Oracle
Database/Physical Structures

• INIT.ORA
• Audit view VPARAMETER
• SQLgt select name,value from vparameter
• License/version
• Audit view vversion
• SQLgt select from vversion

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
SQLgt 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_o
ption
67
Privileges Audit Checklist
PRIVILEGE_GRANTED GRANTEE
ADM --------------------------------------------
----- --- QUERY REWRITE AROLE
NO QUERY REWRITE DBA
YES RESTRICTED SESSION DBA
YES SELECT ANY
DICTIONARY OEM_MONITOR NO
SELECT ANY DICTIONARY DBA
YES SELECT ANY TABLE DBA YES SELECT
ANY TABLE IMP_FULL_DATABASE NO
68
Database Link

• Used to connect one database to another
• The 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 /SQLNET LISTENER Configuration
Server
Client
70
Oracle NET / NET8 /SQLNet LISTENER

• 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 listeners
  behavior, including tracing, timeout and
  logging and password

72
Harden the Listener Process

• Prevent unauthorized administration of the Oracle
  Listener by
• ADMIN_RESTRICTIONS_listener_nameON
• Use a password on all listeners
• At the lsnrctl prompt enter change_password

73
Oracle NET / Net8 /SQLNET Configuration

• 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 its
  administrative region
• All database listeners
• Global database links
• Clients, interchanges and Names Servers.

74
Oracle NET/ NET8 / SQLNet 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 doesnt have to
  change if the Names Server changes.

75
Oracle NET / NET8 /SQLNET Configuration
3
Names Server
76
Oracle NET/ NET8 /SQLNet 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 listener Audit 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 VVERSION view Keep
informed on the latest security
patches http//otn.oracle.com/deploy/security/aler
ts.htm Have a policy on how quickly a critical
security patch should be applied Oracle rates
severity of patches one is most severe
79
Patches
Alerts (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 2003PDFUnauthorize
d 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 Domain Defines the security settings
that Apply to the user Authentication
Mechanism Database Operating system Network Pri
vilegesDirect/Indirect (via roles)
81
Oracle's built-in toolsUSERS / Database SCHEMAS
Authentication Mechanism Database passwords
are kept internally in a database table
encrypted Operating systemPasswords are kept
in the operating system
82
Oracle's tools to purchase

• Authentication Mechanism using
• Oracle 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,
ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER,
and includes the RESTRICTED SESSION privilege.  
86
Oracle's built-in tools
SYSDBA Contains all system privileges with ADMIN
OPTION, and the SYSOPER system privilege CREATE
DATABASE and time-based recovery. Listing
Password File Members Audit view VPWFILE_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
privileges The 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
privilege REVOKE CREATE SESSION to smith
89
Oracle's built-in toolsSYSTEM PRIVILEGES
Administering privilegesDisplaying System
Privileges DATABASE LEVEL SESSION
LEVEL DBA_SYS_PRIVS SESSION_PRIVS Grantee Priv
ilege Privilege Admin option
90
Oracle's built-in toolsOBJECT PRIVILEGES
OBJECT SQL STATEMENT Allowed ALTER   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_PRIVS Grantee Grantee Ow
ner Owner Table_name Table_name Grantor Col
umn_name Privilege Grantor grantable  Privile
ge Grantable
92
Oracle's built-in toolsUSERS / Database SCHEMAS
Restriction privilege system by enabling
O7_DICTIONARY_ACCESSIBILITYFALSE 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 compatibility DBA All
systems privileges w/admin EXP_FULL_DATABASE Priv
ileges to export the DB IMP_FULL_DATABASE Privil
eges to Import the DB DELETE_CATALOG_ROLE Delete
privileges on dictionary EXECUTE_CATALOG_ROLE Exe
cute privilege on dictionary SELECT_CATALOG_ROLE
Select privilege dictionary tables PUBLIC Role
that all users have
95
Oracle's built-in toolsDisplaying information on
roles
DATABASE ROLE VIEW DESCRIPTION DBA_ROLES All
roles which exist in the DB DBA_ROLE_PRIVS Roles
granted to users and roles ROLE_ROLE_PRIVS Role
s which are granted to roles DBA_SYS_PRIVS Syste
m privileges grated to users and
roles ROLE_SYS_PRIVS System privileges granted
to roles ROLE_TAB_PRIVS Table privileges
granted to roles SESSION_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 passwords
• And system resources
• CPU time
• I/O operations
• IDLE time (inactive time measured in minutes)
• Connect time (measured in minutes)
• Memory space
• Concurrent sessions
• Passwords aging and expiration
• Password history
• Password complexity verification
• Account locking

98
Oracle's built-in toolsPROFILES management
Create a profile CREATE PROFILE end_user_prof
LIMIT SESSIONS_PER_USER 1 IDLE_TIME 60 CONNECT
TIME 600 Modify a profile ALTER PROFILE
end_user_prof limit IDLE_TIME 10 Remove a
profile DROP 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 smith
• PROFILE end_user_prof
• Resource limits must be enable to enforce profile
  limits,
• This does not include password management.
• Two ways to enable
• ALTER SYSTEM SET RESOURCE_LIMITTRUE
• 1. Modify the init.ora file and set
  RESOURCE_LIMITTRUE,
• 2. Restart the instance

100
Oracle's built-in toolsPROFILES displaying
resource limits
DBA_USERS Profile username
DBA_PROFILES Profile Resource_name Resource_type L
imit
Join the views DBA_USERS and DBA_PROFILES To
display the resource limits
101
Oracle's built-in toolsPASSWORD MANAGEMENT
PARAMETER DESCRIPTION FAILED_LOGIN_ATTEMPTS Nu
mber 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_TIM
E Grace period in days for changing the
password, after it has expired
102
Oracle's built-in toolsPASSWORD MANAGEMENT
PARAMETER DESCRIPTION PASSWORD_REUSE_TIME Numb
er of days before a password can be
reused PASSWORD_REUSE_MAX Maximum number of
times a password can be reused PASSWORD_VERI
FY_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 to enable
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_mgt FAILED_LOGIN_ATTEMPTS
3 PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_TIME
30 PASSWORD_VERIFIY_FUNCTION verify_function PA
SSWORD_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,sele
  ct,update,write

108
Auditing the Databasesyntax

• AUDIT statementsystem_priv
• BY user
• BY SESSIONACCESS
• WHENEVER NOT SUCCESSFUL
• NOTE
• BY SESSION will create only one audit record
• Per session
• BY 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_OPERATIONSTRUE
• 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 trail
• AUDIT delete ON sys.aud BY ACCESS
• Only the DBA or SECURITY personnel should
• Have the DELETE_CATALOG_ROLE

114
Auditing the DatabaseViews to the AUDIT Table

• ALL_DEF_AUDIT_OPTS
• AUDIT_ACTIONS
• DBA_AUDIT_EXISTS
• DBA_AUDIT_OBJECT
• DBA_AUDIT_SESSION
• DBA_AUDIT_STATEMENT
• DBA_AUDIT_TRAIL
• DBA_OBJ_AUDIT_OPTS
• DBA_PRIV_AUDIT_OPTS
• DBA_STMT_AUDIT_OPTS

115
Audit/Security Approach

• Account Security
• Object Privileges
• System Level Roles and Privileges

116

Audit/Security Approach

• 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

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

Audit/Security Approach

• 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.

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 accounts 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 users password
• Access the users account
• Run the file containing the alter user command to
  reset the users 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
  systems audit trail. The ability to use the
  operating systems audit trail is operating
  system-dependent
• Three different types of actions may be audited
• Login attempts, Object accesses and Database
  actions
• The databases 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 systems 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 tables 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

• Audit Features

• 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

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
  SQLplus commands.

145
Securing the SQL Plus Tool

•  SQLplus commands
• EDIT EXECUTE EXIT GET HOST (or your operating
  systems alias for HOST, such as on VMS and !
  on UNIX)
• QUIT RUN SAVE SET (Spool start
•  
• SQL commands
• Alter analyze audit connect create delete
• Drop grant insert lock NOAUDIT rename revoke
• Select 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
•  
• GRANT allows 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',
  'SYSjava.io.FilePermission','ltltALL
  FILESgtgt','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
• SQLNet), 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, Inc
• http//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 sites
• http//otn.oracle.com/deploy/security/alerts.htm
• http//technet.oracle.com/deploy/security/alerts.h
  tm
• http//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
• OSEHTTPADMIN 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
• AURORAJISUTILITY OPEN
• AURORAORBUNAUTHENTICATED OPEN
• BLAKE EXPIRED LOCKED
• CLARK EXPIRED LOCKED
• CTXSYS EXPIRED LOCKED
• DBSNMP OPEN
• HR EXPIRED LOCKED