user_management.sql
-- ------------------------------------------------------------------------------------- File Name : user_management.sql-- Author : Pierre Montbleau-- Description : Oracle script for creating and managing database users with roles and privileges-- Purpose : Standardize user creation and permission assignment in Oracle databases-- Call Syntax : @F:\DBA\Scripts\user_management.sql (username) (tablespace_name) (temp_tablespace_name)-- Parameters : username - Name of the user to create-- tablespace_name - Default tablespace for the user-- temp_tablespace_name - Temporary tablespace for the user-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- SETUP: Initialize environment and verify parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_user_management_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting User Management Script...PROMPT =================================
-- Check if required parameters are providedWHENEVER SQLERROR EXIT FAILURE
-- Display parameter valuesPROMPT Parameters Received:PROMPT Username: &1PROMPT Tablespace: &2PROMPT Temp Tablespace: &3PROMPT
-- ------------------------------------------------------------------------------------- VALIDATION: Verify tablespace existence and parameter validity-- -----------------------------------------------------------------------------------
PROMPT STEP 1: Validating parameters and environment...PROMPT
-- Verify tablespace existsDECLARE v_tablespace_exists NUMBER; v_temp_tablespace_exists NUMBER;BEGIN -- Check default tablespace SELECT COUNT(*) INTO v_tablespace_exists FROM dba_tablespaces WHERE tablespace_name = UPPER('&2'); IF v_tablespace_exists = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Tablespace ' || UPPER('&2') || ' does not exist'); END IF; -- Check temporary tablespace SELECT COUNT(*) INTO v_temp_tablespace_exists FROM dba_tablespaces WHERE tablespace_name = UPPER('&3'); IF v_temp_tablespace_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Temporary tablespace ' || UPPER('&3') || ' does not exist'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- USER CREATION: Create the database user with specified parameters-- -----------------------------------------------------------------------------------
PROMPT STEP 2: Creating user &1...PROMPT
DECLARE v_user_exists NUMBER;BEGIN -- Check if user already exists SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = UPPER('&1'); IF v_user_exists = 0 THEN -- Create user if doesn't exist EXECUTE IMMEDIATE 'CREATE USER &1 IDENTIFIED BY "TempPassword123" DEFAULT TABLESPACE &2 TEMPORARY TABLESPACE &3 QUOTA UNLIMITED ON &2'; DBMS_OUTPUT.PUT_LINE('User &1 created successfully'); ELSE DBMS_OUTPUT.PUT_LINE('User &1 already exists - skipping creation'); END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error creating user: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- PRIVILEGE ASSIGNMENT: Grant basic privileges and roles-- -----------------------------------------------------------------------------------
PROMPT STEP 3: Assigning privileges and roles...PROMPT
DECLARE v_username VARCHAR2(30) := UPPER('&1'); v_user_exists NUMBER;BEGIN -- Check if username is provided IF v_username IS NULL OR v_username = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Username parameter is required'); END IF; -- Check if user exists in the database SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = v_username; -- If user doesn't exist, raise an error IF v_user_exists = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'User ' || v_username || ' does not exist in the database'); END IF; DBMS_OUTPUT.PUT_LINE('User ' || v_username || ' found. Granting privileges...'); -- Grant basic session privilege EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || v_username; -- Grant resource role for basic database operations EXECUTE IMMEDIATE 'GRANT RESOURCE TO ' || v_username; -- Grant additional commonly needed privileges EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO ' || v_username; EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO ' || v_username; EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO ' || v_username; EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO ' || v_username; DBMS_OUTPUT.PUT_LINE('Basic privileges granted successfully to ' || v_username); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- VERIFICATION: Confirm user creation and privileges-- -----------------------------------------------------------------------------------
PROMPT STEP 4: Verifying user creation and privileges...PROMPT
COLUMN username FORMAT A20COLUMN default_tablespace FORMAT A20COLUMN temporary_tablespace FORMAT A20COLUMN account_status FORMAT A15
SELECT username, default_tablespace, temporary_tablespace, account_status, createdFROM dba_users WHERE username = UPPER('&1');
PROMPT PROMPT User privileges:PROMPT
COLUMN privilege FORMAT A30COLUMN admin_option FORMAT A5
SELECT privilege, admin_option FROM dba_sys_privs WHERE grantee = UPPER('&1')UNIONSELECT granted_role || ' (ROLE)' as privilege, admin_optionFROM dba_role_privs WHERE grantee = UPPER('&1')ORDER BY 1;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion message-- -----------------------------------------------------------------------------------
PROMPT PROMPT =================================PROMPT User Management Script CompletedPROMPT =================================PROMPT Note: Remember to change the default password for user &1PROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- SETUP: Initialize environment and verify parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_user_management_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting User Management Script...PROMPT =================================
-- Check if required parameters are providedWHENEVER SQLERROR EXIT FAILURE
-- Display parameter valuesPROMPT Parameters Received:PROMPT Username: &1PROMPT Tablespace: &2PROMPT Temp Tablespace: &3PROMPT
-- ------------------------------------------------------------------------------------- VALIDATION: Verify tablespace existence and parameter validity-- -----------------------------------------------------------------------------------
PROMPT STEP 1: Validating parameters and environment...PROMPT
-- Verify tablespace existsDECLARE v_tablespace_exists NUMBER; v_temp_tablespace_exists NUMBER;BEGIN -- Check default tablespace SELECT COUNT(*) INTO v_tablespace_exists FROM dba_tablespaces WHERE tablespace_name = UPPER('&2'); IF v_tablespace_exists = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Tablespace ' || UPPER('&2') || ' does not exist'); END IF; -- Check temporary tablespace SELECT COUNT(*) INTO v_temp_tablespace_exists FROM dba_tablespaces WHERE tablespace_name = UPPER('&3'); IF v_temp_tablespace_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Temporary tablespace ' || UPPER('&3') || ' does not exist'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- USER CREATION: Create the database user with specified parameters-- -----------------------------------------------------------------------------------
PROMPT STEP 2: Creating user &1...PROMPT
DECLARE v_user_exists NUMBER;BEGIN -- Check if user already exists SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = UPPER('&1'); IF v_user_exists = 0 THEN -- Create user if doesn't exist EXECUTE IMMEDIATE 'CREATE USER &1 IDENTIFIED BY "TempPassword123" DEFAULT TABLESPACE &2 TEMPORARY TABLESPACE &3 QUOTA UNLIMITED ON &2'; DBMS_OUTPUT.PUT_LINE('User &1 created successfully'); ELSE DBMS_OUTPUT.PUT_LINE('User &1 already exists - skipping creation'); END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error creating user: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- PRIVILEGE ASSIGNMENT: Grant basic privileges and roles-- -----------------------------------------------------------------------------------
PROMPT STEP 3: Assigning privileges and roles...PROMPT
DECLARE v_username VARCHAR2(30) := UPPER('&1'); v_user_exists NUMBER;BEGIN -- Check if username is provided IF v_username IS NULL OR v_username = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Username parameter is required'); END IF; -- Check if user exists in the database SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = v_username; -- If user doesn't exist, raise an error IF v_user_exists = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'User ' || v_username || ' does not exist in the database'); END IF; DBMS_OUTPUT.PUT_LINE('User ' || v_username || ' found. Granting privileges...'); -- Grant basic session privilege EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || v_username; -- Grant resource role for basic database operations EXECUTE IMMEDIATE 'GRANT RESOURCE TO ' || v_username; -- Grant additional commonly needed privileges EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO ' || v_username; EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO ' || v_username; EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO ' || v_username; EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO ' || v_username; DBMS_OUTPUT.PUT_LINE('Basic privileges granted successfully to ' || v_username); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- VERIFICATION: Confirm user creation and privileges-- -----------------------------------------------------------------------------------
PROMPT STEP 4: Verifying user creation and privileges...PROMPT
COLUMN username FORMAT A20COLUMN default_tablespace FORMAT A20COLUMN temporary_tablespace FORMAT A20COLUMN account_status FORMAT A15
SELECT username, default_tablespace, temporary_tablespace, account_status, createdFROM dba_users WHERE username = UPPER('&1');
PROMPT PROMPT User privileges:PROMPT
COLUMN privilege FORMAT A30COLUMN admin_option FORMAT A5
SELECT privilege, admin_option FROM dba_sys_privs WHERE grantee = UPPER('&1')UNIONSELECT granted_role || ' (ROLE)' as privilege, admin_optionFROM dba_role_privs WHERE grantee = UPPER('&1')ORDER BY 1;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion message-- -----------------------------------------------------------------------------------
PROMPT PROMPT =================================PROMPT User Management Script CompletedPROMPT =================================PROMPT Note: Remember to change the default password for user &1PROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------