Database Driven Oracle Security


How to Build Security Between Oracle and Users

This page will explain a method of approaching security for a company's internal and external internet users. It is not practical to allow every potential user to be an Oracle database user. Thus effective security can be accomplished by creating a security structure which mimics the role, group and user security process within Oracle using user created Oracle tables, stored functions and procedures. Use of stored processes to contain SQL statements will secure the database with respect to what tables are in the Oracle database and how data is retrieved from them. Additionally, in this way, much if not all of the SQL processing required is executed on the Oracle server database itself thus reducing network traffic and client data accessing processing time to a minimum.

Types of Tables Required

No matter what method is used with security there has to be two types of tables built in the database.

  1. Security tables containing attributes such as user names and user identification details.
  2. Tables specific to a particular application or applications.

Relationships Between Tables

There must also me some type of linkage between these two types of tables in order to establish relationships between application data and security aspects to be applied to that application data.

The Implementation

We will use two tables, one function and two procedures. All SQL statements are executed on the Oracle database from within stored procedures or functions. Note that the parameters and return variables can be altered to accept and/or return other variables if so required. The intention is to allow only input of a user name and password where the result passed back is a data object which is applicable only to that user name and password combination. Presently the returned result is a distinct list of customers within regions within which the logged-in user can view and/or manipulate data in the Oracle database.

The Tables

We will create two security level tables, SECURITY and WEB_ACCESS tables. Indexes will not be covered within the scope of this document.

The SECURITY Table

USERID		NOT NULL VARCHAR2(15)
PASSWORD	NOT NULL VARCHAR2(15)
USERNAME	NOT NULL VARCHAR2(35)
ORGANIZATION	NOT NULL VARCHAR2(40)
CONTACT		NOT NULL VARCHAR2(50)

The WEB_ACCESS Table

This table contains duplicate REGION_CODE + CUSTOMER_CODE tuples. These duplicate tuples are made unique using the WEB_DATE field. For the purposes of security a distinct set of REGION_CODE + CUSTOMER_CODE tuples are required.

WEB_DATE		NOT NULL DATE
USERID			NOT NULL VARCHAR2(15)
USER_TYPE		VARCHAR2(20)
REGION_CODE		CHAR(4)
CUSTOMER_NUMBER		CHAR(4)

Application Tables

Application tables used here represent regions (REGION) and customers (CUSTOMER) within those regions. These tables are not detailed in this model.

The Stored Functions and Procedures

The GET_SECURE_REGION_CUSTOMER Stored Procedure

This is a simple procedure which calls the function and the other procedure. By embedding the two calls into this single procedure this mechanism can be used for any requirement, such as, an ASP page, a Crystal report or a VB GUI application. Also note once again that the input and ouput parameters can be altered in order to increase Oracle database data security if so required; the general concept will still be the same.

(cWeb_Access IN OUT RECORDS.rWeb_Access, pUSERNAME IN VARCHAR2, pPASSWORD IN VARCHAR2) AS

BEGIN
	GET_WEB_ACCESS (cWeb_Access, VALID_SECURITY (pUSERNAME, pPASSWORD));
EXCEPTION
	WHEN OTHERS THEN
		NULL;
END; 

The GET_WEB_ACCESS Stored Procedure

GET_WEB_ACCESS accepts the USERID value retrieved by the VALID_SECURITY function. A distinct set of REGION_CODE + CUSTOMER_CODE tuples will be returned in the by-reference record structure.

(cWeb_Access IN OUT RECORDS.rWeb_Access, pUSERID IN VARCHAR2) IS
BEGIN
	OPEN cWeb_Access FOR
		SELECT DISTINCT REGION_CODE, CUSTOMER_CODE
		FROM WEB_ACCESS WHERE USERID = pUSERID;
        
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;
END;

The VALID_SECURITY Stored Function

VALID_SECURITY gets a USERID for a username and password entry.

(pUSERNAME IN VARCHAR2, pPASSWORD IN VARCHAR2) RETURN VARCHAR2 AS

lUSERID VARCHAR2(15) DEFAULT '';

BEGIN

	SELECT	USERID INTO lUSERID
	FROM 	SECURITY
	WHERE	USERNAME = pUSERNAME
	AND	PASSWORD = pPASSWORD;

	RETURN lUSERID;

EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;

END;

The RECORD Package

This package contains the data types for the record structures retrieved.

AS

	TYPE jWeb_Access is record
	(
		REGION_CODE	WEB_ACCESS.REGION_CODE%TYPE,
		CUSTOMER_CODE	WEB_ACCESS.CUSTOMER_CODE%TYPE
	);
	TYPE rWeb_Access IS REF CURSOR RETURN jWeb_Access;

	TYPE rRegion IS REF CURSOR RETURN REGION%ROWTYPE;
	TYPE rCustomer IS REF CURSOR RETURN CUSTOMER%ROWTYPE;

	TYPE jRegionCustomer is record
	(
		REGION_CODE	REGION_LOOKUP.REGION_CODE%TYPE,
		CUSTOMER_CODE	CUSTOMER.CUSTOMER_CODE%TYPE,
		CUSTOMER_NAME	CUSTOMER.CUSTOMER_NAME%TYPE
	);
	TYPE rRegionCustomer IS REF CURSOR RETURN jRegionCustomer;

END;

Below is a diagram representing this security solution.

Security based on the original (current) database table structure of the SECURITY and WEB_ACCESS tables

The diagram shows how the function of this simplistic security approach works. Note that the boxed in area is contained within the database. This area is never visible outside of the Oracle database itself since it is only ever called from the GET_SECURE_REGION_CUSTOMER stored procedure. The procedure GET_SECURE_REGION_CUSTOMER can effectively be called from any type of application which is capable of calling stored procedures in an Oracle database, even another stored procedure or function. Applications could be written in ASP, Visual Basic, Crystal Reports, etc. The GET_SECURE_REGION_CUSTOMER will accept a username and password and return a list of region plus customer combinations which are available to that user. Note that this region plus customer set only has to be retrieved ONCE. This could be a security concern. However, since a user will only ever view his own customers who else would know which customers that user is accessing unless another user knows the password. The service providor can not be held responsible for a user giving away his or her password. If the return of the region plus customer set is a security issue then the userid can be returned instead. However, in this case additional overhead will be incurred by this requirement since every suqsequent database search will require searching of or joining with both the SECURITY and WEB_ACCESS tables. This will definitely affect access speed, even though these two tables are very small. However, objects such as Crystal Report objects generally require an SQL statement in its entirety. Thus a Crystal Report report would have to execute the GET_SECURE_REGION_CUSTOMER stored procedure for each report execution anyway. Thus it should also be noted that for any access whatsoever to the SECURITY and the WEB_ACCESS tables for the purposes of security validation using a user name and password, the GET_SECURE_REGION_CUSTOMER stored procedure can be utilised. This is a fully resuable piece of code. Note that Crystal Reports is extremely inefficient in its use of database access and is incapable of handling complexity.