I have 3 tables:


Emp_Class (FK) ref Employee_class in table1

Transaction_id (PK), 
Emp_id (FK) ref Emp_id in table2, 

table1 is used to know if a certain class employee can access a particular location

table2 contains details of employees

table3 records every time an employee accesses a particular location. Now, access should be permitted only when the employee class of the employee is allowed to access that particular location.

So what I am trying to do is to insert into table3, but I wish to give constraints as well, i.e., I want to check Emp_Class of Emp_id from table2 and then further check from table1 if Employee_class has access to that particular location, only then allow insert in table3.

How can I do this? I was thinking about writing an insert query with a join on table3 and table2, with case but that is apparently not allowed in SQL. I am free to consider procedures and triggers but as I am new to advanced SQL, I am not sure how to do that.

Thanks. Also, I apologize if my question is framed poorly, I am new to this , please bear.

  • What is the Allowed column in table3 for if records can only be entered when access is allowed? Oct 7, 2018 at 0:32
  • I am planning to add records even when access was denied so as to track suspicious activity, so Allowed would describe if the employee was actually granted access or not. Oct 7, 2018 at 2:17
  • I am having problems understanding why you were attempting to JOIN with a CASE statement. Oct 7, 2018 at 12:34
  • What I wish to do is insert a record in table3, Emp_id is checked against Emp_class (from table2), and further it is checked if that Emp_class has access to a particular location (2-7 of table1). So location will be entered only if value of an Emp_class against that location is true (table1). The values of location, I am now realizing can be from column names 2-7 in table1. If we get true from table1 then Allowed is TRUE, else FALSE. table1 and table2 are given but table3 can be modified or discarded to make new table(s). Oct 7, 2018 at 14:59
  • 1
    SomendraChaudhary - and as @MichaelKutz pointed out, you will need to adjust exactly how you craft your SELECT to account for days, weeks, months, whatever. My point wasn't that a simple sysdate - hiredate would necessarily give you exactly what you need, but that instead of trying to store 'time of service' you should store hire date, then compute time of service as relative to sysdate at report time.
    – EdStevens
    Oct 8, 2018 at 13:54

2 Answers 2


Since the LOCATIONS and employee CLASSES are quite important for your database, you should probably give them separate tables. Here some suggestions - which may serve as a starting point (including some test data, using Oracle 12c).

create table locations ( id, description )
select 100, 'Executive Lounge' from dual union all
select 200, 'Executive Washroom' from dual union all
select 300, 'Supply Closet' from dual union all
select 400, 'Employee Lounge' from dual union all
select 500, 'Server Room' from dual union all
select 600, 'Server Closet' from dual;

create table employeeclasses ( id, description )
select 1, 'class 1' from dual union all
select 2, 'class 2' from dual union all
select 3, 'class 3' from dual union all
select 4, 'class 4' from dual union all
select 5, 'class 5' from dual union all
select 6, 'class 6' from dual;

create table employees ( id, fname, lname, employeeclass )
select 5000, 'fname5000', 'lname5000', 1 from dual union
select 5001, 'fname5001', 'lname5000', 2 from dual union
select 5002, 'fname5002', 'lname5000', 1 from dual union
select 5003, 'fname5003', 'lname5000', 2 from dual union
select 5004, 'fname5004', 'lname5000', 6 from dual ;

alter table locations
add constraint pkey_locations primary key ( id ) ;

alter table employeeclasses 
add constraint pkey_eclasses primary key ( id );

alter table employees
add constraint pkey_employees primary key ( id ) ;

alter table employees
add constraint fkey_empclass 
foreign key ( employeeclass ) references employeeclasses ( id ) ;

When it comes to permissions, things get a bit more complicated :-). Apparently, you want to be able to assign access permissions to GROUPS (classes of employees). However, in the "access log" (your table3), you are recording events that are associated with SINGLE users. Thus, (just as an idea) we could create a PERMISSIONS table, where we record all permissions (per employee). You see that any of the valid combinations of EMPLOYEEID and LOCATIONID can be recorded in the access log table (code below), whose foreign key constraint references the permissions table.

create table permissions (
  employeeid number references employees( id )
, locationid number references locations( id )
, constraint pkey_permissions 
  primary key ( employeeid, locationid ) 

create table accesslog (
  id number generated always as identity start with 2000
, employeeid number
, locationid number
, access_time date default sysdate
, constraint fkey_accesslog 
  foreign key ( employeeid, locationid ) 
  references permissions ( employeeid, locationid ) 


-- Grant all members of class 1 access to locations 100 and 200.
insert into permissions ( employeeid, locationid )
select id, 100
from employees
where employeeclass in ( 1, 2 )
select id, 200
from employees
where employeeclass in ( 1, 2 ) ;

-- Employee 5000 accesses location 100 -> access granted, row inserted.
insert into accesslog ( employeeid, locationid )
values ( 5000, 100 ) ;
-- 1 row inserted.

-- Is employee 5000 allowed access to location 500? No.
insert into accesslog ( employeeid, locationid )
values ( 5000, 500 ) ;
ORA-02291: integrity constraint (...FKEY_ACCESSLOG) violated - parent key not found

-- Employees 5000, 5001, 5002, and 5003 want to access location 200.  
-- They have permission to do so.
  location constant number := 200 ; 
  for i in 5000 .. 5003 
    insert into accesslog ( employeeid, locationid )
    values ( i, location ) ;
  end loop;
-- PL/SQL procedure successfully completed.

Our "access log" now contains:

select id, employeeid, locationid
, to_char( access_time, 'YYYY-MM-DD HH24:MI:SS' )
from accesslog ;

---------- ---------- ---------- -------------------
      2000       5000        100 2018-10-08 04:46:35
      2002       5000        200 2018-10-08 04:50:20
      2003       5001        200 2018-10-08 04:50:20
      2004       5002        200 2018-10-08 04:50:20
      2005       5003        200 2018-10-08 04:50:20

Now - if you also want to record "access denied" events, and want to use GROUP/class level permissions, too, then we need triggers (frowned upon...) or procedures (the bee's knees...). The main reason for this being: CHECK constraints only work within a single table. Hence, we cannot check whether a particular employee belongs to a particular group AND whether this group is allowed to access a particular location (even in your original layout, the class data and employee data are located in 2 tables ...).

For recording all valid combinations of classes and locations, we should probably add another table (your original design will contain loads of NULLs in table1). Also, the accesslog gets an additional column, that allows us to record "access denied" events.

alter table accesslog 
add allowdeny varchar2(128) default 'access granted' ;

SQL> select * from accesslog;
2000  5000        100         08-OCT-18    access granted  
2002  5000        200         08-OCT-18    access granted  
2003  5001        200         08-OCT-18    access granted  
2004  5002        200         08-OCT-18    access granted  
2005  5003        200         08-OCT-18    access granted 

-- just an example!
create table locations_classes ( locationid, classid )
select 100, 1 from dual union all
select 200, 2 from dual union all
select 300, 3 from dual union all
select 400, 4 from dual ;

The procedure code should go into a package (which is omitted here, for clarity). The code given below is far from perfect, but it should give you something to work with - if you have done some PL/SQL before.

create or replace procedure getAccess (
  employeeid_ number
, locationid_ number
  employeeclass_ number := 0 ;
  classfound_ number := 0 ;
  select employeeclass into employeeclass_
  from employees where id = employeeid_ ;

  select count(*) into classfound_
  from locations_classes
  where rownum = 1 and locationid = locationid_ and classid = employeeclass_ ;   

  if classfound_ > 0 then
    insert into accesslog ( employeeid, locationid )
    values ( employeeid_, locationid_ ) ;
    insert into accesslog ( employeeid, locationid, allowdeny )
    values ( employeeid_, locationid_, 'ACCESS DENIED' ) ;
  end if;

When testing the procedure, you may find that some calls will cause an ORA-02991. You can disable the accesslog's FK constraint in order to allow the logging of "access denied" events.

-- parameters: employee id, location id
  getAccess( 5000, 100 ) ;
  getAccess( 5003, 100 ) ;
  getAccess( 5002, 100 ) ;
  getAccess( 5004, 300 ) ;
  commit ;
end ;

-- ORA-02291: integrity constraint (...FKEY_ACCESSLOG) violated - parent key not found

alter table accesslog disable constraint fkey_accesslog ; 

SQL> select * from accesslog ;
2024  5000        100         08-OCT-18    access granted  
2025  5003        100         08-OCT-18    ACCESS DENIED   
2026  5002        100         08-OCT-18    access granted  
2027  5004        300         08-OCT-18    ACCESS DENIED 

Sorry for the length of this answer! The gist is: do whatever you can with integrity constraints! Let the DDL code do the work. CHECK constraints do not work across tables. When you look at the (very crude) diagram, you will see that the LOCATIONS_CLASSES is not connected to the access log. The procedure "enforces a constraint" - but not really, as we want to record "access denied" as well.

If you want to use a procedure, you may not need the PERMISSIONS table. However, if you can get away WITHOUT writing a procedure, and only use integrity constraints, you'll get a more robust solution!

enter image description here

  • In order to keep failed access attempts in the ACCESSLOG table after a ROLLBACK, you have to use an AUTONOMOUS_TRANSACTION procedure. Oct 8, 2018 at 15:54
  • @stefan Great, great insights! Thank you. :) Oct 13, 2018 at 4:41
  • @SomendraChaudhary You are welcome!
    – stefan
    Oct 13, 2018 at 7:14


As @stefan suggested, your table1 should be split into a EMPLOYEECLASSES table, a LOCATIONS table and a linking table ( LOCATIONS_CLASSES ). (I believe this brings the design to a "4th Normal Form")

I'll be enhancing stefan's schema design.

Error Handling

To assist the application developers, you should use a PROCEDURE that "does nothing" if the employee has access and RAISE a specific error if the employee's access is denied. (raising general "FK Constraint" errors exposes the schema design to hackers.) This PROCEDURE should record an entry into ACCESSLOG irregardless of results.

The error is defined as part of a PACKAGE. In this example, the procedure will throw the application error ORA-20013 if access is denied.

create or replace
package security_api
   *  This package is used to verify employee's access to stuff
   * @headcom
  access_denied  exception;
  pragma exception_init( access_denied, -20013);

   * verifies employee's access to a specific location.
   * This throws an ACCESS_DENIED error ORA-20013 if DENIED or ERROR
   * @param  employeeid_  ID of an Employee
   * @param  locationid_  ID of a Location
   * @throws ACCESS_DENIED  This error is thrown if access id DENIED or there was an ERROR
  PROCEDURE getAccess (
                          employeeid_ IN employees.id%TYPE
                        , locationid_ IN locations.id%TYPE


Maintaining the PERMISSIONS table is probably a PITA. This table should actually be a VIEW. The VIEW makes is easier to implement in the PROCEDURE. (See the "Future Proofing" section)

create or replace
view permissions2
SELECT distinct
    e.id            AS employee_id
  , l.id            AS location_id
  locations_classes cl
  INNER JOIN employeeclasses c ON c.id = cl.classid
  INNER JOIN employees e ON e.employeeclass = c.id
  INNER JOIN locations l ON l.id = cl.locationid;

Yes, you can have constraints on a VIEW. (caveat emptor: I'm still evaluating their usefulness. )

alter view permissions2
  add constraint pkey_permissions2 primary key (employee_id,location_id)
    disable novalidate;

alter view permissions2
  add constraint fk1_permissions2 foreign key (employee_id) references employees (id)
   DISABLE novalidate;

alter view permissions2
  add constraint fk2_permissions2 foreign key (location_id) references locations (id)
   DISABLE novalidate;

Always Record a Value

As I stated in my comments to stefan, the procedure needs to be an AUTONOMOUS_TRANSACTION in order for the data in ACCESSLOG to stay after someone calls ROLLBACK from the calling session.

Beware: AUTONOMOUS_TRANSACTION procedures have been abused. I have seen them used, improperly, within a TRIGGER to get around Mutating Table errors. This is a "bad" usage of them.

One "good" purpose for using an AUTONOMOUS_TRANSACTION is for logging events such as "This person attempted to access that room". In other words, this is one of the few occasions were its usage is valid (and required).

create or replace
package body security_api

  PROCEDURE getAccess (
                          employeeid_ IN employees.id%TYPE
                        , locationid_ IN locations.id%TYPE
    buffer_        permissions2%rowtype;
    allowdeny_     accesslog.allowdeny%type := 'ERROR';

    pragma autonomous_transaction;
      -- do we have permission? if a row is found then "yes" else "no".
      select *
        into getAccess.buffer_
      from permissions2 p
      -- you can use the function/procedure name like a "table alias"
      where p.employee_id = getAccess.employeeid_
        and p.location_id = getAccess.locationid_;

      -- a row is found
      allowdeny_ := 'ALLOWED';
      when no_data_found then
        -- a row is not found
        allowdeny_ := 'DENIED';
      when too_many_rows then
        -- error in the data (this shouldn't happen)
        allowdeny_ := 'Bad Data';

    INSERT INTO accesslog ( employeeid, locationid, access_time, allowdeny )
        VALUES ( employeeid_, locationid_, systimestamp, allowdeny_  );


    if allowdeny_ = 'ALLOWED'
    end if;

    raise access_denied;


Future Proofing

Personally, I would record the current name (fname,lname) and the current location's description in ACCESSLOG also.

The four tables ( EMPLOYEES, EMPLOYEECLASSES, LOCATIONS, LOCATIONS_CLASSES ) will most likely need to be turned into a Type 2 SCD (handled by Oracle's Temporal Validity (12c+) feature) along with a "data change audit" (Type 4 SCD) handled by Oracle's Flashback Data Archive (now included in all editions as of

Since the getAccess() procedure is using a VIEW, you can update the procedure to use Temporal Validity by updating the VIEW with the appropriate AS OF PERIOD FOR clause.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.