[SQL*PLUS] 스키마(사용자) 생성 및 권한 종류 (Feat. Apple Silicon & Docker)

작성일자 : 2024-08-28

Ver 0.1.1

0. 배경

Applie Silicon M1 칩에 Oracle 설치를 위해서 Docker을 활용하였고, 그 과정의 상세 내용은 아래 게시물에 있다.

[Docker] Apple Silicon M1 칩 맥북에 Oracle 설치

 

그 이후 실습 및 연습을 위해 스키마(사용자)를 생성하고자 했는데, SQL*PLUS를 통해 스키마 생성 및 권한 부여를 할 수 있다. 

[SQL*PLUS] SQL*PLUS의 개념, SQL과의 차이

SQL*PLUS에 대한 자세한 특징은 위 게시글을 참고하길 바라며,

한줄로 요약하자면, 데이터베이스 관리에서 중요하며, 그 관리를 SQL*PLUS에서 특정 스키마(사용자)에게 권한을 부여하는 작업을 할수 있다. 권한은 주로 객체 생성, 데이터 조작, 사용자 관리 등 다양한 작업을 허용하거나 제한하기 위해 사용된다.

 

이번 게시글에서는 SQL*PLUS를 통해 생성했던 스키마 내용과 스키마에 권한을 줄 수 있는 종류 및 상세 설명에 대해서 정리해보고자 한다.

---

1.  접속 방법

접속 방법은 아래와 같다.

우선 전제는 docker와 oracle 컨테이너가 실행되어 있어야 하며, 'docke exec -it oracle sqlplus' 명령어를 입력하고,

oracle container 생성시 입력했던 Adim user id와 password를 입력한다.

(base) limjongjun@jeijeiui-MacBookAir ~ % docker exec -it oracle sqlplus

SQL*Plus: Release 21.0.0.0.0 - Production on Sun Aug 25 08:04:09 2024
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter user-name: system
Enter password:

 

password는 입력을 해도 보이지 않는다.

정확하게 입력하고 Enter를 치면 아래와 같이 SQL*PLUS에 접속이 성공했다는 결과를 얻을 수 있다.

 

[OUTPUT]

Last Successful login time: Sun Aug 25 2024 08:03:19 +00:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

---

2. 스키마 생성 방법 및 권한 종류

SQL*PLUS에 접속하면 터미널 창이 'SQL>' 형태로 변경된다. 이제 SQL 언어를 입력할 수 있는 것이다.

아래 create user 명령어를 통해 스키마를 생성할 수 있으며 <schema name>과 <password>에 각각 설정하고자 하는 값으로 입력 하면 된다.

 

그 이후 생성한 스키마에 grant 명령을 통해 역할 권한을 부여했다.

SQL> create user <schema name> identified by <password>;

User created.

SQL> grant resource, connect, dba to <schema name>;

Grant succeeded.

 

권한을 줄 수 있는 종류는 크게 시스템, 객체, 역할 위 3가지 종류에 대해서 권한을 관리할 수 있다.

종류별로 상세 내용에 대해서 정리해보겠다.

2-1. 시스템 권한 (System Privileges)

시스템 권한은 데이터베이스 수준에서 특정 작업을 수행할 수 있는 권한이다. 이는 전체 데이터베이스에 영향을 미칠 수 있는 권한을 부여할 때 사용된다.

CREATE SESSION	사용자가 데이터베이스에 접속할 수 있도록 허용
CREATE TABLE	사용자가 테이블을 생성할 수 있도록 허용
CREATE VIEW	사용자가 뷰를 생성할 수 있도록 허용
CREATE SEQUENCE	사용자가 시퀀스를 생성할 수 있도록 허용
CREATE PROCEDURE	사용자가 스토어드 프로시저를 생성할 수 있도록 허용
CREATE TRIGGER	사용자가 트리거를 생성할 수 있도록 허용
CREATE SYNONYM	사용자가 시노님을 생성할 수 있도록 허용
CREATE USER	다른 사용자를 생성할 수 있는 권한을 부여
DROP USER	사용자를 삭제할 수 있는 권한을 부여
ALTER USER	사용자의 정보를 수정할 수 있는 권한을 부여
GRANT ANY PRIVILEGE	사용자가 다른 사용자에게 권한을 부여
GRANT ANY ROLE	사용자가 다른 사용자에게 역할을 부여
UNLIMITED TABLESPACE	사용자가 테이블스페이스를 무제한 사용할 수 있게 허용
RESOURCE	일반적인 자원(테이블, 인덱스 등) 생성 권한을 부여
DBA	데이터베이스 관리자 권한으로, 모든 권한을 부여

2-2. 객체 권한 (Object Privileges)

객체 권한은 특정 객체(테이블, 뷰, 시퀀스 등)에 대해 수행할 수 있는 작업을 제어한다. 객체 권한은 특정 스키마가 소유한 객체에 대해 다른 사용자에게 권한을 부여할 때 사용된다.

SELECT	사용자가 테이블이나 뷰의 데이터를 조회 가능
INSERT	사용자가 테이블에 데이터를 삽입 가능
UPDATE	사용자가 테이블의 데이터를 수정 가능
DELETE	사용자가 테이블의 데이터를 삭제 가능
ALTER	사용자가 테이블이나 뷰의 구조를 변경 가능
INDEX	사용자가 테이블에 대해 인덱스를 생성 가능
REFERENCES	사용자가 다른 테이블을 참조하는 외래 키를 생성 가능
EXECUTE	사용자가 프로시저나 함수를 실행 가능

 

2-3. 역할 (Roles)

역할은 여러 권한을 묶어 하나의 역할로 정의하여, 사용자가 그 역할을 부여받으면 해당 역할에 포함된 모든 권한을 가지게 된다. 역할을 사용하면 관리가 용이해진다.

CONNECT	기본적인 데이터베이스 접속 권한과 일부 객체 생성 권한을 부여
RESOURCE	테이블, 인덱스, 프로시저 등 리소스 생성 권한을 부여
DBA	모든 권한을 포함하는 데이터베이스 관리자 역할
CUSTOM_ROLE	특정 업무에 맞춰 권한을 커스터마이징하여 만든 사용자 정의 역할

2-4. 권한 부여 예시

SQL*Plus에서 권한을 부여하는 기본 구문은 다음과 같다:

GRANT 권한 TO 사용자 [WITH ADMIN OPTION];

- `WITH ADMIN OPTION`: 이 옵션을 사용하면 사용자가 부여받은 권한을 다른 사용자에게도 부여할 수 있다.

 

예시)

GRANT CREATE SESSION, CREATE TABLE TO hr;
GRANT SELECT, INSERT ON employees TO scott;
GRANT DBA TO admin WITH ADMIN OPTION;

 

2-5. 권한 철회 (Revoking Privileges)

부여된 권한을 철회할 때는 `REVOKE` 명령을 사용한다:

REVOKE 권한 FROM 사용자;

예시)

REVOKE CREATE TABLE FROM hr;
REVOKE DBA FROM admin;

---

3. 요약

• SQL*PLUS : 특정 스키마(사용자)의 권한 관리에 사용
• 권한 
  • 시스템 권한: 전체 데이터베이스에 영향을 미치는 작업을 허용하는 권한.
  • 객체 권한: 특정 객체에 대해 수행할 수 있는 작업을 허용하는 권한.
  • 역할: 여러 권한을 묶어서 관리할 수 있는 개념.
  • 사용 예: `GRANT`와 `REVOKE`를 통해 권한을 부여하거나 철회할 수 있음.

이렇게 권한을 적절히 설정하면 데이터베이스의 보안과 관리 효율성을 높일 수 있다.