Oracle 12c中如何复制/克隆用户？含权限、角色及Mandant的PL/SQL实现

Got it, let's build a practical PL/SQL procedure that handles copying all roles, system/object permissions, and the Mandant (I'm assuming this refers to a tenant or organizational unit tied to your user) from a source account to a target user in Oracle 12c. This uses dynamic SQL to generate and run the necessary GRANT statements, plus handles Mandant associations (adjust that section if your Mandant setup is custom).

Prerequisites #

• You need a user with sufficient privileges: CREATE PROCEDURE, SELECT_CATALOG_ROLE, and the ability to grant roles/permissions to the target user.
• The target user should already exist (you can add a user creation step if needed, but I'll assume you've already set up the target account).

PL/SQL Procedure #

CREATE OR REPLACE PROCEDURE copy_user_privs_and_mandant(
    p_source_user IN VARCHAR2,
    p_target_user IN VARCHAR2
) IS
    v_sql VARCHAR2(1000);
    v_mandant_id NUMBER; -- Swap to VARCHAR2 if your Mandant uses string IDs
BEGIN
    -- First, validate both users exist
    BEGIN
        SELECT username INTO v_sql FROM dba_users WHERE username = UPPER(p_source_user);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'Source user ' || p_source_user || ' doesn''t exist.');
    END;

    BEGIN
        SELECT username INTO v_sql FROM dba_users WHERE username = UPPER(p_target_user);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20002, 'Target user ' || p_target_user || ' doesn''t exist.');
    END;

    -- 1. Copy granted roles (including admin option)
    DBMS_OUTPUT.PUT_LINE('Copying roles from ' || p_source_user || ' to ' || p_target_user || '...');
    FOR rec IN (
        SELECT granted_role, admin_option
        FROM dba_role_privs
        WHERE grantee = UPPER(p_source_user)
    ) LOOP
        v_sql := 'GRANT ' || rec.granted_role || ' TO ' || UPPER(p_target_user);
        IF rec.admin_option = 'YES' THEN
            v_sql := v_sql || ' WITH ADMIN OPTION';
        END IF;
        EXECUTE IMMEDIATE v_sql;
        DBMS_OUTPUT.PUT_LINE('Ran: ' || v_sql);
    END LOOP;

    -- 2. Copy system privileges (including admin option)
    DBMS_OUTPUT.PUT_LINE('Copying system privileges...');
    FOR rec IN (
        SELECT privilege, admin_option
        FROM dba_sys_privs
        WHERE grantee = UPPER(p_source_user)
    ) LOOP
        v_sql := 'GRANT ' || rec.privilege || ' TO ' || UPPER(p_target_user);
        IF rec.admin_option = 'YES' THEN
            v_sql := v_sql || ' WITH ADMIN OPTION';
        END IF;
        EXECUTE IMMEDIATE v_sql;
        DBMS_OUTPUT.PUT_LINE('Ran: ' || v_sql);
    END LOOP;

    -- 3. Copy object privileges (including grantable option)
    DBMS_OUTPUT.PUT_LINE('Copying object privileges...');
    FOR rec IN (
        SELECT owner, table_name, privilege, grantable
        FROM dba_tab_privs
        WHERE grantee = UPPER(p_source_user)
    ) LOOP
        v_sql := 'GRANT ' || rec.privilege || ' ON ' || rec.owner || '.' || rec.table_name || ' TO ' || UPPER(p_target_user);
        IF rec.grantable = 'YES' THEN
            v_sql := v_sql || ' WITH GRANT OPTION';
        END IF;
        EXECUTE IMMEDIATE v_sql;
        DBMS_OUTPUT.PUT_LINE('Ran: ' || v_sql);
    END LOOP;

    -- 4. Copy Mandant association (adjust this section to match your actual Mandant setup)
    -- I'm assuming Mandant is stored in a custom table MANDANT_USERS with USERNAME and MANDANT_ID columns
    DBMS_OUTPUT.PUT_LINE('Copying Mandant association...');
    BEGIN
        SELECT mandant_id INTO v_mandant_id
        FROM mandant_users
        WHERE username = UPPER(p_source_user);

        -- Check if target user already has a Mandant entry
        BEGIN
            SELECT 1 INTO v_sql FROM mandant_users WHERE username = UPPER(p_target_user);
            RAISE_APPLICATION_ERROR(-20003, 'Target user ' || p_target_user || ' already has a Mandant association.');
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                v_sql := 'INSERT INTO mandant_users (username, mandant_id) VALUES (''' || UPPER(p_target_user) || ''', ' || v_mandant_id || ')';
                EXECUTE IMMEDIATE v_sql;
                COMMIT; -- Remove this line if you want to handle transactions outside the procedure
                DBMS_OUTPUT.PUT_LINE('Ran: ' || v_sql);
        END;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_WARNING('No Mandant association found for source user ' || p_source_user);
    END;

    DBMS_OUTPUT.PUT_LINE('All privileges, roles, and Mandant copied successfully!');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
        RAISE;
END copy_user_privs_and_mandant;
/

How to Use #

1. Compile the procedure as a privileged user (like SYS or a user with the required permissions).
2. Execute it with:

SET SERVEROUTPUT ON;
EXEC copy_user_privs_and_mandant('SOURCE_USER', 'TARGET_USER');

Key Notes #

• Mandant Customization: If your Mandant isn't stored in a custom table (e.g., it's a multi-tenant CDB/PDB property), tweak the Mandant section. For example, if the source user lives in a specific PDB, ensure the target user is created in the same PDB or adjust the context.
• Error Handling: The procedure includes basic validation, but you can expand it to skip duplicate grants or handle edge cases specific to your environment.
• Case Sensitivity: Oracle usernames default to uppercase, so the procedure converts inputs to avoid mismatches.

内容的提问来源于stack exchange，提问作者Sangathamilan Ravichandran