DB - 22. Intermediate SQL 2

1. Complex check condition

- section 테이블의 time_slot_id 는 time_slot 테이블의 time_slot_id중에서만 값이 올 수 있다는 제약 구문이다.

- 이 상황에서 time_slot 테이블에서 time_slot_id가 A인 튜플들을 삭제하려고 하면 section에서 A를 쓰는 튜플도 문제가 생기기 때문에 삭제 연산을 reject한다.

2. Assertion

- check구문이 복잡해질때 assertion을 사용하여 별도 구문으로 작성할수 있다.

- DB가 항상 조건을 만족하는 상태를 유지하도록 하기 위해 assetion구문으로 제약을 정의할수 있다.

- 예를들면 student 테이블의 tot_cred은 takes에서 해당 학생이 수강한 과목들의 학점 합과 같아야한다. 이 상태를 assertion으로 항상 유지하도록 할수 있다.

- assertion의 이름은 credits_earned_constraint이고 충족해야하는 조건은 check절에 나와있다.

- 참고로 assertion의 구문이 너무 복잡하면 system에 부담이 될 수 있다.

3. Built-in data type

- 기본적인 자료형 뿐만아니라 time과 관련된 built-in type도 제공한다.

- 또한 사진, 영상, cad file등을 위한 large-object type도 제공한다.

- blob은 해독되지 않은 바이너리 형식의 데이터이고 어플리케이션에서 해석해서 사용해야 한다.

- clob은 긴 문자열을 커버하는 타입이다.

- 멀티미디어 데이터는 크기가 매우 크기 때문에 그 데이터를 요청할 일이 있을 때는 포인터를 반환한다.

4. User Defined Type

- 사용자가 정의한 타입이다.

- 아래는 Dollars라는 타입을 정의하는 구문이다.

- 이렇게 정의하면 built in type과 동일한 지위를 갖는다.

5. User defined domain

- 사용자가 도메인도 직접 정의할 수 있다.

- 도메인은 해당 컬럼에 올수 있는 값의 집합이다.

- person_name은 고정길이 char 20자이고 null이어서는 안된다. (타입뿐만아니라 무결성 제약도 걸 수 있다.)

- degree_level은 가변길이 char 10자이고 학사, 석사, 박사 값만을 가질 수 있다는 제약이 걸려있다.

- 위에서 도메인을 정의했으니 varchar(20) not null 대신 person_name이 와도 된다

6. Authorization -  grant

- 사용자에 따라서 db에 접근권한을 설정할 수 있다.

- read : 조회가능, 수정불가

- insert : 삽입가능, 수정불가

- update : 수정가능, 삭제불가

- delete : 삭제가능

- 이 priviledge를 정의하는 방법을 알아보자. 대표적으로 grant문을 사용한다.

- 특정 사용자가 어떤 relation이나 view에 대해 권한을 부여하는 구문이다.

- 사용자의 id를 통해 부여할 수 있고 혹은 public 키워드를 통해 모든 사용자를 지칭할 수 있다.

- Amit, Satoshi 두 사용자에게 department테이블의 select를 사용할 수 있는 권한을 준 예시이다.

- 이 권한을 부여하는 주체는 DB의 관리자 이다. (grantor)

- 꼭 관리자가 아니더라도 어떤 권한을 가지고 있는 주체가 자신의 권한을 다른 주체에게 부여하는것도 가능하다.

7. Authorization - revoke

- revoke는 주었던 권한을 다시 회수하는 방법이다.

- U1, U2, U3로부터 student 테이블의 조회 권한을 뺏는 예시이다.

- select말고 all을 쓰면 모든 권한을 회수한다.

- public은 모든 사용자를 대상으로 할 수 있다.

- U3에게 U1, U2가 p의 권한을 주었다고 해보자 U1이 revoke문으로 p를 뺏어가도 U3는 여전히 p의 권한을 가지고 있다.

- DBA가 U1에게 p권한을 부여했다. 근데 U1이 p를 U2, U3에게 부여한 상황이다. 만약 DBA가 U1으로부터 p의 권한을 뺏으면 U1이 나누어준 권한들도 회수해야 마땅하다.

- 이를 구체적으로 구현하는 방법을 알아보자.

- 첫번째 예시 : Amit에게 department를 조회할 수 있는 권한을 준다. with grant option은 amit이 이 권한을 다른 사용자에게 줄 수 있다는 옵션을 말한다.

- amit이 다른 사용자에게 권한을 주었을 수 있기 때문에 revoke시에 cascade 옵션을 주어서 amit이 준 권한도 모두 회수하게 한다.

- restrict는 만약 amit이 다른 사용자에게 권한을 부여했다면 아예 amit에게 권한을 회수하는 구문을 취소하라는 옵션이다. 만약 amit이 권한을 부여하지 않았다면 권환을 회수한다.

- 즉 restrict는 side effect가 발생하면 회수하지 않고 아니라면 회수한다.

8. role

- role은 접근 권한 부여에 효율적으로 활용될 수 있다.

- role이라는 이름으로 역할을 생성하고 role에 grant로 권한을 부여해둔다. 

- 그럼 사용자에게 role을 부여하는 방식으로 권한을 줄 수 있다.

- 즉 instructor라는 role을 마치 권한인 것 처럼 사용자에게 부여한다.

9. Authorization - reference

- references를 통해 다른 테이블을 참조할 수 있는 권한을 부여할 수있다.

- Mariano에게 department테이블의 dept_name을 외래키로 설정할 수 있는 권한을 부여한다.

- 외래키 설정은 많은 제약을 만들어내기 때문에 권한으로 관리할 필요가 있다.