Database_60_사용자 접근 제어 실습_SQL 자격증 시험 데이터 기반

------------------------------------------------------------------------
    -- 3Tier_Architecture : 3계층 구조
        -- Presentation > Application > Data
        -- WEB 
            -- > 입력받은 정보를 전송
        -- WAS
            -- > SQL문장으로 데이터베이스에 접속
            -- 동시 접속 세션 수가 정해져 있습니다.
        -- DATABASE
        
        -- WAS >>> DATABASE : DB라고 합니다.
------------------------------------------------------------------------

-- 사용자 접근 제어 실습

--< 인사 관리, K 리그, DEMO >--
-- 자신이 가지고 있는 권한을 확인합니다.
SELECT *
FROM session_privs; 
    /*
    --<인사 관리>--
    PRIVILEGE
    ---------------------------
    CREATE SESSION
    UNLIMITED TABLESPACE
    CREATE TABLE
    CREATE CLUSTER
    CREATE SYNONYM
    CREATE VIEW
    CREATE SEQUENCE
    CREATE PROCEDURE
    CREATE TRIGGER
    CREATE TYPE
   
--< DEMO >--
    PRIVILEGE
    ---------------------------
    CREATE SESSION
    UNLIMITED TABLESPACE
    CREATE TABLE
   
    --< K 리그 >--
    PRIVILEGE
    ---------------------------
    CREATE SESSION
    UNLIMITED TABLESPACE
    CREATE TABLE
    CREATE CLUSTER
    CREATE SEQUENCE
    CREATE PROCEDURE
    CREATE TRIGGER
    CREATE TYPE
    CREATE OPERATOR
    CREATE INDEXTYPE
    */

--< 인사 관리, K 리그, DEMO >--
-- 부여받은 ROLL을 확인합니다.    
SELECT *
FROM session_roles;
    /*
    --<인사 관리>--
    ROLE
    ----------------------
    CONNECT
    RESOURCE
    
    --< DEMO >--
    ROLE
    ----------------------
    
    --< K 리그 >--
    ROLE
    ----------------------
    CONNECT
    RESOURCE
    */

--< 인사 관리, K 리그, DEMO >--
-- 시스템에 설정되어 있는 ROLL을 확인합니다.
SELECT *
FROM role_sys_privs;
    /*
    --<인사 관리>--
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    RESOURCE         CREATE SEQUENCE             NO        
    RESOURCE         CREATE TRIGGER                 NO        
    RESOURCE         CREATE CLUSTER                 NO        
    RESOURCE         CREATE PROCEDURE             NO        
    RESOURCE         CREATE TYPE                     NO        
    CONNECT         CREATE SESSION                 NO        
    RESOURCE         CREATE OPERATOR             NO        
    RESOURCE         CREATE TABLE                     NO        
    RESOURCE         CREATE INDEXTYPE             NO        
   
    --< K 리그 >--
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    RESOURCE         CREATE SEQUENCE             NO
    RESOURCE         CREATE TRIGGER                 NO
    RESOURCE         CREATE CLUSTER                 NO
    RESOURCE         CREATE PROCEDURE             NO
    RESOURCE         CREATE TYPE                     NO
    CONNECT         CREATE SESSION                 NO
    RESOURCE         CREATE OPERATOR             NO
    RESOURCE         CREATE TABLE                     NO
    RESOURCE         CREATE INDEXTYPE             NO
    */


--< 관리자 >--
-- 구성 정보를 확인합니다.
DESC dba_roles;
    /*
    이름                              널?              유형           
    -------------------         --------        ------------ 
    ROLE                        NOT NULL    VARCHAR2(30) 
    PASSWORD_REQUIRED                   VARCHAR2(8)  
    AUTHENTICATION_TYPE                  VARCHAR2(11) 
    */
    
-- DBA에 기본적으로 있는 ROLE을 조회합니다.
SELECT role FROM dba_roles;
    /*
    ROLE
    -----------------------------------------
    CONNECT
    RESOURCE
    DBA
    SELECT_CATALOG_ROLE
    EXECUTE_CATALOG_ROLE
    DELETE_CATALOG_ROLE
    EXP_FULL_DATABASE
    IMP_FULL_DATABASE
    LOGSTDBY_ADMINISTRATOR
    DBFS_ROLE
    AQ_ADMINISTRATOR_ROLE
    AQ_USER_ROLE
    DATAPUMP_EXP_FULL_DATABASE
    DATAPUMP_IMP_FULL_DATABASE
    ADM_PARALLEL_EXECUTE_TASK
    GATHER_SYSTEM_STATISTICS
    XDB_WEBSERVICES_OVER_HTTP
    RECOVERY_CATALOG_OWNER
    SCHEDULER_ADMIN
    HS_ADMIN_SELECT_ROLE
    HS_ADMIN_EXECUTE_ROLE
    HS_ADMIN_ROLE
    OEM_ADVISOR
    OEM_MONITOR
    PLUSTRACE
    CTXAPP
    XDBADMIN
    XDB_SET_INVOKER
    AUTHENTICATEDUSER
    XDB_WEBSERVICES
    XDB_WEBSERVICES_WITH_PUBLIC
    APEX_ADMINISTRATOR_ROLE
    */

-- DBA에서 사용할 수 있는 권한을 조회합니다.
SELECT *
FROM role_sys_privs
WHERE role='DBA';
    -- 202개 출력
    
-- DEMO 사용자에게 CONNECT 롤을 부여합니다.
GRANT connect TO demo;
    /*
    Grant을(를) 성공했습니다.
    */


--< DEMO >--
SELECT * 
FROM session_roles;
    /*
    ROLE
    -----------
        -- 아무것도 조회되지 않습니다.
        -- ROLE은 사용자가 접속할 때 한 번 조회합니다.
    */

-- DEMO 사용자 계정의 접속을 해제 후 다시 접속하여 확인합니다.
SELECT * 
FROM session_roles;
    /*
    ROLE
    -----------
    CONNECT
    */


--< 관리자 >--
-- CONNECT ROLE에 데이터베이스 생성 권한을 부여합니다.
GRANT create database link TO connect;
    /*
    Grant을(를) 성공했습니다.
    */
    
  
--< 인사 관리, K 리그, DEMO >--  
-- 권한을 확인합니다. 
SELECT *
FROM role_sys_privs;
    /*
    --<인사 관리>--
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    RESOURCE         CREATE SEQUENCE             NO        
    RESOURCE         CREATE TRIGGER                 NO        
    RESOURCE         CREATE CLUSTER                 NO        
    RESOURCE         CREATE PROCEDURE             NO        
    RESOURCE         CREATE TYPE                     NO        
    CONNECT         CREATE SESSION                 NO        
    RESOURCE         CREATE OPERATOR             NO        
    CONNECT         CREATE DATABASE LINK         NO
    RESOURCE         CREATE TABLE                     NO        
    RESOURCE         CREATE INDEXTYPE             NO        
   
    --< K 리그 >--
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    RESOURCE         CREATE SEQUENCE             NO
    RESOURCE         CREATE TRIGGER                 NO
    RESOURCE         CREATE CLUSTER                 NO
    RESOURCE         CREATE PROCEDURE             NO
    RESOURCE         CREATE TYPE                     NO
    CONNECT         CREATE SESSION                 NO
    RESOURCE         CREATE OPERATOR             NO
    CONNECT         CREATE DATABASE LINK         NO
    RESOURCE         CREATE TABLE                     NO
    RESOURCE         CREATE INDEXTYPE             NO
   
--< DEMO >--
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    CONNECT         CREATE SESSION                 NO
    CONNECT         CREATE DATABASE LINK         NO
    */


--< 관리자 >--
-- RESOUCE ROLE에 데이터베이스 생성 권한을 부여합니다.
GRANT select any table TO resource;
    /*
    Grant을(를) 성공했습니다.
    */


--< 인사 관리, K 리그, DEMO >--
-- 권한을 확인합니다. 
SELECT *
FROM role_sys_privs;
    /*
    --<인사 관리>--
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    RESOURCE         CREATE SEQUENCE             NO        
    RESOURCE         CREATE TRIGGER                 NO        
    RESOURCE         CREATE CLUSTER                 NO        
    RESOURCE         CREATE PROCEDURE             NO        
    RESOURCE         CREATE TYPE                     NO        
    RESOURCE         SELECT ANY TABLE             NO
    CONNECT         CREATE SESSION                 NO        
    RESOURCE         CREATE OPERATOR             NO        
    CONNECT         CREATE DATABASE LINK         NO
    RESOURCE         CREATE TABLE                     NO        
    RESOURCE         CREATE INDEXTYPE             NO        
   
    --< K 리그 >--
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    RESOURCE         CREATE SEQUENCE             NO
    RESOURCE         CREATE TRIGGER                 NO
    RESOURCE         CREATE CLUSTER                 NO
    RESOURCE         CREATE PROCEDURE             NO
    RESOURCE         CREATE TYPE                     NO
    RESOURCE         SELECT ANY TABLE             NO
    CONNECT         CREATE SESSION                 NO
    RESOURCE         CREATE OPERATOR             NO
    CONNECT         CREATE DATABASE LINK         NO
    RESOURCE         CREATE TABLE                     NO
    RESOURCE         CREATE INDEXTYPE             NO
   
--< DEMO >--
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    CONNECT         CREATE SESSION                 NO
    CONNECT         CREATE DATABASE LINK         NO
        -- DEMO에는 RESOURCE 권한이 없으므로 조회되지 않습니다.
    */
    
    
--< 관리자 >--
-- 부여한 권한을 철회합니다.
REVOKE create database link FROM connect;
    /*
    Revoke을(를) 성공했습니다.
    */
    
-- 부여한 권한을 철회합니다.
REVOKE select any table FROM resource;
    /*
    Revoke을(를) 성공했습니다.
    */

-- ROLE을 생성합니다.
CREATE ROLE D1;
    /*
    Role D1이(가) 생성되었습니다.
    */
    
-- ROLE에 권한을 부여합니다.
GRANT create table, create view, create synonym
TO D1;
    /*
    Grant을(를) 성공했습니다.
    */

-- 생성한 ROLE을 DEMO에 부여합니다.
GRANT d1 TO demo;
    /*
    Grant을(를) 성공했습니다.
    */


--< DEMO >--
-- 권한을 확인합니다. 
SELECT *
FROM role_sys_privs;
    /*
    ROLE                 PRIVILEGE                 ADMIN_OPTION 
    ------------------------------------------------------------------
    D1                     CREATE SYNONYM             NO
    D1                     CREATE TABLE                     NO
    CONNECT         CREATE SESSION                 NO
    D1                     CREATE VIEW                     NO
    */

SELECT * 
FROM session_roles;
    /*
    ROLE
    -------------
    CONNECT
    D1
    */

SELECT * FROM employees;
    -- demo.employees를 조회합니다.

-- 인사관리의 테이블을 조회합니다.
SELECT * FROM hr.employees;
    /*
        <오류>------------------------------------------
        ORA-00942: table or view does not exist
        00942. 00000 -  "table or view does not exist"
        *Cause:    
        *Action:
        --------------------------------------------------
            -- DEMO가 HR의 테이블에 접근할 권한이 없습니다.
    */
    
   
    --<인사 관리>--
-- 테이블 조회 원한을 부여합니다.
GRANT select ON hr.employees TO demo;
    -- = GRANT select ON hr.employees TO D1;
    /*
    Grant을(를) 성공했습니다.
    */
   
--< DEMO >--
-- 테이블을 조회합니다.
SELECT last_name FROM hr.employees;
    /*
   LAST_NAME
   ---------------
    Kim
    Lee
    Raphealy
    King
    Kochhar
    De Haan
    Hunold
    Ernst
    Lorentz
    Mourgos
    Rajs
    Davies
    Matos
    Vargas
    Zlotkey
    Abel
    Taylor
    Grant
    Whalen
    Hartstein
    Fay
    */

-- 데이터를 수정합니다.
UPDATE hr.employees
SET salary=salary*1.1;
    /*
        <오류>-----------------------------------------------------------------------
        오류 보고 -
        SQL 오류: ORA-01031: insufficient privileges
        01031. 00000 -  "insufficient privileges"
        *Cause:    An attempt was made to perform a database operation without
                   the necessary privileges.
        *Action:   Ask your database administrator or designated security
                   administrator to grant you the necessary privileges
        -------------------------------------------------------------------------------
            -- 조회 권한만 부여했습니다.
    */
   
    --<인사 관리>--
-- 권한을 철회합니다.
REVOKE select ON hr.employees FROM demo;
    /*
    Revoke을(를) 성공했습니다.
    */
   
--< DEMO >--
-- 테이블을 조회합니다.
SELECT last_name FROM hr.employees;
    /*
        <오류>-----------------------------------------
        ORA-00942: table or view does not exist
        00942. 00000 -  "table or view does not exist"
        *Cause:    
        *Action:
        -------------------------------------------------
    */
   
    --<인사 관리>--
-- 90번 부서 직원을 제외한 직원의 정보를 뷰로 생성합니다.
CREATE OR REPLACE VIEW emp_list_vu
AS
SELECT employee_id emp_id, first_name || ' ' || last_name AS emp_name, email, job_id, department_id dept_id
FROM employees
WHERE department_id <> 90;
    /*
    View EMP_LIST_VU이(가) 생성되었습니다.
    */

-- 뷰를 조회합니다.
SELECT * FROM emp_list_vu;

-- 뷰에 접근할 수 있는 권한을 부여합니다.
GRANT select on hr.emp_list_vu TO demo;
    /*
    Grant을(를) 성공했습니다.
    */
   
--< DEMO >--
-- 뷰를 조회합니다.
SELECT * FROM hr.emp_list_vu;

    
-- 동의어를 지정합니다.
CREATE SYNONYM emp FOR hr.emp_list_vu;
    /*
    Synonym EMP이(가) 생성되었습니다.
    */

-- 동의어를 사용하여 뷰를 조회합니다.
SELECT * FROM emp;

    
    
   
    --<인사 관리>--
-- 뷰 접근 권한을 철회합니다.
REVOKE select on hr.emp_list_vu FROM demo;
    /*
    Revoke을(를) 성공했습니다.
    */
   
--< DEMO >--
-- 권한이 철회된 뷰에 대한 동의어가 삭제되었는지 확인합니다.
SELECT synonym_name FROM user_synonyms;
    /*
    SYNONYM_NAME
    --------------------
    EMP
        -- 동의어는 삭제되지 않았습니다.
    */
    
-- 동의어를 사용하여 뷰를 조회합니다.
SELECT * FROM emp;
    /*  
        <오류>------------------------------------------
        ORA-00942: table or view does not exist
        00942. 00000 -  "table or view does not exist"
        *Cause:    
        *Action:
        --------------------------------------------------
            -- 권한이 없어 동의어를 사용할 수 없습니다.
    */
 

    --<인사 관리>--
-- 뷰 접근 권한을 부여합니다.
GRANT select on hr.emp_list_vu TO demo;
    /*
    Grant을(를) 성공했습니다.
    */
 
-- 또 다른 뷰에 대한 접근 권한을 부여합니다.
GRANT select on hr.emp_dept_join_sum_vu TO demo;
   /*
   Grant을(를) 성공했습니다.
   */
  

-- DEMO 계정에 동의어 생성 권한을 부여합니다.
GRANT CREATE SYNONYM TO demo;
   /*
   Grant을(를) 성공했습니다.
   */

--< DEMO >--
-- 뷰를 조회합니다.
SELECT * FROM hr.emp_dept_join_sum_vu ;
        -- 테이블 접근 권한이 없어도 뷰에 접근할 수 있습니다.
   

   
-- 동의어를 지정합니다.
CREATE SYNONYM d_sum FOR hr.emp_dept_join_sum_vu;
    /*
    Synonym D_SUM이(가) 생성되었습니다.
    */


-- 동의어를 사용하여 뷰를 조회합니다.
SELECT * FROM d_sum ;

    
    

        -- PUBLIC
            -- 모든 사용자에게 엑세스 권한을 부여합니다.

    --<인사 관리>--
-- 부서 번호, 부서 이름, 부서 위치를 조회합니다.
SELECT d.department_id, d.department_name, l.city
FROM departments d JOIN locations l
ON (d.location_id = l.location_id);

    
-- 뷰를 생성합니다.
CREATE OR REPLACE VIEW dept
AS
SELECT d.department_id, d.department_name, l.city
FROM departments d JOIN locations l
ON (d.location_id = l.location_id);
    /*
    View DEPT이(가) 생성되었습니다.
    */
    
-- 모든 사용자가 접근할 수 있는 권한을 부여합니다.
GRANT select on hr.dept TO public;
    /*
    Grant을(를) 성공했습니다.
    */
    
--< DEMO, K 리그 >--
-- 뷰를 조회합니다.
SELECT * FROM hr.dept;

    
    
        -- PUBLIC SYNONYM
            -- PRIVATE SYNONYM : 기본적으로 사용자 본인만 사용할 수 있는 동의어입니다.
            -- 생성
                /*
                CREATE PUBLIC SYNONYM synonym FOR object;
                */
            -- 삭제
                /*
                DROP PUBLIC SYNONYM synonym;
                */
           
--< 관리자 >--
-- 인사 관리 사용자에게 공용 동의어 생성 권한을 부여합니다.
GRANT create public synonym TO hr;
    /*
    Grant을(를) 성공했습니다.
        -- 삭제 권한은 없습니다.
    */
   
    --<인사 관리>--
-- 공용 동의어를 생성합니다.
CREATE PUBLIC SYNONYM d FOR hr.dept;
    /*
    SYNONYM D이(가) 생성되었습니다.
    */

--< DEMO >--
-- 공용 동의어를 사용합니다.
SELECT * FROM d;

--< 관리자 >--
-- DEPT와 D의 요소를 조회합니다.
SELECT object_name, object_type, owner
FROM dba_objects
WHERE object_name IN ('DEPT', 'D');

-- DUAL에 관한 정보
SELECT object_name, object_type, owner
FROM dba_objects
WHERE object_name='DUAL';