{"id":865,"date":"2014-10-01T13:01:43","date_gmt":"2014-10-01T16:01:43","guid":{"rendered":"http:\/\/www.dbarj.com.br\/?p=865"},"modified":"2014-10-01T13:01:43","modified_gmt":"2014-10-01T16:01:43","slug":"restricting-oracle-username-pattern-using-trigger","status":"publish","type":"post","link":"https:\/\/www.dbarj.com.br\/en\/2014\/10\/restricting-oracle-username-pattern-using-trigger\/","title":{"rendered":"Restricting oracle Username using Trigger"},"content":{"rendered":"

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.<\/p>\n

To accomplish this, the DBA can create a system trigger that will check if the username follows the naming rule. See the Example:<\/p>\n

CREATE OR REPLACE TRIGGER TRG_FILTRA_USER\r\n  BEFORE CREATE ON DATABASE\r\nBEGIN\r\n  IF ORA_DICT_OBJ_TYPE = 'USER' AND ORA_LOGIN_USER <> 'SYS'\r\n  THEN\r\n    IF NOT REGEXP_LIKE(ORA_DICT_OBJ_NAME, '^(T|F)\\d{7}?$')\r\n    THEN\r\n      RAISE_APPLICATION_ERROR(-20999, 'User must be on \"FNNNNNNN\" or \"TNNNNNNN\" format.');\r\n    END IF;\r\n  END IF;\r\nEND TRG_FILTRA_USER;\r\n\/<\/pre>\n
In this scenario, we are creating a trigger that all logged users (except SYS) can only create usernames starting with “T<\/strong>” or “F<\/strong>” followed by 7 digits<\/strong>.<\/p>\n
It is always triggered before a “create<\/strong>” 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:<\/p>\n