2012.01.12 SQL 8일차(VIEW, 사용자권한제어, Role)

http://gyh214.tistory.com/65 : 인라인 뷰 연습예제입니다.

VIEW(뷰)
; 가짜테이블 입니다. 아래에 자세한 설명

   뷰는 기존에 생성된 테이블 또는 다른 뷰를 이용하여 생성되는 가상 테이블로

 접근 할 수 있는 전체 데이터 중에서 일부만 접근 할 수 있도록 제한하기 위한 

 가상 테이블 기법이다.

전체 데이터 중 일부 사용자만 접근할 수 있도록 하여 데이터의 보안과 사용자의

편의성을 높이기 위해 사용한다.

(* 한마디로 해당 view 로 쿼리가 들어오면 뷰를 생성할때 실행시켰던 서브쿼리들을 다시 실행시켜줍니다.)
(결국 서브쿼리들을 묶어서 이름 지은것)

특징 :  데이터가 없습니다., 가상의 테이블이므로 실제 다른 테이블에게서 자료를 뺴와서 보여주기만 할 뿐 데이터가 없습니다. 

주의사항 : 관련해서 인덱스를 만들고 싶다면, view의 컬럼으로는 인덱스를 만들면 안되고, 그 view가 어떤 테이블의 컬럼을 참조해서 생성하는지 알아낸 다음, 원래 테이블의 컬럼으로 인덱스를 만들어야 됩니다. 


VIEW 의 종류 
1. 단순 VIEW : 한 테이블에서만 참조해서 생성한 VIEW (아래 예제를 보시면 이해가 가실 겁니다.)
2. 복합 VIEW : 여러테이블에 JOIN걸어서 생성한 VIEW
3. INLINE VIEW(인라인 뷰) : 소위, 일회용 뷰라고 하며, FROM절에 쓰인 VIEW입니다.( 사용예제 : http://gyh214.tistory.com/65  )

VIEW관련 딕셔너리 : USER_VIEWS


VIEW 생성 및 조회

  문법)

   CREATE [OR REPLACE] [ FORCE | NOFORCE] VIEW view

   [ (alias, alias,;;;)]

   AS subquery;

   * OR REPLACE : 기존 뷰와 동일한 이름으로 뷰를 재생성하는 경우

   * FORCE : 기본 테이블의 존재 여부에 상관없이 뷰 생성

   * NOFORCE : 기본 테이블이 존재할 경우에만 뷰 생성, 기본 값

   * ALIAS : 기본 테이블의 칼럼 이름과 다르게 지정한 뷰의 칼럼 이름

 학생 테이블에서 101번 학과 학생들의 학번, 이름, 학과 번호로 정의되는
  단순 뷰를 생성 하여라.

   SQL> CREATE VIEW v_stud_dept101(학번, 이름, 학과번호)
	AS SELECT studno, name, deptno
	     FROM   student
	     WHERE  deptno = 101;

   SQL> SELECT * FROM v_stud_dept101; <- 뷰의 내용 조회

복합 뷰 생성 예제 1)
  학생 테이블과 부서 테이블을 조인하여 102번 학과 학생들의 학번, 이름, 학년,
  학과 이름으로 정의되는 복합 뷰를 생성하여라.

  SQL>  CREATE VIEW v_stud_dept102(학번, 이름, 학년, 학과이름)
	AS SELECT s.studno, s.name, s.grade, d.dname
	     FROM   stduent s, department d
 	     WHERE   s.deptno=d.deptno 
                 AND   s.deptno=102;
 

뷰의 삭제
문법) DROP VIEW view;

 
------------------VIEW 연습문제--------------------------------

* 교수 , 학생, 부서 테이블을 참조하여 다음 질문에 답하세요.

1. 학생 테이블에서 학생 이름과 전화번호로 구성되는 뷰 (v_stud_addr)을 

    만드세요

2. 학생 이름과 학생이 소속한 학과 이름으로 구성된 뷰(v_stud_dept)를 만드세요

3. 101번 학과 학생들의 학생 이름과 지도 교수 이름을 가지는 뷰 (v_stud_prof)를

    정의하세요. 아직 지도교수가 정해지지 않은 학생도 함께 출력하세요.

4. 각 학과에 소속되어 있는 교수 이름과 학생 이름으로 구성되는 뷰(v_all1)를

    생성하세요. 출력 칼럼이름은 학과 이름, 학생 이름, 교수 이름입니다.

5. 학과별 평균 몸무게와 평균 키 정보를 가지는 뷰(v_dept_avg)를 생성하세요.

================================================================================================
================================================================================================ 

사용자 권한제어

1. 권한

  사용자가 데이터베이스 시스템을 관리하거나 객체를 이용할 수 있는 권리를 의미

 1) 시스템 권한

    데이터베이스 관리자나 일반 사용자에 의해 데이터베이스를 관리하는 권한으로

    사용자 생성,삭제, 객체의 생성 및 수정, 데이터베이스 백업 등이 있다.

DBA의 시스템 권한		일반 사용자의 시스템 권한
시스템 권한	기능	시스템 권한	기능
CREATE USER	사용자를 생성할 수 있는 권한	CREATE SESSION	DB에 접속할 수 있는 권한
DROP USER	사용자를 삭제할 수 있는 권한	CREATE TABLE	테이블을 생성할 수 있는 권한
DROP ANY TABLE	모든 테이블을 삭제할 수 있는 권한	CREATE SEQUENCE	시퀀스를 생성할 수 있는 권한
QUERY REWRITE	함수기반인덱스(FBI)를 생성하기 위한 권한	CREATE VIEW	뷰를 생성할 수 있는 권한
BACKUP ANY TABLE	EXPORT UTILITY를 사용해서 임이의 테이블을 백업할 수 있는 권한	CREATE PROCEDURE	프로시져, 함수, 패키지를 생성할 수 있는 권한

2) 권한 할당하기 예제

  query rewrite 시스템 권한을 scott 사용자와 모든 사용자에게 부여하라.

 

  SQL>  connect system/manager;

  SQL> grant query rewrite to scott;

  SQL> grant query rewrite to PUBLIC;

3) 권한 해제 하기 예제

  SQL> connect system/manager;

  SQL> revoke query rewrite from scott;

2. ROLE (롤)

  다수 사용자와 다양한 권한을 효과적으로 관리하기 위하여 서로 관련된 권한을

  그룹화 한 개념이다. 즉 사용자 별로 일일이 권한을 주기보다 그룹에 권한을

  주는 것이 훨씬 효과적이기 때문에 권한을 그룹화해서 관리하는 것이다.

롤 종류	롤에 부여된 권한
CONNECT	ALTER SESSION, CREATE CLUSTER,
	CREATE DATABASE LINK
	CREATE SEQUENCE, CREATE SESSION
	CREATE SYNONYM, CREATE TABLE
	CREATE VIEW
RESOURCE	CREATE CLUSTER, CREATE PROCEDURE,
	CREATE SEQUENCE, CREATE TABLE
	CREATE TRIGGER
DBA	WITH ADMIN OPTION이 있는 모든 시스템 권한

사전 정의된 롤

  * CONNECT 롤

     사용자가 데이터베이스에 접속하여 세션을 생성하거나 테이블 또는 뷰와

     같은 객체를 생성할 수 있는 권한을 그룹화 한 롤 이다

  

  * RESOURCE 롤

      사용자에게 자신의 테이블, 시퀀스, 프로시져, 트리거와 같은 객체를 생성

      할 수 있는 권한을 부여한 롤 이다. 

  * DBA 롤

      시스템 자원의 무제한적인 사용이나 시스템 관리에 필요한 모든 권한

      그리고 DBA 권한을 다른 사용자에게 부여 할 수 있는 강력한 권한을 

      보유한 롤 이다. 또한  모든 사용자의 CONNECT, RESOURCE, DBA권한을

      포함한 어떠한 권한을 부여하거나 철회 할 수 있다.  

롤 생성

  문법)

     CREATE ROLE role [NOT IDENTIFIED | IDENTIFIED]

        (BY password | EXTERNALLY]

 * NOT IDENTIFIED : 롤 활성화 시 암호에 의한 검증 과정 생략

 * IDENTIFIED : 롤 활성화 시 암호에 의한 검증 과정 활성화

 * BY password : 롤 활성화 시 사용되는 암호 지정

 * EXTERNALLY : 롤 활성화 시 운영체제 인증을 통한 사용자 검증

롤 생성 예제

  암호를 지정한 롤과 지정하지 않은 롤을 생성하여라

 SQL> connect system/manager;

 SQL> CREATE ROLE hr_clerk;

 SQL> CREATE ROLE hr_mgr

          IDENTIFIED BY manager; <- hr_mgr 롤의 암호가 manager 이다.

롤에 권한 부여하기

   1) 롤에 시스템 권한 부여

     SQL> GRANT create session TO hr_MGR;

   

   2) 롤에 객체 권한 부여

     SQL> GRANT select, insert, delete ON hr_clerk;

사용자에게 롤 부여하기

   1) 사용자와 롤에게 특정 롤 부여하기

     SQL> GRANT hr_clerk TO hr_mgr;

     SQL> GRANT hr_clerk TO tiger;

3. SYNONYM (동의어)

  하나의 특정 객체에 다른 이름을 지정하는 방법이다.

  테이블에서 칼럼의 별명과 비슷한 개념이다.

  3-1) 동의어 생성 방법

     문법) CREATE [PUBLIC] SYNONYM [schema.] synonym

 FOR [schema.] object;

     사용예 1)

       SQL> CREATE SYNONYM my_project FOR system.project;

      

     사용예 2)

       system 사용자의 project 테이블에 대해 공용 동의어를 생성하여라

         

       SQL> connect system/manger;

       SQL> CREATE PUBLIC SYNONYM project FOR project; 

  3-2) 동의어 삭제 방법

       SQL> DROP SYNONYM my_project;

SYNONIM을 왜 사용할까?
          좀 더 알기 쉬게 하기 위해

이름이 너무 길어서

특별한 이름을 주고 싶어서

원래이름을 숨기고 싶어서

구분하고 싶어서

PUBLIC SYNONYM

; 다른 사용자도 동의어를 사용할 수 있는 SYNONYM

---------------------------연습문제----------------------------

1. scott 사용자에게 함수 기반 인덱스를 생성할 수 있는 query rewrite 권한을

 부여하여라.

2. scott 사용자가 소유한 부서 테이블을 조회 및 수정할 수 있는 권한을 tiger

    사용자에게 부여하여라.

3. tiger 사용자에게 부여한 부서 테이블을 조회 할 수 있는 권한을 철회하여라.