"su functionality" with sqlplus

Now this sounds a bit unbelievable at first but with 10g R2 "su functionality as we know it from Unixes" can be accomplished.

SQL> create user tester identified by tester;
User created.

SQL> grant connect, resource to tester;
Grant succeeded.

SQL> create user datauser identified by datauser;
User created.

SQL> grant connect, resource to datauser;
Grant succeeded.

SQL> connect datauser/datauser
Connected.

SQL> create table sample(a number);
Table created.

SQL> insert into sample values (99999);
1 row created.

SQL> commit;
Commit complete.

SQL> connect system
Enter password: ******
Connected.

SQL> alter user datauser grant connect through tester;
User altered.

SQL> connect tester[datauser]/tester
Connected.

SQL> show user
USER is "DATAUSER"

SQL> select user from dual;

USER
------------------------------
DATAUSER

SQL> desc sample
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> select sys_context('userenv','session_user') "Sess. User",
2 sys_context('userenv','proxy_user') "Prox.User",
3 sys_context('userenv','authentication_type') "Auth.type" from dual;

Sess. User Prox.User Auth.type
-------------------- -------------------- ----------
DATAUSER TESTER PROXY

Restrict DBA's data access

Preventing the DBA and other powerful users from seeing company confidential data has not been an easy task. With Oracle 10g R2 this can be easily accomplished via the use of Database Vault. DV allows you to define realms around your application data to block DBA's from seeing the data they're not supposed to see. DV allows you to define realms to protect your data through an easy to use web interface or through PL/SQL API's. Here are the basic steps to protect a schema with a realm:

1. Create the Realm:

Exec dbms_macadm.create_realm('Protected_Scott','Scott schema DV protected','YES',0);

2. Define the Schema and objects to be protected, this protects all objects of Scott's schema:

Exec dbms_macadm.add_object_to_realm(’Protected_Scott',’scott','%','%');

3. Give access to appropriate users for accessing the schema protected data:

Exec dbms_macadm.add_auth_to_realm(realm_name => ’Protected_Scott', grantee => ’scott', auth_options => 1);

And that's basically it. Now the schema scott is protected and scott is made the owner of the realm. From now on users such as sys and system will not be able to select data from scott's tables.

Taking the Database Vault configuration a step further enables features such as: multifactor authorization, Oracle Label Security integration etc. Utilizing these features one can for example create an environment where database access from certain IP-address at certain time of day restricts data visibility to certain OLS level.

Verifying your data hasn't been tampered

I was recently asked how one can verify that some data in a table hasn't been changed.
A solution that comes to mind is to use the hash procedure of DBMS_CRYPTO package.
Here's a quick example of how the procedure works:

SQL> select dbms_crypto.hash(utl_raw.cast_to_raw('Some Data..'),3) from dual;

DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('SOMEDATA..'),3)
--------------------------------------------------------------------------------
7C0266521736A069E4D057CB452A034F7C850C31

Here I'm asking for a hash using the number three as input parameter for the procedure that gives us the SHA-1 version hash. The procedure returns raw values (automatically converted to hex for us) . Valid input values are either raw or lob values, that's why the example uses the UTL_RAW.CAST_TO_RAW function to first convert the varchar2 value into a raw value. Comparing the generated hash value with the original hash value (stored somewhere) one can easily see if the data has been changed.

By the way if you don't have the documentation on hand you could always ask the database for the information about those supplied packages. For example I didn't remember the parameter for SHA-1 on the previous example so I used this query to find out what it was (DBMS_CRYPTO also supports md-4 and md-5 hashes):
select text from dba_source where name like'%DBMS_CRYPTO%';
...
-- PACKAGE NOTES
--
-- DBMS_CRYPTO contains basic cryptographic functions and
-- procedures. To use correctly and securely, a general level of
-- security expertise is assumed.
--
-- VARCHAR2 datatype is not supported. Cryptographic operations
-- on this type should be prefaced with conversions to a uniform
-- character set (AL32UTF8) and conversion to RAW type.
--
-- Prior to encryption, hashing or keyed hashing, CLOB datatype is
-- converted to AL32UTF8. This allows cryptographic data to be
-- transferred and understood between databases with different
-- character sets, across character set changes and between
-- separate processes (for example, Java programs).
--
---------------------------------------------------------------------------


-------------------------- ALGORITHM CONSTANTS ----------------------------
-- The following constants refer to various types of cryptographic
-- functions available from this package. Some of the constants
-- represent modifiers to these algorithms.
---------------------------------------------------------------------------

-- Hash Functions
HASH_MD4 CONSTANT PLS_INTEGER := 1;
HASH_MD5 CONSTANT PLS_INTEGER := 2;
HASH_SH1 CONSTANT PLS_INTEGER := 3;
...

there I have the information needed :)