AN INTRODUCTION TO PL/SQL

Implementing High Security in Developer Applications

John C. Lennon, Dulcian, Inc.

Introduction

There is no such thing as a totally secure computer system or application of any type on any platform. To achieve 100% security, it would be necessary to deny access to anyone at anytime and for any reason, which somewhat restricts the usefulness of the system to say the least. We therefore have to compromise and concentrate our efforts on protecting the data and preventing unauthorized access.

Oracle offers multiple levels of security, ranging from simple to complex. At the simplest level, we can control access with usernames and passwords either at the operating system level or within the database. Access to database objects can be controlled through grants, roles, procedures, triggers and views. At the highest level, and at extra cost, Oracle offers options such as Trusted Oracle( primarily used by government), the Advanced Networking Option and Oracle Application Server.

Whichever option is chosen implementation and maintenance requires skilled and expensive personnel. This is not a problem for larger companies who will normally need these skilled people in any case. It is more of a problem for the smaller entities where cost plays a large role.

The challenge we faced was to devise a strategy that offered a reasonably high level of security and auditing, but could be easily administered by relatively unskilled personnel.

Access Control

It was decided that the application objects would be accessed by a single user account. All of the application users would have their own accounts only for login purposes. These accounts would be granted no privileges other than CONNECT. The single user account does not own the application. It only has grants to the necessary application objects. The application schema owner has no CONNECT privilege therefore making it impossible for anyone, other than a user with high level privileges, to access the application objects directly.

The Security Data Model

Figure 1: The Security Data Model

The model consists of five tables plus the system table SYS.ALL_USERS that is used as a reference table, as shown in Figure 1.

Figure 2: User, Form and Role Maintenance Screens

A form with four tabs is provided to facilitate creating and maintaining roles, registering forms in the application, registering users and maintaining the relationships between roles, forms and users. The form is illustrated in Figure 2. Both users and forms can be assigned multiple roles.

The table APP_VERSION exists principally to store the name of the common login account, which the users actually never need to know, and the encrypted password for login. The password is contained in a column with a name that has no similarity to “password”. Code for the Login procedure is shown in Code Example 1.

Procedure CHECK_LOGIN IS

v_User_Name VARCHAR2(30);

v_password VARCHAR2(30);

v_connect_string VARCHAR2(30) := Get_Application_Property(CONNECT_STRING);

BEGIN

-- Populate a global with the username for use on the client side.

:global.real_user_name := get_application_property(username);

--Retrieve the application username and password.

security_pkg.get_logon(:global.real_user_name

,v_User_Name

,v_Password);

--and then log the user out

logout;

--If the user is invalid exit the form.

IF v_User_Name = 'Invalid User' THEN

exit_form(no_validate);

END IF;

-- Append the connect string (if it exists) to the username.

IF v_Connect_String IS NOT NULL THEN

v_User_Name := v_User_Name||'@'||v_Connect_String;

END IF;

-- Log on again, using the common login ID.

logon(v_User_Name,v_Password);

security_pkg.Set_Variables(:global.real_user_name

,v_User_Name

,v_Password);

-- Navigate to the application menu

New_Form(‘APP_MENU’):

EXCEPTION

WHEN OTHERS THEN

exit_form(no_validate);

END;

Code Example 1: Login Procedure

A form is provided that has only one function, to log the user into the application. No other form in the application has the capability of logging in. When the user logs in, the username and password are verified in the normal way. The procedure in Figure 3 is then called. This procedure passes the user name to a database procedure, which verifies that the user is registered in the application’s user table. The common user name and password are then returned to the calling procedure, which logs the user out of their own account and logs them in as the common user. The original user name is stored both in a form global variable and as a package variable in the security package. On the database side, this is required both for auditing and for access to any database object.

Form/Role Verification

The FORM_ROLES and USER_ROLES tables are used to control what forms are displayed to the users in the application menu. However, as an added precaution, the user’s right access a form is checked in a pre-form trigger in each form. For audit purposes, a variable in the database security package is populated with the name of the form whenever navigation between forms occurs.

The Security Package

The specification for the security package and private variables and functions are shown in Code Example 2. There are no grants to any user for the security tables so, to all but the owner, they appear not to exist. All access to the security related tables is through this package or table through APIs developed to support the maintenance forms.

Package Specification

SECURITY_PKG IS

PROCEDURE Get_Logon(p_Logon_User IN VARCHAR2

,p_User_Name OUT VARCHAR2

,p_Password OUT VARCHAR2);

FUNCTION Change_Password(p_Password VARCHAR2

,p_Old_Password VARCHAR2)

RETURN VARCHAR2;

FUNCTION logon_user

RETURN VARCHAR2;

FUNCTION Get_Set_Form_Name(p_Form_Name VARCHAR2)

RETURN VARCHAR2;

FUNCTION Is_App_User(p_User VARCHAR2)

RETURN BOOLEAN;

FUNCTION User_Has_Form_Role(p_User VARCHAR2

,p_Form VARCHAR2)

RETURN BOOLEAN;

PROCEDURE Set_Variables(p_Logon_User IN VARCHAR2

,p_User_Name IN VARCHAR2

,p_Password IN VARCHAR2);

END Security_pkg;

Private Variables and Functions in the Package Body

pbv_Logon_User VARCHAR2(30);

pbv_Form_Name VARCHAR2(30);

FUNCTION decode_password(p_Password VARCHAR2)

RETURN VARCHAR2

IS……………

FUNCTION Encrypt_Password(p_Password VARCHAR2)

RETURN VARCHAR2

IS……………

Code Example 2

Application Objects

The following section describes various application objects.

Tables, Packages and Triggers

Like the security tables, the tables in the application have no grants and are therefore not directly accessible to any user except the schema owner. As mentioned previously the schema owner has no connect privilege and therefore there can be no logon to this account. All access to the data is through either packages or updateable views. The each package has a call to the function in the security package that checks for a value in the variable pbv_Logon_User. If this variable has not been populated, the package will abort. Similarly the views have a reference to the same function in their WHERE clause. This will cause the view to apparently contain no data if the variable is null as illustrated in the example shown in Code Example 3. As a further precaution, each table has a before INSERT/UPDATE trigger which, like the packages, checks for a value in the security package variable.

Create or replace

VIEW EMPLOYEE_VU IS

SELECT *

FROM employee

WHERE security_pkg.logon_user IS NOT NULL

Code Example 3: Example of a View

Auditing

Oracle does not provide an easy way to track changes that are made to individual columns or even to specific rows in the database. Having a requirement to do this an audit application was developed, once again, with the aim of ease of maintenance by relatively unskilled users.

Figure 3: Maintenance Form and Data Model for Audit Application

The form shown in Figure 3 allows the users’ administrator to select which tables and columns should be audited and on what event; any or all of insert, update and delete.

When the user invokes the form the WHEN-NEW-FORM-INSTANCE TRIGGER calls the procedure shown in Code Example 4, which keeps the AUDIT_TABLES table in synchronization with the data dictionary.

PROCEDURE update_audit_tables IS

BEGIN

INSERT INTO audit_columns

SELECT utc.table_name

,utc.column_name

,utc.data_type

,'N'

FROM user_tab_columns utc

WHERE NOT EXISTS (SELECT 1

FROM audit_columns

WHERE table_name = utc.table_name

AND column_name = utc.column_name)

AND NOT EXISTS (SELECT 1

FROM user_views

WHERE view_name = utc.table_name)

AND utc.table_name NOT LIKE '%AUDIT%';

END;

BEGIN

INSERT INTO audit_tables

SELECT ut.table_name

,'N'

FROM user_tables ut

WHERE NOT EXISTS (SELECT 1

FROM audit_tables

WHERE table_name = ut.table_name)

AND NOT EXISTS (SELECT 1

FROM user_views

WHERE view_name = ut.table_name)

AND ut.table_name NOT LIKE '%AUDIT%';

END;

BEGIN

DELETE FROM audit_columns ac

WHERE NOT EXISTS (SELECT 1

FROM user_tab_columns

WHERE table_name = ac.table_name

AND column_name = ac.column_name)

OR EXISTS (SELECT 1

FROM user_views

WHERE view_name = ac.table_name)

OR ac.table_name LIKE '%AUDIT%';

END;

BEGIN

DELETE FROM audit_tables a

WHERE NOT EXISTS (SELECT 1

FROM user_tables

WHERE table_name = a.table_name)

OR EXISTS (SELECT 1

FROM user_views

WHERE view_name = a.table_name)

OR a.table_name LIKE '%AUDIT%';

END;

Code Example 4: Procedure to Synchronize Tables

When the administrator has finished the updates to the form and saved the changes, pressing the button marked “Create ddl File” causes the code generating procedure shown in Code Example 5 to run. This first creates code to drop existing triggers that will no longer be required, and then generates the code to create or replace the triggers on the tables to be included in the audit.

PROCEDURE create_ddl IS

v_Table_Name VARCHAR2(32);

v_File text_io.file_type := text_io.fopen('audtrigs.sql', 'w');

v_Trigger_Id VARCHAR2(3) := '0';

v_Type VARCHAR2(32);

v_Key_Column VARCHAR2(32);

v_col_count NUMBER;

CURSOR cur_user_triggers IS -- cursor for routine to drop existing triggers

SELECT trigger_name -- that will no longer be required.

,table_name

FROM user_triggers

WHERE trigger_name LIKE 'AUDIT%'

AND table_name NOT IN (SELECT table_name

FROM audit_tables

WHERE on_insert = 'Y'

OR on_update = 'Y'

OR on_delete = 'Y');

CURSOR cur_tables IS -- cursor to find tables to create triggers for.

SELECT table_name

,on_insert

,on_update

,on_delete

FROM audit_tables

WHERE on_insert = 'Y'

OR on_update = 'Y'

OR on_delete = 'Y';

CURSOR cur_Columns IS -- cursor to find columns to include in audit.

SELECT column_name -- (applies to insert and update only.)

FROM audit_columns

WHERE table_name = v_table_name

AND include_in_audit = 'Y';

FUNCTION add_or(p_instring VARCHAR2) RETURN VARCHAR2

BEGIN

IF p_instring IS NULL THEN

RETURN NULL;

ELSE

RETURN p_instring||' or ';

END IF;

END add_or;

FUNCTION Find_First_Column(p_Table VARCHAR2) RETURN VARCHAR2

CURSOR cur_Colname IS

SELECT column_name

FROM user_tab_columns

WHERE column_id = 1

AND table_name = p_Table;

v_Column_Name VARCHAR2(30);

BEGIN

OPEN cur_Colname;

FETCH cur_Colname

INTO v_Column_Name;

CLOSE cur_Colname;

RETURN v_Column_Name;

END Find_First_Column;

BEGIN

--** First create the DDL to drop existing triggers **

BEGIN

FOR trg IN cur_user_triggers LOOP

text_io.put_line(v_file,'PROMPT Dropping trigger '||trg.trigger_name

||' on table '||trg.table_name||';');

text_io.put_line(v_file,'DROP TRIGGER '||trg.trigger_name||';');

END LOOP;

END;

BEGIN

-- ** Loop through the tables flagged for inclusion **

FOR tab IN cur_tables LOOP

v_Trigger_Id := v_Trigger_Id + 1;

v_Table_Name := tab.table_name;

v_Type := NULL;

IF tab.on_insert = 'Y' THEN

v_type := 'Insert';

END IF;

IF tab.on_update = 'Y' THEN

v_type := add_or(v_Type)||'Update';

END IF;

IF tab.on_delete = 'Y' THEN

v_type := add_or(v_type)||'Delete';

END IF;

text_io.put_line(v_file,'PROMPT Creating trigger AUDIT_'||v_Trigger_id||'

on table '||v_table_name);

text_io.put_line(v_file,'Create or Replace Trigger AUDIT_'||v_Trigger_id);

text_io.put_line(v_file,'After '||v_Type||' on '||v_Table_Name);

text_io.put_line(v_file,'For each row');

text_io.put_line(v_file,'DECLARE');

text_io.put_line(v_file,' v_Sysdate DATE := SYSDATE;');

text_io.put_line(v_file,' v_Transaction_Type VARCHAR2(8);');

text_io.put_line(v_file,'BEGIN');

text_io.put_line(v_file,' IF INSERTING THEN');

text_io.put_line(v_file,' v_Transaction_Type := '||in_quotes('INSERT')||';');

text_io.put_line(v_file,' ELSIF UPDATING THEN');

text_io.put_line(v_file,' v_Transaction_Type := '||in_quotes('UPDATE')||';');

text_io.put_line(v_file,' ELSE');

text_io.put_line(v_file,' v_Transaction_Type := '||in_quotes('DELETE')||';');

text_io.put_line(v_file,' END IF;');

-- First find the first column in the table - assume this to be the primary key

-- This may need re-visiting to more reliably determine the PK column.

v_Key_Column := Find_First_Column(v_Table_Name);

v_Col_Count := 0; -- Now loop through columns flagged for inclusion

FOR col IN cur_Columns LOOP -- in audit and create code.

v_Col_Count := v_Col_Count + 1;

text_io.put_line(v_file,' IF :old.'||col.column_name||' <> :new.'||col.column_name

||' OR v_Transaction_Type <> '||in_quotes('UPDATE')||' THEN ');

text_io.put_line(v_file,' audit_trail_insert('||in_quotes(v_Table_Name));

text_io.put_line(v_file,' ,:old.'||v_Key_Column);

text_io.put_line(v_file,' ,v_Transaction_Type');

text_io.put_line(v_file,' ,'||in_quotes(col.column_name));

text_io.put_line(v_file,' ,:old.'||col.column_name);

text_io.put_line(v_file,' ,:new.'||col.column_name);

text_io.put_line(v_file,' ,USER');

text_io.put_line(v_file,' ,v_Sysdate');

text_io.put_line(v_file,' );');

text_io.put_line(v_file,' END IF;');

END LOOP;

IF v_Col_Count = 0 THEN

text_io.put_line(v_file,' audit_trail_insert('||in_quotes(v_Table_Name));

text_io.put_line(v_file,' ,:old.'||v_Key_Column);

text_io.put_line(v_file,' ,v_Transaction_Type');

text_io.put_line(v_file,' ,NULL');

text_io.put_line(v_file,' ,USER');

text_io.put_line(v_file,' ,v_Sysdate');

text_io.put_line(v_file,' );');

END IF;

text_io.put_line(v_file,'END;');

text_io.put_line(v_file,'/');

END LOOP;

text_io.fclose(v_File);

END;

Code Example 5: Procedure to Create DDL

The drawbacks here are that the administrator needs to know the otherwise very secret password for the application owner, and connect privilege has to be temporarily granted to the application owner. It is also desirable that, as DDL is going to be executed to modify the database, someone with more than a rudimentary knowledge of database administration oversees the operation. However, since this is not something that is going to happen often, bringing in expert help is probably not a real problem.

Conclusion

It is possible to simplify the implementation of a reasonably high level of data security without the level of expertise and expense normally associated with Oracle’s solutions. Network security, be it a private network or the Internet, is a separate issue and not addressed here. Implementation of security at these levels is a separate issue and varies greatly according to the level of exposure involved. However, with the level of data security outlined in this paper, external intrusions should not prove to be a great problem.

To further protect access to the database, the packages described here should be WRAPped to make it even more difficult to access the data by concealing package code.