Oracle Database Security

(1)

Oracle Database Security

By

By –– Raman JatharRaman Jathar

(2)

Database Security

Lately, database security issues have been flooding the media and Internet. The integrity and privacy of data is at risk

from unauthorized users, external sources listening in on the network and internal users. Risky situations like Data

tampering, data theft, password threats, falsifying user identities etc can compromise the data.

Database security can be broken down into following key points.

• Authorization– using user roles, privileges, profiles, views etc.

(3)

Database Security

• Access Control – using FGAC • Server security

-• Database connections. – User authentication

Fundamental Security Requirements - Following are the fundamental database security requirements.

• Confidentiality • Integrity

(4)

User

To connect to the database, the user account must have been established. Appropriate privileges (system and object) and roles are grated to the user to control user access to data. A user must have RESOURCE role in order to connect to the database.

SYS and SYSTEM users are created at the time of database creation. SYS user is granted with all system privileges and roles created by default. A user can be assigned any number of roles.

(5)

Role

A database role is collection of privileges and other roles. A role groups together privileges and other roles which facilitate the granting of multiple privileges and roles to users.

CONNECT, RESOURCE, DBA are examples of Oracle’s predefined roles. A role can be assigned any number of privileges.

(6)

Privileges

Privileges can be divided into 2 types

• System Privilege – These are system defined privileges which allow user to perform specific database operations. These privileges can only be granted by the administrators. CREATE SESSION, DROP USER, ALTER DATABASE are some examples of system privileges.

• Object Privilege – These are system defined privileges that control access to a specific database object. Object level

privileges are usually granted by the object owner. INSERT, UPDATE, DELETE, ALTER on a specific database table are examples of object level privileges.

(7)

Profiles

A profile is a collection of parameters, assigned to one or more oracle users. Profile place limits on what a user can do. Profile allows to limit the system resources used by a particular group of users.

Profiles, once created can be assigned to users. DEFAULT profile is preloaded. All user are assigned DEFAULT

profile unless stated otherwise.

SESSIONS_PER_USER, CPU_PER_SESSION,

CONNECT_TIME, FAILED_LOGIN_ATTEMPTS are few examples of the parameters defined on the profile.

(8)

Profiles

Through profiles, Oracle implements several features that gives control over how users manage their passwords. They are

• Expire a password – parameter PASSWORD EXPIRE • Prevent reuse of password – parameter

PASSWORD_REUSE_TIME

• Enforce password complexity – parameter PASSWORD_VERIFY_FUNCTION.

(9)

Views

A view is a stored SELECT statement that presents data in a more convenient way. A view can encapsulate complex SQL statements relieving users from writing those

statements.

Oracle will execute view’s query each time a view is referenced.

View are used to limit a user’s access to the rows and

columns in a table and to restrict a user’s ability to insert data into a table. In such case user will not have access to the underlying database table. User will access table data

(10)

Fine Grain Access Control

Fine Grained Access Control (FGAC) allows to create security policies to implement row level security on table and view data. It gives ability to dynamically attach a WHERE clause (predicate), at runtime to all queries. It provides the ability to procedurally modify a query at run-time and give a dynamic view capability.

It gives ability to know who is running the query, which terminal the query is being run, what time of day is the query being run etc. and then build a predicate based on the set of circumstances.

(11)

Fine Grain Access Control

Following are the effective approaches for this implementation.

1. Complex and Dynamic Views - Application designers build their own security tables and join the application tables with the newly created security table based on the name of the application user. These complex view’s maintenance become overhead as security requirement changes.

2. Virtual Private Database (VPD) – In this approach, a person creates his/her own row level security

implementation. It allows query modification based on a security policy defined in a package and associated

(12)

Fine Grain Access Control

3. Label based access control – This is customization of a ready made VPD policy. It allows organizations to

assign sensitivity labels to data rows, control access to data based on the label and ensure that the data is

marked with the appropriate sensitivity level. Advantages –

1. Ease of Maintenance – Can be achieved using a single table and single stored procedure.

2. Server side processing – Security is determined at the database level.

(13)

Fine Grain Access Control

3. Easier Application development – It takes the security logic out of the application logic. Security policy

changes can be done gracefully. How FGAC works?

FGAC is implemented in 2 ways.

1. Application Context – An application context is an application scratchpad area in memory that can be used to store information which is required by the

security policy. An application context may store User Id, Name, Dept information for a person when he/she

(14)

Fine Grain Access Control

An application context is always bound to some PL/SQL package. This package is only method for setting values in the context.

2. Security Policy – Security policy is defined as

enforcement of limits on what a user can do using a

given application. A security policy is a function that is developed which will return the predicate to be used to filter data dynamically, when a query is executed. This function will be bound to a database table or view and may be invoked for some or all the statements that

access the table. This function will make use of values in the application context.

(15)

Server Data Encryption

Encryption is process of encoding data in such a way that only authorized users can access it.

• Selective Encryption – In this data is encrypted selectively as additional security measure. Application specific

sensitive information for e.g. SSN Number etc. can be encrypted before saving on to the database.

• Encryption Algorithm – In this, Number of industry standard encryption algorithms are used to encrypt and

decrypt data on the server. DES and 3DES are examples of algorithm.

(16)

Oracle Advance Security

• Network Data Encryption and Integrity – This deals with making secure data transfer across network protocol

boundaries.

• Strong Authentication – This deals with configuring

strong authentication methods for oracle network. RADIUS Kerberos, SSL some examples for strong authentication.

• Enterprise User Security – This deals with the Oracle database directory and security integration functionality which enables single sign-on in a client-server environment.

(17)

Questions And Answers

• Q and A

(18)

Oracle Database Security