오라클에서 윈도우 로그인 시 사용자 이름 알아내는 방법

오라클 데이터베이스에서 사용자가 윈도우 인증(Windows Authentication)을 통해 로그인했을 때, 접속한 윈도우 사용자 이름을 알아내는 방법은 여러 가지가 있습니다. 이는 보안 관리, 감사 로그 작성, 또는 사용자별 맞춤 처리를 위해 유용합니다. 오라클은 윈도우와의 통합 인증을 지원하며, 이를 통해 네트워크 사용자 정보를 가져올 수 있습니다. 이 글에서는 주로 SYS_CONTEXT 함수와 관련 뷰를 활용하는 방법을 중심으로, 단계별로 자세히 설명하고 예제를 포함하겠습니다.

---

1. 윈도우 인증 설정 확인

먼저, 오라클 데이터베이스가 윈도우 인증을 지원하도록 설정되어 있어야 합니다. 이는 일반적으로 Externally Authenticated Users 또는 OS Authentication을 통해 구현됩니다. 이를 확인하려면 다음 단계를 따릅니다:

• sqlnet.ora 파일 설정: 오라클 네트워크 설정 파일(sqlnet.ora)에 다음이 포함되어야 합니다:
   

  SQLNET.AUTHENTICATION_SERVICES = (NTS)

  여기서 NTS는 Windows Native Authentication을 의미합니다.
• 사용자 생성: 데이터베이스에서 윈도우 사용자를 외부 인증 사용자로 생성해야 합니다. 예를 들어, 도메인 이름이 MYDOMAIN이고 사용자 이름이 john.doe라면:

  CREATE USER "MYDOMAIN\JOHN.DOE" IDENTIFIED EXTERNALLY;
  GRANT CONNECT TO "MYDOMAIN\JOHN.DOE";

• 접속 테스트: 윈도우 환경에서 SQL*Plus나 SQL Developer로 로그인 시 사용자 이름/비밀번호 입력 없이 접속이 되는지 확인합니다:

  sqlplus /

---

2. SYS_CONTEXT로 사용자 이름 가져오기

가장 간단하고 일반적인 방법은 SYS_CONTEXT 함수를 사용하는 것입니다. SYS_CONTEXT는 현재 세션의 컨텍스트 정보를 반환하며, 윈도우 인증 사용자의 경우 USERENV 네임스페이스를 통해 사용자 정보를 얻을 수 있습니다.

기본 예제

sql

 

SELECT SYS_CONTEXT('USERENV', 'OS_USER') AS os_user FROM DUAL;

• 설명: OS_USER는 운영 체제에서 인증된 사용자 이름을 반환합니다. 윈도우 환경에서는 도메인\사용자명 형식(예: MYDOMAIN\JOHN.DOE)으로 출력됩니다.
• 출력 예시: ``` OS_USER
  MYDOMAIN\JOHN.DOE
   

세션 정보 확인

더 많은 정보를 얻고 싶다면, USERENV 네임스페이스의 다른 파라미터도 활용할 수 있습니다:

SELECT 
    SYS_CONTEXT('USERENV', 'OS_USER') AS os_user,
    SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
    SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') AS auth_type
FROM DUAL;

• 출력 예시:
   

  OS_USER           SESSION_USER      AUTH_TYPE
  ----------------- ----------------- ------------
  MYDOMAIN\JOHN.DOE MYDOMAIN\JOHN.DOE EXTERNAL

  • SESSION_USER: 데이터베이스 내 사용자 이름.
  • AUTHENTICATION_TYPE: 인증 방식 (EXTERNAL은 OS 인증을 의미).

---

 

 

3. V$SESSION 뷰 활용

V$SESSION 뷰는 현재 데이터베이스 세션의 상세 정보를 제공합니다. 관리자 권한이 있는 경우 이를 통해 접속한 윈도우 사용자 이름을 확인할 수 있습니다.

예제

SELECT 
    osuser,
    username,
    machine,
    program
FROM V$SESSION
WHERE sid = SYS_CONTEXT('USERENV', 'SID');

• 설명:
  • osuser: 운영 체제 사용자 이름 (예: MYDOMAIN\JOHN.DOE).
  • username: 데이터베이스 사용자 이름.
  • machine: 접속한 클라이언트 머신 이름.
  • program: 접속에 사용된 프로그램 (예: sqlplus.exe).
• 출력 예시:
   

  OSUSER            USERNAME          MACHINE       PROGRAM
  ----------------- ----------------- ------------- ------------
  MYDOMAIN\JOHN.DOE MYDOMAIN\JOHN.DOE MYPC         sqlplus.exe

모든 세션 확인 (관리자용)

SELECT osuser, username, machine, program
FROM V$SESSION
WHERE osuser IS NOT NULL;

---

4. PL/SQL에서 활용

윈도우 사용자 이름을 프로시저나 트리거에서 사용하려면 PL/SQL로 작성할 수 있습니다.

예제: 로그 기록 프로시저

CREATE OR REPLACE PROCEDURE log_user_action(p_message IN VARCHAR2)
AS
    l_os_user VARCHAR2(100);
BEGIN
    l_os_user := SYS_CONTEXT('USERENV', 'OS_USER');
    INSERT INTO debug_log (procedure_name, line_number, message)
    VALUES ('LOG_USER_ACTION', $$PLSQL_LINE, 'User ' || l_os_user || ': ' || p_message);
    COMMIT;
END;
/

-- 테스트
BEGIN
    log_user_action('Performed an action');
END;
/

SELECT * FROM debug_log;

출력 예시:

 

LOG_ID  LOG_TIME            PROCEDURE_NAME    LINE_NUMBER  MESSAGE
1       2025-03-17 10:00:00 LOG_USER_ACTION   5            User MYDOMAIN\JOHN.DOE: Performed an action

---

728x90

5. 고급 활용: NTILE 또는 Kerberos 인증

• NTILE: 일부 환경에서 SQLNET.AUTHENTICATION_SERVICES가 KERBEROS로 설정된 경우, 사용자 이름이 다르게 나타날 수 있습니다. 이 경우 SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL')을 확인해 네트워크 프로토콜을 점검하세요.
• Kerberos: Kerberos 티켓을 사용하는 경우, SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY')로 인증된 ID를 얻을 수 있습니다:

  SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') AS kerberos_user FROM DUAL;

---

6. 문제 해결 팁

• OS_USER가 null: 윈도우 인증이 제대로 설정되지 않았거나, sqlnet.ora의 NTS 설정이 누락된 경우.
• 대소문자 문제: 윈도우 사용자 이름은 대문자로 저장되므로, 비교 시 UPPER나 LOWER 함수 사용 권장.
• 권한 부족: V$SESSION 조회 시 SELECT ANY DICTIONARY 권한 필요.

---

7. 실제 활용 사례

• 감사 로그: 모든 접속자의 윈도우 사용자 이름을 기록해 보안 감사에 활용.
• 사용자별 권한: 접속한 OS 사용자에 따라 다른 로직 실행:

  BEGIN
      IF SYS_CONTEXT('USERENV', 'OS_USER') = 'MYDOMAIN\JOHN.DOE' THEN
          DBMS_OUTPUT.PUT_LINE('Welcome, John!');
      ELSE
          DBMS_OUTPUT.PUT_LINE('Restricted access');
      END IF;
  END;

---

결론

오라클에서 윈도우 로그인 사용자 이름을 알아내려면 SYS_CONTEXT('USERENV', 'OS_USER')가 가장 간단하고 강력한 방법입니다. 추가로 V$SESSION 뷰나 PL/SQL을 통해 더 풍부한 정보를 얻거나 활용할 수 있습니다. 윈도우 인증 설정만 제대로 되어 있다면, 이 방법들은 대부분 환경에서 바로 동작합니다. 필요 시 Kerberos나 네트워크 설정을 점검하며 확장하세요.