[Oracle] 2020.12.24. day_44 synonym 생성, 삭제, 권한부여, view(단순,복합 view), with check option(단순 view DML)

◎ synonym

synonym

테이블의 별명, 테이블의 이름을 설정해주는 것 alias와 같은 역할을 한다고 이해

테이블을 다른 이름으로 호출할 수 있는 객체

권한이 설정된 계정만 synonym을 생성할 수 있다

관리자계정으로 권한을 확인할 수 있다

계정이 달라도 사용할 수 있다

다른 유저의 객체(테이블, 뷰, 프로시저, 함수, 패키지, 시퀀스 같은)를 참조할 때 많이 사용

다른 유저의 객체를 사용할 때 유저의 이름, 객체의 실제이름을 사용하는데 그 두개를 감춤으로 database 보안을 개선하기 위해 사용된다

 

▼ 시노님 생성문법

  생성문법)

create synonym 시노님명 for 테이블명;

▼ 시노님 삭제문법

drop synonym 시노님명;

---

◎ 권한

	권한의 종류
시스템권한	dba_role_privs (dictionary에서 볼 수 있다)
	connect, resource 권한
쿼리문생성권한	dba_sys_privs  (dictionary에서 볼 수 있다)

▼ 해당 계정만 사용할 수 있는 시노님생성을 할 권한부여 문법

grant create synonym to 계정명;

▼ 모든 계정이 사용할 수 있는 시노님생성을 할 권한부여 문법

grant create public synonym to 계정명;

 

 

※ 시노님 생성해보기

1. 시노님을 생성해본다

--시노님 생성
--cp_emp4 테이블의 시노님을 ce4로 설정
create synonym ce4 for cp_emp4;

2. 실패이유 - 권한없음 : 권한부여 해보기

권한이 없기 때문에 만들어지지 않는다 따라서 권한을 부여해보자

권한부여는 관리자계정에서 해야한다

--권한설정
grant create synonym to scott;

※ 쿼리문 생성권한 확인해보기

select * from dba_sys_privs 
where grantee = 'SCOTT';

※ 조회 결과가 똑같이 수행된다

--조회
select * from cp_emp4;

select * from ce4;

---

---

◎ view

	view
	실제 테이블을 사용하지 않고 조회를 수행할 때
	단순 view, 복합 view를 지원
	권한이 부여된 계정만 뷰를 생성할 수 있다
	복잡한 쿼리문을 단순화하여 조회할 수 있다, 속도가 빠르다(memory에서 작업하는것이라)
	실제 테이블에 DML작업이 발생하면 view에 바로 반영된다
	생성된 view는 user_views 테이블에서 조회
단순 view	테이블 하나에서 도출된 뷰
	함수나 수식을 포함하지 않고 단순한 컬럼으로만 도출된 뷰
	DML 수행이 가능 (뷰에 작업한 내용이 실제 도출된 테이블에 반영된다)
복합 view	여러 테이블에서 도출된 뷰 (join, union)
	함수나 수식을 포함하여 도출된 뷰
	DML 수행이 안된다 : insert 불가능 join으로 되어있으면 DML 수행 가능 (update, delete) 함수나 수식을 포함한 뷰는 DML 전체 수행 불가

◉ view 생성, 삭제

메모리를 쓰기 때문에 쓰지 않을 때도 메모리를 점유하고 있다 
--> 아무나 만들 수 없다 
--> 권한이 부여되어야 만들 수 있다

▼ view를 만드는 문법 (권한이 들어있어야한다)

create [or replace] view 뷰명(컬럼명,,,) as (select,,,);

◉ 컬럼명 안써도 된다
◉ select 에 join이 들어가면 복합뷰

권한이 부여된 계정만 뷰를 만들 수 있기 때문에 권한을 먼저 부여한다

▼ view를 삭제

drop view 뷰명;

---

※ 단순 view DML : 실제로 존재하는 테이블이 아니지만 수행할 수 있다

view에서 작업하거나 실제테이블에서 작업해도, 둘다 반영된다

※ 단순 view에서 DML이 안될 때,  with check option 부여하면 된다

1. view_ insert 

insert into v_cp_emp(empno, ename) values(1111, '김ㅇ규');

view, 실제 테이블 모두 반영된다

2. view_ update

update	v_cp_emp
set		job='사원', mgr=1234
where	empno = 1111;

view, 실제 테이블 모두 반영된다

3. view_ delete

delete from v_cp_emp where empno=1111;

view, 실제 테이블 모두 반영된다

 

※ 복합 view

dept, emp 테이블을 복사한 cp_dept, cp_emp 테이블을 생성하여 사용해보기

create table cp_dept as (select * from dept);

create table cp_emp as (select * from emp);

※ 복합 view는 모든 DML 불가 _ select 만 가능하다

 cp_dept , cp_emp 테이블을 조인해서, v_dept 뷰를 생성

create view v_dept as
(select	d.deptno, d.dname, d.loc, e.empno, e.ename, e.job, e.hiredate
from    dept d, emp e
where		e.deptno(+) = d.deptno
);

select * from v_dept;

 

--view를 사용하면 조인하지 않아도 서로다른 테이블의 레코드를 얻을 수 있다
select 	deptno, dname, loc, empno, initcap(ename), job, hiredate
from		v_dept
where		deptno = 10;

 

복합 view : insert 할 수 없다

 

복합 view : update 할 수 없다

 

※ view 변경

create or replace view v_dept as
(select	d.deptno, d.dname, d.loc, e.empno, e.ename
from    dept d, emp e
where	e.deptno(+) = d.deptno
);