Database Demo:
Item Level Security, Database Encryption

The following document contains screen snippets from a live demo showing the new Eloquence 8.10 features item masking and data encryption. It extends on the previous demo of forward-logging and recovery, database auditing and server replication. The screen snippets have been taken on Linux, but the same demo could also be performed on HP-UX with only minor changes (e.g. in path names like /etc/init.d versus /sbin/init.d).


  1. Our ToyDB database server
  2. Item Level Security
  3. Database Encryption
  4. Documentation References

Our ToyDB database server

In this demo we continue to use the ToyDB database server from the previous demo.
It is a small Eloquence B.08.10 database server with a single database named TOYDB. It is
installed in the home directory of a demo user and uses dedicated TCP port numbers that differ
from the default Eloquence port numbers. This way we can run the demo without interfering with
an existing Eloquence installation.
demo@linux:~> /etc/init.d/eloq81 info demo
 eloqdb instance id = demo
  configuration file = /home/demo/eloqdb.cfg
  service = 3000
  args =
  run prefix =
  automatic start = 0
  user = demo
  eloqdb = /opt/eloquence/8.1/bin/eloqdb

Please refer to the previous demo for details on the database server (config, volumes, logs).


Note that the last part of the previous demo also covered installing a slave server and
implementing database replication, so we actually have two database servers running now.
demo@linux:~> /etc/init.d/eloq81 status demo demo-slave
eloqdb[demo] process is active (pid 21655)                           running
eloqdb[demo-slave] process is active (pid 22357)                     running


Our primary focus during this demo will be the master server running on port 3000:
demo@linux:~> env | grep EQ
EQ_DBSERVER=:3000


Item Level Security
For simplicity, we continue to use the TOYDB.PRODUCTS dataset during this demo and
pretend that PRODUCT-NAME and PRICE are sensitive items that need to be protected by
limiting access to selected users. (In a more realistic scenario this might be items
like CUSTOMER-NAME or credit card number, which ToyDB does not include).
As our ToyDB sample database defaults to grant full access to the public user, we
have to first implement a more reasonable database security. For this demo we choose
to add a password protected user for write access and limit "public" to read access.
(In a more realistic scenario, you would probably disable public access completely
and create dedicated password-protected users for different roles or persons).


Here is some info from the ToyDB schema:
demo@linux:~> prschema toydb

ELOQUENCE PRSCHEMA (C) Copyright 2010 Marxmeier Software AG (B.08.10)

# Schema definition for data base TOYDB
# Eloquence schema file, Mon Jul 26 10:25:23 2010 CEST

BEGIN DATA BASE TOYDB;

PASSWORDS:
        1 READING;
        2 WRITING;
   ...

N:      PRODUCTS, M (1/2);
   ...
N:      CUSTOMERS, M (1/2);
   ...
N:      ORDER-MASTER, A (1/2);
   ...
N:      INVOICES, D (1/2);
   ...
N:      ORDERS, D (1/2);
   ...
N:      ORDER-DETAILS, D (1/2);
   ...


And here is some info on users and groups from the database catalog:
demo@linux:~> dbdumpcat -t 30

ELOQUENCE DBDUMPCAT (C) Copyright 2010 Marxmeier Software AG (B.08.10)

----------------------------------------------------------------
#30 sysuser (2 entries)
----------------------------------------------------------------
|uid |name                |password        |priv    |tspw      |
----------------------------------------------------------------
|1   |dba                 |NULL            |03000000|2010-07-23|
|2   |public              |NULL            |04000000|2010-07-23|
----------------------------------------------------------------

demo@linux:~> dbdumpcat -t 2 toydb

ELOQUENCE DBDUMPCAT (C) Copyright 2010 Marxmeier Software AG (B.08.10)

------------------------------------
#2 sysgroup (4 entries)
------------------------------------
|gid |name                |priv    |
------------------------------------
|1   |dba                 |03000000|
|2   |public              |00000000|
|3   |READING             |00000000|
|4   |WRITING             |00000000|
------------------------------------

demo@linux:~> dbdumpcat -t 5 toydb

ELOQUENCE DBDUMPCAT (C) Copyright 2010 Marxmeier Software AG (B.08.10)

-----------
#5 sysmember (4 entries)
-----------
|uid |gid |
-----------
|1   |1   |
|2   |2   |
|2   |3   |
|2   |4   |
-----------


To implement the desired database security, we remove user "public" from the
"WRITING" group and create a new (password protected) user "clerk" and assign
it to the "WRITING" group for granting respective read/write permissions.
demo@linux:~> dbutil -v -

ELOQUENCE DBUTIL (C) Copyright 2010 Marxmeier Software AG (B.08.10)
Processing script ...

create user "clerk" password "rosebud";

database "toydb";

revoke "writing" from "public";
grant  "writing" to   "clerk";

exit;
done


For convenience, we placed the clerk credentials in a properly secured file:
demo@linux:~> ll clerk.id
-rw------- 1 demo users 14 2010-07-26 10:39 clerk.id

demo@linux:~> cat clerk.id
clerk
rosebud


Based on the above database security, we can now implement the desired item masking
that protects our sensitive items PRODUCT-NAME and PRICE. We want to only grant full
access to the two items to users with write access (like "clerk"). All other users
will no longer be able to see PRICE contents and are limited to see the first three
characters of PRODUCT-NAME.


This configuration is applied with dbutil:
demo@linux:~> dbutil -v -

ELOQUENCE DBUTIL (C) Copyright 2010 Marxmeier Software AG (B.08.10)
Processing script ...

database "toydb";

set item access on "product-name" in "products" to masked "cover(x,3,0)";
set item access on "product-name" in "products" to allowed for "writing";

set item access on "price" in "products" to masked;
set item access on "price" in "products" to allowed for "writing";

exit;
done


Access to the sensitive items is now limited for "public" and allowed for "clerk":
demo@linux:~> query3k
B.08.10.00 Eloquence QUERY3K  MON, JUL 26, 2010, 11:02 AM
Copyright 2004-2010 Marxmeier Software AG
Copyright 2004 Hewlett-Packard Development Company, L.P.

>base=toydb
PASSWORD = >>
MODE = >>5
>list products

PRODUC  PRODUCT-NAME         PRICE  PR  QUANTI

A00003  POKxxxxxxxxxxx           0  10     500
A00008  POSxxxxxxxxxx            0  30    1250
A00009  COLxxxxxxxxxxx           0  30    1000
A00001  PACxxxxxxxxxx            0  10    1500
A00010  ERAxxxxxxxxxxxx          0  30    1500
A00002  LUDxxxxx                 0  10     750
A00005  15"xxxxxxxxxxxx          0  20     200
A00007  SETxxxxxxxxxxx           0  30     500
A00004  12"xxxxxxxxxxx           0  20     250
A00006  SETxxxxxxxxxx            0  20     150
4711    eauxxxxxxxxxxx           0  xy     456
4712    donxxxxxx                0  xx     789

>exit

demo@linux:~> EQ_DBUSER=file:$HOME/clerk.id  query3k
B.08.10.00 Eloquence QUERY3K  MON, JUL 26, 2010, 11:02 AM
Copyright 2004-2010 Marxmeier Software AG
Copyright 2004 Hewlett-Packard Development Company, L.P.

>base=toydb
PASSWORD = >>
MODE = >>5
>list products

PRODUC  PRODUCT-NAME         PRICE  PR  QUANTI

A00003  POKER DICE SET         125  10     500
A00008  POSTER PAINTS           95  30    1250
A00009  COLOURING BOOK          65  30    1000
A00001  PACK OF CARDS           75  10    1500
A00010  ERASER GIFT SET        185  30    1500
A00002  LUDO SET              1250  10     750
A00005  15" PINK RABBIT       1745  20     200
A00007  SET OF CRAYONS         175  30     500
A00004  12" TEDDY BEAR        1525  20     250
A00006  SET OF PANDAS         2500  20     150
4711    eau de cologne         123  xy     456
4712    dont care              321  xx     789

>exit


Note that masking applies to all database clients, including ODBC access, for example:
demo@linux:~> isql toydb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select product_no,product_name,price,product_line from products
+-----------+-----------------+-----------------------+-------------+
| product_no| product_name    | price                 | product_line|
+-----------+-----------------+-----------------------+-------------+
| A00003    | POKxxxxxxxxxxx  | 0                     | 10          |
| A00008    | POSxxxxxxxxxx   | 0                     | 30          |
| A00009    | COLxxxxxxxxxxx  | 0                     | 30          |
| A00001    | PACxxxxxxxxxx   | 0                     | 10          |
| A00010    | ERAxxxxxxxxxxxx | 0                     | 30          |
| A00002    | LUDxxxxx        | 0                     | 10          |
| A00005    | 15"xxxxxxxxxxxx | 0                     | 20          |
| A00007    | SETxxxxxxxxxxx  | 0                     | 30          |
| A00004    | 12"xxxxxxxxxxx  | 0                     | 20          |
| A00006    | SETxxxxxxxxxx   | 0                     | 20          |
| 4711      | eauxxxxxxxxxxx  | 0                     | xy          |
| 4712      | donxxxxxx       | 0                     | xx          |
+-----------+-----------------+-----------------------+-------------+
SQLRowCount returns -1
12 rows fetched
SQL>


The item security settings are also effective on the replication slave:
demo@linux:~> dbexport -s - :4000/toydb products

ELOQUENCE DBEXPORT (C) Copyright 2010 Marxmeier Software AG (B.08.10)

# DATABASE = TOYDB
WARNING: Masked items found, values may be incomplete (set 1)

# SET 1 = PRODUCTS
"A00003","POKxxxxxxxxxxx",0,"10",500
"A00008","POSxxxxxxxxxx",0,"30",1250
"A00009","COLxxxxxxxxxxx",0,"30",1000
"A00001","PACxxxxxxxxxx",0,"10",1500
"A00010","ERAxxxxxxxxxxxx",0,"30",1500
"A00002","LUDxxxxx",0,"10",750
"A00005","15\"xxxxxxxxxxxx",0,"20",200
"A00007","SETxxxxxxxxxxx",0,"30",500
"A00004","12\"xxxxxxxxxxx",0,"20",250
"A00006","SETxxxxxxxxxx",0,"20",150
"4711","eauxxxxxxxxxxx",0,"xy",456
"4712","donxxxxxx",0,"xx",789


Note that item masking may be implemented without any changes to the
client applications. However, careful evaluation and testing is advised
to verify that all the applications handle the masked data as intended,
especially when considering to use item masking on key or search items.


Database Encryption
The item masking functionality shown above helps to protect sensitive data
by restricting access to sensitive items based on the database user's group
membership. It applies to all kinds of retrievals controlled by the database
server, regardless of the client application.
However, using the item masking functionality does not help to improve the
protection of sensitive data stored in volume files or forward logfiles... we can still
find clear text of sensitive items in the forward log, for example the last DBPUT from
the previous demo on forward logging and recovery, database auditing and replication:
demo@linux:~> hexdump -C log/fw-5-1.log | fgrep -C 2 4712
00000220  10 36 4d 4c 0d 02 3d 00  71 00 00 00 01 00 00 00  |.6ML..=.q.......|
00000230  05 00 00 00 00 00 00 00  00 00 00 00 0c 00 00 00  |................|
00000240  92 00 00 00 03 34 37 31  32 20 20 64 6f 6e 74 20  |.....4712  dont |
00000250  63 61 72 65 20 20 20 20  20 20 20 00 00 32 1f 78  |care       ..2.x|
00000260  78 15 03 00 00 00 00 00  00 00 00 00 00 00 00 00  |x...............|

(we can see product-name "dont care" in the above forward log snippet)


To also protect sensitive data in volume files, forward logs, backups and
dbstore archives, we implement database encryption in this part of the demo.
The database server will create and maintain data encryption keys internally,
which will then be used to store sensitive database fields in encrypted form.
To protect those data encryption keys, the database server uses a master key
that is not stored inside the database volume files and has to be supplied
after every database server restart.


First we create the master key using dbkeyutil:
demo@linux:~> dbkeyutil -help

ELOQUENCE DBKEYUTIL (C) Copyright 2010 Marxmeier Software AG (B.08.10)
usage: dbkeyutil [options] cmd [args ...]

options:
 -help             - show usage (this list)
 -k keyfile        - keyfile file name (default is eqdb.key)
 -t type           - key type (AES[128|256], default is AES)
 -v                - verbose output
 -u name           - user name (to connect to database server)
 -p pswd           - password (to connect to database server)
 -h host           - host name or address and service
 -s service        - service name or port number
 -d flags          - debug flags
 -b rsabits        - size of RSA session key (min. 1024)

commands:
 keygen id [parts] - generate new key
 chpass id         - change passphrase
 check id ...      - test key(s)
 submit id         - submit master key to database server
 revoke id         - revoke master key on database server
 status            - database server key status

demo@linux:~> dbkeyutil keygen DemoKey
Enter passphrase for DemoKey:
Confirm passphrase:

demo@linux:~> ll eqdb.key
-rw-r--r-- 1 demo users 217 2010-07-26 12:01 eqdb.key

demo@linux:~> cat eqdb.key
[DemoKey]
cksum=b9bb367e669ce959503f04a61c38363b
keyt=AES
key=819c1f4299095b01aef14c4b8d9fda64
cipher=hmac-sha1:des-ede3-cbc:d754612d501769af:1000
comment=AES-128 key, created 2010-07-26 12:01:32 by demo@linux

The above master key is protected by a passphrase. Both, key file and passphrase
have to be stored very carefully, since they will become crucial for enabling the
database server to access encrypted data. Loss of the key file or passphrase will
result in the irrevocable loss of access to the encrypted data.


Next we supply the master key to the running database server:
demo@linux:~> dbkeyutil -u dba submit DemoKey
Enter passphrase for DemoKey:
Passphrase is valid
Master key activated
Master key submitted successfully

demo@linux:~> dbkeyutil status
idx  master key checksum              stat type     ts
---- -------------------------------- ---- -------- -------------------
1    b9bb367e669ce959503f04a61c38363b ACTV AES 128  2010-07-26 12:03:17

Note that the above has to be done after every restart of the database server.


With our master key in place, we now use dbutil to have the database server
create a data encryption key for our database and then configure our sensitive
data items PRODUCT-NAME and PRICE for encrypted storage. (We need to reference
our master key by its checksum, as the server may hold multiple master keys).
demo@linux:~> dbutil -v -

ELOQUENCE DBUTIL (C) Copyright 2010 Marxmeier Software AG (B.08.10)
Processing script ...

database "toydb";

create encryption key using "b9bb367e669ce959503f04a61c38363b";

change item "product-name" in "products" set encrypted;
change item "price"        in "products" set encrypted;

exit;
Checking database consistency ...
Consistency check completed successfully

Database restructure analysis:
 PRODUCTS
  * Record reorganized due to encryption change
Data restructure process required.

Uploading modified schema ...
Restructuring database ...
done

(the above restructure process encrypts the data and may take some time)


With encryption in place, we are no longer able to see the clear text of
our sensitive items in the volume files or in (new) forward log entries:
demo@linux:~> EQ_DBUSER=file:$HOME/clerk.id  query3k
B.08.10.00 Eloquence QUERY3K  MON, JUL 26, 2010,  1:15 PM
Copyright 2004-2010 Marxmeier Software AG
Copyright 2004 Hewlett-Packard Development Company, L.P.

>base=toydb
PASSWORD = >>
MODE = >>1
>add products
PRODUCT-NO      =>>4713
PRODUCT-NAME    =>>eau de parfum
PRICE           =>>123
PRODUCT-LINE    =>>xy
QUANTITY        =>>456

PRODUCT-NO      =>>//

>exit

demo@linux:~> dbctl forwardlog status
Forward-logging is enabled.
Forward-log is '/home/demo/log/fw-5-2.log'.

demo@linux:~> ll log/fw-5-*
-rw------- 1 demo users 366826 2010-07-26 13:16 log/fw-5-1.log
-rw------- 1 demo users    890 2010-07-26 13:16 log/fw-5-2.log

demo@linux:~> hexdump -C log/fw-5-2.log | fgrep -C 2 4713
00000140  00 00 94 6e 4d 4c 0d 02  51 00 71 00 00 00 22 00  |...nML..Q.q...".|
00000150  00 00 2e 00 00 00 00 00  00 00 00 00 00 00 0d 00  |................|
00000160  00 00 25 00 00 01 03 34  37 31 33 20 20 78 79 c8  |..%....4713  xy.|
00000170  01 00 00 01 00 00 00 b6  a6 56 66 a9 3a 6e 6d d7  |.........Vf.:nm.|
00000180  5c 3e 71 a3 e4 a5 e8 81  6e e3 ea 8e 67 56 f7 0d  |\>q.....n...gV..|
--
00000260  00 00 00 2c 00 07 00 70  00 00 00 00 00 00 00 00  |...,...p........|
00000270  00 00 00 48 00 10 1f 02  00 00 00 02 00 14 00 06  |...H............|
00000280  00 00 00 04 00 00 00 00  34 37 31 33 20 20 0d 00  |........4713  ..|
00000290  00 00 ee 36 09 00 00 00  9b 6e 4d 4c 00 00 00 00  |...6.....nML....|
000002a0  15 00 08 00 64 00 00 00  ff ff ff ff 36 0a 00 00  |....d.......6...|


Note that the use of encryption also affects database auditing. The fwaudit
utility will no longer be able to display encrypted fields when not supplied
with the appropriate master key:
demo@linux:~> fwaudit -r -v log/fw-5-*.log

ELOQUENCE FWAUDIT (C) Copyright 2010 Marxmeier Software AG (B.08.10)

processing file: log/fw-5-1.log

SIGN-ON session:5 connection time: 2010-07-26 09:15:13
 protocol{a}os{Linux}ip{127.0.0.1}user{demo}login{public}conntime{1280128513}
 uid{1000}pid{22503}pname{query3k}

DBPUT TOYDB.PRODUCTS (#113) recno:12 session:5
 timestamp: 2010-07-26 09:15:28
  PRODUCT-NO            : "4712"
  PRODUCT-NAME          : "dont care"
  PRICE                 : 321
  PRODUCT-LINE          : "xx"
  QUANTITY              : 789

processing file: log/fw-5-2.log
WARNING: encryption key 1 is ignored: required master key not present

SIGN-ON session:46 connection time: 2010-07-26 13:15:53
 protocol{a}os{Linux}ip{127.0.0.1}user{demo}login{clerk}conntime{1280142953}
 uid{1000}pid{28744}pname{query3k}

DBPUT TOYDB.PRODUCTS (#113) recno:13 session:46
 timestamp: 2010-07-26 13:16:36
  PRODUCT-NO            : "4713"
  PRODUCT-NAME          : ""
  PRICE                 : 0
  PRODUCT-LINE          : "xy"
  QUANTITY              : 456
WARNING: unexpected EOF encountered on forward-log action 5-2.15 while reading next tag opcode


Supplying the master key enables fwaudit to also access encrypted data:
demo@linux:~> EQ_MKEYID=DemoKey  fwaudit -M -r -v log/fw-5-*.log

ELOQUENCE FWAUDIT (C) Copyright 2010 Marxmeier Software AG (B.08.10)
Enter passphrase for DemoKey:
DemoKey: Master key activated

processing file: log/fw-5-1.log

SIGN-ON session:5 connection time: 2010-07-26 09:15:13
 protocol{a}os{Linux}ip{127.0.0.1}user{demo}login{public}conntime{1280128513}
 uid{1000}pid{22503}pname{query3k}

DBPUT TOYDB.PRODUCTS (#113) recno:12 session:5
 timestamp: 2010-07-26 09:15:28
  PRODUCT-NO            : "4712"
  PRODUCT-NAME          : "dont care"
  PRICE                 : 321
  PRODUCT-LINE          : "xx"
  QUANTITY              : 789

processing file: log/fw-5-2.log

SIGN-ON session:46 connection time: 2010-07-26 13:15:53
 protocol{a}os{Linux}ip{127.0.0.1}user{demo}login{clerk}conntime{1280142953}
 uid{1000}pid{28744}pname{query3k}

DBPUT TOYDB.PRODUCTS (#113) recno:13 session:46
 timestamp: 2010-07-26 13:16:36
  PRODUCT-NO            : "4713"
  PRODUCT-NAME          : "eau de parfum"
  PRICE                 : 123
  PRODUCT-LINE          : "xy"
  QUANTITY              : 456
WARNING: unexpected EOF encountered on forward-log action 5-2.15 while reading next tag opcode


Note that encrypted data will also arrive encrypted on the replication slave.
The dbrepl utility does not need access to any master keys for this to work.
However, as long as you do not supply the master key to the running slave server,
it will not be able to access encrypted database content. We thus need to also
submit the appropriate master key to the replication after every server restart.
demo@linux:~> dbexport -s - :4000/toydb products

ELOQUENCE DBEXPORT (C) Copyright 2010 Marxmeier Software AG (B.08.10)

Fatal error #-812 while opening database
Secondary status = 0
DBOPEN(9): Encrypted database not available [-812:0]

demo@linux:~> dbkeyutil -s 4000 -u dba submit DemoKey
Enter passphrase for DemoKey:
Passphrase is valid
Master key activated
Master key submitted successfully

demo@linux:~> dbexport -s - :4000/toydb products

ELOQUENCE DBEXPORT (C) Copyright 2010 Marxmeier Software AG (B.08.10)

# DATABASE = TOYDB
WARNING: Masked items found, values may be incomplete (set 1)

# SET 1 = PRODUCTS
"A00003","POKxxxxxxxxxxx",0,"10",500
"A00008","POSxxxxxxxxxx",0,"30",1250
"A00009","COLxxxxxxxxxxx",0,"30",1000
"A00001","PACxxxxxxxxxx",0,"10",1500
"A00010","ERAxxxxxxxxxxxx",0,"30",1500
"A00002","LUDxxxxx",0,"10",750
"A00005","15\"xxxxxxxxxxxx",0,"20",200
"A00007","SETxxxxxxxxxxx",0,"30",500
"A00004","12\"xxxxxxxxxxx",0,"20",250
"A00006","SETxxxxxxxxxx",0,"20",150
"4711","eauxxxxxxxxxxx",0,"xy",456
"4712","donxxxxxx",0,"xx",789
"4713","eauxxxxxxxxxx",0,"xy",456

demo@linux:~> EQ_DBUSER=file:$HOME/clerk.id dbexport -s - :4000/toydb products

ELOQUENCE DBEXPORT (C) Copyright 2010 Marxmeier Software AG (B.08.10)

# DATABASE = TOYDB

# SET 1 = PRODUCTS
"A00003","POKER DICE SET",125,"10",500
"A00008","POSTER PAINTS",95,"30",1250
"A00009","COLOURING BOOK",65,"30",1000
"A00001","PACK OF CARDS",75,"10",1500
"A00010","ERASER GIFT SET",185,"30",1500
"A00002","LUDO SET",1250,"10",750
"A00005","15\" PINK RABBIT",1745,"20",200
"A00007","SET OF CRAYONS",175,"30",500
"A00004","12\" TEDDY BEAR",1525,"20",250
"A00006","SET OF PANDAS",2500,"20",150
"4711","eau de cologne",123,"xy",456
"4712","dont care",321,"xx",789
"4713","eau de parfum",123,"xy",456


The above demo only showed the basics of using database encryption.
Additional utility commands and options are available, for example for maintenance
tasks like changing passphrases or encryption keys periodically. These activities are
discussed in the Eloquence documentation; they are beyond the scope of this demo.
One important point shall be re-emphasized, though:
When using database encryption, you need to store key files and passphrases (as well
as possible changes over time) very carefully, since they are crucial for enabling the
database server to access encrypted data. Loss of key file or passphrase will result
in the irrevocable loss of access to the encrypted data.
Note that the above does not only apply to "current" key files and passphrases, but
is also important for "historic" key files and passphrases, as long as you need to be
able to access archived database backups or audit logs, for example.





(End of Demo)






Documentation References





Eloquence B.08.10 Release Notes


http://eloquence.marxmeier.com/support/B0810/relnotes/index.html


Enhanced Item Level Security


http://eloquence.marxmeier.com/support/B0810/relnotes/item_access.html


Database Encryption


http://eloquence.marxmeier.com/support/B0810/relnotes/encryption.html