Previous | Table of Contents | Next

Page 391

Using Triggers and Program Units to Check User
Security

This section of the chapter illustrates a common security procedure I use on forms that I create. The technique consists of two security triggers. The first trigger authenticates a user by means of a password. The second trigger determines whether the user has the proper security to perform the transaction. The section also covers the use of program units to create common PL/SQL code blocks.

Form Security Triggers

Most of the forms and systems that are developed require security features to be embedded. I have seen various types of security requests. Some forms allow all users to query the records but not to add, update, or delete the records. The same form allows other users to perform database modifications. In several systems, a security trigger is used to prevent some users from launching specific applications. This section presents a security scheme you can use to meet most security demands.

The heart of the system is a security options table called Sec_tab in the practice database. The table contains two columns: payroll_ number and security_option. Throughout a typical system, various transactions are identified and given a code if security is needed. For instance, the privilege to add, update, or delete records on a form will be represented by a security_option value of AUD. Users without this privilege will not have the authority to update the database through the use of the form. Each unique type of transaction that requires security in the system will be assigned a code.

Users will have a record in the security table for each type of transaction they are permitted to perform. In addition, each user that has a security privilege will have a record containing a password in the security option field. Listing 15.1 displays the contents of the Employee security table. Employees #25 and #35 have passwords of FUZZ and RON. Each of the employees also has the ability to add, update, and delete records. This privilege has not been extended to the remainder of the employees.

Listing 15.1L_15_01.TXT—Example Security Password and Option Records for the Employee Update Form

SQL> select * from sectab order by 1;

FK_PAYROLL_NUMBER SECU
----------------- ----
               25 AUD
               25 FUZZ
               35 AUD
               35 RON

Page 392

NOTE
This is an example of what security options may look like. The sec_tab table does not have any values in it at this time.n

Security monitoring is activated when the user launches the system's startup form. On the form are two text items. The user enters a payroll number into the first item, and the corresponding password into the second. Each of the form items has a post-change trigger. The payroll number trigger assigns the global variable payroll_number with the contents of the item. The password trigger performs a security check.

The password post-change trigger is shown in Figure 15.10. This trigger verifies the authenticity of the user. This trigger is executed when the user navigates from the item. It opens a cursor that counts the number of records in the security table that match the values entered by the user in the payroll_number and password fields. If the cursor finds a match, a message is issued stating the user has passed the initial security check. The global variable security_check receives a value of OK. This variable is passed to the various applications in the system.


FIG. 15.10
The password post-
change trigger used to
validate that the user
has security options.



The second part of the security scheme consists of a trigger that fires before the controlled action is performed by the form. The controlled action consists of calling a form or performing a database transaction. When-button-pressed, Pre-insert, Pre-update, and Pre-delete triggers are used for the security checks. The When-button-pressed trigger is attached to a menu option that calls a form. It prevents the user from launching the form. The Pre-insert, Pre-update, and Pre-delete triggers prevent database transactions. Use of these triggers allows all users to call forms and query records. The security check fires when the database transactions are performed and only prevents database transactions.

Figure 15.11 shows a form-level Pre-insert trigger that performs the security check on the Employee Update form. This trigger has a cursor that counts the number of security records for the user. The selected records must have a security_option value of AUD, the security code for performing database transactions. Before the cursor is opened, the first if statement

Page 393

determines whether the user has passed the original security check. If the user has not passed the check, an error message appears and the insert transaction is terminated by the raise form_trigger_failure statement. If the user passes the security check procedure, the cursor executes. The second if statement evaluates the result of the cursor. If the user does not have a record with a value of AUD in the security_option column, an error message appears and the transaction terminates. The transaction occurs only when the user passes the password security procedure and has the proper security option record.


FIG. 15.11
A Pre-insert trigger
prevents unauthorized
users from adding
records to the
database.



You can use the same trigger PL/SQL code block in the Pre-delete and Pre-update triggers. In this example, the transaction triggers were created at the form-level. The security checks can be used to control block and even items. This is done by assigning the triggers to a block or item. I think you will find this scheme an efficient security mechanism.

Using Form Procedures

The security scheme in the previous section used the same PL/SQL code block in the Pre-update, Pre-insert, and Pre-delete triggers. It is bad practice and time consuming to have the same code in multiple places. It is very easy to modify the code in one of the locations and forget to modify it in the remainder of the locations. Even if the developer remembers to change the code in all of the locations, it still takes more time than changing it in one location. Form procedures allow the developer to avoid this problem.

Form procedures are program units that are defined in the form. They have a name and can be called by other PL/SQL objects in the form. To create a program unit perform the following:

  1. Open the Object Navigator.

  2. Locate the program unit object and click the create icon. Figure 15.12 displays the Object Navigator and the program unit object.

Previous | Table of Contents | Next