Oracle Database Security Features in the Banking Environment. Dr. Matthias Mann, DOAG

(1)

Dr. Matthias Mann, DOAG

University of Applied Sciences, Cologne Campus Gummersbach

(2)

●

Database User Authentication and Authorization

●

Authentication

●

Authorization

●

Multi Tier Application Architectures

●

Virtual Private Database

●

Database Vault

●

Advanced Security Option

●

Transparent Data Encryption (Data at Rest)

●

Encryption of Network Traffic (Data in Transit)

●

Auditing

(3)

Control & Data Accees Duties Process Mgmt. 4. Finanzmarkt- unterstützungsgesetz Aktiengesetz Bundesdatenschutz- gesetz 3

(4)

●

authentication methods:

○

locally in the database

○

on the client (host based)

○

client / server (multi tier)

(5)

5

SQL> create user scott profile pf_01 identified by tiger; SQL> select resource_name, limit from

dba_profiles where resource_type='PASSWORD' and profile ='PF_01'; RESOURCE_NAME LIMIT --- --- FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME 71 PASSWORD_REUSE_TIME 1 PASSWORD_REUSE_MAX 5 PASSWORD_VERIFY_FUNCTION F_IS_PW_OK PASSWORD_LOCK_TIME DEFAULT PASSWORD_GRACE_TIME 14

(6)

●

in large organisations:

○

several hundred databases

○

several 10000 end users

○

central usermanagement managing all kind of users

●

local account management is a "nightmare"

○

inconsistencies in database profiles

○

no match of installed database users with actual end users

○

reporting / inventory nearly impossible

(7)

7

SQL> create user scott profile pf_01 identified externally; $ whoami $ scott $ sqlplus / SQL>show user SQL>User is "scott"

●

client host OS acts as trusted authority

●

requires identical user scott on OS level

(8)

●

local authentication:

○

used in the past for "passwordless" batch operations

○

not compatible with multi tier architectures

●

remote authentication

○

uncalculable security risk => should not be used

(9)

9

●

in the database have a locally identified user

●

on the client create a wallet (password store):

$ mkstore -wrl <path> -create

-rw--- 1 oracle oinstall 3589 Nov 13 13:19 cwallet.sso -rw--- 1 oracle oinstall 3512 Nov 13 13:19 ewallet.p12

●

configure sqlnet.ora:

sqlnet.wallet_override=true wallet_location=<path>

●

configure naming resolution (place TNS alias in tnsnames.ora)

●

place database user credentials in the wallet referencing the corresponding tnsnames.ora entry

●

useful for batch accounts

(10)

●

Enterprise User Security (EUS)

○

centralized user administration capability for Oracle databases

○

user authentication and authorization data are stored in a LDAP compliant directory

○

password, Kerberos, X.509 certificates

(11)

(12)

Local schema Global Role Local Role Enterprise Role Global schema Enterprise Directory _Database Enterprise Account(s) Enterprise Roles(s) Database Role(s) Database Account(s)

(13)

13

Step Description Who / Tool Where Comment

(1) define directory access for DB Instance DBA / sqlplus database server (ldap.ora) once (2) let database access directory DBA / sqlplus spfile once (1) $TNS_ADMIN/ldap.ora

# ldap.ora Network Configuration File: /opt/oracle/admin/BIP6TD/network/pfile/ldap.ora # Generated by Oracle configuration tools.

DIRECTORY_SERVERS= (sybasetst1.sys.hypovereinsbank.de:10389:10636) DEFAULT_ADMIN_CONTEXT = "dc=hvb,dc=de"

DIRECTORY_SERVER_TYPE = OID

(2) set the directory lookup indicator

(14)

(3) display database instance in directory:

> ldapsearch -h sybasetst1 -p 10389 -D cn=admin -w ovd_1 "cn=BIP6TD" cn=BIP6TD,cn=OracleContext,dc=hvb,dc=de orclOracleHome=/opt/oracle/product/10.2.0.2.1 orclServiceType=DB orclDBGlobalName=BIP6TD userPassword={SSHA}3YCCKN62GPaFZcH4dqZXzdKe8yYvO2l6b0p5BQ== orclcommonrpwdattribute={SASL-MD5}kMmejQPWIT46aiiS/h542Q== orclSystemName=orainfra1 orclNetDescString=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=BIP6TD)(Port=1561))(CONNECT_DATA=(SE RVICE_NAME=BIP6TD))) orclSid=BIP6TD orclNetDescName=000:cn=DESCRIPTION_0 cn=BIP6TD orclVersion=102000 objectClass=top objectClass=orclService objectClass=orclDBServer objectClass=orclApplicationEntity objectClass=orclDBServer_92

(3) register database in directory

DBA / dbca directory / wallet on

database server

wallet for database directory

(15)

15

> ldapsearch -h sybasetst1 -p 10389 -D cn=admin -w ovd_1 "uid=p863061" uid=p863061,ou=People,dc=hvb,dc=de uid=p863061 objectClass=inetOrgPerson objectClass=organizationalPerson objectClass=person objectClass=top objectClass=orcluser objectClass=orcluserv2 givenName=Matthias sn=Mann cn=Matthias Mann authpassword;orclcommonpwd={SSHA}F5dxazpwLyXM3eN48id9rl8blx9+Y7oi1FP/fA==

(4) create

enterprise user

directory admin

(16)

Step Description Who / Tool Where Comment (5) create shared schema DBA / sqlplus database (5) Shared Schema

SQL>create user db_entry identified globally as ''; SQL>grant create session to db_entry;

SQL> select username, password from

dba_users where username = 'DB_ENTRY';

USERNAME PASSWORD

--- --- DB_ENTRY GLOBAL

(17)

17

Step Description Who / Tool Where Comment

(6) create directory entry for schema user management / ESM directory once (7) assign schema to database user management / ESM directory once (8) assign directory user to schema user management / ESM directory once

(9) Authentication end user workplace need to know her amd password

(18)

(9) Authentication (login into database) sqlplus p863061

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Jul 4 14:07:09 2009

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

SQL> select sys_context ('userenv','external_name') "Directory User", user schema,

sys_context ('userenv','db_name') database from dual;

Directory User SCHEMA DATABASE

--- uid=p863061,ou=people,dc=hvb,dc=de DB_ENTRY BIP6TD

(19)

19

(10) create global database role DBA / sqlplus database once (11) grant privileges to global role DBA / sqlplus database once

(10,11) Global database Role and Privileges

SQL> connect / as sysdba Connected.

SQL> create role r_read_global identified globally; SQL> grant r_catalog to r_read_global;

(20)

Step Description Who / Tool Where Comment (12) create Enterprise Role user management / ESM Directory once

(13) map global role to enterprise role user management / ESM Directory (12,13) Role Mapping

> ldapsearch -h sybasetst1 -p 10389 -D cn=admin -w ovd_1 "cn=r_eus01"

cn=r_eus01,cn=OracleDefaultDomain,cn=OracleDBSecurity,cn=Products,cn=OracleContext ,dc=hvb,dc=de orclDBServerRole=cn=bip6td,cn=oraclecontext,dc=hvb,dc=de,GlobalRole=R_READ_GLOBAL uniqueMember=uid=p863061,ou=People,dc=hvb,dc=de cn=r_eus01 objectClass=top objectClass=orclDBEnterpriseRole objectClass=orclprivilegegroup objectClass=groupOfUniqueNames objectClass=orclDBEnterpriseRole_82 objectClass=orclDBEnterpriseRole_10i

(21)

21

(14) Authorization end user workplace

(14) Authorization

sqlplus p863061

SQL> desc dba_tablespaces;

Name Null? Type

--- --- ---

TABLESPACE_NAME NOT NULL VARCHAR2(30) BLOCK_SIZE NOT NULL NUMBER

INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER MIN_EXTLEN NUMBER STATUS VARCHAR2(9) CONTENTS VARCHAR2(9) LOGGING VARCHAR2(9) FORCE_LOGGING VARCHAR2(3) EXTENT_MANAGEMENT VARCHAR2(10) ALLOCATION_TYPE VARCHAR2(9) PLUGGED_IN VARCHAR2(3) SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) DEF_TAB_COMPRESSION VARCHAR2(8) RETENTION VARCHAR2(11) BIGFILE VARCHAR2(3)

(22)

●

Basic Principle:

SQL>create user proxy identified by proxypw; SQL>grant connect to proxy;

SQL>alter user client grant connect to proxy; SQL>connect proxy[client]/proxypw SQL> select sys_context('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA, sys_context('USERENV','SESSION_USER') SESSION_USER, sys_context('USERENV','PROXY_USER') PROXY_USER from dual;

CURRENT_SCHEMA SESSION_USER PROXY_USER --- --- --- client client proxy

(23)

23

●

Flexibility

○

Authorization Granularity

SQL>alter user A grant connect through B with all roles except <…>; SQL>alter user A grant connect through B with no roles;

SQL>alter user A grant connect through B with role C;

○

Authentication

SQL>alter user A grant connect through B authentication required;

○

for use with EUS

SQL>alter user A grant connect through B authenticated using Distinguished Name;

(24)

●

Oracle authorization is based on a privilege model through which you can allow or deny access to data, actions or processing.

●

Object Privileges

○

select, insert, update, delete, execute …

○

can be granted, revoked

○

with grant option

○

Granularity: on column level

●

Procedures BE CAREFUL !!!

○

can be executed with definer (default !!) or invoker rights (Example 1) depending how they are defined

(25)

25

●

System Privileges

○

right to perform an action without a qualification on what the action is applied to

○

~ 200 different system privs

○

create table, create user, drop profile, create tablespace …

○

admin option

○

~ 100 ANY privileges (BE CAREFUL !!!)

○

drop any table …

●

Best Practice

(26)

●

Database Roles

○

named groups of privileges

○

easy and transparent administration

○

avoid complex hierarchies (Risk!)

○

default roles (alter user default role …)

○

Default: ALL (all roles granted subsequently are default roles)

○

max_enabled_roles

○

password protected roles are possible

●

PUBLIC: not a role and not a user, but:

○

any user is automatically assigned all privileges granted to PUBLIC

(27)

27

●

Secure Application Roles (see Code Example 2)

○

allow you to dynamically control privileges through application logic

○

can only be set by a procedure

○

can be enabled by logon trigger or dynamically within the current session

(28)

●

ACLs and Network Services

○

revoke execute privilege for sensitive packeages from PUBLIC

○

UTL_TCP: open TCP/IP connection in PL/SQL

○

UTL_SMTP: send mail from PL/SQL

○

UTL_HTTP: HTTP request over TCP/IP from PL/SQL

○

starting with Oracle 11 ACL have tp be defined to grant access to network services to a database user

(29)

29

(30)

●

end user is captured by middle tier

●

middle tier instead of database is authenticating the end user

●

middle tier needs to invoke roles and privileges in the database on behalf of the end user

●

challenges with regard to data access control:

○

identify the "real" end user

○

authenticate the end user => access limitation to objects and actions in the database

(31)

31

●

Architecture Approaches

1.

Pass – through (Client / Server)

○

1:1 relationship end user : db user

○

user authentication in the database

○

unsuitable for Web Applications

2.

middle tier is responsible for user security in DB

○

application user account has all privs for all end users in the system (contradicts the "least privilege" principle)

(32)

3.

Re – Authentication of the end user in the DB

○

application forwards login information to the DB for authentication

4.

end user to session mapping via token – passing true end user not known to the DB

token can be used for auditing connection pooling

(dbms_session.set_client_identifier)

5.

Proxy – Authentication

(33)

33

●

Advantages

○

separate application logic from data storage

○

enable appserver to access DB

(34)

●

synonyms

●

application independent, enforced by the DB

●

policies on row- or column level

●

uses query rewriting technique

●

Code Example 3a: Use an application context to define a namespace

(35)

35

db user

application context

(namespace)

RLS Policy

Predicate

Rewrite

Query

Object

(36)

USERENV with the following information:

○

session_user ○ current_user ○ current_schema ○ external_name ○ client_identifier ○ client_info ○ proxy_user ○ audited_cursorid ○ entryid ○ sessionid ○ isdba ○ ip_address ○ db_name ○ host ○ network_protocol ○ authentication_type ○ policy_invoker ○ current_sql

(37)

37

(38)

●

control of access to application data based on business requirements

●

prevention of unauthorized access of highly privileged database accounts following legal / business requirements (ANY privs)

(39)

(40)

Realms Boundaries within the Oracle database that act like a

firewall to prevent privileged users from using their special privileges to access application data

Command Rules Security rules that control the execution of database commands

Factors Environmental parameters (IP address, Authentication method) that can be used with Database Vault command rules and realms to create trusted paths to data, defining who, when, where and how applications, data and

databases are accessed

Rule Sets Collection of one or more rules that you can associate with other components (a realm authorization, command rule, factor assignment): it evaluates to true or false based on the evaluation of each rule it contains and the evaluation type.

Secure application roles

Oracle Database role that can be enabled based on the evaluation of an Oracle Database Vault rule set.

(41)

41

Function Responsibility

Security Administrator Realms, Rule Sets, Command Rules, Factors

Account Administrator Database Accounts and Profiles

DBA Backup / Recovery

Tuning

Startup/Shutdown Patching/Upgrade

Application Administrator Schema Management

Privilege Management within

application schema / users scope

(42)

(43)

43

●

realm checking is done before the use of ANY privs and for grant and revoke of roles within the realm

●

normal object or system privileges are not affected

●

use realms when you protect groups of objects from DBA or other privileged users

(44)

(45)

45

Rule Sets

●

rule sets are used from within command rules

●

are composed of rules (PL/SQL expressions => true/false)

●

OR and AND rulesets

●

Examples:

○

Enabled

○

Disabled

(46)

(47)

(48)

●

global rules that are checked for every access and for all users

●

can be used to secure any statement

●

Example: begin

dvsys.dbms_macadm.create_command_rule( command => 'DROP TABLE',

rule_set_name => 'Disabled', object_owner => 'SCOTT', object_name => 'EMP', enabled => 'Y'); end; /

(49)

(50)

●

Factors

○

variables used within rules

○

custom factors may be built (e.g. source_program)

●

Built In Factors:

○

client IP

○

database domain

○

enterprise identity

○

proxy user

○

…

●

Secure Application Roles

●

like "normal" secure application roles (are set using pl/sql procedures)

(51)

51

●

Oracle TDE provide two types of encryption:

○

Column Encryption: individual application table columns such as credit card and social security numbers. This requires a deep knowledge of the columns that must be encrypted

○

Tablespace Encryption: tablespace encryption eliminates the

complexities of identifying and encrypting individual columns since ALL the data stored on the tablespace is automatically

(52)

●

Tablespace Encryption:

○

automatically encrypts data written to storage by the Oracle database,

○

If database files or storage disks are stolen from original

servers, data cannot be decrypted and read on different server

○

No storage overhead

(53)

53

●

Encryption keys are the secrets used in combination with an encryption algorithm to encrypt data

●

Transparent Data Encryption uses a 2-tier key architecture for flexible and non-intrusive key rotation and least operational and performance impact

○

Each application table with at least one encrypted column has its own table key, which is applied to all encrypted columns in that table.

○

each encrypted tablespace has its own tablespace key. Are managed automatically – no human interaction

●

Each of these keys is encrypted with the TDE master encryption key, which is stored outside of the database in an external security module: Oracle Wallet.

●

Transparent Data Encryption supports standard encryption algorithms including AES256, AES192, AES128 and 3DES168

(54)

○

Standard

○Requires manual password entry to open the wallet and make the master key available. Not possible to read data without the password

○

Auto-Login

○Created from the standard wallet, no password entry required, enables unattended DB startup.

○

Local Auto-Login

○Similar to Auto-Login Wallet, but only works on the database machine where it was created. Password required if data is read on different server/machines.

●

All types of Wallets guarantee the following:

○

Protected by password entry and strong encryption

○

periodic password rotation

○

Enables separation of duties between the Security Administrator and the Database Administrator

○

OS permissions and file system features can lock-down the wallet and establish separation of duties

(55)

55

●

Integration with other Database features

Feature Comment

Compression compression bevor encryption Backup / Restore double encryption for backups

avoided

Export / Import optional export encryption

High Availability (Dataguard) transparent, DR site encryption optional

Replication (Streams, Golden Gate)

(56)

●

Wallet and Password Management

○

Wallet Backup:

○

Backup the wallet in a separate location from the encrypted data. Wallet can be backed up with standard Oracle and third party products/architectures

○

Strong Wallet Password:

○

Password can be split with different custodians

○

Person A enters the first part of the password before Person B enters the 2nd half of the password, without Person B being able to see what Person A typed into the password field

(57)

(58)

●

client and server negotiate encryption parameters Parameter Comment sqlnet.encryption_server/client rejected accepted requested required

sqlnet.encryption_types_server/client encryption algorithm sqlnet.crypto_checksum_server/client rejected

accepted requested required

(59)

(60)

(61)

61

●

Standard Auditing

○

audit_trail : { none | os | db [, extended] | xml [, extended] }

○

AUD$ or OS Files

○

XML Files or name-value delimited text files

○

Audit Command Qualifiers:

○

when successful

○

when unsuccessful

○

both (default)

○

by session

○

by access

○

Statement Auditing

○

Object Auditing

○

Privilege Auditing

(62)

●

mandatory auditing:

○

DB Startup / Shutdown

○

sysdba logon

○

changes to audit_trail

●

administrator auditing

○

audit_sys_operations: true => OS Files

●

syslog auditing:

○

audit_syslog_level=<valid syslog facility>

○

prevents possibility for altering audit records by DBA

(63)

○

standard auditing: "all or nothing"

○

possibility to specify granular conditions for creating audit records

○

configuration via policies

begin dbms_fga.add_policy( object_schema=>'SCOTT', object_name=>'EMP', policy_name=>'EMP_ACCESS', audit_columns=>'SAL', audit_conditions=>'SAL>9999', statement_types=>'SELECT', audit_column_options=>'DBMS_FGA.ANY_COLUMNS', audit_trail=>'DBMS_FGA.XML + DBMS_FGA.EXTENDED' handler_schema='SECADM' handler_module=>'RT_ALERT'); end; / 63

(64)

DBA_COMMON_AUDIT_TRAIL DBA_AUDIT_TRAIL (standard Auditing) V$XML_AUDIT_TRAIL (standard Auditing) DBA_FGA_AUDIT_TRAIL (fine grained Auditing)

SYS.AUD$ (table)

<audit_file_dest>/*.xml

(65)