데이터베이스의 관리자는 일반 사용자에 일부/모든 권한 부여/회수 가능. 또한, 객체를 생성한 사용자는 해당 객체에 대한 모든 권한 가짐.
- 데이터베이스 관리자 : DBMS 내의 모든 객체에 대한 모든 권한 가짐. 모든 권한이란 객체에 대한 연산(삽입, 삭제, 수정 등)뿐 아니라, 객체의 제거(drop table), 변경(alter table)도 포함. 또한, 다른 사용자에 해당 객체에 대한 권한 부여/회수 가능함
- 객체 소유자 : 특정 사용자가 객체 생성했으면 그 사용자가 해당 객체의 소유자 됨. 소유자는 생성한 테이블에 대해 관지라와 같이 모든 권한 가짐. 차이점은, 객체 소유자는 자신이 생성한 객체에 한정해 모든 권한 가짐. 모든 권한이란, 해당 객체에 대한 권한의 부여/회수도 포함됨
- 기타 사용자 : 객체에 대한 일체의 사용권한 없음. BUT, 데이터베이스 관리자/객체 소유자로부터 일부/모든 권한 부여 받을 수 있음.
SQL에서의 권한 부여 : grant(권한 부여), revoke(권한 회수)
- grant <권한 리스트> on <객체명> to <사용자 리스트>
예) grant select on student to kim
=> kim 사용자에게 student 테이블에 대해 select 연산 수행 권한 부여
*권한 리스트 중 select, update는 특별히 테이블 내의 특정 필드에 대해서만 연산을 허용할 수 있음!
예) grant select (stu_id) on student to kim
=> student 테이블에서 stu_id에 대해서만 select 연산 허용! (나머지 필드는 kim이 select 불가능)
if) 사용자 lee가 department 테이블 소유중이고, 사용자 kim이 student 테이블 생성해서 student 테이블이 lee의 department 테이블의 기본키이 dept_id 참조하는 외래키 정의하려고 하면 정의 불가능
=> kim은 department에 대한 아무런 권한 없으므로 => lee가 department 테이블 참조하는 권한 줘야함
‘references’
grant references (dept_id) on department to kim
if) 모든 사용자에게 권한 부여하려면? <사용자 리스트>에 ‘public’ 사용하면 됨
예) grant select on student to public
if) 모든 종류의 권한을 부여하려면? ‘all privileges’ 사용
예) grant all privileges on student to lee (lee에게 student 테이블에 대한 모든 권한 부여)
with grant option : 부여받은 권한을 다른 사용자에게 또 전파할 수 있는 명령어
예) grant select on student to kim with grant option
=> student 테이블의 select 권한 부여 받은 kim은 위 옵션을 통해 또 다른 사용자에게 이 권한 전파 가능
revoke : 다른 사용자에게 부여한 권한 회수
revoke <권한 리스트> on <객체명> from <사용자 리스트>
예) revoke select on student from kim : kim에게 부여한 student 테이블의 select 권한 회수
이 경우엔, kim이 권한을 부여한 또 다른 사용자의 권한도 자동적으로 회수됨
롤(role) : 권한에 따른 사용자 그룹. 원칙적으로 데이터베이스 관리자만이 생성 가능함!
create role <롤 이름>
예) create role employee / create role manager => 사원과 임원에 대한 롤 생성
각 사용자에게 롤 배정
예) grant employee to lee, kim / grant manager to chang, choi
*사용자는 하나 이상의 롤에 배정 가능함 (즉, 사용자가 employee, manager에 모두 배정될 수 있음)
롤에 권한 부여
예) grant select on student to employee (롤 employee에게 student테이블에 대한 select권한 부여)
롤에 부여된 권한 회수 : revoke 사용
예) revoke select on student from employee
배정된 롤에서 특정 사용자 배제 : revoke 사용
예) revoke manager from choi : 사용자 choi manager로부터 배제
롤 삭제 : drop role <롤이름>
예) drop role manager
뷰(view) : 논리적 스키마/테이블로부터 정의된 가상 테이블. 강력한 보안 수단 제공.
예로, 특정 테이블에서 일부 필드/레코드에 대해서만 접근 허용할 때 이 부분들을 뷰로 정의하고 사용자에게 정의된 뷰에 대해 접근 권한 부여하고 실제 테이블에 대한 접근 차단하면 사용자에게 감추고 싶은 부분에 대한 보안만 자연스럽게 구현 가능
create view junior
as select stu_id, name, year, dept_id
from student
where year=3
=> student 테이블에서 3학년 학생들만을 대상으로 위 필드 추출해 junior란 이름으로 만든 가상 테이블
이 뷰에 대해 사용자 kim에게 select 권한 부여
=> grant select on junior to kim
==> 이 경우, kim은 junior에 대한 접근은 허용되지만, 실제 student 테이블에 대해선 권한 없으므로 접근 차단됨. 따라서, 뷰에 포함되지 않는 3학년 이외의 학생들에 대한 레코드와 뷰에서 배제된 필드에 대한 정보는 kim이 접근 불가함.
이처럼, 뷰를 이용하면 필드 뿐 아니라 레코드 일부에 대해서도 접근제어가 가능함.
기본적 무결성 제약의 보장 – 기본키 무결성 제약 & 참조 무결성 제약
기본키 무결성 제약 : 레코드의 기본키가 이전 레코드와 중복되면 삽입 거부
참조 무결성 제약
1) 참조되는 레코드의 외래키가 참조하는 테이블의 기본키에 존재하면 삽입 거부
2) 레코드 삭제
; student 테이블에서 department 테이블의 dept_id=‘920’ 를 참조 중이면, department 테이블의 dept_id가 ‘920’인 레코드 삭제 불가. (레코드가 삭제되면 student 테이블에서 dept_id=‘920’인 레코드 삭제되니까)
3) 테이블 삭제 : department 테이블을 drop table 문으로 삭제하면, student 테이블의 dept_id는 더 이상 department 테이블을 참조하지 못하므로 department 테이블 삭제 불가.
=> student 테이블에서 정의한 참조 무결성 제약 취소하든지 student 테이블 먼저 삭제해야함.
테이블 무결성 제약의 보장 : not null, unique, check, default
not null : 널 삽입 불가
unique : 중복된 값 불가 (이미 삽입된 레코드와 중복되는 레코드 삽입하면 거부함)
check default : 필드의 값을 생략한 경우 대체할 디폴트 값 지정. default가 지정된 필드에 의도적으로 널 값 삽입한 경우엔 default값이 아닌 널 값이 입력됨
오라클에서의 사용자 권한 제어 (오라클은 시스템 전반에 대한 접근 권한 제어 기능 갖고있음)
: 시스템 권한 / 객체 권한
시스템 권한 : 사용자의 생성, 테이블/테이블 스페이스의 생성 등의 주로 시스템 자원을 관리하는 권한
*데이터 베이스 관리자 계정(sys, system = 모든 시스템 권한 갖고있으며, 다른 사용자에 권한 부여 O)
객체 권한 : 해당 객체에 대해 select, insert, delete, update 등과 같은 DML 실행할 수 있는 권한
*객체란? 테이블/뷰/사용자 정의 함수 등등
사용자 계정 생성 & 권한 관리
create user lee -> 사용자 계정 생성
identified by lee_pass -> 비밀번호
default tablespace users -> users : 오라클 설치할 때 기본적으로 만들어진 테이블 스페이스 이름
quota 2M on users -> 기본 할당공간
*오라클에서 계정 생성후 이 계정으로 로그인/테이블 생성 등 하려면 권한 필요
로그인 권한 = create session 권한
=> grant create session to lee
테이블 생성 권한 = create table 권한
=> grant create table to lee
if) sys계정이 kim,lee 계정 생성 후 로그인/테이블 생성 권한 부여. lee로 로그인 후 student 테이블과 department 테이블 생성 후 student 테이블에 대한 select 권한 kim에게 부여
=> kim으로 로그인 후 select stu_id from student; // 오류남
=> select stu_id from lee.student; // 소유자명.테이블명 같이 테이블 이름 앞에 소유자명 기입해야함
with grant option 권한
grant select on student to kim with grant option (lee가 실행)
=> lee가 with grant option으로 kim에게 권한의 전파 허용함!
grant select on lee.student to chang (kim이 실행)
=> student 테이블의 소유주 kim이 아니므로 테이블 이름 앞에 소유주 명시
revoke : 권한 회수
revoke select on studdent from kim
=> chang 또한 kim으로부터 이 권한 간접적으로 부여받았으므로 chang의 권한도 자동적으로 회수됨!
*with grant option : 객체 권한 다른 사용자에게 전파 (select, delete, update 등)
*with admin option : 시스템 권한 다른 사용자에게 전파 (create session, create table 등)
롤의 생성 & 사용자 배정
create role : 데이터베이스 관리자 or 관리자로부터 롤 생성 권한 부여받은 사용자만 실행 가능
if)사용자 lee가 student 테이블 생성했고 park 이란 계정 생성. 이 상태에서sys계정으로 로그인 한 후 stu_role 생성하고 이 롤에 student 테이블에 대한 select&insert 권한 부여
*create role stu_role
*grant select, insert on lee.student to stu_role
*grant stu_role to partk => 사용자 park을 stu_role에 배정!
*롤에 배정된 사용자 배제 : revoke stu_role from park