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.
For more information about functions that can be used in DDL triggers, PSOUG has a good listing.
Have you enjoyed? Please leave a comment or give a 👍!