국비 10일차 VIEW, SEQUENCE, SYNONYM, INDEX, ROLE

2021.01.18 국비교육 10일차

[TOC]

뷰 VIEW

• 테이블 또는 다른 뷰를 기초로하는 논리적 테이블
• 뷰는 그 자체로서 소유하는 데이터는 없지만, 창문처럼 어떤 데이터를 보거나 변경할 수 있음

단순뷰

• 단일 뷰

복합뷰

• 여러개의 테이블 구성

GRANT CREATE VIEW TO SCOTT;
-- VIEW생성 권한 부여

CREATE VIEW EMP_VIEW
AS
SELECT EMPNO, ENAME, SAL, HIREDATE
FROM EMP
WHERE DEPTNO = 10;
--VIEW 생성

SELECT * FROM EMP_VIEW;
----------------------------------------
CREATE VIEW DEPT_VIEW
AS
SELECT DEPTNO NO, DNAME NAME
FROM DEPT;

DESC DEPT_VIEW;
----------------------------------------
CREATE OR REPLACE VIEW EMP_VIEW
AS
SELECT e.EMPNO, e.ENAME, d.DNAME, d.DEPTNO
FROM EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO
WHERE e.DEPTNO = 20;
----------------------------------------
CREATE VIEW EMP_VIEW2
AS
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, COMM, DEPTNO
FROM EMP;
--SAL을 제외한 EMP_VIEW

SELECT * FROM USER_VIEWS;
----------------------------------------
CREATE OR REPLACE VIEW DEPT_VIEW
AS
SELECT DEPTNO NO , DNAME NAME, LOC
FROM DEPT;

----------------------------------------
CREATE VIEW EMP_VIEW3(사원번호, 이름, 월급)
AS
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO =20;

CREATE VIEW EMP_VIEW4
AS
SELECT DEPTNO, SUM(SAL) 총합
FROM EMP
GROUP BY DEPTNO;
-- ALIAS 넣어줘야 하는 경우
----------------------------------------
CREATE VIEW EMP_VIEW5
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP;

SELECT * FROM EMP_VIEW5;

DELETE FROM EMP_VIEW5
WHERE DEPTNO = 10;
----------------------------------------
CREATE OR REPLACE VIEW EMP_VIEW6
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO =30
WITH CHECK OPTION;

UPDATE EMP_VIEW6 SET DEPTNO = 40
WHERE EMPNO = 7499;
-- WITH CEHCK OPTION으로 수정 제한

--
CREATE OR REPLACE VIEW EMP_VIEW6
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WITH READ ONLY;
-- READ ONLY

인라인 뷰

--EMP와 DEPT테이블에서 부서별 SAL총합과 평균
SELECT e.DEPTNO, TOTAL_SUM, TOTAL_AVG, CNT
FROM (SELECT DEPTNO, SUM(SAL) TOTAL_SUM, AVG(SAL) TOTAL_AVG, COUNT(*) CNT FROM EMP GROUP BY DEPTNO) e, DEPT d
WHERE e.DEPTNO = d.DEPTNO;

--부서별 평균급여보다 많은 급여를 받는 사원의 정보 출력
SELECT A.ENAME, A.SAL, A.DEPTNO, B.SALAVG
FROM EMP A, (SELECT DEPTNO, AVG(SAL) SALAVG FROM EMP GROUP BY DEPTNO) b
WHERE A.DEPTNO = B.DEPTNO
AND A.SAL >B.SALAVG;

시퀀스

• 여러 사용자들이 공유하는 데이터베이스 객체로서,

  호출 될 때마다 중복되지 않은 고유한 숫자를 리턴하는 객체

• 시퀀스는 수정안됨, DROP시키고 다시 CREATE해야함

CREATE SEQUENCE EMP_SEQ
INCREMENT BY 1
START WITH 100
MAXVALUE 9999
NOCACHE
NOCYCLE;

SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME IN ('EMP_SEQ');
----------------------------------------
CREATE SEQUENCE DEPT_DEPTNO_SEQ
INCREMENT BY 10
START WITH 10
MAXVALUE 100
MINVALUE 5
NOCACHE
CYCLE;

SELECT DEPT_DEPTNO_SEQ.NEXTVAL,
DEPT_DEPTNO_SEQ.CURRVAL FROM DUAL;
-- NEXTVAL 다음 시퀀스 (호출하면 증가시킴)
-- CURRVAL 현재 시퀀스
----------------------------------------
CREATE SEQUENCE DEPT_DEPTNO_SEQ3; -- 조건없이 시퀀스 생성 (기본값 설정됨)
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'DEPT_DEPTNO_SEQ3';

동의어

• 동의어는 객체에 대한 별칭

• 객체에 대한 접근방법을 단순화 할 수 있음

-- SYS
GRANT CREATE SYNONYM TO SCOTT;

--SCOTT
CREATE SYNONYM D_SYN FOR DEPT;
-- DEPT의 별칭 생성

SELECT * FROM SCOTT.DEPT;
CREATE SYNONYM DEPT_S FOR SCOTT.DEPT;
SELECT * FROM DEPT_S;

인덱스

• 테이블에서 행을 검색할 때 검색 속도를 높이기 위해 Oracle서버가 사용하는 스키마 객체
• 데이터의 실제 저장 위치인 ROWID를 저장하고 관리함
• 인덱스를 사용하면 디스크의 I/O를 감소시킬 수 있다.

SELECT * FROM EMP WHERE ENAME='SMITH';
--검색속도 0.063

CREATE INDEX EMP_ENAME_IDX
ON EMP(ENAME);
-- 검색 속도빨라짐 (0.032)

SELECT * FROM USER_INDEXES WHERE TABLE_NAME IN('EMP', 'DEPT');
--인덱스 확인

-- 계획 설명 창 f10

DROP INDEX EMP_ENAME_IDX;
--인덱스 삭제

사용자 관리

• 사용자 생성
  • 생성된 사용자는 아무런 권한도 부여받지 못했기 때문에 어떠한 작업도 불가능

CREATE USER user01 IDENTIFIED BY Oracle
ALTER USER user01 IDENTIFIED BY USER01;

grant create session, create table to user01;

• 권한 (privilege)

  • 특별한 sql문을 실행할 수 있는 ㄴ권리
  • dba가 일반 사용자에게 데이터베이스와 데이터베이스 객체에 접글 할 수 있는 권한을 부여할 수 있다.

• 권한 종류

  • 시스템 권한

    • 사용자의 데이터베이스 접근권한

    • 사용자가 데이터베이스에 특별한 작업을 수행하는 것을 가능하게 해줌

  • 객체 권한

    • 데이터베이스내의 객체의 내용을조작하기 위한 권한
    • 사용자가 특정 객체에 접근하고 조작하는 것을 가능하게 해줌

select * from session_privs;
-- 권한 확인

REVOKE CREATE SESSION FROM USER01;
--권한 회수

SELECT * FROM SCOTT.DEPT;
DELETE FROM SCOTT.DEPT WHERE DEPTNO = 10;

GRANT UPDATE(DNAME) ON DEPT TO USER01;
UPDATE SCOTT.DEPT SET DNAME = '인사' WHERE DEPTNO = 40;

GRANT SELECT, INSERT, ON DEPT TO USER01 WITH GRANT OPTION
-- WITH GRANT OPTION사용, 권한을 다른사람에 부여가능


select * from user_tab_privs_made;
-- 사용자가 다른 사용자에게 부여한 권한 정보 조회

select * from user_tab_privs_recd
-- 사용자가 다른 사용자로부터 부여받은 권한 정보 조회

롤 ROLE

• 권한들의 묶음을 의미

• 일반 사용자에게 권한을 부여 및 회수하듯이 롤에 권한 부여 및 회수 가능

• 롤의 장점

  • 편리한 권한 관리
  • 동적 권한 관리

• Built-in Role

롤	시스템 권한
CONNECT	CREATE SESSION
RESOURCE	CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, CREATE TYPE, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
SCHEDULAR_ADMIN	CREATE ANY JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULER, CREATE EXTERNAL JOB
DBA	대부분의 시스템 권한과 일부 롤을 포함. 일반 사용자에게 부여해서는 안된다.

--TESTER01 / TEST
CREATE USER TESTER01 IDENTIFIED BY TEST;

--패스워드 변경 TESTER01
ALTER USER TESTER01 IDENTIFIED BY TESTER01;

--TESER01에게 접속권한, 테이블 사용권한 부여 롤 이용
GRANT CONNECT, RESOURCE TO TESTER01;

• 롤 생성 및 부여 방법

CREATE USER USER02 IDENTIFIED BY USER02;
GRANT CONNECT, RESOURCE TO USER02

CREATE ROLE clerk;
-- role 생성

GRANT create session, create table to clerk;
GRANT SELECT ON SCOTT.DEPT TO clerk;
-- clerk롤에 권한 부여

GRANT clerk TO USER02

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'CLERK';
--시스템 권한 확인
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'CLERK';
-- 데이터 사전 조회

GRANT CREATE VIEW TO CLERK;
-- CREATE VIEW 권한 부여