Otdfbt

I have this logon trigger to only allow certain users to log in to an Oracle database (even if they have the correct password to enter the database):

CREATE OR REPLACE TRIGGER SYS.LOGON_TRIGGER
 AFTER LOGON ON DATABASE
DECLARE
 THIS_USER VARCHAR2(50);
BEGIN
 SELECT OSUSER INTO THIS_USER FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV','SID');
 IF THIS_USER NOT IN (<List of Users>)
 THEN RAISE LOGIN_DENIED;
 ENDIF;
END;
/

It works for preventing users from entering most schemas but not all (e.g. the SYS or SYSTEM schemas can still be entered regardless of the user - this logon trigger is seemingly completely bypassed).

Is there a way to lock out these users even for these SYS type schemas?

A bit of context:

Due to decisions made way before I got involved with this, all of the logins for this database have the same password. Additionally, most users use the same login as many of our processes that read/write to this database automatically.

We don't want to simply change the passwords because it would be a very large effort to see what impact changing these passwords actually does to the system. (We would have to modify the code that the processes use to access the database, and there are many of these.) An easier solution for us is to just lock out based on usernames, if possible.

I suggest a multi-phase approach that can be implemented in stages and will minimize the impact of changing to a more secure approach. I assume that you have a development environment to test in and the support of a manager who is interested and will support the effort.

• use the existing Oracle audit logging to start logging when users logon and logoff.


• after a period of time consistent with usage (90 days for a fiscal quarter?, a year end?) identify the unused accounts and lock them


• identify any service accounts that are not used by people to log on.


• identify the remaining accounts and try to link usernames to people to job roles


• create Oracle profiles for service accounts, read only accounts and more privileged user accounts
  
  
  • set password expiration, complexity, reuse, failed attempts before lockout for these profiles. For example you may decide that service accounts should never change their password but that it should be 24 characters and only one failed attempt before lockout whereas a person's password should only be 8 characters with three failed attempts before lockout.
  
  
  • one by one reassign accounts to the correct profile and force a password change
  
  


• at the same time look at creating roles that grant only enough privileges for accounts to do their job and assign the roles.

This is just the tip of the iceberg for securing the database. The level of effort you put in should be commensurate with the potential damage if the information in the database were breached.

The reason why your trigger does not work for users like SYS or SYSTEM is because they have the ADMINISTER DATABASE TRIGGER privilege.

The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe.

So, the answer is: no, you cannot prevent login for such users - at least not with a login trigger.