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.
Best Answer
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.
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.