Implementing Value Based Security

Note: This is NOT Banner's Value Based Security or Oracle's Fine-Grained Access Control.

Here is a sample of a trigger you can install for implementing value based security to only allow certain users to insert, update, or delete certain address types (atyp_code) in an address table (addresses) owned by aowner (substitute your address table name for addresses and address table owner for aowner in this generic example). Doing VBS this way does not require any baseline code changes to your forms. I've checked it in some Banner screens and it seems to work fine, but, you should check it on a test database with the other Banner screens before adding it to your production database.

Before loading the trigger (by running @gen_addresses_atyp_trigger.sql from sqlplus from a DBA account or from user system), you'll need to set up an atypchk table from user aowner, and place all the allowable atype code / user id records into atypchk. Any atyp code not given in atypchk can be modified by anyone. Otherwise, only the user id's listed in atypchk for the atyp code can modify that atyp code.

In Banner, the status line will just show "unable to update record" (or delete or insert). Clicking on Help and then on Display Error will show the application error message from the attempted modification by the unauthorized user. If it doesn't do what you want, you can just drop the trigger to get rid of it.

-- Script: gen_addresses_atyp_trigger.sql
--
-- Function: Create a trigger on inserts, updates, and deletes from
--     addresses to check the address type being processed and
--     the user doing the processing to see if the user is
--     authorized to process that address type, raising an
--     application error on unauthorized address type
--     processing.
--
-- Notes: The following sql must have already been run to create
--     the aowner.atypchk table with the atyp_code and atyp_user
--     columns, and the authorized type code / user name pairs
--     must have already been entered into the atypchk table.
--
-- Log into sqlplus as user aowner and enter:
--     create table atypchk (atyp_code varchar2(2),
--     atyp_user varchar2(30));
--     grant select on atypchk to public;
-- Enter address types and user names into atypchk (probably
-- all in upper case), such as:
--     insert into atypchk values ('B1','MYUSERID');
--
-- Author: Stephen Rea <srea@uaex.edu>
--     University of Arkansas Cooperative Extension Service

drop trigger aowner.srea_addresses_atyp_trigger;

create trigger aowner.srea_addresses_atyp_trigger
before insert or delete or update on addresses
for each row
declare
    cursor atypchk_cur (atyp varchar2, usr varchar2) is
        select atyp_code from aowner.atypchk
            where atyp_code = atyp
            and atyp_user like usr;
    atypchk_rec atypchk_cur%ROWTYPE;
    this_atyp varchar2(2) := ' ';
    this_func varchar2(10);
    unauthorized_atyp_proc exception;
begin

    -- Get the current address type and function being performed.

    if inserting then
        this_atyp := :new.addresses_atyp_code;
        this_func := 'Inserting';
    else
        this_atyp := :old.addresses_atyp_code;
        if deleting then
            this_func := 'Deleting';
        else
            this_func := 'Updating';
        end if;
    end if;

    -- If this address type code is one to be checked (the type
    -- is in the atypchk table) ...

    open atypchk_cur(this_atyp,'%');
    fetch atypchk_cur into atypchk_rec;
    if atypchk_cur%FOUND then
        close atypchk_cur;

        -- Then check to see if this user is listed for this type,
        -- raising an exception if the type/user pair is not in
        -- the atypchk table.

        open atypchk_cur(this_atyp,user);
        fetch atypchk_cur into atypchk_rec;
        if atypchk_cur%NOTFOUND then
            close atypchk_cur;
            raise unauthorized_atyp_proc;
        end if;
    end if;
    close atypchk_cur;

exception

    -- Raise an error when an unauthorized user tries to process
    -- this address type.

    when unauthorized_atyp_proc then
        raise_application_error (-20500,this_func || ' of address type ' ||
            this_atyp || ' not allowed by user ' || user);
end;
/

University of Arkansas • Division of Agriculture
Cooperative Extension Service
2301 South University Avenue
Little Rock, Arkansas 72204 • USA
Phone (501) 671-2000 • Fax (501) 671-2209

Mission • Disclaimer • EEO •
Privacy • FOI

Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service





Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search \| Publications \| Jobs \| Personnel Directory \| Links