Press "Enter" to skip to content

Checking For User Permissions Before Updating or Inserting The Records in Oracle Forms

Suppose you want to check the user permissions on inserting or updating the records in Oracle Forms, then you can use Pre-Insert and Pre-Update triggers for that particular data block to check whether user is having proper permission or not.
The example is given for HR schema and the following demo table is created for this:
Create Table User_Permissions (User_Name varchar2(50) Primary Key,
Can_Insert Varchar2(1) default ‘N’,
Can_Update Varchar2(1) default ‘N’)
Insert into User_Permissions values (‘FORMUSER1’, ‘Y’, ‘N’);
Commit;
Below is the screen shot for the examle:
You can download this form from the following link: Pre-Update-Insert.fmb
The following is the code written in Pre-Update trigger to check the permissions from the database:
Declare
n_allow number;
begin
Select 1 into n_allow
  from hr.user_permissions
  where user_name = ‘FORMUSER1’
  and can_update = ‘Y’;
  — all is well if no exception raised else stop in exception area
  exception
  when others then
     message(‘You have no permission to update the record.’);
     message(‘You have no permission to update the record.’);
     raise form_trigger_failure;
end;
The following is the code written in Pre-Insert trigger to check the permissions from the database on Insert:
Declare
n_allow number;
begin
Select 1 into n_allow
  from hr.user_permissions
  where user_name = ‘FORMUSER1’
  and can_insert = ‘Y’;
  — all is well if no exception raised else stop in exception area

  exception
  when others then
     message(‘You have no permission to insert the record.’);
     message(‘You have no permission to insert the record.’);
     raise form_trigger_failure;
   
end;

The code is written for Save button:

Commit_form;

Vinish Kapoor

FoxInfotech.in is created, written, and maintained by Vinish Kapoor. It is built on WordPress, and hosted by Bluehost. Connect with Vinish on Facebook, Twitter, and GitHub.

You may also like: