DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01700 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01700: Duplicate Username in List — Causes, Fixes & Prevention

ORA-01700 is an Oracle error that occurs when the same username appears more than once in a GRANT statement's recipient list. Oracle does not allow granting the same privilege to a user multiple times within a single statement and throws this error immediately upon detection. This error is most common in large-scale permission management scripts or automated deployment pipelines.

Top 3 Causes

1. Manually Duplicated Username in GRANT Statement

The most straightforward cause — accidentally typing the same username twice in the target list.

-- Error: SCOTT appears twice
GRANT SELECT ON hr.employees TO scott, james, scott;
-- ORA-01700: duplicate username in list

-- Fix: Remove the duplicate
GRANT SELECT ON hr.employees TO scott, james;
Enter fullscreen mode Exit fullscreen mode

2. Duplicate Entries from Dynamic SQL or Automation Scripts

When user lists are built programmatically from multiple sources and merged without deduplication, duplicates can slip in unnoticed.

-- Problematic dynamic SQL
DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  -- user_a appears twice due to merged lists
  v_sql := 'GRANT SELECT ON hr.employees TO user_a, user_b, user_a';
  EXECUTE IMMEDIATE v_sql;
  -- ORA-01700: duplicate username in list
END;
/

-- Safe fix: Grant individually with deduplication
DECLARE
  TYPE t_users IS TABLE OF VARCHAR2(30);
  v_users t_users := t_users('USER_A', 'USER_B', 'USER_A');
BEGIN
  FOR i IN 1 .. v_users.COUNT LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'GRANT SELECT ON hr.employees TO ' || v_users(i);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Skipped ' || v_users(i) || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Mixed Case Username Treated as Duplicate

Oracle treats unquoted usernames as case-insensitive. Writing Scott and SCOTT in the same GRANT list causes ORA-01700 because Oracle normalizes both to SCOTT.

-- Error: Scott and SCOTT are the same user
GRANT SELECT ON hr.departments TO Scott, james, SCOTT;
-- ORA-01700: duplicate username in list

-- Fix: Use consistent uppercase
GRANT SELECT ON hr.departments TO SCOTT, JAMES;

-- Verify exact username from dictionary
SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE '%SCOTT%';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Always audit existing privileges before running new GRANT statements to avoid both duplicates and redundant grants.

-- Check who already has privileges on a specific object
SELECT GRANTEE, PRIVILEGE, GRANT_OPTION
FROM   DBA_TAB_PRIVS
WHERE  TABLE_NAME = 'EMPLOYEES'
AND    OWNER      = 'HR'
ORDER BY GRANTEE;

-- Pre-check your user list for duplicates before granting
WITH target_users AS (
  SELECT 'SCOTT' AS username FROM DUAL UNION ALL
  SELECT 'JAMES' AS username FROM DUAL UNION ALL
  SELECT 'SCOTT' AS username FROM DUAL
)
SELECT username, COUNT(*) AS occurrences
FROM   target_users
GROUP  BY username
HAVING COUNT(*) > 1;
-- If this returns rows, fix your list before running GRANT
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always use individual GRANT statements in scripts.
Granting one user at a time eliminates the risk of list duplication entirely and makes audit logs cleaner.

GRANT SELECT ON hr.employees TO scott;
GRANT SELECT ON hr.employees TO james;
GRANT SELECT ON hr.employees TO adams;
Enter fullscreen mode Exit fullscreen mode

2. Add a deduplication check before any bulk GRANT operation.
In automated pipelines, normalize all usernames to uppercase and run a duplicate check query before executing any GRANT statement. Treat a non-empty result from a HAVING COUNT(*) > 1 check as a hard blocker that prevents the script from proceeding.

Related Errors

  • ORA-01917 — User or role does not exist; often encountered alongside ORA-01700 when GRANT target lists contain errors.
  • ORA-01919 — Role does not exist; similar context involving invalid GRANT targets.
  • ORA-00990 — Missing or invalid privilege; occurs when an invalid privilege keyword is used in a GRANT statement.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)