11.시퀀스, 사용자권한과 롤

/*
<시퀀스-SEQUENCE>
    - 정수값을 순차적으로 생성하는 역할을 하는 객체
    [표현법]
    CREATE SEQUNCE 시퀀스명
        [START WITH 숫자] : 처음 발생시킬 시작 갑, 기본값 1
        [INCREAMENT BY 숫자] : 다음 값에 대한 증가치, 기본값 1
        [MAXVALUE 숫자] : 발생시킬 최대값 범위 : 10의27승 -1까지
        [MINVALUE 숫자] : 발생시킬 최소값 범위 : -10의26승까지
        [CYCLE|NOCYCLE] : 시퀀스가 최대값에 도달할 경우 START WITH로 돌아감
        [CACHE 바이트크기|NOCACHE]; : 메모리 상에서 시퀀스 값 관리(기본값 20바이트) 
*/
CREATE SEQUENCE seq_emp_copy_id START WITH 100;
-- 현재 계정이 가지고 있는 시퀀스들에 대한 정보를 조회
SELECT * FROM user_sequences;
-- NEXTVAL를 한 번이라도 수행하지 않는 이상 GURRVAL를 가져올 수 없다.
--  CURRVAL는 마지막으로 수행된 NEXTVAL값을 저장해서 보여줌
SELECT seq_emp_copy_id.NEXTVAL FROM DUAL; 
SELECT seq_emp_copy_id.CURRVAL FROM DUAL; 

-- 시퀀스명 seq_test 시작 300 5씩 증가 310 nocycle nocache
-- NOCYCLE로 설정하고 최대값을 넘어가면 오류 -> exceeds MAXVALUE은 사례로 될 수 없습니다
CREATE SEQUENCE seq_test START WITH 300 INCREMENT BY 5 MAXVALUE 310 NOCYCLE NOCACHE;
SELECT * FROM user_sequences;
SELECT seq_test.NEXTVAL FROM DUAL;
SELECT seq_test.CURRVAL FROM DUAL;
-- MAX값을 400으로 변경
-- START WITH는 수정 불가, 구문오류 발생<-시퀀스 시작 번호는 변경할 수 없습니다. -> 시작값을 변경하려면 삭제하고 다시 만듬
ALTER SEQUENCE seq_test INCREMENT BY 1 MAXVALUE 400; 
-- SELECT결과 집합을 테이블로 생성
CREATE TABLE tmp_copy
AS SELECT * FROM emp;

CREATE SEQUENCE seq_emp_copy START WITH 207 INCREMENT BY 1;

DESC tmp_copy;
INSERT INTO tmp_copy(emp_id, emp_name, hire_date) VALUES(seq_emp_copy.NEXTVAL,'홍길동', '2005-02-01');

SELECT MAX(emp_id)
FROM tmp_copy;

/* 
    의사컬럼
    - 테이블의 컬럼처럼 동작하지만 실제로 테이블에 저장되지 않는 컬럼
    SEQUENCE에서 사용되는 의사컬럼 
        - NEXVAL, CURRVAL
    ROWNUM : 쿼리에서 반환되는 각 로우의 순서값
    ROWID : 테이블에 저장된 각 로우가 저장된 주소값
*/
-- 페이징 처리 할 때 사용
SELECT *
FROM (
    SELECT ROWNUM RN, tmp_copy.* 
    FROM tmp_copy
    -- WHERE 조건 불가 
    ) WHERE RN BETWEEN 10 AND 20;

 

-- 사용자 계정 삭제\
-- 사용자 계정에 오브젝트가 생성되어 있다면 오브젝트도 함께 제거 할 수 있도록 CASCADE 옵션 사용
-- DROP USER 계정명 [CASCADE]
DROP USER jungang CASCADE;

-- 계정생성
CREATE USER jungang IDENTIFIED BY 1234;

-- 계정접속/ 권한을 부여하지 않으면 접속불가
CONN jungang/1234;

-- 사용자가 가진 권한을 확인
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'JUNGANG'; -- 대문자로 적어야함
-- 사용자가 가진 롤 확인
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'JUNGANG';

-- 권한 회수
REVOKE CONNECT, RESOURCE FROM JUNGANG;

-- DBA 모든 시스템 권한이 부여된 롤
GRANT DBA TO JUNGANG;

-- 사용자계정에 권한 부여
GRANT CREATE SESSION TO jungang;
-- 계정에 접속은 되지만 테이블 생성 등 다른 작업 불가능
-- 롤 : 하나이상의 권한으로 이루어진 집합체, 오라클에서 미리 정의해 놓은것도 있고 사용자가 정의 할 수도 있음
-- CONNECT : 접속권한
-- RESOURCE : 오브젝트 생성 권한
GRANT CONNECT, RESOURCE, CREATE VIEW TO JUNGANG;

-- 오라클에서 미리 정의해 놓은 ROLE
SELECT * FROM DBA_ROLES;
-- ROLE에 등록된 권한을 확인
SELECT GRANTEE, PRIVILEGE 
FROM DBA_SYS_PRIVS 
WHERE GRANTEE = 'CONNECT' OR GRANTEE='RESOURCE';

/*
    데이터 딕셔너리
    - 자원을 효율적으로 관리하기 위한 다양한 객체들의 정보를 저장하는 시스템 테이블
    사용자가 객체를 생성하거나 객체를 변경하는 등의 작업을 할 때 데이터베이스에 의해서 자동으로 갱신되는 테이블
    데이터에 관한 데이터가 저장되어 있다고 해서 메타 데이터라고도 함

    DBA_XXX : 관리자만 접근 가능한 객체등의 정보 조회
    USER_XXX : 계정이 소유한 객체등에 관한 정보 조회
    ALL_XXX : 계정소유 또는 권한을 부여받은 객체등에 관한 정보 조회

    DBA_SYS_PRIVS : 현재 사용자나 롤에 부여된 시스템 권한(privilege) 나열
    DBA_ROLE_PRIVS : 사용자 및 롤에 할당된 롤 확인
    USER_CONSTRAINTS : 제약조건을 확인
*/