240327 DB - 데이터 제어어(DCL)

데이터 제어어

권한 부여, 회수

 

※정리

SQL 유형

• DQL(데이터질의어) : select 
  DQL을 DML로 구분하는 경우도 종종 있음. 책마다 다름
• DML(데이터조작어) : insert update delete => commit rollback
• DDL(데이터정의어) : drop alter create truncate => auto-commit을 내포
• DCL(데이터제어어) : grant revoke => auto-commit을 내포

 

유저 생성

권한을 부여하거나 회수하기 위해서는 새로운 유저가 있어야함

create user 권한 소유자(dba)가 DB에 user를 생성할 수 있음

 

sys로 접속 후 진행

show user -- 접속된 유저 확인

-- demo 유저 생성
create user demo
identified by demo;

-- demo 유저로 접속
conn demo/demo;

-- 권한이 부족하기 때문에 접속하지 못함 => 권한이 필요함

 

권한 유형

System 권한

• DB를 조작할 수 있는 권한(create session, create user, create table, create view, create sequence,.....)
• 소유자

Object 권한

• Object를 조작할 수 있는 권한(hr.employees -> select insert update delete,..........)
• Object 소유자(위의 예시에서는 hr) 

System 권한 부여, 회수

show user;
-- 위에서 demo로 접속을 시도하다가 실패함 => sys 접속이 되어 있는 것이 아니라 접속되어 있는 유저가 없음!
-- sys로 접속한 후 권한 부여
grant create session, create table, create sequence, create view
to demo;

-- demo로 접속
conn demo/demo;
-- 접속이 됨
create table test1
(id number(10), name varchar2(10));
-- 테이블도 생성됨

-- demo 권한 회수
-- sys로 접속 후 권한 회수
revoke create table
from demo;

-- 확인
conn demo/demo
create table test2
(id number(10), name varchar2(10));
-- 권한이 없기 때문에 테이블을 생성하지 못함
desc test1;
-- test1을 권한을 회수하기 전에 만들어진 것이므로 남아있음

 

Object 권한

모든 Object 권한은 Object의 주인이 소유함

-- 현재 demo임

select employee_id, last_name, salary, department_id
from employees; -- 오류 메세지

select employee_id, last_name, salary, department_id
from hr.employees; --오류 메세지
-- 즉 demo가 사용할 수 있는 테이블 중에서는 존재하지 않음
-- 다른 유저 소유의 테이블을 조회하기 위해서는 권한이 있어야함
-- Ojbect 소유자(hr)이 권한을 부여해야함

conn hr/hr
grant select, update on employees to demo;

conn demo/demo
select employee_id, last_name, salary, department_id
from hr.employees;

 

그림 1.

 

update table hr.employees
set department_id = 100
where employee_id = 206;

 

-- update 권한 회수
conn hr/hr
revoke update on employees from demo;

 

그림 2.

 

---

 

create table 시스템 권한을 회수 당한 경우

-- sys로 접속
create user turner identified by turner; --turner 유저 생성
create user ford identified by ford; -- ford 유저 생성
grant create session, unlimited tablespace to turner, ford; -- turner와 ford에 권한 부여
grant create table to turner with admin option; -- turner에게 admin option을 부여
conn turner/turner; -- turner 접속
create table turner_table(my_col number(20)); -- turner가 테이블 생성
grant create table to ford; -- turner가 ford에게 권한 부여
conn ford/ford; -- ford 접속
create table ford_table(my_col number(20)); -- ford가 테이블 생성
-- sys로 접속
revoke create table from turner; -- turner의 권한 회수
conn ford/ford; -- ford 접속
create table my_talbe_2(my_col number(20)); -- ford가 테이블 생성. 가능

 

turner가 create table 시스템 권한을 회수 당한 경우 ford의 create table 시스템 권한은 회수 안된다.

 

객체 권한을 회수

conn hr/hr -- employees Object 소유자 hr로 접속
grant select on employees to turner with grant option; -- turner에게 select 권한 부여
conn turner/turner; -- turner 접속
select last_name, job_id from hr.employees where employee_id=103; -- turner가 조회
grant select on hr.employees to ford; -- turner가 ford에게 select 권한 부여
conn ford/ford; -- ford로 접속
select last_name, job_id from hr.employees where employee_id=103; --ford가 조회
conn hr/hr -- hr 접속
revoke select on employees from turner; -- turner에게 select 권한 회수
conn ford/ford; -- ford 접속
select last_name, job_id from hr.employees where employee_id=103; -- talbe or view does not exist

 

turner가 객체 권한을 회수 당한 경우 ford의 객체 권한이 회수 된다.

 

system 권한은 회수가 되지 않고, object 권한은 회수됨