SQL(Structured Query Language)의 종류에는 DDL, DML DCL이 있죠.
그 중 DCL(Data Control Language)는 데이터베이스 사용자에게 권한을 부여하거나 권한을 회수하는 기능을 합니다.
오늘은 DCL문에서 GRANT & REVOKE (권한부여와 권한회수)에 관한 명령어를 알아보도록 하겠습니다☺️
목차
권한을 부여하기 전에 권한을 부여해 줄 계정부터 생성해야 겠죠?
📌유저 생성
CREATE USER 사용자명 IDENTIFIED BY 비밀번호;
🔷예제
CREATE USER test_user INDENTIFIED BY pass1234;
이제 권한에 관한 명령어를 알아봅시다.
권한은 크게 시스템 권한과 객체 권한으로 나눌 수 있습니다.
시스템 권한은 사용자가 데이터베이스 객체를 생성하거나 관리할 수 있는 권한이고,
객체 권한은 특정 테이블, 뷰, 시퀀스 등에 대한 조작 권한입니다.
📌시스템 권한
시스템 권한: 사용자가 데이터베이스 객체를 생성하거나 관리할 수 있는 권한
🔹 주요 시스템 권한
| CREATE SESSION | 데이터베이스에 접속할 수 있는 권한 |
| CREATE TABLE | 테이블을 생성할 수 있는 권한 |
| ALTER ANY TABLE | 모든 테이블을 수정할 수 있는 권한 |
| DROP ANY TABLE | 모든 테이블을 삭제할 수 있는 권한 |
| CREATE SEQUENCE | 시퀀스를 생성할 수 있는 권한 |
| CREATE VIEW | 뷰(View)를 생성할 수 있는 권한 |
| CREATE PROCEDURE | 저장 프로시저(Procedure)와 함수(Function)를 생성할 수 있는 권한 |
| CREATE USER | 새로운 사용자를 생성할 수 있는 권한 |
| DROP USER | 사용자를 삭제할 수 있는 권한 |
| GRANT ANY PRIVILEGE | 다른 사용자에게 권한을 부여할 수 있는 권한 |
| SELECT ANY TABLE | 모든 테이블을 조회할 수 있는 권한 |
| UPDATE ANY TABLE | 모든 테이블을 수정할 수 있는 권한 |
| DELETE ANY TABLE | 모든 테이블을 삭제할 수 있는 권한 |
| INSERT ANY TABLE | 모든 테이블에 데이터를 삽입할 수 있는 권한 |
| DBA | 최고 관리자 권한 (모든 권한 포함) |
🔹권한 부여/회수 문법
GRANT 권한명 (ON 테이블명) TO 사용자명;
REVOKE 권한명 (ON 테이블명) FROM 사용자명;
✔️예제
//DB에 접속할 수 있는 권한
GRANT CREATE SESSION TO test_user;
//test_user가 데이터베이스에 접속하고 테이블을 만들 수 있도록 허용
GRANT CREATE SESSION, CREATE TABLE TO test_user;
//admin_user에게 모든 권한을 부여
GRANT DBA TO admin_user;
//test_user에게서 테이블 생성 권한을 회수
REVOKE CREATE TABLE FROM test_user;
📌객체 권한
객체 권한 :
• 특정 테이블, 뷰, 시퀀스 등에 대한 권한
• 해당 객체를 소유한 사용자가 다른 사용자에게 부여할 수 있다.
🔹 주요 객체 권한
| SELECT | 해당 테이블의 데이터를 조회할 수 있음 |
| INSERT | 해당 테이블에 데이터를 삽입할 수 있음 |
| UPDATE | 해당 테이블의 데이터를 수정할 수 있음 |
| DELETE | 해당 테이블의 데이터를 삭제할 수 있음 |
| ALTER | 해당 테이블의 구조를 변경할 수 있음 (예: 컬럼 추가/삭제) |
| INDEX | 해당 테이블에 인덱스를 생성할 수 있음 |
| REFERENCES | 외래 키(Foreign Key)로 참조할 수 있음 |
| EXECUTE | 저장 프로시저 또는 함수(Function)를 실행할 수 있음 |
🔹예제
//test_user가 sample_table 테이블을 조회하고 데이터를 삽입할 수 있는 권한
GRANT SELECT, INSERT ON sample_table TO test_user;
//test_user가 sample_table 테이블에한 모든 권한을 가짐
GRANT ALL ON sample_table TO test_user;
//test_user가 sample_table 테이블을 조회하는 권한을 회수
REVOKE SELECT ON sample_table FROM test_user;
📌역할(Role) : 여러 개의 시스템 권한을 묶어놓은 집합
🔹 주요 기본 역할
| CONNECT | 기본적인 접속 권한 (CREATE SESSION 포함) |
| RESOURCE | 테이블, 시퀀스, 프로시저 등을 만들 수 있는 권한 |
| DBA | 모든 권한을 가진 최고 관리자 권한 |
🔹예제
//test_user가 접속할 수 있고 테이블을 생성할 수 있음
GRANT CONNECT, RESOURCE TO test_user;
//admin_user에게 DBA(최고 관리자) 권한 부여
GRANT DBA TO admin_user;
//test_user의 테이블 생성 권한 회수
REVOKE RESOURCE FROM test_user;
📌권한 확인 방법
1. 로그인 중인 현재 계정의 권한 확인
SELECT * FROM USER_SYS_PRIVS; //시스템 권한 확인
SELECT * FROM USER_TAB_PRIVS; //객체 권한 확인
2. 관리자 계정에서 특정 계정의 권한 확인
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘사용자명'; //시스템 권한 확인
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '사용자명'; //객체 권한 확인
3. 유저가 부여받은 역할(Role) 확인
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '사용자명'; //관리자 계정에서 확인
SELECT * FROM USER_ROLE_PRIVS; //현재 로그인 중인 사용자
끝까지 읽어주셔서 감사합니다 :)
Have a good day🐱
📢
1. 개발자 준비생이 공부한 내용을 정리한 글입니다. 내용에 오류가 있을 수 있습니다.
2. 위와 같은 이유로 내용에 대한 지적과 조언은 감사하게 받습니다.
3. 이 글의 내용은 계속 공부함으로써 언제든지 추가/수정 될 수 있습니다.