如何在Oracle SQL Developer中编写脚本查询LDAP用户ID并存储到外部Oracle表？

Got it, let's walk through how to build this solution step by step—since you're working in Oracle SQL Developer and need to pull LDAP user IDs into an external table first, here's a practical approach aligned with @Connor's suggestion:

1. Create an External Table to Store LDAP User IDs #

First, you'll need an external table (or even a regular table if you prefer) to hold the list of active user IDs from LDAP. Let's go with an external table for flexibility:

CREATE TABLE ldap_active_users (
    user_id VARCHAR2(100) NOT NULL
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY temp_dir -- Ensure this directory exists and you have read/write access
    LOCATION ('ldap_users.txt')
)
REJECT LIMIT UNLIMITED;

Note: If you don't have a directory set up, create one first with CREATE DIRECTORY temp_dir AS '/path/to/your/directory'; and grant permissions with GRANT READ, WRITE ON DIRECTORY temp_dir TO your_user;.

2. Grant Required Permissions for LDAP Access #

Oracle uses the DBMS_LDAP package to interact with LDAP servers. Make sure your database user has execute permissions:

GRANT EXECUTE ON DBMS_LDAP TO your_database_user;

3. Write the PL/SQL Script to Fetch LDAP Users #

This script will connect to your LDAP server, search for all active users, and insert their IDs into the external table. Adjust the variables (LDAP server, port, bind DN, etc.) to match your environment:

DECLARE
    l_ldap_conn DBMS_LDAP.session;
    l_host VARCHAR2(100) := 'myldap.server.com';
    l_port PLS_INTEGER := 389; -- Use 636 for LDAPS (SSL)
    l_bind_dn VARCHAR2(200) := 'cn=admin,dc=example,dc=com'; -- Your LDAP bind user DN
    l_bind_password VARCHAR2(100) := 'your_bind_password';
    l_search_base VARCHAR2(200) := 'ou=users,dc=example,dc=com'; -- Base DN for user search
    l_filter VARCHAR2(200) := '(&(objectClass=inetOrgPerson)(uid=*))'; -- Adjust for your LDAP schema
    l_attrs DBMS_LDAP.string_collection;
    l_msg DBMS_LDAP.message;
    l_entry DBMS_LDAP.message;
    l_attr_name VARCHAR2(100);
    l_attr_vals DBMS_LDAP.string_collection;
    l_user_id VARCHAR2(100);
BEGIN
    -- Initialize LDAP connection
    l_ldap_conn := DBMS_LDAP.init(l_host, l_port);
    -- Bind to LDAP server
    DBMS_LDAP.simple_bind_s(l_ldap_conn, l_bind_dn, l_bind_password);
    
    -- Set attributes to retrieve (we only need the user ID, e.g., uid)
    l_attrs(1) := 'uid';
    
    -- Search LDAP for users
    l_msg := DBMS_LDAP.search_s(
        ld => l_ldap_conn,
        base => l_search_base,
        scope => DBMS_LDAP.SCOPE_SUBTREE,
        filter => l_filter,
        attrs => l_attrs,
        attronly => 0
    );
    
    -- Process search results
    l_entry := DBMS_LDAP.first_entry(l_ldap_conn, l_msg);
    WHILE l_entry IS NOT NULL LOOP
        -- Extract the user ID value
        l_attr_name := DBMS_LDAP.first_attribute(l_ldap_conn, l_entry, l_attr_vals);
        IF l_attr_name = 'uid' THEN
            l_attr_vals := DBMS_LDAP.get_values(l_ldap_conn, l_entry, l_attr_name);
            IF l_attr_vals.COUNT > 0 THEN
                l_user_id := l_attr_vals(1);
                -- Insert into external table
                INSERT INTO ldap_active_users VALUES (l_user_id);
            END IF;
        END IF;
        l_entry := DBMS_LDAP.next_entry(l_ldap_conn, l_entry);
    END LOOP;
    
    -- Commit the inserts
    COMMIT;
    
    -- Unbind and close connection
    DBMS_LDAP.unbind_s(l_ldap_conn);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        IF DBMS_LDAP.is_open(l_ldap_conn) THEN
            DBMS_LDAP.unbind_s(l_ldap_conn);
        END IF;
        RAISE;
END;
/

Key adjustments to make:

• Swap l_filter to match your LDAP schema (e.g., use sAMAccountName instead of uid for Active Directory)
• Update l_search_base to point to the correct OU where your users are stored
• For LDAPS (SSL), use port 636 and ensure your Oracle server trusts the LDAP server's certificate

4. Identify & Disable Stale Database Users #

Once you have the list of active LDAP users, compare it against your database user table to find accounts that no longer exist in LDAP. For example, if your app users are stored in app_users:

-- First, verify stale users
SELECT user_id
FROM app_users
WHERE user_id NOT IN (SELECT user_id FROM ldap_active_users);

-- Then disable them (adjust to match your table's active flag)
UPDATE app_users
SET is_active = 'N', last_disabled_date = SYSDATE
WHERE user_id NOT IN (SELECT user_id FROM ldap_active_users);

COMMIT;

If managing Oracle database accounts directly, use:

-- Lock stale database accounts
FOR rec IN (
    SELECT username
    FROM dba_users
    WHERE username NOT IN (SELECT user_id FROM ldap_active_users)
      AND account_status = 'OPEN'
) LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || rec.username || ' ACCOUNT LOCK';
END LOOP;

内容的提问来源于stack exchange，提问作者Dimitris Baltas