Automating routine tasks in database administration can significantly improve efficiency. In Oracle Database, creating multiple users with similar patterns can be a time-consuming process. In this blog post, we’ll explore how to use PL/SQL to automate the creation of users with specific naming conventions and associated privileges.

Objective

Our goal is to create Oracle users with names following the pattern ‘DBADEEDS002’, ‘DBADEEDS003’, …, ‘DBADEEDS300’, and grant them the ‘ROLE’.

PL/SQL Script

SET SERVEROUTPUT ON
DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  FOR i IN 1..300 LOOP
    DECLARE
      v_username VARCHAR2(30);
      v_password VARCHAR2(30);
    BEGIN
      v_username := 'DBADEEDS'||LPAD(i, 3, '0')||'';
      v_password := 'DBADEEDS_' || LPAD(i, 3, '0');
      -- Create User
      v_sql := 'CREATE USER ' || v_username || ' IDENTIFIED BY "' || v_password || '" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK';
      EXECUTE IMMEDIATE v_sql;
      DBMS_OUTPUT.PUT_LINE('Executed SQL: ' || v_sql);
      -- Grant Role
      v_sql := 'GRANT ROLE TO ' || v_username;
      EXECUTE IMMEDIATE v_sql;
      DBMS_OUTPUT.PUT_LINE('Executed SQL: ' || v_sql);
    END;
  END LOOP;
END;
/

Execution

1. Open Oracle SQL*Plus or SQLcl.
2. Paste the script and execute.

This script efficiently creates users following a specific pattern and grants them the necessary role. It can be adapted for various scenarios where bulk user creation is required. Always ensure proper security measures and adhere to best practices when implementing automation scripts in a production environment.