Virtual Private Databases (VPD)

Virtual Private Databases (VPD) allow multiple users to access a single schema whilst preventing them from accessing data that is not relevant to them. Although this type of access can be controlled by the application, access via other methods (SQL*Plus) would leave the data open to abuse. Setting up a VPD involves the following steps:


Setup Test Environment
First we must create a user to act as the schema owner for this example. Obviously, you will perform the following tasks using your current schema owner:
CONNECT sys/password@service AS SYSDBA;
CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;
CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;
CREATE USER user2 IDENTIFIED BY user2
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user2;
GRANT EXECUTE ON DBMS_RLS TO PUBLIC;
CONN schemaowner/schemaowner@service
CREATE TABLE users
(id NUMBER(10) NOT NULL,
ouser VARCHAR2(30) NOT NULL,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL);
CREATE TABLE user_data
(column1 VARCHAR2(50) NOT NULL,
user_id NUMBER(10) NOT NULL);
INSERT INTO users VALUES (1,'USER1','User','One');
INSERT INTO users VALUES (2,'USER2','User','Two');
COMMIT;
GRANT SELECT, INSERT ON user_data TO user1, user2;

Create an Application Context
Grant CREATE ANY CONTEXT to the schema owner then create the context and context package:
CONNECT sys/password@service AS SYSDBA;
GRANT create any context, create public synonym TO schemaowner;
CONNECT schemaowner/schemaowner@service;
CREATE CONTEXT SCHEMAOWNER USING SCHEMAOWNER.Context_Package;
CREATE OR REPLACE PACKAGE Context_Package AS
PROCEDURE Set_Context;
END;
/
Next we create the Context_Package body which will actually set the user context:
CREATE OR REPLACE PACKAGE BODY Context_Package IS
PROCEDURE Set_Context IS
v_ouser VARCHAR2(30);
v_id NUMBER;
BEGIN
DBMS_Session.Set_Context('SCHEMAOWNER','SETUP','TRUE');
v_ouser := SYS_CONTEXT('USERENV','SESSION_USER');
BEGIN
SELECT id
INTO v_id
FROM users
WHERE ouser = v_ouser;
DBMS_Session.Set_Context('SCHEMAOWNER','USER_ID', v_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_Session.Set_Context('SCHEMAOWNER','USER_ID', 0);
END;
DBMS_Session.Set_Context('SCHEMAOWNER','SETUP','FALSE');
END Set_Context;
END Context_Package;
/
SHOW ERRORS
Next we make sure that all users have access to the Context_Package:
GRANT EXECUTE ON SCHEMAOWNER.Context_Package TO PUBLIC;
CREATE PUBLIC SYNONYM Context_Package FOR SCHEMAOWNER.Context_Package;

Create Login Trigger
Next we must create a trigger to fire after the user logs onto the database:
CONNECT sys/password@service AS SYSDBA;
CREATE OR REPLACE TRIGGER SCHEMAOWNER.Set_Security_Context
AFTER LOGON ON DATABASE
BEGIN
SCHEMAOWNER.Context_Package.Set_Context;
END;
/
SHOW ERRORS

Create Security Policies
In order for the context package to have any effect on the users interaction with the database, we need to define a Security_Package for use with the security policy. This package will tell the database how to treat any interactions with the specified table:
CONNECT schemaowner/schemaowner@service;
CREATE OR REPLACE PACKAGE Security_Package AS
FUNCTION User_Data_Insert_Security(Owner VARCHAR2, Objname VARCHAR2)
RETURN VARCHAR2;
FUNCTION User_Data_Select_Security(Owner VARCHAR2, Objname VARCHAR2)
RETURN VARCHAR2;
END Security_Package;
/
Next we create the Security_Package body:
CREATE OR REPLACE PACKAGE BODY Security_Package IS
FUNCTION User_Data_Select_Security(Owner VARCHAR2, Objname VARCHAR2)RETURN VARCHAR2 IS
Predicate VARCHAR2(2000);
BEGIN
Predicate := '1=2';
IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'SCHEMAOWNER') THEN
Predicate := NULL;
ELSE
Predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''USER_ID'')';
END IF;
RETURN Predicate;
END User_Data_Select_Security;
FUNCTION User_Data_Insert_Security(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2 IS
Predicate VARCHAR2(2000);
BEGIN
Predicate := '1=2';
IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'SCHEMAOWNER') THEN
Predicate := NULL;
ELSE
Predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''USER_ID'')';
END IF;
RETURN Predicate;
END User_Data_Insert_Security;
END Security_Package;
/
SHOW ERRORS
Next we make sure that all users have access to the Security_Package:
GRANT EXECUTE ON SCHEMAOWNER.Security_Package TO PUBLIC;
CREATE PUBLIC SYNONYM Security_Package FOR SCHEMAOWNER.Security_Package;

Apply Security Policies to Tables
The DBMS_Rls package is used to apply the security policay, implemented by Security_Package, to the the relevant tables:
BEGIN
DBMS_Rls.Add_Policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_INSERT_POLICY',
'SCHEMAOWNER', 'SECURITY_PACKAGE.USER_DATA_INSERT_SECURITY',
'INSERT', TRUE);
DBMS_Rls.Add_Policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_SELECT_POLICY',
'SCHEMAOWNER', 'SECURITY_PACKAGE.USER_DATA_SELECT_SECURITY',
'SELECT');
END;
/

Test VPD
Finally, test that the VPD is working correctly:
CONNECT user1/user1@service;
INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 2', 2);
COMMIT;
CONNECT user2/user2@service
INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 2', 2);
COMMIT;
CONNECT schemaowner/schemaowner@service
SELECT * FROM schemaowner.user_data;
CONNECT user1/user1@Service;
SELECT * FROM schemaowner.user_data;
CONNECT user2/user2@Service
SELECT * FROM schemaowner.user_data;
Notice that:
When connected to USER1, only the first insert will work.
When connected to USER2, only the second insert will work.
The failing inserts produce the error:
ORA-28115: policy with check option violation
Once the inserts are finished, there will be two rows in the table, as seen when connected as SCHEMAOWNER. When connected as USER1 or USER2, only the single row they inserted will be visible.

Storing Passwords In The Database

When security is managed within applications there is often a need to store passwords in database tables. This in itself can lead to security issues since people with appropriate privileges can read the contents of the security tables. A common approach to solving this is to encrypt the password before storing it. The problem with encryption is that it implies a possible decryption mechanism that could expose a hole in your security. A safer alternative is to store a hash of the username and password. In this article I'll present a simple example of this process using the DBMS_OBFUSCATION_TOOLKIT package that is available in Oracle8i and Oracle9i:
· Security Table
· Security Package
· Testing
Top of Form
Security Table
First we must build a table to hold the security information:
CREATE TABLE app_users (
id NUMBER(10) NOT NULL,
username VARCHAR2(30) NOT NULL,
password VARCHAR2(16) NOT NULL
)
/
ALTER TABLE app_users ADD (
CONSTRAINT app_users_pk PRIMARY KEY (id)
)
/
ALTER TABLE app_users ADD (
CONSTRAINT app_users_uk UNIQUE (username)
)
/
CREATE SEQUENCE app_users_seq
/
Security Package
Next we create the package that contains the specification of the security code:
CREATE OR REPLACE PACKAGE app_user_security AS
FUNCTION get_hash (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN VARCHAR2;
PROCEDURE add_user (p_username IN VARCHAR2,
p_password IN VARCHAR2);
PROCEDURE change_password (p_username IN VARCHAR2,
p_old_password IN VARCHAR2,
p_new_password IN VARCHAR2);
PROCEDURE valid_user (p_username IN VARCHAR2,
p_password IN VARCHAR2);
FUNCTION valid_user (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN BOOLEAN;
END;
/
We then create the package body to define the actual operations:
CREATE OR REPLACE PACKAGE BODY app_user_security AS
FUNCTION get_hash (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(
input_string => UPPER(p_username) '/' UPPER(p_password));
END;
PROCEDURE add_user (p_username IN VARCHAR2,
p_password IN VARCHAR2) AS
BEGIN
INSERT INTO app_users (
id,
username,
password
)
VALUES (
app_users_seq.NEXTVAL,
UPPER(p_username),
get_hash(p_username, p_password)
);
COMMIT;
END;
PROCEDURE change_password (p_username IN VARCHAR2,
p_old_password IN VARCHAR2,
p_new_password IN VARCHAR2) AS
v_rowid ROWID;
BEGIN
SELECT rowid
INTO v_rowid
FROM app_users
WHERE username = UPPER(p_username)
AND password = get_hash(p_username, p_old_password)
FOR UPDATE;
UPDATE app_users
SET password = get_hash(p_username, p_new_password)
WHERE rowid = v_rowid;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
END;
PROCEDURE valid_user (p_username IN VARCHAR2,
p_password IN VARCHAR2) AS
v_dummy VARCHAR2(1);
BEGIN
SELECT '1'
INTO v_dummy
FROM app_users
WHERE username = UPPER(p_username)
AND password = get_hash(p_username, p_password);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
END;
FUNCTION valid_user (p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN BOOLEAN AS
BEGIN
valid_user(p_username, p_password);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
END;
/
The overloads of VALID_USER allow the security check to be performed in a different manner.The GET_HASH function is used to hash the combination of the username and password. It always returns a VARCHAR2(16) regardless of the length of the input parameters. This level of compression means that the hash value may not be unique, hence the unique constraint on the USERNAME column.The DBMS_UTILITY.GET_HASH_VALUE function could be used to replace the DBMS_OBFUSCATION_TOOLKIT.MD5 function, but the hashing algorithm of the former is not garaunteed to stay constant between database versions.
Testing
First we create a new user:
SQL> exec app_user_security.add_user('tim','hall');
PL/SQL procedure successfully completed.
SQL> select * from app_users;
ID USERNAME PASSWORD
---------- ------------------------------ ----------------
1 TIM [w?44Z䪿?8fE?pre>

Next we check the VALID_USER procedure:
SQL> EXEC app_user_security.valid_user('tim','hall');
PL/SQL procedure successfully completed.
SQL> EXEC app_user_security.valid_user('tim','abcd');
BEGIN app_user_security.valid_user('tim','hall1'); END;
*
ERROR at line 1:
ORA-20000: Invalid username/password.
ORA-06512: at "W2K1.APP_USER_SECURITY", line 37
ORA-06512: at line 1
Next we check the VALID_USER function:
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF app_user_security.valid_user('tim','hall') TH
3 DBMS_OUTPUT.PUT_LINE('TRUE');
4 ELSE
5 DBMS_OUTPUT.PUT_LINE('FALSE');
6 END IF;
7 END;
8 /
TRUE
PL/SQL procedure successfully completed.
SQL> BEGIN
2 IF app_user_security.valid_user('tim','abcd') T
3 DBMS_OUTPUT.PUT_LINE('TRUE');
4 ELSE
5 DBMS_OUTPUT.PUT_LINE('FALSE');
6 END IF;
7 END;
8 /
FALSE
PL/SQL procedure successfully completed.
SQL>
Finally we check the CHANGE_PASSWORD procedure:
SQL> exec app_user_security.change_password('tim','hall','hall1');
PL/SQL procedure successfully completed.
SQL> exec app_user_security.change_password('tim','abcd','abcd1');
BEGIN app_user_security.change_password('tim','abcd','abcd1'); END;
*
ERROR at line 1:
ORA-20000: Invalid username/password.
ORA-06512: at "W2K1.APP_USER_SECURITY", line 47
ORA-06512: at line 1
SQL>

Delete duplicate values from a table

DELETE FROM my_table
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM my_table
GROUP BY delete_col_name);

-- Example :
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- To delete the duplicate values:
--
-- DELETE FROM emp
-- WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);
--
-- COMMIT;
--

Simple encoding and decoding of secret messages

SELECT TRANSLATE(
'HELLO WORLD', -- Message to encode
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE
FROM DUAL
/

SELECT TRANSLATE(
'85@@%._%*@4', -- Message to decode
'1234567890!@#$%^&*()-=_+;,.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE
FROM DUAL
/