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.
No matter what method is used with security there has to be two types of tables built in the database.
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.
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.
We will create two security level tables, SECURITY and WEB_ACCESS tables. Indexes will not be covered within the scope of this document.
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)
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 used here represent regions (REGION) and customers (CUSTOMER) within those regions. These tables are not detailed in this model.
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;
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;
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;
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.
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.