[데이터베이스] 권한과 권한 그룹(역할)

Authorization / Privileges

• 사용자는 데이터베이스의 특정 부분에 대해 여러 형태의 권한(authorization) 을 가질 수 있다.
• 이러한 각각의 권한은 privilege(권한) 라고 불리며, 필요에 따라 모두, 일부, 혹은 전혀 부여하지 않을 수 있다.
• 권한은 테이블(relation) 이나 뷰(view) 와 같은 데이터베이스의 특정 객체에 대해 설정할 수 있다.

 

주요 권한 종류

권한 유형	설명
Read	데이터를 읽을 수 있음. 수정은 불가능함
Insert	새로운 데이터를 삽입할 수 있음. 기존 데이터는 수정 불가능
Update	기존 데이터를 수정할 수 있음. 삭제는 불가능
Delete	데이터를 삭제할 수 있음

권한 부여 방식

• 사용자는 특정 테이블 또는 뷰에 대해 하나 이상의 권한을 부여받을 수 있음
• 예: 어떤 사용자에게는 특정 테이블에 대해 Read와 Insert 권한만 줄 수 있고, 다른 사용자에게는 Update와 Delete 권한만 줄 수도 있음

이러한 권한 체계를 통해 데이터의 무결성과 보안을 유지하면서, 사용자에게 필요한 범위 내에서만 접근을 허용할 수 있다.

 

Authorization Specification in SQL

데이터베이스 스키마 수정 권한 (Schema Modification Authorization) 데이터베이스의 스키마 구조를 수정하기 위한 권한들은 다음과 같은 형태로 구분된다:

권한 종류

권한 유형	설명
Index	인덱스를 생성하거나 삭제할 수 있는 권한
Resources	새로운 릴레이션(테이블)을 생성할 수 있는 권한
Alteration	릴레이션에 속성을 추가하거나 삭제할 수 있는 권한
Drop	릴레이션 자체를 삭제할 수 있는 권한

이러한 권한은 스키마 구조의 변경이 필요한 경우에만 부여되어야 하며,

데이터베이스의 안정성과 무결성을 위해 제한된 사용자 또는 관리자에게만 부여하는 것이 일반적이다.

권한 부여 (Grant)

SQL에서 grant 문은 사용자에게 특정 권한(privilege)을 부여할 때 사용된다.

기본 구문

grant <privilege list> on <relation or view> to <user list>;

• <privilege list>: 부여할 권한들 (예: select, insert, update, delete 등)
• <relation or view>: 권한을 적용할 테이블 또는 뷰
• <user list>: 권한을 부여할 대상

사용자 리스트 (<user list>)의 구성

• user-id: 예) Amit, Satoshi
• public: 데이터베이스의 모든 유효한 사용자에게 권한을 부여
• 역할(Role): (추후 설명) 여러 사용자에게 권한을 일괄적으로 관리할 수 있는 그룹 단위

예시

grant select on department to Amit, Satoshi;

department 테이블에 대해 select 권한을 사용자 Amit와 Satoshi에게 부여

주의사항

• 뷰(view) 에 대해 권한을 부여해도, 해당 뷰가 사용하는 기본 테이블들에 대한 권한이 자동으로 부여되지는 않는다.
• 권한을 부여하는 사용자(grantor) 는 그 권한을 이미 가지고 있어야 하며, 또는 데이터베이스 관리자(DBA) 여야 한다.

 

Data Manipulation Privileges in SQL

데이터베이스에서 사용자가 테이블 또는 뷰의 데이터를 조회하거나 조작할 수 있는 권한은 다음과 같다:

튜플에 관한 권한 종류

권한 유형	설명
select (read)	릴레이션의 데이터를 조회할 수 있는 권한, 뷰(view)를 사용하여 쿼리할 수 있는 권한 포함
insert	새로운 튜플(레코드)을 삽입할 수 있는 권한
update	기존 튜플의 값을 수정할 수 있는 권한 (UPDATE 문 사용)
delete	튜플을 삭제할 수 있는 권한 (DELETE 문 사용)
all privileges	위 튜플에 대한 4개의 권한을 포함하는 축약 표현

이러한 권한들은 개별적으로 부여할 수도 있고, all privileges를 사용하여 한 번에 부여할 수도 있다.

예시

grant select on instructor to U1, U2, U3;

• instructor 테이블에 대해 사용자 U1, U2, U3에게 조회 권한(select) 을 부여

 

Revoking Authorization in SQL

revoke 문은 사용자에게 부여된 권한을 회수(철회) 할 때 사용한다.

기본 구문

revoke <privilege list> on <relation or view> from <user list>;

• <privilege list>: 철회할 권한들 (예: select, insert, all 등)
• <relation or view>: 권한이 부여된 테이블 또는 뷰
• <user list>: 권한을 철회할 사용자 목록

예시

revoke select on student from U1, U2, U3;

student 테이블에 대해 사용자 U1, U2, U3의 select 권한을 철회함

추가 설명

• <privilege list>에 all을 사용하면 해당 사용자가 보유한 모든 권한을 한 번에 철회할 수 있다.
• <user list>에 public이 포함되면, 모든 사용자에게서 해당 권한이 제거된다.
• 단, 명시적으로 권한이 부여된 사용자는 계속 권한을 유지한다.
• 동일한 권한이 다른 사용자로부터 중복 부여된 경우,
• 한 쪽에서 철회해도 다른 쪽의 권한은 유지될 수 있다.
• 철회된 권한에 의존하는 다른 권한들도 함께 철회된다.
• 예를 들어, 누군가에게 권한을 부여할 수 있는 권한을 갖고 있던 사용자의 권한이 철회되면, 그로 인해 부여된 권한도 함께 사라질 수 있다.

---

Roles

• Role(역할) 은 사용자가 데이터베이스에서 무엇을 접근하거나 수정할 수 있는지 구분하기 위한 방법이다.
• 동일한 권한을 여러 사용자에게 부여해야 할 때, 역할을 통해 권한을 그룹화하면 효율적으로 관리할 수 있다.

역할 생성

create role <역할이름>;

예시

create role instructor;

instructor라는 역할(권한 그룹)을 생성

역할 부여

grant <role> to <user1>, <user2>, ...;

예시

grant instructor to Alice, Bob;

사용자 Alice, Bob에게 instructor 역할을 부여

역할의 활용

• 역할 자체에 권한을 부여하면, 역할을 부여받은 모든 사용자가 해당 권한을 가지게 된다.
• 예를 들어:

grant select, insert on course to instructor;

→ 이제 instructor 역할을 부여받은 사용자들은 course 테이블에 대해 select와 insert 권한을 가진다.

 

Roles Example

역할(Role)과 역할 계층 구조 (Role Hierarchy)

역할(Role)은 사용자 권한을 그룹화하여 관리할 수 있게 해주는 기능이다.

역할은 사용자에게 부여할 수도 있고, 다른 역할에게도 부여할 수 있다,

이를 통해 역할 간 계층 구조(chain of roles)를 만들 수 있다.

---

역할 생성 및 사용자에게 부여

create role instructor;
grant instructor to Amit;

• instructor 역할 생성
• 사용자 Amit에게 instructor 역할을 부여

---

역할에 권한 부여

grant select on takes to instructor;

• instructor 역할에게 takes 테이블에 대한 select 권한을 부여
• 이 역할을 가진 사용자는 takes 테이블을 조회할 수 있음

---

역할 간의 권한 상속

create role teaching_assistant;
grant teaching_assistant to instructor;

• teaching_assistant 역할을 생성
• 이 역할을 instructor 역할에 부여 → 이제 instructor는 teaching_assistant의 모든 권한을 상속받는다

---

역할 계층 구조 (Chain of Roles)

create role dean;
grant instructor to dean;
grant dean to Satoshi;

• dean 역할 생성
• instructor 역할을 dean에 부여
• dean 역할을 사용자 Satoshi에게 부여

→ 결과적으로, Satoshi는 dean → instructor → teaching_assistant로 이어지는 모든 권한을 상속받게 된다.

 

Authorization on Views

뷰(View)를 통해 데이터 접근을 제어할 수 있으며, 사용 권한(authorization)은 뷰 단위로도 설정 가능하다.

예시 시나리오

1. 뷰 생성

create view geo_instructor as
select * from instructor
where dept_name = 'Geology';

→ instructor 테이블 중 Geology 학과 소속 교수들만 포함하는 뷰 생성.

2. 뷰에 권한 부여

grant select on geo_instructor to geo_staff;

뷰의 권한 부여 받으면 원본에 권한 없어도 접근 가능하나, 생성하는것은 권한이 필요하다.

상황 1: geo_staff가 instructor 테이블에 권한이 없는 경우

• 정상 작동함.
• 뷰에 대한 select 권한만 부여되어 있으면, 기초 테이블(instructor)에 직접 접근 권한이 없어도 뷰를 통해 제한된 정보만 접근 가능.
• 이는 보안을 유지하면서 데이터 접근 범위를 세분화하는 데 매우 유용하다.

상황 2: 뷰 생성자가 instructor 테이블의 일부 컬럼이나 행에 대한 권한이 없는 경우

• 뷰 생성 자체가 실패하거나,
• 뷰 정의에 포함된 컬럼이나 행에 대해 권한이 부족하면 오류 발생.
• 뷰는 생성 시점에 기준 테이블에 대한 적절한 권한이 있는 사용자만 생성 가능