i have to create a trigger after logon which checks authentication but based on certain conditions.

For this i have two tables:

1> Client tables: having only one column Username

so i suppose the usernames are abc bcd cde

2> Machine table: having one column machine name

it has application names like visiual studio power-bi This specifies the application used to login to the database


My need is that each time a user tries to access this database it should check if the username belongs to the client table and that the machine used is the one from machine table.

-->If the username is correct but not the machine then there should be an error.
-->If Machine is correct but wrong username then normal database check should take place as user maybe connecting to other database.

So can i have a conditional check at the beginning: something like this?


CREATE OR REPLACE TRIGGER user_logon_track
AFTER LOGON ON database
when (username= select username from client table)
DECLARE

Or is there any other method to solve this issue?

SQL> create or replace
  2  trigger check_all_logins
  3  after logon on database
  4  declare
  5    x int;
  6  begin
  7    select count(*)
  8    into   x
  9    from   clients
 10    where  username = user;
 11
 12    select count(*)
 13    into   x
 14    from   machines
 15    where  machine = sys_context('userenv','TERMINAL');
 16  end;