How can I insert records in a table that are dependent on other tables?

Question

I have 3 tables:

table1:
Employee_class(PK), 
Executive_lounge, 
Executive_washroom, 
Supply_closet, 
Employee_lounge, 
Server_room, 
Server_closet

table2:
Emp_id(PK), 
LName, 
Years_of_service, 
Emp_Class (FK) ref Employee_class in table1

table3:
Transaction_id (PK), 
Emp_id (FK) ref Emp_id in table2, 
Access_time, 
Access_location, 
Allowed

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.

Answer 1

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 )
as
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 )
as
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 )
as
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 ) 
);

Testing

-- 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 )
union
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.
declare
  location constant number := 200 ; 
begin
  for i in 5000 .. 5003 
  loop
    insert into accesslog ( employeeid, locationid )
    values ( i, location ) ;
  end loop;
end;
/
-- 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 ;

        ID EMPLOYEEID LOCATIONID TO_CHAR(ACCESS_TIME
---------- ---------- ---------- -------------------
      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;
ID    EMPLOYEEID  LOCATIONID  ACCESS_TIME  ALLOWDENY       
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 )
as
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
)
is
  employeeclass_ number := 0 ;
  classfound_ number := 0 ;
begin
  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_ ) ;
  else
    insert into accesslog ( employeeid, locationid, allowdeny )
    values ( employeeid_, locationid_, 'ACCESS DENIED' ) ;
  end if;
end;
/

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
begin
  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 ;
ID    EMPLOYEEID  LOCATIONID  ACCESS_TIME  ALLOWDENY       
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!

Answer 2

Table1

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
as
  /**
   *  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
                      );
end;
/

The PERMISSIONS table

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
as
SELECT distinct
    e.id            AS employee_id
  , l.id            AS location_id
FROM
  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
as

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

    pragma autonomous_transaction;
  begin
    begin
      -- 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';
    exception
      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';
    end;

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

    commit;

    if allowdeny_ = 'ALLOWED'
    then
      return;
    end if;

    raise access_denied;
  end;

end;
/

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 11.2.0.4).

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.