Restricting oracle Username using Trigger

This post is also available in: Português

One of the important tasks that the DBA has is to keep organized and controlled the schemas that exist in the database. Some companies have a standard nomenclature for its employees and service providers usernames and to avoid the creation of wrong logins, you can make a process to force an username pattern.

To accomplish this, the DBA can create a system trigger that will check if the username follows the naming rule. See the Example:

CREATE OR REPLACE TRIGGER TRG_FILTRA_USER
  BEFORE CREATE ON DATABASE
BEGIN
  IF ORA_DICT_OBJ_TYPE = 'USER' AND ORA_LOGIN_USER <> 'SYS'
  THEN
    IF NOT REGEXP_LIKE(ORA_DICT_OBJ_NAME, '^(T|F)\d{7}?$')
    THEN
      RAISE_APPLICATION_ERROR(-20999, 'User must be on "FNNNNNNN" or "TNNNNNNN" format.');
    END IF;
  END IF;
END TRG_FILTRA_USER;
/

In this scenario, we are creating a trigger that all logged users (except SYS) can only create usernames starting with “T” or “F” followed by 7 digits.

It is always triggered before a “create” command, but in conjunction with the filter “ora_dict_obj_type” we restrict to the specific creation of users. There are other variables that we can use inside a DDL trigger to further reduce the scope of execution, such as:

ora_sysevent – The system event that was fired. In the example, the value is “CREATE”. Is usually what comes after the “AFTER” or “BEFORE” clause at the beginning of the trigger.
ora_login_user – The logged-in user who executed the command.
ora_instance_num – In case of a RAC environment, the number of the instance where the command is being executed.
ora_database_name – The name says.
ora_dict_obj_type – The type of object that is being changed by the system event. May be, for example, a table, role, procedure or, as above, a user.
ora_dict_obj_name – The name of the object being changed by the DDL.
ora_dict_obj_owner – The owner of the object being changed by the DDL. It does not apply for “ora_sysevent=USER“, as the example trigger event demonstrated above.