Oracle Database Application Security: With Oracle Internet Directory, Oracle Access Manager, and Oracle Identity Manager

By Osama Mustafa and Robert P. Lockard

Focus on the security aspects of designing, building, and maintaining a secure Oracle Database application. Starting with data encryption, you will learn to work with transparent data, back-up, and networks. You will then go through the key principles of audits, where you will get to know more about identity preservation, policies and fine-grained audits. Moving on to virtual private databases, you’ll set up and configure a VPD to work in concert with other security features in Oracle, followed by tips on managing configuration drift, profiles, and default users. 
Shifting focus to coding, you will take a look at secure coding standards, multi-schema database models, code-based access control, and SQL injection. Finally, you’ll cover single sign-on (SSO), and will be introduced to Oracle Internet Directory (OID), Oracle Access Manager (OAM), and Oracle Identity Management (OIM) by installing and configuring them to meet your needs. 
Oracle databases hold the majority of the world’s relational data, and are attractive targets for attackers seeking high-value targets for data theft. Compromise of a single Oracle Database can result in tens of millions of breached records costing millions in breach-mitigation activity. This book gets you ready to avoid that nightmare scenario.
What You Will Learn

• Work with Oracle Internet Directory using the command-line and the console 
• Integrate Oracle Access Manager with different applications 
• Work with the Oracle Identity Manager console and connectors, while creating your own custom one 
• Troubleshooting issues with OID, OAM, and OID
• Dive deep into file system and network security concepts 

Who This Book Is For
Oracle DBAs and developers. Readers will need a basic understanding of Oracle RDBMS and Oracle Application Server to take complete advantage of this book.

• Language: English
• Publisher: Apress
• Release date: Oct 31, 2019
• ISBN: 9781484253670

Book preview

© Osama Mustafa, Robert P. Lockard 2019

O. Mustafa, R. P. LockardOracle Database Application Securityhttps://doi.org/10.1007/978-1-4842-5367-0_1

1. Encryption

Osama Mustafa¹  and Robert P. Lockard²

(1)

Amman, Jordan

(2)

Baltimore, MD, USA

From the time of Julius Caesar and the Caesar shift algorithm, people have been using encryption to protect information from prying eyes. To use encryption properly, you need to understand how data moves through systems, from storage to where the data is presented. If you are not careful, there will be places where encrypted data will spill out unencrypted. You also need to understand that encryption is just one part of securing your information. Even after you set up encryption, you need to concern yourself with ghost data that may be left behind in storage unencrypted.

In this chapter, we’ll discuss how to set up Transparent Data Encryption (TDE), how to implement network encryption, and where ghost data can be found so it can be safely destroyed and kept from prying eyes.

Transparent Data Encryption

Transparent Data Encryption is the technology used to encrypt the data that is on disk and used by the Oracle database. TDE covers about 15 percent to 20 percent of the attack surface of the Oracle database. If the database and the keystore are open, then the database will do exactly what it’s designed to do: encrypt and decrypt the data moving through the database. What does TDE protect you from? TDE protests when someone tries to bypass the database and get to the data on disk. Make no mistake, TDE is a powerful tool to protect your information if the files (data files, RMAN backups, and Data Pump Export files) are compromised.

Is there an advantage to using TDE over whole-disk encryption? Well, it depends. There are pros and cons to each. With whole-disk encryption, if the disk is ever compromised, retrieving the data is difficult. However, a user with access to the operating system can bypass the database and access the encrypted data. If you’re using TDE, then the user must go through the database to access the data.

To set up TDE, the first thing you need to do is set up a keystore. A keystore is an encrypted file that holds the master key that is used to decrypt the keys for table columns and tablespaces. In the past, the keystore was called the wallet, but this has changed, likely to keep the naming consistent with the Java keystore. You will notice that many of the views still use the term wallet. Don’t let this confuse you; the terms are interchangeable, but we’ll stick to using the term keystore.

There are a couple types of keystores. There is a file keystore that is stored on the file system, and there are hardware keystores. An important thing to consider is where are you going to store the file keystore. You should never store the file keystore under ORACLE_BASE or with your data files. We normally place the file keystore in /etc/oracle/wallet/orclcdb/. This is done so the keystore can be backed up independently of the Oracle binaries and the data files. Remember, the keystore is used to decrypt your data, so if you were to back up your keystore with your data files, then all an attacker would need to do is to figure out the password; further, if you are using an auto open keystore, then an attacker would not even need to figure out the password.

You should be backing up your keystore before and after any operation that changes the keystore. If you get an error and the keystore gets corrupted, you will need the backup to recover. After you back up your keystore, keep a copy on-site and off-site.

To create the keystore, you’ll need to set the WALLET_ROOT value in the init.ora or spinit.ora file. Either you can create a backup of the init.ora file, edit it, and then use it to restart the database and re-create the spinit.ora file, or you can use alter system set wallet_root using a scope of spfile and then restart the database. The choice is yours. We prefer to use the alter system command .

SQL> alter system set wallet_root='/etc/oracle/wallet/orclcdb' scope=spfile;

System altered.

SQL> – now bounce the database so wallet_root takes effect

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2432694552 bytes

Fixed Size 8898840 bytes

Variable Size 654311424 bytes

Database Buffers 1761607680 bytes

Redo Buffers 7876608 bytes

Database mounted.

Database opened.

Then you need to set TDE_CONFIGURATION so the database knows whether you are using a file keystore or a hardware keystore.

SQL> ALTER SYSTEM SET TDE_CONFIGURATION=KEYSTORE_CONFIGURATION=FILE scope=BOTH;

System altered.

Create the user c##sec_admin that will be used to manage TDE, create the keystore, and do the rekeying operations. This user must be granted the syskm and connect privileges.

SQL> create user c##sec_admin identified by SecretPassword;

User created.

SQL> grant syskm, connect to c##sec_admin;

Grant succeeded.

SQL> conn c##sec_admin as syskm

Enter password:

Connected.

SQL>

Now that we have the account set up to manage TDE, we can create the keystore. There are a couple of options when we create the keystore. One of them is to set up an auto open keystore. There are some corner cases where you would not want an auto open keystore, but most databases that we have worked with were up 24/7; therefore, if there is a case where you needed to restart the database, the keystore would open up automatically.

There is an argument to not use an auto open keystore in case the keystore ever gets lost. If you are concerned about forgetting the password to the keystore, put the password in the password envelope that is kept in the safe along with the other admin passwords.

When we create the keystore using the administer key management command , we do not need to set the keystore location because we set the wallet_root value in the configuration.

c##sec_admin > administer key management create keystore

identified by SecretPassword;

keystore altered.

Now if you look in the wallet_root location, you will see that an empty keystore named ewallet.p12 has been created.

 [oracle@localhost ~]$ cd /etc/oracle/wallet/orclcdb/tde

[oracle@localhost tde]$ ls -l

total 4

-rw-------. 1 oracle oinstall 2555 Dec 22 09:05 ewallet.p12

[oracle@localhost tde]$

Notice that Oracle created the directory tde under wallet_root.

Before we do any operations on the keystore, we need to open it. We can create an auto open keystore later, but for now we’re just going to stick to a normal keystore.

c##sec_admin > administer key management set keystore open

identified by SecretPassword;

keystore altered.

Now we’re going to set the master encryption key. This is needed to decrypt the tablespace and table encryption keys. We use a tag to indicate this is the initial master encryption key and automatically create a backup with bk1 in the backup name.

c##sec_admin > administer key management set key

using tag 'initial'

identified by SecretPassword

with backup using 'bk1';

keystore altered.

You’ll notice that after you create the master key, the file size will change, and a backup of the original keystore will be created.

[oracle@localhost tde]$ ls -ltr

total 12

-rw-------. 1 oracle oinstall 2555 Dec 22 09:27 ewallet_2018122214274238_bk1.p12

-rw-------. 1 oracle oinstall 4171 Dec 22 09:27 ewallet.p12

[oracle@localhost tde]$

Rekey the Keystore Master Encryption Key

If you suspect your keys have been compromised, then you’ll need to rekey your data. There are three keys you should rekey.

Master key

Table key

Tablespace key

When you rekey the master encryption key, a history of the master encryption keys is kept in the keystore; this history will be needed if you need to recover backups.

You will notice that in the example, we used the current date for the tag. This will make it easier to know what master keys are out there and when they were created.

[oracle@localhost ~]$ sqlplus c##sec_admin as syskm

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Dec 23 07:38:47 2018

Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Enter password:

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> administer key management set key

using tag '23Dec2018'

identified by SecretPassword

with backup using 'bk1';

keystore altered.

SQL>

Query the Master Key Information

You can query the v$encryption_keys and v$database_key_info views to get the history of your master encryption keys, and you can query the v$encryption_wallet view to get the status of the keystore.

V$ENCRYPTION_WALLET

Let’s see what keystores (wallets) you have and their statuses. You see that the type of keystore is FILE, and you can see the location of the keystore, which is open. A single wallet is configured, which is in the ROOT container. It’s fully backed up and has connection ID 1 (the root container).

select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID

-------- ------------------------- ------- -------------------- --------- -------- --------- ----------

FILE /etc/oracle/wallet/tde/ OPEN AUTOLOGIN SINGLE NONE YES 1

FILE CLOSED UNKNOWN SINGLE UNITED UNDEFINED 2

FILE OPEN AUTOLOGIN SINGLE UNITED YES 3

The columns returned are discussed here:

V$ENCRYPTION_KEYS

To get a list of the encryption keys and what they apply to, use the following (see Figure 1-1):

select tag,

 activation_time,

 creator,

 key_use,

 keystore_type,

 backed_up,

 activating_pdbname

from V$ENCRYPTION_KEYS;

../images/478348_1_En_1_Chapter/478348_1_En_1_Fig1_HTML.jpg

Figure 1-1

Getting a list of the encryption keys and what they apply to

Here you’ll see the master encryption keys for the root container and the pluggable database ORCLPDB1:

V$DATABASE_KEY_INFO

Query the keys that are being used to encrypt the system, sysaux, temp, and undo tablespaces . You will notice that when you query from the root container, you get all the keys, and when you query from the pluggable database, you get only the key information for the pluggable database.

syskm@orclroot > select encryptionalg,

  2                     masterkey_activated,

  3                     masterkeyid,

  4                     con_id

  5* from v$database_key_info

ENCRYPT MAS MASTERKEYID                          CON_ID

------- --- -------------------------------- ----------

AES128  YES 8613EEC551AB4FB8BF38898D8989CBFB          1

NONE    NO  00000000000000000000000000000000          2

AES128  YES 4FE6A1BA0CFA4F50BF415F095E1A033A          3

r@orclpdb1 > select encryptionalg,

  2                     masterkey_activated,

  3                     masterkeyid,

  4                     con_id

  5  from v$database_key_info;

ENCRYPT MAS MASTERKEYID                          CON_ID

------- --- -------------------------------- ----------

AES128  YES 4FE6A1BA0CFA4F50BF415F095E1A033A          3

Here is a table of the relevant columns:

Rekey a Table Key

Rekeying a table key is just as easy as rekeying the master key. Simply alter the table with the rekey option.

orclpdb1 > alter table t rekey;

Table altered.