요약

VIEW

필요 권한 : CREATE VIEW

전체 내용을 요약, 제한해서 제공함

문법 :

• CREATE OR REPLACE VIEW viewname AS SELECT...
• DROP VIEW viewname

상대방에게 VIEW의 SELECT와 DML부여하면 VIEW를 통해 권한 사용 가능.
DML의 경우 실제 테이블에도 영향을 미치니 주의.
DROP VIEW를 사용할 경우 기존에 VIEW를 사용할 수 있게 부여한 권한들도 같이 사라지니 주의

복합/단순 VIEW가 존재하나 상황에 따라 DML 사용가능 여부 달라짐

SEQUENCE

필요 권한 : CREATE SEQUENCE

AUTOINCREMENT랑 유사함

문법 :

• CREATE SEQUENCE seqname;
• DROP SEQUENCE seqname;
• ALTER SEQUENCE seqname ...;

옵션 :

• MAXVALUE / INCREMENT BY / START WITH / CYCLE / CACHE

가상 칼럼 :

• .currval : 현재 sequence의 번호 호출
• .nextval : 다음 sequence의 번호 호출

SYNONYM

필요 권한 : CREATE SYNONYM 또는 CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM

public synonym을 사용하면 다른 사람에게 해당하는 테이블, 뷰에 대해서도 synonym을 사용하여 접근할 수 있다.

VIEW

하나 이상의 테이블이 있는 데이터를 논리적으로 처리하는 오브젝트

즉, select문만 가지고 있는 객체(object). --> 간접 ACCESS ( 궁극적인 목표 )

io 낭비, 스토리지 낭비를 예방한다. 조회를 위한 테이블을 생성할 필요가 없기 때문.

어떤 테이블에 대해서 조회되는 내용을 제한한다.

간접 ACCESS : 테이블에 대해 직접적으로 접근하지 않고 VIEW를 통해서 ACCESS 한다.

quotas 조회

select * from user_ts_quotas;

VIEW 생성 조건

자주 보는 데이터에 대해 간단한 조회를 제공한다.

CREATE VIEW 시스템 권한만 있으면 생성할 수 있다.

테이블은 CREATE TABLE, QUOTAS에 대한 권한이 있어야 한다.

주로 데이터 딕셔너리에서 사용되는 것을 볼 수 있다.

• 내가 dba로 부터 직접 부여받은 시스템 권한

select * from user_sys_privs;

• 내가 받은 role 권한

select * from role_sys_privs;

• 내가 받은 시스템 + role 권한

select * from session_privs;

이러한 권한 조회도 view이다.

이러한 권한을 통해 CREATE VIEW권한이 있는지 확인해야 한다.

VIEW 생성

CREATE OR REPLACE VIEW hr.emp_view
AS
SELECT employee_id, last_name, first_name, email, job_id, department_id
FROM hr.employees;

view를 통해 hr.employees에 대해 간접적으로 ACCESS 가능하다.

OR REPLACE

VIEW는 ALTER를 통해 수정할 수 없다.

동일한 이름의 VIEW가 있으면 해당 뷰를 드랍하고 새로 생성하는 옵션.

즉 DROP을 알아서 실행해준다.

DROP VIEW hr.emp_view;
CREATE VIEW hr.emp_view as ...;

DROP + CREATE

내가 만든 VIEW 조회

SELECT * FROM user_views WHERE view_name = 'EMP_VIEW';

딕셔너리 안에는 대문자로 입력되기 때문에 VIEW의 이름은 대문자로 작성해야 한다.

TEXT_LENGTH는 입력된 SELECT 문의 길이로 현재 101글자임을 확인할 수 있다.

FROM절의 내용이 VIEW인지 판단

이것은 VIEW 인가 TABLE인가...

아키텍처를 알야야 한다.

1. 내가 생성한 객체 존재 여부 확인해야한다.

SELECT * FROM user_objects;

• 사진의 내용에서 OBJECT_ID의 아이디는 고유한 값을 가진다. OBJECT_ID와 DATA_OBJECT_ID의 동일성 여부에 따라 테이블 재구성 여부를 알 수 있다.

• VIEW는 실제 스토리지를 가지고 있지 않기 때문에 DATA_OBJECT_ID가 NULL이다.
  즉 실제 저장공간에 존재하지 않는다는 뜻이다.(PROCEDURE, TRIGGER, SEQUENCE, VIEW)

• TIMESTAMP칼럼은 LAST_DDL_TIME과 같다.

SELECT * FROM dba_objects;

• DBA는 항상 STATUS의 VALID, INVALID 상태를 확인해야 한다. (출근시 항상 수행)
• DB 서비스 하기 전에 INVALID 객체에 대해 RECOMPILE 해야 한다.

2. OBJECT_TYPE을 체크한다.

SELECT * FROM user_objects WHERE object_name IN ('EMPLOYEES','EMP_VIEW');

3. VIEW에 대한 TEXT칼럼의 SELECT 문을 실행한다.

SELECT * FROM user_views WHERE view_name = 'EMP_VIEW';

다른 유저에서 view 접근

유저 생성

create user james identified by oracle;

제임스 유저를 생성했지만, default_tablespace가 system이기 때문에 이를 수정해야 한다.

ALTER USER james DEFAULT TABLESPACE users
QUOTA 10m ON users;

권한 부여

james 계정의 로그인을 위한 CREATE SESSION 부여

GRANT CREATE SESSION TO james;

부여한 권한 확인

select * from dba_sys_privs where grantee = 'JAMES';

이때 유저명도 대문자로 입력되기 때문에 대문자로 입력해야 한다.

hr.emp_view 조회 권한 부여

hr 계정에서 진행한다.

GRANT SELECT ON hr.emp_view TO james;

내가 받은, 부여한 객체 권한 확인

SELECT * FROM user_tab_privs;

james 계정의 테이블 권한 확인

select * from user_tab_privs;

조회 확인

select * from hr.emp_view;

hr.emp_view에 대한 select 권한은 있지만, hr.employees에 대한 select 권한은 부여할 수 없다.

즉, james 계정에서는 hr.employees의 테이블에 대하여 hr.emp_view를 통해 간접적으로 access할 수 있다.

권한 회수

hr 세션에서 진행

REVOKE SELECT ON hr.emp_view FROM james;

[문제 62] 부서 이름별, 총액급여, 평균 급여를 조회하는 dept_sum_sal 뷰를 생성하시오. 그리고 해당 뷰에 대하여 james 유저에게 select 할 수 있는 권한을 부여하시오.

• hr 세션

CREATE OR REPLACE VIEW hr.dept_sum_sal
AS
select department_name, sum(salary) as sum_sal, avg(salary) as avg_sal
from (select salary, department_name
    from hr.employees e, hr.departments d
    where d.department_id = e.department_id)
group by department_name;

grant select on hr.dept_sum_sal to james;

또는

CREATE OR REPLACE VIEW hr.dept_sum_sal
(부서명,총액급여,평균급여)
AS
select department_name,sum_sal , avg_sal
from (select department_id,sum(salary) as sum_sal, avg(salary) as avg_sal
    from hr.employees 
    group by department_id) e, hr.departments d
where e.department_id = d.department_id;

grant select on hr.dept_sum_sal to james;

AS 위에 별칭 사용 가능.

DROP VIEW

DROP VIEW view_name;

수정을 위해 DROP VIEW를 사용하면 해당 VIEW에 대한 모든 권한이 사라진다.

하지만 OR REPLACE를 사용하면 권한은 그대로 유지된다.

이에 주의해야 한다.

VIEW에 대한 DML

grant select, insert, update, delete on hr.emp_temp_view to james;

view에 대한 권한을 select 뿐만 아니라 insert,update,delete 권한을 부여하고 실제 테이블에 영향을 미치며 DML을 사용할 수 있다.

VIEW INSERT

INSERT INTO hr.emp_temp_view(id, name,sal)
VALUES (300,'JAMES',1000);
COMMIT;

실제로 VIEW에 1행이 추가된 것을 확인할 수 있다.

이때 원본 테이블에도 해당 isnert의 결과가 반영된 것도 확인할 수 있다.

즉, 원본 테이블에 대해 직접적인 권한은 없지만, view를 통해 해당 테이블에 접근하면서 권한을 사용할 수 있다.(간접 ACCESS)

VIEW UPDATE

UPDATE hr.emp_temp_view
SET sal = 20000
WHERE id = 300;
COMMIT;

이처럼 UPDATE를 통해서도 간접적으로 ACCESS를 진행할 수 있다.

VIEW DELETE

DELETE FROM hr.emp_temp_view
WHERE id = 300;
commit;

DELETE도 동일하게 작용하는 것을 확인할 수 있다.

단순 VIEW, 복합 VIEW

• 복합 VIEW : 여러 테이블이 JOIN되거나 GROUP BY로 생성된 VIEW
• 단순히 연결 연산자로 칼럼이 합쳐진 표현식인 경우 단순 VIEW의 칼럼에 대해서는 INSERT,UPDATE,DELETE가 어렵다. 즉, 특정 칼럼에 대해서 조작하는 경우를 주의해야 한다.

단순 VIEW

VIEW를 통해서 DML작업을 수행할 수 있는 VIEW

즉, VIEW안에 SELECT 테이블이 하나만 있는 경우. 조인 문장이 없는 경우. 또한 함수를 사용하지 않아야 한다.

연결 연산자를 포함한 표현식이 있는 칼럼인 경우 해당 필드에 대해서 INSERT, UPDATE는 수행할 수 없다. DELETE는 가능

단순 VIEW이지만 선택되지 않은 칼럼에 NOT NULL 제약조건이 있는 경우 INSERT할 수 없음.

복합 VIEW

VIEW를 통해서 DML작업을 수행할 수 없는 VIEW

함수(GROUP)를 사용한 VIEW, 조인 문장을 포함하는 VIEW.

복합 VIEW이지만, DML작업을 수행하려면 트리거로 구현해야한다.

VIEW WITH READ ONLY

CREATE OR REPLACE VIEW hr.emp_temp_view
as
select * from hr.emp_temp
WITH READ ONLY;

단순 VIEW이지만, DML작업을 제한하고 싶을 때.
WITH READ ONLY를 사용하면 권한을 가지고 있더라도 DML조작을 할 수 없다.

VIEW WITH CHECK OPTION CONSTRAINT

view 생성과 체크 제약 조건 같이 생성

CREATE OR REPLACE VIEW hr.emp_temp_view
as 
select * from hr.emp_temp
where dept_id = 20
WITH CHECK OPTION CONSTRAINT emp_temp_view_ck;

이 때 where 절이 check 제약 조건의 조건식이 된다.

select * from user_constraints where table_name = 'EMP_TEMP_VIEW';

check 제약 조건이지만 view로 표기된다.

• dept_id는 20의 값만 insert 가능
  

• dept_id는 20번만으로 수정 가능
  

해당하는 값 이외의 데이터가 insert 또는 update될 경우 오류를 발생시키는 것을 확인할 수 있다. 삭제는 해당하지 않는다.

하지만 조건을 만족하면 정상적으로 수행되는 것을 확인할 수 있다.

VIEW와 TABLE 종속관계

CREATE OR REPLACE VIEW hr.emp_temp_view
as 
select * from hr.emp_temp
where dept_id = 20
WITH CHECK OPTION CONSTRAINT emp_temp_view_ck;

해당 코드에 대해서 hr.emp_temp가 사라지더라도 hr.emp_temp_view는 살아있다. 하지만 STATUS가 INVALID로 변경된다.

이때 해당 객체가 무엇을 참조하는지 다음 코드를 통해 확인할 수있다.

select * from user_views;

만약 참조하고 있는 테이블을 복구 할 수 없다면 방법이 없다~
그냥 view 삭제 ㄱㄱ

종속, 참조 관계 조회

select * from user_dependencies where referenced_name = 'EMP_TEMP';

해당 내용을 초회하며 영향도를 평가할 수 있다.

REFERENCE_NAME은 참조를 당하는 객체 NAME은 참조하는 객체를 의미한다.

즉 EMP_TEMP_VIEW라는 VIEW는 EMP_TEMP의 TABLE을 참조하고 있는 것을 확인할 수 있따.

SEQUENCE OBJECT

자동으로 일련번호를 생성하는 객체. (AUTOINCREMENT)

의미없는 숫자 일련번호 생성할 때 사용.

CREATE SQUENCE 시스템 권한이 필요하다.

시퀀스는 성능에 큰 영향을 미친다. (cahce size)

SEQUENCE 생성

CREATE SEQUENCE id_seq;

정상적으로 생성된 것을 확인할 수 있다. 1~ 10^27까지 표현 가능.

CACHE_SIZE는 한 번에 메모리에 올려두는 SEQUENCE의 개수이다. (IO 감소)

SEQUENCE 사용

INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval);

이때 rollback 한다고 해서 sequence객체의 번호는 변하지 않는다.

1,2,3에서 rollback 하더라도 4,5,6으로 입력된다.

즉 한번 생성하면 영구 결번으로 처리된다.

현재 사용한 sequence 번호 조회

SELECT id_seq.currval FROM dual;

가장 최근에 사용한 sequence의 번호를 조회한다.

sequence_name.nextval

가상 칼럼, 현재 사용 가능한 번호를 리턴해준다.

select id_seq.nextval from dual;

select로 조회하더라도 다음 번호로 넘어가 gap이 생길 수 있다.

sequence_name.currval

가상 칼럼, 현재 사용한 번호를 리턴한다.

DROP SEQUENCE

DROP SEQUENCE id_seq;

SEQUENCE OPTION

• START WITH / MAXVALUE / INCREMENT BY / CYCLE / CACHE

CREATE SEQUENCE id_seq
START WITH 1 -- 기본값 1
MAXVALUE 3 -- 기본 값 10**27
INCREMENT BY 1 -- 기본값 1
NOCYCLE -- 기본값 NOCYCLE
NOCACHE -- 기본값 CACHE 20
;

NOCHACE를 설정하지 않는다. 성능 너무 떨어진다.

마지막에 입력되는 값이 최댓값 보다 크고, NOCYCLE일 경오 오류를 발생시킨다.

SEQUENCE 수정

ALTER SEQUENCE id_seq
MAXVALUE 100
INCREMENT BY 2
CACHE 30;

START WITH 빼고 수정 다 가능

TIPS MAXVALUE의 크기는 성능, 메모리에 영향을 미치지 않는다.

where절 가상칼럼

UPDATE hr.seq_set
SET id = id_set.nextval
WHERE id = id_seq.currval; -- 오류 발생 where절에는 가상컬럼 사용 x

.currval, .nextval 과 같은 가상 칼럼은 where 절에 사용할 수 없다.

SYNONYM(동의어) OBJECT

긴 객체 이름을 짧은 이름으로 사용하는 객체이다.

생성시 CREATE SYNONYM 시스템 권한이 있어야 한다.

보편적으로 잘 하지 않는다.

다만 타인의 테이블에 대해 소유자명.테이블명표기하기 번거로울 때 사용한다.
타인은 이미 생성된 SYNONYM에 대해 특정 권한을 소유할 필요가 없다.
또한 타인은 다른 TABLE에 대해 SYNONYM을 생성할 권한이 없다.

SYNONYM 생성

CREATE SYNONYM hr.ec2 for hr.emp_copy_2024;

SYNONYM 삭제

DROP SYNONYM ec2;

PUBLIC SYNONYM

모든 유저들이 사용할 수 있는 PUBLIC SYNONYM을 사용하기 위해서는 CREATE PUBLIC SYNONYM 시스템 권한이 필요하다. dba로부터 해당 권한을 부여 받아야 한다.

grant create public SYNONYM to hr;

CREATE PUBLIC SYNONYM emp_s FOR hr.employees;

단! 내가 만든 public synonym에 대해서는 조회되지 않는다.

이때 all_synonyms를 사용할 수 있다.

SELECT * FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR';

이후 다른 계정에 대해서도 조회하고자 하는 테이블에 권한이 있으면 all_synonyms를 통해 SYNONYMS를 조회할 수 있고 SYNONYM을 사용하여 테이블을 조회할 수 있다.

public synonym과 select revoke

권한을 회수했더라도 다른 사람의 계정에서 public synonym을 조회할 수 있다.

하지만 실제로 synonym을 사용했을 때 오류가 발생하는 것을 확인할 수 있다.

public synonym 삭제

DROP PUBLIC SYNONYM emp_s;

DROP PUBLIC SYNONYM 시스템권한이 필요하다. DBA로부터 해당 권한을 부여받아야 한다.

이후 해당 쿼리문을 실행시키면 정상적으로 public synonym이 삭제되는 것을 확인할 수 있다.

느낀점

피곤타. 자체공강 마렵다. 몸이 찌뿌둥 하구만. 컨디션 조절 잘 하자.

VIEW 수정시 권한에 대해 항상 주의해야 한다. OR REPLACE 사용 권장

회사 엔코아, SAP - PL/SQL

금융공학 석사 필요하다.

모델링 전문가가 되고 싶다. 엑센치오