[Database] 데이터베이스 시스템 주요 기능

뷰(View)

• 특정 사용자로부터 특정 속성(DB에 저장된 모든 실제 관계)을 숨기는 기능
• 개념적 스키마가 아닌 사용자가 "virtual relation"로 볼 수 있는 모든 관계를 View라고 함
• 주 목적은 데이터 보호이며, 이외에도 사용자 편리성 제공, 질의 간소화가 있음
• "Create view"로 정의하며 쿼리에서 일반 테이블처럼 사용할 수 있음

ex)

Create view v as <query expression>;

- <query expression> is any legal SQL expression

- "v" is the view name

 

ex) A view of professors without their salary

Create view myProfessor as
SELECT pID, name, deptName
from professor;

 

• 뷰는 한 개 이상의 테이블로부터 정의할 수 있으며, 일반 테이블과 다르게 튜플을 실제 뷰 내부에 저장하지 않음
• 가상 관계(virtual relation or virtual table)라고 부름
• 뷰는 항상 가장 최신의 데이터를 가지고 있음

 

View using Other Views

• 뷰를 정의할 때 베이스 테이블 말고도 다른 뷰를 사용할 수 있음
• 자신 뷰를 정의할 때 사용하는 뷰를 순환뷰(recursive veiw)라고 함

 

View Expansion

•  DBSM은 뷰에 대한 쿼리가 들어오면 해당 뷰를 기존에 저장되어 있는 뷰 정의로 치환하여 뷰가 아닌 base table에 대한 쿼리가 되도록 만듬
• 변환 과정은 뷰 정의가 base 관계만으로 구성될 때까지 진행이 되며, 치환하는 뷰가 순환 뷰가 아니면 상기 치환 연산은 궁극적으로 종료됨
• 반환된 쿼리엔 더 이상 뷰가 존재하지 않고 base table로만 구성이 되며, 이를 DBSM이 평가하여 쿼리문의 결과를 구함

 

View Modifications

• 뷰에 대한 변경(입력, 삭제, 갱신) 연산도 가능함
• 뷰를 정의하고 있는 base table에 대한 변경 연산으로 변환되어 수행됨

ex) Add a new tuple to "myProfessor" view

Create view myProfessor as
SELECT pID, name, deptName
from professor; # A view of professors without their salary

Insert into myProfessor values('12345', 'Lee', 'CS');
# This insertion causes the tuple ('12345', 'Lee', 'CS') to be inserted into the "professor" relation

• professor 테이블의 salary 속성에는 Null 값이 지정됨

 

Unsupported modification type

ex) 

Create view professorInfo as
SELECT pID, name, building
from professor, department
where professor.deptName=department.deptName;

Insert into professorInfo values('2345', 'White', 'Vision Hall');

 

ex)

Create view departmentTotalSalary(deptName, totalSalary) as
SELECT deptName, sum(salary)
from professor
group by deptName;

Insert into departmentTotalSalary values ('CS', 100,000);

 

다음 요소들이 포함된 뷰는 변경할 수 없음:

• GROUP BY, HAVING
• DISTINCT
• 집계 함수 (SUM, AVG, MAX, ...)
• 표현식 (a * b, CASE, ...)
• ORDER BY
• 여러 테이블을 조인한 뷰 (조인 뷰)

 

Updatable View

뷰가 변경 가능하려면 다음 조건을 모두 만족해야 함:

조건	설명
✅ FROM절에 단일 테이블만 사용	조인 없이 하나의 테이블만 기반
✅ SELECT절에 표현식 없이 컬럼명만 사용	예: salary * 1.1, COUNT(*) 이런 거 ❌
✅ DISTINCT, GROUP BY, HAVING 없음	중복 제거, 집계 연산 있으면 ❌
✅ ORDER BY 없음	결과 순서 자체는 뷰 업데이트에 의미 없음
✅ SELECT에 포함되지 않은 속성은 NULL로 설정 가능해야 함	기본 테이블의 NOT NULL 제약도 고려해야 함

 

With check option

• 뷰는 사용자가 보는 가상의 테이블이기 때문에, 조건에 안 맞는 데이터가 들어가도 사용자는 그게 삽입된 줄 모를 수 있음.

ex)

Create view CSProfessor as
SELECT * from professor
where deptName='CS';

Insert into CSProfessor values(255, 'Brown', 'EE', 100,000);

• CSProfessor는 deptName='CS'인 교수만 보여줌
• deptName='EE'인 값을 넣어도 에러 없이 삽입되지만 CSProfessor 뷰에서는 절대 조회되지 않음

이러한 문제를 막기 위해 다음과 같이 뷰 정의 마지막에 with check option을 사용하면 위 같은 상황에서 에러 발생

Create view CSProfessor as
SELECT * from professor
where deptName='CS'
with check option;

 

Restrictions on Views

제약 항목	이유
❌ 색인(INDEX) 생성 불가	뷰는 실제 데이터를 저장하지 않기 때문
❌ 키 제약조건 (PRIMARY, FOREIGN, UNIQUE 등) 불가	뷰에는 튜플이 없으므로 무결성 검사 의미 없음

 

---

 

Integrity Constraints(ICs) 무결성 제약

• 사람의 나이 속성 값이 음수이거나 (not accuracy), 다른 테이블에서는 다른 나이를 가지는(not consistency) 잘못된 데이터들이 들어가지 않게 해주는 제약 
• 데이터베이스의 일치성 및 정확도를 유지하기 위하여 사용됨

 

Constraints on a Single Relation

not null

• null 값이 들어오지 못한다는 제약
• 개별 속성에 적용이 가능 

primary key

• 기본 키로 설정, 중복 불가
• 테이블당 1개만 가능
• not null을 default로 가지고 있음

unique

• 어떤 컬럼의 값이 테이블 전체에서 유일해야 함을 보장(중복 불가)
• null 값을 가질 수 있음
• 다중 컬럼에 적용 가능

ex) not null + primary key + unique

CREATE TABLE student (
  studentID   INT PRIMARY KEY,         -- 기본키: 중복 불가 + NULL 불가
  name        VARCHAR(50) NOT NULL,    -- NULL 불가: 이름은 반드시 입력
  email       VARCHAR(100) UNIQUE,     -- 이메일은 중복 불가 (NULL은 허용)
  phone       VARCHAR(20) UNIQUE,      -- 전화번호도 중복 불가
  birthYear   INT                      -- 제약 없음 (NULL 가능, 중복 가능)
);

 

check Clause

• 관련 테이블이 항상 만족하여야 하는 조건을 명시

ex) To ensure that semester is one of fall, winter, spring or summer

Create table teaches(
	pID	char(5),
    cId char(5),
    semester varchar(10),
    year numeric(4,0),
    classroom char(5),
    primary key (pID, cID, semester, year),
    check (semester in ('Spring', 'Summer', 'Fall', 'Winter') )
);

 

Referential Integrity Constraint (참조 무결성 제약)

• 한 테이블의 외래키(Foreign Key) 값은, 반드시 다른 테이블의 기본키(primary key) 값 중 하나여야 함
• 즉, 외래키는 반드시 실제 존재하는 값만 참조할 수 있다는 제약

-- 교수 정보를 갖는 테이블
CREATE TABLE professor (
  pID CHAR(5) PRIMARY KEY,
  name VARCHAR(50)
);

-- 교수가 과목을 가르치는 정보를 담는 테이블
CREATE TABLE teaches (
  pID CHAR(5),
  cID CHAR(5),
  semester VARCHAR(10),
  year NUMERIC(4,0),
  PRIMARY KEY (pID, cID, semester, year),
  FOREIGN KEY (pID) REFERENCES professor
);

• teaches.pID는 반드시 professor.pID에 존재하는 값이어야 함

 

참조 무결성 제약 action 선언

• 참조하는 테이블에 다음과 같이 action 선언

FOREIGN KEY (pID) REFERENCES professor
  ON DELETE CASCADE
  ON UPDATE SET NULL

Action 종류:

Action	설명
CASCADE	부모가 삭제/수정되면 자식도 같이 삭제/수정
SET NULL	부모가 삭제/수정되면 자식의 외래키를 NULL로 설정
SET DEFAULT	외래키 값을 기본값으로 변경 (거의 안 씀)

 

"on delete" operation

- teaches의 pID는 professor 주 키 pID를 참조하는 외래 키임

- professor 테이블의 첫 번째 튜플 <100, Kim, ...>이 삭제한다 가정

• Action이 명시되지 않으면 삭제 연산이 허용되지 않음
• Action이 cascade인 경우 삭제 연산이 teaches 테이블에 파급되어 참조하는 모든 튜플을 삭제함
• Action이 set null일 경우 teaches 테이블에서 참조하는 모든 튜플의 pID를 null로 바꿔야 하지만 pID가 teaches 테이블의 primary key 중 하나이기 때문에 null이 허용되지 않음  >> 갱신 연산 허용 X
• teaches table의 튜플 <100, CS101, ...>은 편하게 삭제 가능

 

"on update" operation

- professor 테이블의 첫 번째 튜플이 <400, Kim, ...>으로 바뀐다고 가정

• Action이 명시되지 않으면 변경 연산 자체가 수행되지 않음
• Action이 cascade인 경우 professor의 첫 번째 튜플을 참조하는 teaches 테이블의 모든 튜플의 pID값이 100에서 400으로 변경됨
• Action이 set null일 경우 teaches 테이블에서 참조하는 모든 튜플의 pID를 null로 바꿔야 하지만 pID가 teaches 테이블의 primary key 중 하나이기 때문에 null이 허용되지 않음 >> 갱신 연산 허용 X
• teaches table의 튜플 <100, CS101, ...> 등의 pID를 400으로 바꾸는 것은 참조 무결성을 위반하므로 갱신 연산이 수행되지 않으며, Action과는 관련 X

 

튜플 입력 문제

CREATE TABLE person (
  ID      CHAR(10) PRIMARY KEY,
  name    CHAR(40),
  mother  CHAR(10),
  father  CHAR(10),
  FOREIGN KEY (mother) REFERENCES person,
  FOREIGN KEY (father) REFERENCES person
);

- 다음과 같은 자기참조 외래키가 설정된 경우 ('101', 'John', '100', '102')를 넣으려면 ID가 100인 엄마와 102인 아빠가 먼저 person 테이블에 있어야 함, 없으면 외래키 제약 위반으로 Insertion fail

해결 방법

1. 부모 먼저 넣기 (엄마, 아빠 먼저 입력) - 가능하지만 입력 순서 제어가 번거로움

2. 일단 null로 넣고 나중에 부모 update - 현실적으로 많이 쓰는 방식이지만 여전히 두 번 작업해야 함

3. 무결성 제약 연기

- SQL은 외래키 검사 시점을 “연기”할 수 있는 기능을 제공함
- 연기된 무결성 제약 (Deferred Integrity Constraint)

 

• 원래 외래키는 즉시 검사 (immediate)가 기본
• 하지만 SQL 표준에선 다음과 같이 연기 가능

SET CONSTRAINTS ALL DEFERRED;
# or
FOREIGN KEY (mother) REFERENCES person DEFERRABLE INITIALLY DEFERRED

 

• 다음과 같이 트랜잭션이 끝날 때까지 무결성 제약 검사를 미룰 수 있음

BEGIN;

INSERT INTO person VALUES ('101', 'John', '100', '102');
INSERT INTO person VALUES ('100', 'Mom', NULL, NULL);
INSERT INTO person VALUES ('102', 'Dad', NULL, NULL);

COMMIT;

 

Complex Integrity Constraints

항목	설명
CHECK 제약	특정 속성의 값이 특정 조건을 만족해야 함
제한점	대부분 DBMS는 CHECK 안에서 서브쿼리 사용 불가
예시	CHECK (pID IN (SELECT pID FROM professor)) ← 대부분 DBMS에서 ❌

• 부분적으로 지원되므로 아싸리 지원 안 해버림 (부하가 심해서 잘 사용 X)

 

Assertion(주장)

• 여러 테이블에 걸쳐 복잡한 조건을 만족해야 할 때 사용하는 제약 선언

ex)

Create assertion myVerifyTotalCredit check
(not exists
	(SELECT s1.sID
	from student s1
	where s1.totalCredit <> (select sum(credit)
		from takes, course
		where s1.sID = SID
		and course.cID=takes.cID
		and grade is not null
		and grade <> 'F')
    )
);

• student 테이블의 totalCredit 속성 값을 점검하는 assertion
• takes 테이블에서 grade가 null 값이 아니고, 'F'가 아니면 정상이므로 이들 과목의 credit 속성값들의 합은 student의 totalCredit 값과 일치하여야 한다는 assertion
• 위에서 언급되는 테이블(student, takes, course)에 대한 변화(data 입력, 삭제, 변경)가 있으면 매번 assertion을 점검해야 함
• 자원 낭비가 너무 많아 DBSM은 assertion 대신 트리거(trigger)를 제공함

 

---

 

Triggers

• 트리거는 무결성 제약 관리를 지원하는 기능으로 1999년 SQL 표준에 제정되었지만 상용 DBSM은 이전부터 자체적으로 지원해옴
• 기본적으로 ECA 규칙으로서 Event(사건)/Condition(조건)/Action(행동) 부문으로 구성됨
• DBS에 Event(Insert/Delete/Update 연산)가 발생하면, 주어진 Condition을 평가하여 이를 만족하면 Action을 하는 규칙

 

Events and Actions in Triggers

• 트리거에서 의미하는 Event는 튜플 인스턴스 변화로 튜플 insert, delete, update 연산을 의미함
• "Update of 속성 on 테이블" 문법으로 쓰임

ex) after update of grade on takes

• 트리거 안에서는 OLD로 변경 전 값을, NEW로 변경 후 값을 참조할 수 있음
• UPDATE는 둘 다, INSERT는 NEW만, DELETE는 OLD만 사용 가능함

문장	의미	사용 시점
referencing old row as old	변경 이전 값을 old라는 이름으로 참조	DELETE, UPDATE 트리거
referencing new row as new	변경 이후 값을 new라는 이름으로 참조	INSERT, UPDATE 트리거

 

Trigger ex I) takes의 grade 속성이 바뀌었을 때, student 테이블의 totalCredit update

Event (사건)

- Update of grade on takes: takes 테이블의 grade 속성이 바뀌었을 때

Condition (조건)

- old grade is 'F' or null, and: grade 속성의 값이 변경 전에 F나 null 값이면서

- new grade is not 'F' and not null: grade 속성의 값이 변경 후에 F나 null 값이 아니어야 함

Action (행동)

- update "totalCredit" of "student" table by adding

  "credit" of "course" with "cID" that is the same as "CID" of the updated tuple: student 테이블의 totalCredit 속성값 조정

Create trigger myCred after update of grade on takes # Event
referencing new row as nrow
referencing old row as orow
for each row # grade 속성 값이 변경된 row
when (nrow.grade <> 'F' and nrow.grade is not null) # Condition
	and (orow.grade = 'F' or orow.grade is null)
begin # Action
	Update student 
	set totalCredit = totalCredit +
			(SELECT credit
			from course
			where cID = nrow.cID)
	where sID = nrow.sID;
end;

 

Trigger ex II) 잔고에 있는 돈보다 많은 돈을 사용 시 부채로 바꿔주는 trigger

Schema

Event - account 테이블이 update 되었을 때

Condition - balance 속성이 0보다 작아졌을 때

Action - balance 0으로 설정, overdraft 만큼 loan(부채) 생성, loan number를 account number로 설정

Create trigger myOverdraft after update on account # Event
referencing new row as nrow
for each row
when nrow.balance < 0 # Condition
begin atomic # atomic은 transaction으로 동작하게 해줌(all-or-nothing)
	Insert into borrower # 바뀐 row의 aNumber로 예금주의 name과 account number를 borrower에 삽입
		(SELECT cName, aNumber
		from depositor
		where nrow.aNumber = depositor.aNumber);
	Insert into loan values (nrow.aNumber, -nrow.balance); # aNumber로 초과 금액만큼 부채 생성
	Update account set balance = 0 # 계좌 잔고 0으로 복구
		where account.aNumber = nrow.aNumber;
end

 

Trigger ex III) employee 테이블의 salary 속성이 바뀌었는데 new.dNumber가 null이 아닐 때 department의 totalSalary에 old salary 빼주고 new salary 더해주는 trigger

Schema

Create trigger myTotalSalary after update of salary on employee
referencing new row as nrow
referencing old row as orow
for each row
when (nrow.dNumber is not null)
Update department
	set totalSalary=totalSalary+nrow.salary-orow.salary
	where dno = nrow.dNumber;

 

 

Before 키워드

• after 대신 before 키워드를 사용하면 Event가 수행되기 전에 trigger가 먼저 수행됨
• OLD: 바뀌기 전 값 (수정 불가)
• NEW: 들어갈 값 (수정 가능)

ex) takes 테이블이 바뀐 상황에서 바뀔 row의 grade가 공백이었을 때, 이를 null 값으로 갱신하는 trigger

Create trigger mySetNull before update on takes 
referencing new row as nrow # 업데이트될 예정인 값, 원래 있던 속성값울 대체할 값
for each row
when (nrow.grade = '')
Update takes set nrow.grade = null;

 

 

Statement Level Triggers

• 문장 수준 트리거는 튜플 단위(for each row)가 아닌 SQL 문장 단위(for each statement)로 Action을 수행함
• 사건 전후 테이블을 row가 아닌 table 단위로 참조해버림
• 많은 터플에 변화가 있는 경우의 트리거를 만들 땐 문장 수준 트리거가 유용함

ex) 직원 급여가 바뀔 때마다 해당 부서의 총 급여(totalSalary)를 자동으로 갱신해주는 Trigger

Schema

Create trigger myTotalSalaryStateLevel after update of salary on employee # 급여가 바뀐 행들 중
referencing old table as O # table 단위의 전후 참조
referencing new table as N
for each statement # for each statement!
when exists(select * from N where N.dnumber is not null) or # 신, 구 table의 dNumber 속성값이 Null이 아닌 값이 존재하면
	exists(select * from O where O.dnumber is not null) # 부서 번호가 존재하는 경우에만 작동
Update department as D
set D.totalSalary = D.totalSalary
		+ (select sum(N.salary) from N where D.dno=N.dnumber) # + new 급여들
		(select sum(O.salary) from O where D.dno=O.dnumber) # - old 급여들
where D.dno in ( (select dnumber from N) union # 급여 변경이 발생한 부서들
		(select dnumber from O)); # N, O 둘 다 해주는 이유: 직원의 부서 이동 시, 변경된 이전/이후 부서 둘 다 반영해야 함

 

 

Comments on Triggers

용도	설명
요약 데이터 유지	예: 부서별 총 급여(total salary) 계산 자동화
복제 데이터 관리	변경 로그를 저장해 다른 복제본에 반영

하지만 요즘은

• Materialized View: 자동 요약 뷰 제공 (트리거 필요 없음)
• Replication 지원 내장: DBMS 자체적으로 복제 지원
• 객체지향의 method 형태로 trigger 대체 가능
• 대량 데이터 적재 시 원치 않는 트리거 실행 위험
• 트리거 체인이 과도하면 성능 저하 및 예측 불가 동작 발생 가능
• 일부 DBMS는 트리거 체인 길이를 16~32로 제한하기도 함

트리거는 유용하지만, 요즘은 뷰, 메서드, 내장 기능으로 대체 가능하고,
무분별한 사용은 성능 저하나 예측 불가 상황을 초래할 수 있으므로 주의가 필요하다.

 

---

Authorization

• 사용자는 DB 연산을 하려면 연산에 필요한 권한을 가지고 있어야 하며, 권한이 없는 연산은 시스템에 의하여 수행이 거부됨
• DBA는 모든 권한을 가지고 있으며, 특정 사용자에게 특정 권한을 부여할 수 있음
• DB 인스턴스에 대한 권한으로는 Read(읽기), Insert(입력), Update(갱신), Delete(삭제)가 있음
• DB 스키마에 대한 권한으로는 Index(색인 생성/삭제), Resources(테이블 생성), Alteration(테이블 속성 변경), Drop(테이블 삭제)이 있음

 

Privileges in SQL

• select: allows read access to relation, or the ability to query using the view
• insert: the ability to insert tuples
• update: the ability to update tuples
• delete: the ability to delete tuples
• references: ability to declare foreign keys when creating relations / 참조되는 table의 주키 값의 insert/delete/update에 영향을 미치므로 이에 대한 권한 설정이 필요
• usage: authorizes a user to use a specified domain
• all privileges: all the allowable privileges

 

Grant Statements

• Grant 문장은 권한을 부여하는 기능으로 "Grant <privilege list> on <relation name or view name> to <user list> [with grant option]"으로 구성됨
• <user list>는 사용자 아이디의 나열이나 role을 사용하여도 되며 public은 모든 사용자를 의미함
• "with grant option"을 사용하면 권한을 받는 사용자가 부여 받은 권한을 다른 사용자에게 부여할 수 있음

ex)

Grant select on professor to U1, U2, U3;
# U1, U2, U3 에게 professor 테이블에 대한 select 문장 사용 권한을 부여

Grant select on professor to U4 with grant option;
# U4 에게 professor 테이블에 대한 select 문장 사용 권한 및 다른 사용자에게 권한을 부여할 수 있는 권한 부여

Grant references (deptName) on department to Lee;
# Lee 사용자에게 department 테이블의 deptName을 참조하는 외래키를 생성하는 권한을 부여
# Lee 사용자는 본인 소유의 테이블에서 department(deptName)을 참조할 수 있음

 

 

Revoke Statements

• revoke 문장은 부여한 권한을 철회하는 기능으로 권한은 중첩될 수 있음
• U1, U2가 동일 권한을 사용자 U3에게 각각 부여할 수 있으며, U1이 권한을 revoke 해도 U3은 U2가 부여한 권한이 있으므로 권한을 계속 가지게 됨

 

Cascade, Restrict

Revoke select on professor from U1, U2, U3 cascade;
# cascade 옵션은 권한 취소시 취소 당하는 사용자가 부여한 권한이 있다면 그 권한도 함께 취소하느 옵션
# default임

Revoke select on professor from U1, U2, U3 restrict;
# restrict 옵션은 사용자가 본의 아니게 취소하는 권한을 방지하는 기능을 제공함
# With restrict, the revoke command gonna fail if cascading revokes are required

grant select on professor to public;
grant select on professor to u2;
revoke select on professor from public;
# u2 Still has select privilege on professor (권한 2번 부여 받았기 때문에)

Revoke grant option for select on professor from U5;
# U5의 "grant option"만 권한 취소

 

Authorization Graph

• DBA가 뿌리로 시작하여 사용자(node)에게 부여한 권한을 edge로 나타내는 그래프

Authorization Graph I

• DBA가 U2의 권한을 취소하면, U3, U4에게 부여한 권한이 함께 취소가 되지만 U3에겐 U1에게 받은 권한이 있기 때문에 권한이 사라지진 않는다.

 

Authorization Graph II

• DBA > U7 edge가 취소되면 U7 > U8 및 U8 > U7 edge도 함께 취소된다.
• DBA> Revoke select on professor from U7 restrict;과 같이 restricted 옵션으로 권한 취소 연산을 수행하면 실행 오류가 발생한다.

 

Authorization on Views

• 뷰 또한 일반 테이블과 마찬가지로 권한 부여 대상이어서 뷰에 대한 검색/삭제/삽입/갱신 권한 등이 존재함
• 하지만 일반 테이블 권한과는 다르게 적용되므로 주의가 필요
• base table의 조합으로 생성되는 뷰의 권한은 일반 테이블의 권한과는 독립적으로 존재함
• base table에 대한 최소한 읽기(select) 권한이 있어야 뷰 생성이 가능, 생성된 뷰에 대해서도 base table에 대한 권한을 능가하는 권한은 가질 수 없음
• 그 이상의 권한은 base table에 의존적으로 본인이 생성한 뷰에 대해서도 삭제/삽입/갱신 권한을 가지지 못할 수 있음
  • 뷰를 통한 갱신은 실제로 base table 갱신이기 때문
  • 사용자가 base table에 갱신 권한을 이미 가지고 있다면 뷰에 대한 갱신 권한도 당연히 가짐

ex) 교수가 2015년 가을 학기에 강의하는 과목에는 접근을 하고 교수의 봉급에는 접근하지 못하게 하는 View 권한

Create view myTeach as
SELECT name, title
from professor, teaches, course
where teaches.pID=professor.pID and course.cID=teaches.cID
and semester='Fall' and year=2015;

• myTeach에 대한 검색 권한을 부여받게 되면 조건에 해당하는 데이터에만 접근이 가능
• 교수의 salary 속성을 숨기는 효과
• 사용자는 base table에 대한 권한이 없으므로, 뷰에 대한 접근 권한 검사는 뷰가 확장되기 전에 뷰에 대하여 수행하여야 함

 

Roles

• 롤은 사용자의 집합으로 사용자 다수에게 동일한 권한을 부여하는 경우, 다수 사용자를 동일한 롤로 정의한 후 롤에 권한을 부영하면, 룰에 속하는 모든 사용자에게 권한이 부여된다.
• 롤은 다른 롤에게도 부여할수 있어서 이 기능을 이용하면 사용자를 계층적으로 관리하는 것도 가능해짐

ex) teller 롤을 manager

Create role teller;
Create role manager;

Grant select on branch to teller;
Grant update(balance) on account to teller;
Grant all privileges on account to manager;

Grant teller to manager;
# teller의 권한을 manager에게 부여하면 manager 롤은 teller 롤의 권한을 모두 가지게 됨

Grant teller to Kim, Park;
Grant manager to Lee;

 

Limitations of SQL Authorization

• 개별 row(튜플)에 대한 권한 제어는 지원하지 않음

ex) 학생이 자기 성적만 조회하도록 제한하는 것은 불가능 / 뷰를 만들어서 뷰 권한을 주는 것은 가능

DB는 웹 환경에서 개발이 주로 이루어지므로 DB를 관리하는 사용자가 하나라면 DB에서 굳이 권한 관리를 사용하지 않음

SQL은 행 단위 권한 제어를 지원하지 않기 때문에,
세밀한 접근 제어는 애플리케이션 코드에서 직접 처리해야 하며,
이로 인해 복잡성과 관리 비용이 증가한다.

 

---

 

Recursion in SQL

• SQL 표준은 순환 뷰를 지원하여 연쇄적인 관계를 반복적으로 추론할 때 사용함

ex) prereq(courseID, prereqID) → prereqID는 courseID의 선수 과목이라 할 때 선수과목을 모두 구할 수 있는 recPrereq 순환 뷰의 논리 표현

∀x ∀y (recPrereq(x,y) ← prereq(x,y))
∀x ∀y ∀z (recPrereq(x,y) ← recPrereq(x,z) ∧ prereq(z,y))

• 첫 번째 논리식: recPrereq 테이블의 초기값을 줌
• 두 번째 논리식: 어떤 과목 x를 위해 z가 필요하고, z를 위해 y가 필요하면 > x를 위해 y도 필요함
• z가 두 테이블을 매개하고 있는 것은 z를 기준으로 조인 연산을 의미함

Computation Example

• 순환 뷰를 구하는 계산 예제로 CS-401 과목의 선수과목을 구하는 점차적인 과정을 나타냄
• 더 이상 새로운 선수과목이 생성되지 않으면 반복 연산을 종료

 

Transitive Closure

• 전이적 폐쇄를 계산하는 방식은 순환을 사용하여 recPrereq 뷰에 새로운 튜플을 첨가하는 것으로 순환은 더 이상 새로운 튜플이 첨가되지 않은 시점까지 계속됨
• 이러한 성질이 성립하려면 순환 뷰가 monotonic(단순증가) 성질을 가져야하며, 종료시점을 설정할 수 없는 순환 뷰는 fixed point를 구할 수 없음

Transitive Closure는 재귀 뷰로 누적해서 계산하며,
반복이 멈추려면 뷰가 단조성을 가져야 한다.

 

ex)

With recursive recPrereq(courseID, prereqID) as ( # x, y
	(SELECT courseID, prereqID
	from prereq)
union
	(SELECT recPrereq.courseID, prereq.prereqID # x, z
	from recPrereq, prereq
	where recPrereq.prereqID = prereq.courseID) # y, y
)
SELECT *
from recPrereq;

• 이건 암기해야 될듯 ..