SQL 권한 및 사용자 생성 가이드

데이터베이스 관리에서 사용자와 권한의 설정은 매우 중요한 작업입니다.

이번 포스트에서는 SQL에서 사용자와 권한을 생성하는 방법에 대해 자세히 알아보겠습니다.

---

권한(Privilege)

개요

권한(Privilege)은 특정 SQL 문장을 실행하기 위한 권리입니다. 데이터베이스 관리자(DBA)는 데이터베이스와 그 객체에 대한 액세스를 사용자에게 부여하는 능력을 갖춘 상급 사용자입니다. 사용자는 데이터베이스에 액세스하기 위해 시스템 권한(System Privilege)이 필요하고, 데이터베이스에서 객체의 내용을 조작하기 위해 객체 권한(Object Privilege)이 필요합니다.

권한 확인

현재 사용자에게 주어진 권한 및 롤(ROLE)을 확인하는 방법은 다음과 같습니다.

• 현재 사용자에게 주어진 롤 확인:
• SELECT * FROM USER_ROLE_PRIVS;
• 현재 사용자에게 주어진 권한 확인:
• SELECT * FROM USER_SYS_PRIVS;

---

DBA의 주요 시스템 권한 (System Privileges)

• CREATE USER: 다른 오라클 사용자를 생성할 수 있는 권한
• DROP USER: 다른 사용자를 삭제할 수 있는 권한
• DROP ANY TABLE: 임의의 스키마에서 테이블을 삭제할 수 있는 권한
• BACKUP ANY TABLE: 임의의 스키마에서 테이블을 백업할 수 있는 권한

---

사용자 생성 및 권한(롤) 부여

1️⃣ 사용자 생성 및 삭제

사용자를 생성하고 삭제하는 명령어는 다음과 같습니다:

CREATE USER 사용자이름 IDENTIFIED BY 비밀번호;
CREATE USER "USER01" IDENTIFIED BY "USER01"; -- USER01 사용자 생성
DROP USER USER01; -- USER01 사용자 삭제

2️⃣ 시스템 권한 부여 및 회수

사용자에게 시스템 권한을 부여하고 회수하는 방법은 다음과 같습니다:

GRANT 권한 TO 사용자 [WITH ADMIN OPTION];
GRANT "CREATE SESSION" TO "USER01"; -- USER01에게 로그인 권한 부여
REVOKE 권한 FROM 사용자; -- 특정 권한 회수

허가되는 시스템 권한 예시:

• CREATE SESSION: 로그인 권한
• CREATE TABLE: 테이블 생성 권한
• CREATE SEQUENCE: 시퀀스 생성 권한
• CREATE VIEW: 뷰 생성 권한
• CREATE PROCEDURE: 프로시저 생성 권한

역할(ROLE) 부여

역할은 관련 권한의 그룹으로, 사용자와 N:N 관계를 가집니다. 오라클의 기본 롤은 CONNECT, RESOURCE, DBA입니다.

GRANT CONNECT, RESOURCE TO USER01; -- USER01에게 CONNECT와 RESOURCE 롤 부여

3️⃣ 테이블 스페이스 지정(연결)

테이블 스페이스는 데이터 저장을 위한 물리적 공간입니다. 사용자의 기본 테이블 스페이스를 지정하고 특정 테이블 스페이스에 무제한 할당량을 부여할 수 있습니다.

ALTER USER 사용자 DEFAULT TABLESPACE 테이블스페이스;
ALTER USER "USER01" QUOTA UNLIMITED ON "MYSPACE"; -- MYSPACE.DBF에 무제한 저장 허용

테이블 스페이스 생성 예시:

CREATE TABLESPACE MYSPACE
DATAFILE 'C:\\oraclexe\\app\\oracle\\oradata\\XE\\MYSPACE.DBF'
SIZE 52428800 AUTOEXTEND ON NEXT 52428800 MAXSIZE UNLIMITED;

---