Admin 실습 04: 사용자 관리 & 권한 / 롤 / 프로파일

실습 스크립트 전체 보기: https://github.com/nsylove97/Seongryeol-OracleDB-Portfolio

실습 환경

OS	Oracle Linux 7.9 (VMware Virtual Machine)
DB	Oracle Database 19c
접속 툴	SQL*Plus, MobaXterm(SSH)

---

1. 사용자 계정 구조 & Predefined 운영 계정

 

오라클 DB 계정 구성 요소

구성 요소	설명
Username	계정 이름
Authentication	인증 방식 (비밀번호 / OS / External)
Default Tablespace	객체를 저장할 기본 공간
Temporary Tablespace	정렬 등 임시 작업 공간
Quota	테이블스페이스 사용 한도
Profile	비밀번호 정책 & 리소스 제한
Initial Consumer Group	로그인 시 배정될 리소스 우선순위 그룹

 

주요 Predefined 계정

계정	역할
SYS	최고 관리자. SYSDBA/SYSOPER 권한 포함. DB 핵심 작업 전용
SYSTEM	SYS 다음 수준의 관리 계정. 일반 유지보수용
DBSNMP	모니터링 에이전트 전용 계정

 

• 이 계정들은 일상 업무(개발, 쿼리 등)에 사용하지 않음
• 실수나 해킹 시 피해가 너무 크기 때문에 별도 계정을 만들어 사용하는 것이 원칙.

-- 현재 DB의 모든 사용자 목록 확인
SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, CREATED
FROM DBA_USERS
ORDER BY CREATED;

orcl DB 모든 사용자 목록 확인

---

2. Administrator Authentication (관리자 인증)

• DBA는 일반 유저와 달리 OS 인증 또는 패스워드 파일 인증 방식으로 접속
• 인증 우선순위: OS 인증 → 패스워드 파일 인증

인증 방식	설명
OS 인증	OS 계정이 dba 그룹에 속하면 비밀번호 없이 SYSDBA 접속 가능
Password File 인증	orapwd로 생성한 파일 기반 인증. 원격 SYSDBA 접속 시 필수

-- OS 인증 방식: 비밀번호 없이 바로 접속
sqlplus / as sysdba

-- 패스워드 파일 확인
ls -l $ORACLE_HOME/dbs/orapw<ORACLE_SID>

-- 패스워드 파일 생성 (19c 기준)
orapwd file=/home/oracle/orapworcl \
        password=oracle \
        entries=10 \
        format=12 \
        force=y

OS 인증 방식으로 접속

기존 orcl의 패스워드 파일 확인 후 홈 디렉토리에 새로운 패스워드 파일 생성 및 확인

-- 패스워드 파일로 인증된 사용자 목록 확인
SELECT * FROM V$PWFILE_USERS;
-- 패스워드 파일은 대소문자를 구분함

 

패스워드 파일로 인증된 사용자 목록 확인, sys계정 하나만 존재

---

3. External Authentication (외부 인증, OS 계정으로 DB 접속)

• External Authentication은 DB 비밀번호 없이 OS 로그인 계정으로 바로 DB에 접속하는 방식
• Oracle DB 계정명 앞에 OPS$를 붙여서 생성

-- STEP 1: OS 계정 생성 (root에서 실행)
useradd stud
passwd stud
# 비밀번호 입력

-- oracle 계정 환경변수 파일을 stud 홈으로 복사
cp /home/oracle/.bash_profile /home/stud/.bash_profile
chown stud:stud /home/stud/.bash_profile

OS 계정 stud 생성 후 oracle 계정 환경변수 파일을 stud로 복사, 권한 변경

-- STEP 2: SYS 계정에서 Oracle DB 계정 생성
-- OS 계정과 연결하려면 계정명 앞에 OPS$ 붙이기
CREATE USER ops$stud IDENTIFIED EXTERNALLY;
-- ops$stud: OS 계정 stud가 DB에 접속하면 자동으로 이 계정으로 매핑됨

-- 접속 및 객체 생성 권한 부여
GRANT CONNECT, RESOURCE TO ops$stud;

-- 생성 확인
SELECT USERNAME, AUTHENTICATION_TYPE
FROM DBA_USERS
WHERE USERNAME = 'OPS$STUD';

opus$stud 계정 생성 및 권한 부여 후 확인

-- STEP 3: OS stud 계정으로 전환 후 비밀번호 없이 접속 확인
su - stud
sqlplus /                  -- 비밀번호 입력 없이 접속 성공

-- 어떤 계정으로 접속됐는지 확인
SHOW USER                  -- USER is "OPS$STUD"

OS 계정 stud 전환 후 비밀번호 없이 접속 성공

-- 번외: OS 관련 인증 파라미터 확인
SHOW PARAMETER os_
-- os_authent_prefix : OPS$ (기본값) -- OS 인증 사용자 접두어
-- os_roles          : FALSE -- OS 역할을 DB 역할로 사용할 수 있는지 여부

 

OS 관련 인증 파라미터

---

4. 계정 잠금 해제 & 비밀번호 초기화

-- 잠긴 계정 목록 확인
SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS
WHERE ACCOUNT_STATUS != 'OPEN'
ORDER BY ACCOUNT_STATUS;

잠긴 계정 목록 확인

-- LOCKED 상태 계정 잠금 해제 (DBSNMP)
ALTER USER dbsnmp ACCOUNT UNLOCK;

-- EXPIRED & LOCKED 상태 계정: 비밀번호 초기화 + 잠금 해제 동시에 (CTXSYS)
-- EXPIRED 상태는 UNLOCK만으로는 안 됨 → 비밀번호도 같이 설정해야 함
ALTER USER ctxsys
IDENTIFIED BY ctxsys
ACCOUNT UNLOCK;

-- 적용 확인
SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS
WHERE USERNAME IN ('DBSNMP', 'CTXSYS');

-- 실습 후 다시 잠금 처리 (시스템 계정은 평소에 잠가두는 것이 보안상 이로움)
ALTER USER dbsnmp ACCOUNT LOCK;
ALTER USER ctxsys ACCOUNT LOCK;

dbsnmp와 ctxsys 계정 잠금 해제 후 확인

원상 복구 후 확인

---

5. 시스템 권한 (System Privilege)

• 시스템 권한은 DB 전체 차원에서 특정 작업을 할 수 있는 권한 (주로 DDL 관련)

-- 권한 부여할 유저 생성
CREATE USER spuser IDENTIFIED BY spuser;

-- 시스템 권한 부여
GRANT CREATE TABLE TO spuser;
GRANT CREATE SESSION TO spuser;

-- ANY가 붙으면 모든 스키마에 영향 → 주의 필요
GRANT CREATE ANY TABLE TO spuser;   -- 다른 스키마에도 테이블 생성 가능

-- ADMIN OPTION: 받은 권한을 다른 사용자에게 줄 수 있는 능력
-- ADMIN OPTION으로 부여한 권한을 회수해도, 
-- hr이 다른 사람에게 전파한 권한은 함께 회수되지 않음 (연쇄 회수 없음)
GRANT CREATE TABLE TO spuser WITH ADMIN OPTION;

-- 시스템 권한 회수
REVOKE CREATE TABLE FROM spuser;

spuser 생성 후 시스템 권한 부여 및 회수

-- 사용자에게 부여된 시스템 권한 확인
SELECT PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'SPUSER';

-- 본인 계정 확인
SELECT * FROM USER_SYS_PRIVS;

 

spuser에게 부여된 시스템 권한

 

spuser로 로그인해서도 확인 가능

---

6. 오브젝트 권한 (Object Privilege)

• 오브젝트 권한은 특정 오브젝트에 대해 접근·조작할 수 있는 권한 (SELECT, INSERT, UPDATE, DELETE 등)

-- spuser가 hr의 employees 테이블을 SELECT할 수 있도록 권한 부여
GRANT SELECT ON hr.employees TO spuser;

-- GRANT OPTION: 받은 권한을 다른 사용자에게 줄 수 있는 능력
-- GRANT OPTION으로 부여한 권한을 회수하면 연쇄 회수 발생
GRANT SELECT ON hr.employees TO spuser WITH GRANT OPTION;

spuser에게 hr의 employees 테이블을 select하는 권한 부여, spuser에서 조회 가능

-- 사용자가 받은 오브젝트 권한 확인
SELECT TABLE_NAME, PRIVILEGE, GRANTABLE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'SPUSER';

-- 또는 본인 확인
SELECT * FROM USER_TAB_PRIVS;

-- 오브젝트 권한 회수
REVOKE SELECT ON hr.employees FROM spuser;

sys에서 spuser의 오브젝트 권한 확인

spuser에서도 확인 가능

권한 회수 후 spuser에서 오브젝트 권한 사라진 것 확인

 

ADMIN OPTION vs GRANT OPTION 비교

	ADMIN OPTION (시스템 권한)	GRANT OPTION (오브젝트 권한)
전파	받은 권한을 타인에게 줄 수 있음	받은 권한을 타인에게 줄 수 있음
회수 시 연쇄	연쇄 회수 없음	연쇄 회수 발생

---

7. 롤 (Role)

• 롤은 여러 권한을 묶은 권한 그룹 
• 개별 사용자에게 일일이 권한을 주는 대신 롤 하나를 부여해서 효율적으로 권한 관리 가능

주요 기본 제공 롤

롤	포함 권한
CONNECT	CREATE SESSION (DB 로그인)
RESOURCE	CREATE TABLE, CREATE PROCEDURE 등 + UNLIMITED TABLESPACE
DBA	대부분의 시스템 권한
SELECT_CATALOG_ROLE	데이터 딕셔너리 조회 권한 (DBA_TABLES 등)

 

-- 롤 생성
CREATE ROLE r1;
CREATE ROLE r2;

-- 롤에 시스템 권한 부여
GRANT CREATE SESSION TO r1;
GRANT CREATE TABLE TO r1;
GRANT SELECT ANY TABLE TO r2;

-- 롤에 다른 롤 부여 (롤 중첩)
GRANT r1 TO r2;

-- 롤 부여할 사용자 생성
CREATE USER ruser IDENTIFIED BY ruser;

-- 사용자에게 롤 부여
GRANT r2 TO ruser;

롤 r1와 r2 생성 후 롤에 시스템 권한 부여, ruser 생성 후 롤 r2 부여

-- 부여 확인
SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'RUSER';

-- 롤에 포함된 권한 확인
SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
WHERE ROLE = 'R2';

-- 롤 회수
REVOKE r2 FROM ruser;

-- 롤 삭제
DROP ROLE r2;

ruser에게 부여된 롤 확인 후 r2에 포함된 권한 확인

롤 회수 및 삭제 후 ruser가 가진 롤 확인 (아무것도 없음)

---

8. 프로파일 (Profile)

• 프로파일은 사용자의 비밀번호 정책과 리소스 사용량을 관리하는 설정

관리 항목	설명
Resource Control	한 사용자가 DB 자원을 과도하게 쓰지 않도록 제한
Password Management	비밀번호 만료, 잠금, 재시도 횟수 등 보안 관리

 

프로파일 기본 규칙

• 한 사용자에게 동시에 하나의 프로파일만 지정 가능
• 변경 시 다음 로그인부터 적용
• 모든 사용자는 기본적으로 DEFAULT 프로파일을 가짐
• 리소스 제한이 실제로 동작하려면 아래 설정 필수 (비밀번호 정책은 항상 적용)

-- 리소스 제한 활성화 (필수)
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

 

주요 비밀번호 보안 파라미터

파라미터	설명
FAILED_LOGIN_ATTEMPTS	비밀번호를 몇 번 틀리면 계정을 잠글지
PASSWORD_LOCK_TIME	잠긴 후 몇 일 동안 잠금 상태를 유지할지
PASSWORD_LIFE_TIME	비밀번호 유효 기간 (일수)
PASSWORD_GRACE_TIME	만료 후 유예 기간
PASSWORD_REUSE_TIME	이전 비밀번호를 다시 사용하기까지 필요한 기간 (일수)
PASSWORD_REUSE_MAX	비밀번호를 몇 번 바꾼 후 이전 비밀번호를 재사용할 수 있는지

-- 프로파일 생성 (비밀번호 3번 틀리면 계정 잠금)
CREATE PROFILE secure_profile LIMIT
    FAILED_LOGIN_ATTEMPTS   3
    PASSWORD_LOCK_TIME      1/24       -- 1시간 잠금 (일 단위이므로 1/24)
    PASSWORD_LIFE_TIME      60         -- 60일마다 만료
    PASSWORD_GRACE_TIME     7          -- 만료 후 7일 유예
    PASSWORD_REUSE_TIME     365        -- 365일 후 재사용 가능
    PASSWORD_REUSE_MAX      10;        -- 10번 바꾼 후 재사용 가능


-- 프로파일 적용할 유저 생성
CREATE USER puser IDENTIFIED BY puser;

-- 사용자에게 프로파일 적용
ALTER USER puser PROFILE secure_profile;

프로파일 및 유저 puser 생성 후 프로파일 적용

-- 적용 확인
SELECT USERNAME, PROFILE
FROM DBA_USERS
WHERE USERNAME = 'PUSER';

-- 프로파일 상세 확인
SELECT RESOURCE_NAME, LIMIT
FROM DBA_PROFILES
WHERE PROFILE = 'SECURE_PROFILE';

프로파일 적용 확인

프로파일 상세 확인

 

비밀번호 3번 틀려서 잠긴 계정 복구

-- 잠금 확인
SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS
WHERE USERNAME = 'PUSER';
-- ACCOUNT_STATUS: LOCKED

-- 잠금 해제
ALTER USER puser ACCOUNT UNLOCK;

-- 또는 잠금 해제 + 비밀번호 초기화 동시에
ALTER USER puser IDENTIFIED BY puser1 ACCOUNT UNLOCK;

비밀번호를 일부러 틀려서 puser 계정 잠김

puser 잠긴 것 확인

잠금 해제 후 다시 open된 것 확인

---

9. 쿼타 (Quota)

• 쿼타는 사용자가 특정 테이블스페이스 안에서 사용할 수 있는 공간의 양

-- 기본적으로 유저는 테이블스페이스에 쿼타가 없으면 객체를 생성할 수 없음
-- 단, UNLIMITED TABLESPACE 시스템 권한을 가지면 쿼타 제한 없이 사용 가능

-- 쿼타 부여
ALTER USER puser QUOTA 100M ON users;
ALTER USER puser QUOTA UNLIMITED ON users;  -- 무제한

-- 쿼타 회수 (0으로 설정)
ALTER USER puser QUOTA 0 ON users;

-- 쿼타 확인
SELECT TABLESPACE_NAME, BYTES, MAX_BYTES
FROM DBA_TS_QUOTAS
WHERE USERNAME = 'PUSER';

 

쿼타 100m / unlimited 부여 후 쿼타 확인 (-1은 무제한)

쿼타를 회수하면 조회 불가

---

실습 핵심 요약

주제	핵심 포인트
Predefined 계정	SYS / SYSTEM은 일상 업무에 사용 금지
Admin 인증	OS 인증 → 패스워드 파일 인증 순으로 적용
External 인증	OPS$계정명 으로 생성, OS 로그인으로 DB 접속
계정 잠금 해제	ALTER USER ... ACCOUNT UNLOCK
시스템 권한	GRANT ... WITH ADMIN OPTION (연쇄 회수 없음)
오브젝트 권한	GRANT ... WITH GRANT OPTION (연쇄 회수 있음)
롤	권한 묶음. 로그인 후 활성화 (CONNECT 제외)
프로파일	비밀번호 정책 + 리소스 제한. RESOURCE_LIMIT=TRUE 필수
쿼타	ALTER USER ... QUOTA. UNLIMITED TABLESPACE 권한 있으면 쿼타 무의미