EncryptFS

STANDARD ECRYPTFS MOUNTS
Encryption of administrative files is vital because attacks can come from anywhere. The notion that firewalls are impenetrable is false. For example, the New York Times was recently hacked because they were printing an article on a wealthy and high-ranking official. It is irresponsible for database professionals to place sensitive administrative files or data in unencrypted volumes.

The purpose of this lab is to familiarize database professionals with encryption techniques. It is by no means an exhaustive class on the topic because to present encryption ad nauseam would take several days and we do not have that luxury.

There are several encryption products freely available to you on both the Linux and Windows platform. Some of the more commonly used packages are ECRYPTFS and GPG. ECRYPTFS differs from other methods that provide block-level encryption. It is an actual file system and this difference allows users to mount folders as if they were encrypted file systems. Other benefits include:

  • No special on-disk allocation is needed and you can mount existing folders.
  • Ecryptfs can obfuscate file access to all users, including root.
  • Support for a myriad of filesystems is available.

GPG encryption is intended to be used at the file level; however, tools like Seahorse (Linux) and GPG4Win allow the user to encrypt entire folders as an encrypted zip file. Take note, that with GPG encryption, you must decrypt the zip, open it, add or modify files, and then encrypt the file/folder in order to protect your sensitive administrative files. Moreover, the root user can see its contents while the file is decrypted.

INSTALLING ECRYPTFS

If you happen to have Red Hat Enterprise Linux or any variant thereof, installing encrypts is a snap. Just issue the following statement:

1

$ sudo yum install ecryptfs-utils -y

ECRYPTFS STANDARD MOUNT

First thing’s first, you’ll need to create a directory that you can mount. In this example, we will mount an encrypted volume over the top of the myStuff directory.

1
2

$ cd
$ mkdir myStuff

You can always mount another folder into your myStuff folder if the system administrator limits the size of your /home directory. If this is the case, try to organize your file system so that all of your sensitive,
administrative files are located in the same place for a particular instance.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

$ cd /u02
$ ll
 
total 8
drwxrw-r-x. 6 oracle oinstall 4096 Jul 18 12:43 admin
drwxrw-r-x. 3 oracle oinstall 4096 Jul 18 14:00 oradata
 
$ cd admin
$ ll
 
total 16
drwxrw-r-x. 2 oracle oinstall 4096 Jul 18 13:48 download
drwxrw-r-x. 2 oracle oinstall 4096 Jul 18 12:43 extdata
drwxrw-r-x. 5 oracle oinstall 4096 Jul 18 13:49 install
drwxrw-r-x. 2 oracle oinstall 4096 Jul 18 12:43 scripts

If you are running Oracle RAC and using ASM, the oradata directory will be accessible via the asmcmd command. We’re using file system in this VM. Let’s make two directories in this location. One for our public files and the other for private files:

1
2
3
4
5
6
7
8

$ mkdir -p t001/public
$ mkdir -p t001/private
$ cd t001
$ ll
 
total 8
drwxr-xr-x. 2 oracle oinstall 4096 Jul 18 20:16 private
drwxr-xr-x. 2 oracle oinstall 4096 Jul 18 20:16 public

As shown, the contents of the /u02/admin/T001/ folder contain both a public and private folder. Be careful to place only those scripts that contain NO SENSITIVE information in the public folder. For now, we will mount the private folder to the /home/admjmh/myStuff directory. Let’s start by creating a ecMount.sh file in your home directory:

1
2

$ cd
$ vim ecMount.sh

Now, add the following lines to your ecMont.sh file:

1
2
3
4
5
6
7

mount \
-t ecryptfs \
-o ecryptfs_cipher=aes \
-o ecryptfs_key_bytes=16 \
-o ecryptfs_passthrough=no \
-o ecryptfs_enable_filename_crypto=n \
"$1" "$2"

Save the file and edit another named ecUmount.sh. Add the following line to that file:

1

sudo umount myStuff/

Now change the mode of each file so you can execute them:

1

$ chmod 750 ecMount.sh ecUmount.sh

We’re almost done. Now all you have to do is call the shell scripts to mount and unmount your encrypted directory. In the next several steps, we will mount the /u02/admin/T001/private folder to your /home/admjmh/myStuff directory. Then, we will create a text file and then unmount the directory. Finally, we will attempt to view the contents of the file.

1
2
3
4
5
6

$ cd
$ sudo ./ecMount.sh /u02/admin/T001/private /home/admjmh/myStuff
$ echo ‘This IS a test OF encryption.’ >> myStuff/test.txt
$ sudo ./ecUmount.sh
$ cd /u02/admin/T001/private
$ vim test.txt

See how the unmounted file is no longer accessible? You should use this method to protect your administrative scripts as well as your backups. Remember unencrypted exports are easily exploited. All a hacker has to do is to obtain your export, build an Oracle instance of his/her own and import your unencrypted export…

As a bonus activity, export the HR and OE schemas in an unencrypted format. Then, place one copy of your *.dmp file in the mounted myStuff directory and another in the /u02/admin/T001/public folder. Unmount the myStuff directory and open both files in vim or any text editor.

We strongly caution you to encrypt all export and backup files. Never leave them unencrypted, especially if your volumes are shared like they are in NFS mounts. If you have a more recent version of Oracle, you can add the encryption option to your expdp command, but we will talk about that more later.

 

Limit Audit Trail Size

Most DBAs are familiar with auditing. They may also be familiar with the problems that arise when the SYSTEM tablespace fills up because the audit tables get large; however, this problem can be eluded.

Run the following query to discover where the current AUD$ and FGA_LOG$ tables reside:

1
2
3
4
5
6
7
8
9
10
11
12

COLUMN TABLE_NAME format a30
COLUMN tablespace_name format a30
SET tab off
SELECT  TABLE_NAME
     ,  tablespace_name
  FROM  dba_tables
 WHERE  TABLE_NAME IN ( 'AUD$', 'FGA_LOG$' );
 
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
FGA_LOG$                       SYSTEM
AUD$                           SYSTEM

See that in this instance that both tables belong to the SYSTEM tablespace. We don’t want it there, especially if we want to be proactive in our auditing. Instead, let’s make a new tablespace just for these two tables.
Type the following (copy/paste) into a SQL*Plus client:

1
2
3
4
5
6
7
8

CREATE smallfile tablespace secad
    datafile  '/u02/oradata/T001/secad-001.dbf'
        SIZE  5g
  autoextend  ON
     maxsize  30g
logging extent management LOCAL
segment SPACE management auto
DEFAULT compress FOR oltp;

Now, let’s move the tables to our new SECAD tablespace. Make sure you use the DBMS_AUDIT_MGMT package when you do this.
Type the following (copy/paste) into a SQL*Plus client.
Re-query the dba_tables view to see where the tables are located.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

BEGIN
  -- this one for your aud$
  dbms_audit_mgmt.set_audit_trail_location
  (
    audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std
  , audit_trail_location_value  => 'SECAD'
  );
  -- this one for your fga_log$
  dbms_audit_mgmt.set_audit_trail_location
  (
    audit_trail_type            => dbms_audit_mgmt.audit_trail_fga_std
  , audit_trail_location_value  => 'SECAD'
  );
END;
/
SELECT  TABLE_NAME
     ,  tablespace_name
  FROM  dba_tables
WHERE  TABLE_NAME IN ( 'AUD$', 'FGA_LOG$' );
 
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
FGA_LOG$                       SECAD
AUD$                           SECAD

Now that we have the audit tables in a space that will not negatively affect the database, let’s talk about the automatic limits that can be added to these tables. Again, we are using the DBMS_AUDIT_MGMT package to perform most of our work.
Type the following (copy/paste) into a SQL*Plus client.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

COLUMN parameter_name format a30
COLUMN parameter_value format a30
COLUMN audit_trail format a30
SET linesize 100
SELECT  parameter_name
     ,  parameter_value
     ,  audit_trail
  FROM  dba_audit_mgmt_config_params
WHERE  parameter_name LIKE '%AUDIT%MAX%';
 
PARAMETER_NAME                 PARAMETER_VALUE                AUDIT_TRAIL
------------------------------ ------------------------------ ------------------------------
AUDIT FILE MAX SIZE            10000                          OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                          XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                              OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                              XML AUDIT TRAIL

The following clarifications should be made before we move on:
1. The sizes are in KBytes
2. The age is in days
Let’s modify these limits for the OS AUDIT trail. Type (copy/paste) the following into a SQL*Plus session

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

BEGIN
  dbms_audit_mgmt.set_audit_trail_property
    (
      audit_trail_type            => dbms_audit_mgmt.audit_trail_os
    , audit_trail_property        => dbms_audit_mgmt.os_file_max_size
    , audit_trail_property_value  => 15360 --> 15 MB
    );
 
  dbms_audit_mgmt.set_audit_trail_property
    (
      audit_trail_type            => dbms_audit_mgmt.audit_trail_xml
    , audit_trail_property        => dbms_audit_mgmt.os_file_max_age
    , audit_trail_property_value  => 30
    );
END;
/

We need to generate some audit information in the FGA_LOG$ table. To do so, let’s make a quick policy that will audit all activity to the hr.employees table. It is important to note that when you leave the audit condition out of the policy, you are basically saying where 1=1. Another important note is that your audit policy functions need to evaluate to true or false. You can do any number of evaluations, effectively filtering out unwanted noise. However, for now we will merely audit everything.

1
2
3
4
5
6
7
8
9

BEGIN
  dbms_fga.add_policy
  (
    object_schema   => 'HR'
  , object_name     => 'EMPLOYEES'
  , policy_name     => 'somePolicy'
  );
END;
/

Now we’ll issue a query against HR.EMPLOYEES to ensure that information is in the FGA_LOG$ table.

1

SELECT * FROM hr.employees WHERE salary > :1;

Perform several DML queries agains the HR.EMPLOYEES table.

  1. Do a select with and without bind variables.
  2. Do an update of the salary column to 20% more than the current amount for employee 105.
  3. Delete a single row.
  4. Query the SYS.FGA_LOG$ table to see what the results of your activities does.

Audit Trail Monitoring
Backing up the audit trail is an important task. You could use that information to perform data mining, build security dashboards, or even create a paging system depending on the severity of the policy being triggered.
The following code will create a backup table of everything in the audit tables. This won’t help you if you don’t perform regular purges. We will show you how to purge the audit trail shortly. For now, lets implement the following block:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

CREATE OR REPLACE PROCEDURE audit_backup ( pi_which    IN varchar2 )
AS
  l_sql varchar2(4000);
  l_now varchar2(30);
BEGIN
  l_now := to_char ( systimestamp, 'DDMONYYYY_HHMISSFF4' );
  IF  dbms_assert.simple_sql_name ( pi_which  ) = pi_which
  THEN
    IF pi_which = 'FGA_LOG$' THEN
      l_sql :=
        ' create table sys.$1_$2                            '||
        ' tablespace secad                                  '||
        ' as                                                '||
        ' select  SESSIONID                                 '||
        '      ,  TIMESTAMP#                                '||
        '      ,  DBUID                                     '||
        '      ,  OSUID                                     '||
        '      ,  OSHST                                     '||
        '      ,  CLIENTID                                  '||
        '      ,  EXTID                                     '||
        '      ,  OBJ$SCHEMA                                '||
        '      ,  OBJ$NAME                                  '||
        '      ,  POLICYNAME                                '||
        '      ,  SCN                                       '||
        '      ,  SQLTEXT                                   '||
        '      ,  LSQLTEXT                                  '||
        '      ,  SQLBIND                                   '||
        '      ,  COMMENT$TEXT                              '||
        '      ,   to_lob ( PLHOL ) as PLHOL                '||
        '      ,  STMT_TYPE                                 '||
        '      ,  NTIMESTAMP#                               '||
        '      ,  PROXY$SID                                 '||
        '      ,  USER$GUID                                 '||
        '      ,  INSTANCE#                                 '||
        '      ,  PROCESS#                                  '||
        '      ,  XID                                       '||
        '      ,  AUDITID                                   '||
        '      ,  STATEMENT                                 '||
        '      ,  ENTRYID                                   '||
        '      ,  DBID                                      '||
        '      ,  LSQLBIND                                  '||
        '      ,  OBJ$EDITION                               '||
        '   from  $1';
    elsif pi_which = 'AUD$' THEN
      l_sql :=
        ' create table sys.$1_$2                            '||
        ' tablespace secad                                  '||
        ' as                                                '||
        ' select  *                                         '||
        '   from  sys.$1';
    END IF;
    l_sql := regexp_replace ( l_sql, '( ){2,}', ' '  );
    l_sql := regexp_replace ( l_sql, '\$1', pi_which );
    l_sql := regexp_replace ( l_sql, '\$2', l_now    );
    EXECUTE immediate l_sql;
  END IF;
END;
/

To purge the audit trail you must do the following. Make sure you initialize the cleanup jobs with the first block and then schedule the second block with dbms_job.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- run once
BEGIN
  dbms_audit_mgmt.init_cleanup
  (
    audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
  , default_cleanup_interval  => 999
  );
 
  dbms_audit_mgmt.init_cleanup
  (
    audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
  , default_cleanup_interval  => 999
  );
END;
/

The following block should be pushed into a job. Notice that we backup the AUD$ and FGA_LOG$ tables before purging.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- run the following on a schedule
BEGIN
  audit_backup ( 'AUD$' );
  audit_backup ( 'FGA_LOG$' );
 
  dbms_audit_mgmt.set_last_archive_timestamp
  (
    audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
  , last_archive_time       => systimestamp
  );
  dbms_audit_mgmt.set_last_archive_timestamp
  (
     audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
  , last_archive_time       => systimestamp
  );
  dbms_audit_mgmt.clean_audit_trail
  (
    audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
  , use_last_arch_timestamp => TRUE
  );
  dbms_audit_mgmt.clean_audit_trail
  (
    audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
  , use_last_arch_timestamp => TRUE
  );
END;
/

Now you have a way to limit the audit trail size and backup audit items. Hopefully, this will help your database performance and size.