1. 오라클 - 테이블에 ROLE 부여

0. ROLE이란? 

권한들의 모음(묶음) 

롤 안에 SYSTEM권한, OBJECT권한을 같이 넣을 수 있다. 

CREATE ROLE 권한을 소유한 유저만 생성 가능 

ROLE 내부의 구성이 바뀔 경우 바로 반영된다 → 권한에 대한 배포가 빠름 

 

[관리자]
X , Y, Z, N 이 있다. 

CREATE SESSION, CREATE TABLE, CREATE USER - MGR(ROLE 이름) 


각 3명한테 3개의 권한을 부여해야한다. 
또 N이라는 관리자가 새로 오게되면 3개의 권한을 부여해야한다. 
X 관리자가 좌천됐다. 권한을 회수해야한다. 
==> 위 사례를 각각 해야하는 것은 매우 번거롭다. 

MGR이라는 ROLE 안에 3가지 기능을 넣어서 관리자들에게 부여하면 된다. 
권한 회수할때도 MGR ROLE만 회수하면 된다. 

MGR ROLE안에 CREATE VIEW를 더 추가해서 넣으면 이미 MGR을 부여 받은 관리자들에게도 적용된다. 
권한의 배포가 빠르다. (회수동일)

 

🎂 흐름 

롤 생성 → 롤에게 권한 부여(추가) → 롤을 유저에게 부여 

 

 

#보안

ROLE을 부여함으로써 사용자가 데이터베이스의 특정 테이블에 직접 접근하는 것을 방지할 수 있습니다. ROLE에 권한을 부여하고, 해당 ROLE을 사용자에게 부여함으로써 데이터베이스의 보안을 강화시킬 수 있다. 

 

#관리 용이성

ROLE을 사용하면 데이터베이스 관리자는 데이터베이스에 액세스 권한을 부여하는 대신 ROLE에 권한을 부여하고 해당 ROLE을 여러 사용자에게 부여할 수 있습니다. 이렇게 함으로써 데이터베이스 관리자는 보안과 권한 관리를 더욱 효율적으로 할 수 있습니다. 

 

#유연성

ROLE에 대한 권한을 부여하면 데이터베이스 관리자는 해당 ROLE이 소유한 권한을 변경하거나 삭제할 수 있습니다. 

 

 

1. 새로운 ROLE 생성 

CREATE ROLE문을 사용하여 새로운 ROLE을 생성한다. 

CREATE ROLE MY_TABLE_ROLE;

 

 

2. ROLE에 대한 권한 부여

GRANT문을 사용하여 ROLE에 권한을 부여합니다. 예를 들어 MY_TABLE_ROLE에 SELECT, INSERT, UPDATE, DELETE 권한을 부여하여 다음과 같이 작성합니다. 

MY_TABLE은 부여할 권한이 있는 테이블 이름입니다. 

GRANT SELECT, INSERT, UPDATE, DELETE ON MY_TABLE TO MY_TABLE_ROLE;

 

 

3. 사용자에게 ROLE 부여 

ROLE을 사용자에게 부여하여 해당 사용자에게 해당 ROLE을 부여할 수 있습니다. 예를 들어 MY_USER 사용자에게 MY_TABE_ROLE을 부여하려면 다음과 같이 작성합니다. 

이제 MY_USER 사용자는 MY_TABLE_ROLE에 부여된 SELECT, INSERT, UPDATE,DELETE 권한을 사용하여 MY_TABLE테이블을 조작할 수 있습니다. 

GRANT MY_TABLE_ROLE TO MY_USER;

 

 

4. USER_ROLE_PRIVS 

사용자에게 부여된 롤에 대한 정보를 보여줌 

DESC USER_ROLE_PRIVS;

SELECT *
FROM USER_ROLE_PRIVS;

[USER_ROLE_PRIVS 구조]

 

 

[SELECT 결과]

 

 

 

MGR , MY_TABLE_ROLE(ROLE)안에 뭐가 들어 있는건지 확인하고 싶다면? 

 

DESC ROLE_SYS_PRIVS; 

SELECT *
FROM ROLE_SYS_PRIVS
-- WHERE ROLE='MY_TABLE_ROLE'
;

[ROLE_SYS_PRIVS 구조]

 

 

[SELECT 결과]

 

 

5. ROLE안에 SYSTEM, OBJECT 권한이 섞여서 존재할 수 있다. 그럴 경우에는 ROLE_TAB_PRIVS, ROLE_SYS_PRIVS에서 각각 조회하면 된다. 

[USER_TAB_PRIVS 구조]

 

 

[USER_ROLE_PRIVS 구조]

 

 

 

 

 

자료출처 

부산 아이티윌 데이터베이스 선생님 

챗GPT