Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Oracle Tips by Burleson

Chapter 11 Oracle Fine Grained Auditing

client identifier. It’s not meaningful to see the value on v$session view, but when decrypted, it will provide a meaningful picture of the session. If the user does manipulate the value, it will not be decrypted properly as the user does not know the encryption key.

This method can be used inside a procedure owned by a secured user, such as SECUSER. The encryption key is supplied by the application. Here we will use the routines we developed earlier in the Chapter on Encryption.

get_encrypted_value – Needs two parameters, the value to be encrypted and the key in RAW datatype. The code can be found in online code depot as get_encrypted_value.sql. It returns the encrypted value in hexadecimal representation in VARCHAR2 datatype.

get_decrypted_value – Needs two parameters, the value to be decrypted in hexadecimal and the key in RAW datatype. The code can be found in get_decrypted_value.sql in the Online Code Depot. The function returns the decrypted value.

The secret to the whole exercise is to have the key supplied by the application. Here we will make up the key as a long list of characters in hexadecimal format. This key is stored in the application code or supplied from a table.

We may decide to store something that is not obtainable in the sys_context function. One possibility is using the Domain Name of the user’s machine. After the application starts and connects as user APPUSER, or some other generic user, it calls

get_encrypted_value('<The Domain Name>',  

The above text is an excerpt from the bestselling book: Oracle Privacy Security Auditing It's only $39.95 and has an download of working security scripts:


This is the only authoritative book on Oracle Security, Oracle Privacy, and Oracle Auditing written by two of the world’s leading Oracle Security experts.

This indispensable book is only $39.95 and has an download of working security scripts:


Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks