Re: FoRMs Level Security

In article <1993Jun3.125620.20433_at_news.unomaha.edu>, moswald_at_cwis.unomaha.edu (Mike Oswald) writes:
> I thought I remember seeing a post concerning security at the FoRMs level.
> Someone had group their customers and placed them in a table which pointed
> to FoRMs or applications that they could run.
>
> Anyway ... is anyone currently during this or has some other means of
> maintaining this type of access to FoRMs/applications? I would rather
> not hard code anything into my FoRMs.
>
> Thanks.

Yes, we store the forms privileges by role in database tables, and then in the pre-form trigger we check if the user has the necessary privilege to run the current form.

The privileges look like the form name, e.g. FAFORM22, and a user with a privilege like FA_ALL has the privilege to run all FAxxxxxxx things. A specific privilege granted by XX_ALL can be overridden by something of the format NO_FAxxxx, e.g. NO_FAFORM22. All privileges may be granted either directly to a user or via a role, with the user privileges taking precedence over the role-based privileges.

We use the same pre-form trigger for all forms, getting the form name from :system.current_form or a substring of :system.current_form, depending on how the form was called.

The pre-form trigger looks like this:

   DEFINE TRIGGER

      NAME = PRE-FORM
      TRIGGER_TYPE = V3
      SHOW_KEY = OFF
      DESCRIPTION = Check for Privilege
      TEXT = <<<
      declare
        form_name char(10);
        has_priv  boolean;
      begin
        if upper(substr(:system.current_form,3,6)) = '_FORM:' then
          form_name := substr(:system.current_form,9,8);
        else
          form_name := :system.current_form;
        end if;
        check_privilege(form_name,has_priv);
        if has_priv = FALSE then
          message_handler (20002,FALSE,'Y');
          raise form_trigger_failure;
        end if;
      end;
      >>>

   ENDDEFINE TRIGGER And the check_privilege procedure looks like this:

   DEFINE PROCEDURE

      NAME = check_privilege
      DEFINITION = <<<
      procedure check_privilege (in_priv_level in char,
                                 has_priv out boolean) is
        dummy_char  char(1);
        xx_all      char(6);
      begin
        /***************************************/
        /* Set the XX_ALL string for the "ALL" */
        /* privileges function for the system. */
        /***************************************/
        xx_all := substr(in_priv_level,1,2) || '_ALL';
        /*************************************/
        /* Test for positive assignment of   */
        /* the specified privilege or XX_ALL */
        /* with no offsetting denial at the  */
        /* same or a higher level.           */
        /*************************************/
        select 'X' into dummy_char from dual
        where exists (select 'x'
        from all_user_functions a
        where username = user
        and ((function_code = in_priv_level
               and function_basis = 'U')
             or (function_code = xx_all
                 and function_basis = 'U'
                 and not exists (select 'x' from all_user_functions
                              where username = user
                              and function_code = 'NO_' || in_priv_level
                              and function_basis = 'U'))
             or (function_code = in_priv_level
                 and function_basis = 'R'
                 and not exists (select 'x' from all_user_functions
                              where username = user
                              and function_code = 'NO_' || in_priv_level
                              and function_basis = 'U'))
             or (function_code = xx_all
                 and function_basis = 'R'
                 and not exists (select 'x' from all_user_functions b
                              where username = user
                              and function_code = 'NO_' || in_priv_level
                              and (function_basis = 'U'
                                   or (function_basis = 'R'
                                       and a.role = b.role))))));
        has_priv := TRUE;
      exception
        when no_data_found then
          has_priv := FALSE;
      end;
      >>>

   ENDDEFINE PROCEDURE Note: We use this also to control who can update and who can only query, or

      who can get to certain pages of the form, etc.

Good luck,

Jennifer R. Amon            PHONE: (216) 775-6987   
Houck Computing Center        FAX: (216) 775-8573    
Oberlin College          
Oberlin, OH 44074        INTERNET: bamon_at_ocvaxc.cc.oberlin.edu
_____________________________________________________________________