Oracle Database 12c Security Cookbook

Oracle Database 12c Security Cookbook

By : Zoran Pavlovic, Maja Veselica

Buy this Book

Oracle Database 12c Security Cookbook

By: Zoran Pavlovic, Maja Veselica

Buy this Book

Overview of this book

Businesses around the world are paying much greater attention toward database security than they ever have before. Not only does the current regulatory environment require tight security, particularly when dealing with sensitive and personal data, data is also arguably a company’s most valuable asset - why wouldn’t you want to protect it in a secure and reliable database? Oracle Database lets you do exactly that. It’s why it is one of the world’s leading databases – with a rich portfolio of features to protect data from contemporary vulnerabilities, it’s the go-to database for many organizations. Oracle Database 12c Security Cookbook helps DBAs, developers, and architects to better understand database security challenges. Let it guide you through the process of implementing appropriate security mechanisms, helping you to ensure you are taking proactive steps to keep your data safe. Featuring solutions for common security problems in the new Oracle Database 12c, with this book you can be confident about securing your database from a range of different threats and problems.

Oracle Database 12c Security Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

Basic Database Security

Introduction

Creating a password profile

Creating password-authenticated users

Changing a user's password

Creating a user with the same credentials on another database

Locking a user account

Expiring a user's password

Creating and using OS-authenticated users

Creating and using proxy users

Creating and using database roles

The sysbackup privilege – how, when, and why should you use it?

The syskm privilege – how, when, and why should you use it?

The sysdg privilege – how, when, and why should you use it?

Security Considerations in Multitenant Environment

Introduction

Creating a common user

Creating a local user

Creating a common role

Creating a local role

Granting privileges and roles commonly

Granting privileges and roles locally

Effects of plugging/unplugging operations on users, roles, and privileges

PL/SQL Security

Introduction

Creating and using definer's rights procedures

Creating and using invoker's right procedures

Using code-based access control

Restricting access to program units by using accessible by

Virtual Private Database

Introduction

Creating different policy functions

Creating Oracle Virtual Private Database row-level policies

Creating column-level policies

Creating a driving context

Creating policy groups

Setting context as a driving context

Adding policy to a group

Exempting users from VPD policies

Data Redaction

Introduction

Creating a redaction policy when using full redaction

Creating a redaction policy when using partial redaction

Creating a redaction policy when using random redaction

Creating a redaction policy when using regular expression redaction

Using Oracle Enterprise Manager Cloud Control 12c to manage redaction policies

Changing the function parameters for a specified column

Add a column to the redaction policy

Enabling, disabling, and dropping redaction policy

Exempting users from data redaction policies

Transparent Sensitive Data Protection

Introduction

Creating a sensitive type

Determining sensitive columns

Creating transparent sensitive data protection policy

Associating transparent sensitive data protection policy with sensitive type

Enabling, disabling, and dropping policy

Altering transparent sensitive data protection policy

Privilege Analysis

Introduction

Creating database analysis policy

Creating role analysis policy

Creating context analysis policy

Creating combined analysis policy

Starting and stopping privilege analysis

Reporting on used system privileges

Reporting on used object privileges

Reporting on unused system privileges

Reporting on unused object privileges

How to revoke unused privileges

Dropping the analysis

Transparent Data Encryption

Introduction

Configuring keystore location in sqlnet.ora

Creating and opening the keystore

Setting master encryption key in software keystore

Column encryption - adding new encrypted column to table

Column encryption - creating new table that has encrypted column(s)

Using salt and MAC

Column encryption - encrypting existing column

Auto-login keystore

Encrypting tablespace

Rekeying

Backup and Recovery

Database Vault

Introduction

Registering Database Vault

Preventing users from exercising system privileges on schema objects

Securing roles

Preventing users from executing specific command on specific object

Creating a rule set

Creating a secure application role

Using Database Vault to implement that administrators cannot view data

Running Oracle Database Vault reports

Disabling Database Vault

Re-enabling Database Vault

Unified Auditing

Introduction

Enabling Unified Auditing mode

Configuring whether loss of audit data is acceptable

Which roles do you need to have to be able to create audit policies and to view audit data?

Auditing RMAN operations

Auditing Data Pump operations

Auditing Database Vault operations

Creating audit policies to audit privileges, actions and roles under specified conditions

Enabling audit policy

Finding information about audit policies and audited data

Auditing application contexts

Purging audit trail

Disabling and dropping audit policies

Additional Topics

Introduction

Exporting data using Oracle Data Pump in Oracle Database Vault environment

Creating factors in Oracle Database Vault

Using TDE in a multitenant environment

Appendix – Application Contexts

Introduction

Exploring and using built-in contexts

Creating an application context

Setting application context attributes

Using an application context

Customer Reviews

5 star

4 star

3 star

2 star

1 star

The sysbackup privilege – how, when, and why should you use it?

It is recommended that you use the sysbackup administrative privilege instead of the sysdba administrative privilege to perform operations related to backup and recovery tasks.

Getting ready

For this recipe, you'll need:

An existing database user (for example, tom) and a password file in 12c format, if you want to complete it using a password-authenticated user
An existing OS user (for example, john), who belongs to the backupdba OS group, in order to connect to the database using OS authentication

How to do it...

Instructions are given in the Database authentication and OS authentication sections.

Database authentication

The instructions for database authentication are as follows:

Connect to the database as sysdba (or another user that can grant the sysbackup privilege):
```
sqlplus / as sysdba
```
Grant the sysbackup privilege to user tom:
```
grant sysbackup to tom;
```

Verify that there is an entry in the password file that grants user tom the sysbackup administrative privilege. Select data from the v$pwfile_users view:

select * from v$pwfile_users;

The following table is the result of the preceding command:

Username	sysdb	sysop	sysas	sysba	sysdg	syskm	con_id
sys	`TRUE`	`TRUE`	`FALSE`	`FALSE`	`FALSE`	`FALSE`	`0`
sysdg	`FALSE`	`FALSE`	`FALSE`	`FALSE`	`TRUE`	`FALSE`	`0`
sysbackup	`FALSE`	`FALSE`	`FALSE`	`TRUE`	`FALSE`	`FALSE`	`0`
syskm	`FALSE`	`FALSE`	`FALSE`	`FALSE`	`FALSE`	`TRUE`	`0`
tom	`FALSE`	`FALSE`	`FALSE`	`TRUE`	`FALSE`	`FALSE`	`0`

Test the connection using RMAN:

rman target '"tom/oracle_123 as sysbackup"'

OS authentication

The instructions for OS authentication are as follows:

Verify that the OS user (for example, john) is a member of the backupdba OS group:
```
$ id john
```
Connect to the database using the sysbackup privilege (SQL*Plus or RMAN):
```
$> sqlplus / as sysbackup
$> rman target '"/ as sysbackup"'
```

How it works...

You can use either Oracle Recovery Manager (RMAN) or SQL*Plus to perform the operations. When you connect to the database as sysbackup, you are connected as a predefined user sysbackup. If you want to check this, run the following statement:

SQL> select user from dual;

Otherwise, the following statement:

SQL> show user

Using the sysbackup privilege, you can connect to the database even when it is not open. This privilege enables better separation of duties and the implementation of the least privilege principle.

Note

From a security perspective, it is recommended that you implement the least privilege principle. The least privilege principle is an important security concept that requires that users are given only those privileges they need to perform their job.

To view the list of privileges a user can exercise when connected to the database using sysbackup privilege, you can create a user (for example, tom) and grant the user only sysbackup privileges. The next step is to connect to the database as user tom, using the sysbackup privilege and the execute statement:

select * from session_privs;

These privileges are shown in the following table:

Privileges (output from the previous statement)
`sysbackup`	`select any transaction`	`select any dictionary`	`resumable`
`create any directory`	`alter database`	`audit any`	`create any cluster`
`create any table`	`unlimited tablespace`	`drop tablespace`	`alter tablespace`
`alter session`	`alter system`

This is how you can check enabled roles:

SQL> select * from session_roles;

ROLE
-------------------
 SELECT_CATALOG_ROLE
 HS_ADMIN_SELECT_ROLE

Note

HS_ADMIN_SELECT_ROLE is granted to SELECT_CATALOG_ROLE.

If you want to view the roles and privileges granted to sysbackup, you can query DBA_ROLE_PRIVS and DBA_SYS_PRIVS:

SQL> select * from dba_role_privs where grantee='SYSBACKUP';
SQL> select * from dba_sys_privs where grantee='SYSBACKUP';

Also, this new administrative privilege enables you to select, insert, delete, execute, and perform operations:

SELECT	PERFORM operations
`X$` tables	`STARTUP`, `SHUTDOWN`
`V$` and `GV$` views	`CREATE PFILE`, `CREATE SPFILE`
`APPQOSSYS.WLM_CLASSIFIER_PLAN`	`CREATE CONTROLFILE`
`SYSTEM.LOGSTDBY$PARAMETERS`	`FLASHBACK DATABASE`
INSERT/DELETE	`DROP DATABASE`
`SYS.APPLY$_SOURCE_SCHEMA`	`CREATE/DROP RESTORE POINT` (including `GUARANTEED` restore points)
`SYSTEM.LOGSTDBY$PARAMETERS`
EXECUTE
`SYS.DBMS_BACKUP_RESTORE`	`SYS.DBMS_DATAPUMP`
`SYS.DBMS_RCVMAN`	`SYS.DBMS_IR`
`SYS.DBMS_PIPE`	`SYS.SYS_ERROR`
`SYS.DBMS_TTS`	`SYS.DBMS_TDB`
`SYS.DBMS_PLUGTS`	`SYS.DBMS_PLUGTSP`

Tip

It is important for you to remember that: When using the sysbackup privilege, you can't view application data.

There's more...

You can't drop user sysbackup. In a multitenant environment, you can restrict a user to be able to perform backups only for the PDB it can connect to. You can accomplish that by creating a local user in the PDB and granting the sysbackup privilege to the user. When you are connected to the database as the sysbackup, you are connected as sysbackup user to SYS schema:

SQL> connect / as sysbackup
Connected.

SQL> show user
USER is "SYSBACKUP"

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')---------------------------------------SYS

Oracle Database 12c Security Cookbook

By : Zoran Pavlovic, Maja Veselica

Oracle Database 12c Security Cookbook

By: Zoran Pavlovic, Maja Veselica

Overview of this book

Related Content you might be interested in

Current Title:

Oracle Database 12c Security Cookbook

The sysbackup privilege – how, when, and why should you use it?

Getting ready

How to do it...

Database authentication

OS authentication

How it works...

Note

Note

Tip

There's more...

See also