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@maristream.org>
--     Maristream, Inc.

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;
/

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.