Trusting Privileged Users, DBMS_SQLHASH, and Three Misconceptions about Encryption

26 Jun

Trusting Privileged Users, DBMS_SQLHASH, and Three Misconceptions about Encryption

Auditing, Security Strategy and Standards, Sensitive Data, Oracle Database, Auditor

Clients often contact Integrigy requesting assistance to protect their sensitive data. Frequently these are requests for assistance to locate and then encrypt sensitive data. While encryption  offers protection for sensitive data, it by no means solves all security problems. How to protect sensitive data (and how to verify the trust of privileged users such as database administrators with sensitive data) requires more than just encryption.

The Oracle Database Security Guide (a great read for anyone interested in Oracle database security) makes three key points in Chapter Eight about encryption:

  1. Encryption does not solve access control problems - A user who has privileges to access data within the database has no more nor any less privileges as a result of encryption
  2. Encryption does not protect against a malicious database administrator - If untrustworthy users have significant privileges, then they can pose multiple threats to an organization, some of them far more significant than viewing unencrypted credit card numbers
  3. Encrypting everything does not make data secure – Data must be available when needed as well as backups and DR solutions considered. Moreover, encrypting all data will significantly affect performance.
     

DBMS_SQLHASH

Besides encryption, one of the security tools that Oracle provides is the DBMS_SQLHASH package. Hash values are similar to data fingerprints and can be used to validate if data has been changed (referred to as data integrity). Hashing is different from encryption and it is important to know the difference. If you need to know more about hashing, see the reference section below.  

The DBMS_SQLHASH package has been delivered since 10g and provides an interface to generate the hash value of the result set returned by a SQL query and provides support for several industry-standard hashing algorithms including SHA-1.

DBMS_SQLHASH.GETHASH(sqltext IN varchar2,

                                                digest_type IN BINARY_INTEGER,

                                                chunk_size IN number DEFAULT 134217728)

 

sqltext

The SQL statement whose result is hashed

digest_type

Hash algorithm used: HASH_MD4, HASH_MD5 or HASH_SH1

Use 3 for HASH_SH1, Use 2 for HASH_MD5 and 1 for HASH_MD4

chunk_size

Size of the result chunk when getting the hash

When the result set size is large, the GETHASH function will break it into chunks having a size equal to chunk_size. It will generate the hash for each chunk and then use hash chaining to calculate the final hash. The default chunk_size is 128 MB.

 

How Can Auditors use DBMS_SQLHASH

One use case for DBMS_SQLHASH is to help auditors trust-but-verify the actions of privileged users such as database administrators. By hashing key tables an auditor can quickly determine if the database administrator has made changes – either authorized or unauthorized. An auditor can do this by recording hashes at the start of an audit period for comparison to hashes at the end of the period. If the hashes at the end of the audit period match the hashes at the beginning of the period, no changes have been made. If there are a large number of databases and/or tables to audit, this approach is a very beneficial means of identifying what requires additional review – assuming sufficient logging has been configured to capture the details of the changes.

For example, to determine if there have been changes to Oracle database users and their associated privileges over a period of time, such as granting access to sensitive data, an auditor can hash the following Dictionary tables:

  • SYS.DBA_USERS
  • SYS.DBA_ROLES
  • SYS.DBA_TAB_PRIVS
  • SYS.DBA_SYS_PRIVS
  • SYS.DBA_ROLE_PRIVS

Examples

Note: to call the SYS.DBMS_SQLHASH package, the user will need execute rights granted from SYS.

Control

DBA_USERS

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH ('SELECT * FROM SYS.DBA_USERS ORDER BY USER_ID', 3) sh1_dba_user_hash FROM DUAL;

Sample

Result

Sh1_dba_user_hash

7BD61E22E35FA2F95035E6A794F5B8CF0E37FDF6

 

Control

SYS.DBA_ROLES

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH('SELECT * FROM SYS.DBA_ROLES ORDER BY ROLE', 3) sh1_dba_roles_hash FROM DUAL;

Sample

Result

sh1_dba_roles_hash

C80D69048D613E926E95AF77B627D9B5D6CB20C8

 

Control

SYS.DBA_TAB_PRIVS

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH('SELECT * FROM SYS.DBA_TAB_PRIVS ORDER BY OWNER,TABLE_NAME', 3) sh1_dba_tab_privs_hash FROM DUAL;

Sample

Result

sh1_dba_tab_privs_hash

53FBDBDBF95186400A4DEEE611F51CD0B1E998DF

 

Control

SYS.DBA_SYS_PRIVS

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH('SELECT * FROM SYS.DBA_SYS_PRIVS ORDER BY GRANTEE, PRIVILEGE', 3) sh1_dba_sys_privs_hash FROM DUAL;

Sample

Result

sh1_dba_sys_privs_hash

A27E8C71AD0CAEFB94AFEAB5DB108871F09BC281

 

Control

SYS.DBA_ROLE_PRIVS

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH('SELECT * FROM SYS.DBA_ROLE_PRIVS ORDER BY GRANTEE, GRANTED_ROLE', 3) sh1_dba_role_privs_hash FROM DUAL;

Sample

Result

sh1_dba_role_privs_hash

5715D1B2C2A775D579B36DEBD2C2F1F608762AEC

 

Key Point

The order by clause will change the HASH. Oracle Support Note 1569256.1 explains this in more detail.  To guarantee the same HASH for a SQL issued at different times, the same ordering of the data must be used.

If you have questions or are interested in Integrigy's hashing methodology for Oracle and the Oracle E-Business Suite, please contact us at info@integrigy.com

References

 Share this post

Subscribe to RSS

Add us to your favorite news reader.

Follow on Twitter

Get the latest updates.