#13-2. 13일차 퀴즈에 대한 문제풀이

1. hr 소유의 모든 테이블에 대해 scott 계정에서 조회 가능하도록 권한부여
    system 계정에서 아래 뷰 조회 시 hr 소유의 모든 테이블이름 조회 가능

select *
  from dba_tables
 where owner = 'HR';

[내 답안]

 

SELECT * FROM HR.REGIONS  TO SCOTT ;
SELECT * FROM HR. COUNTRIES  TO SCOTT ;
SELECT * FROM HR. LOCATIONS  TO SCOTT ;
SELECT * FROM HR. DEPARTMENTS  TO SCOTT ;
SELECT * FROM HR. JOBS  TO SCOTT ;
SELECT * FROM HR. EMPLOYEES  TO SCOTT ;

 

[문제풀이]

 

-- 불필요 테이블 제거(HR 소유 테이블)
-- 아래 실행 결과를 복사/붙여넣기 해서 전체 실행
select table_name, 'drop table HR.'||table_name||' cascade constraints;'
  from dba_tables
 where owner = 'HR'
   and table_name not in ('LOCATIONS','COUNTRIES','JOB_HISTORY',
                          'REGIONS','DEPARTMENTS','EMPLOYEES','JOBS');
                          
                         
SELECT 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO SCOTT;'
  FROM DBA_TABLES
 WHERE OWNER = 'HR';

GRANT SELECT ON HR.LOCATIONS TO SCOTT;
GRANT SELECT ON HR.COUNTRIES TO SCOTT;
GRANT SELECT ON HR.JOB_HISTORY TO SCOTT;
GRANT SELECT ON HR.REGIONS TO SCOTT;
GRANT SELECT ON HR.DEPARTMENTS TO SCOTT;
GRANT SELECT ON HR.EMPLOYEES TO SCOTT;
GRANT SELECT ON HR.JOBS TO SCOTT;

 

 

2. role_sel_scott, role_cud_scott 롤 생성 후
role_sel_scott 롤에 scott 소유 모든 테이블에 대한 조회 권한을,
role_cud_scott 롤에 scott 소유 모든 테이블에 대한 insert, update, delete 권한을 담고
각 롤을 hr 유저에게 부여

[내 답안]

 

모두 타이핑 진행하였음

 

[문제풀이]

 

-- 1. ROLE 생성
CREATE ROLE ROLE_SEL_SCOTT;
CREATE ROLE ROLE_CUD_SCOTT;

select *
  from dba_tables
 where owner = 'SCOTT';
 
--2. 롤에 권한부여 명령어 생성
SELECT TABLE_NAME, 'GRANT SELECT ON SCOTT.'||TABLE_NAME||' TO ROLE_SEL_SCOTT;'
  FROM DBA_TABLES
 WHERE OWNER = 'SCOTT';

SELECT TABLE_NAME, 'GRANT INSERT, UPDATE, DELETE ON SCOTT.'||TABLE_NAME||' TO ROLE_CUD_SCOTT;'
  FROM DBA_TABLES
 WHERE OWNER = 'SCOTT';

--3. HR에게 롤 부여
GRANT ROLE_SEL_SCOTT TO HR;
GRANT ROLE_CUD_SCOTT TO HR;

3. dept2 테이블에 대해 '영업4팀'에서부터 상위부서를 찾아가는 계층형 질의절 작성

[내 답안]

 

SELECT * 
  FROM DEPT2;
  
SELECT D.*, LEVEL
  FROM DEPT2 D
 START WITH D.DCODE = 1011 AND D.PDEPT = 1007
 CONNECT BY PRIOR D.PDEPT = D.DCODE; 

 

 

[문제풀이]

 

SELECT D.*, LEVEL
  FROM DEPT2 D 
 START WITH DNAME = '영업4팀'
 CONNECT BY PRIOR PDEPT = DCODE;

 

4. emp 테이블 내 직원들에 대한 조직도를 아래와 같이 작성

<결과>
KING
 -CLARK
   -MILLER
 -JONES
   -FORD
     -SMITH
   -SCOTT
     -ADAMS
 -BLAKE
   -ALLEN
   -JAMES
   -MARTIN
   -TURNER
   -WARD

[내 답안]

 

SELECT * 
  FROM EMP;
  
SELECT E.*, LEVEL
  FROM EMP E 
 START WITH E.MGR IS NULL
 CONNECT BY PRIOR E.EMPNO = E.MGR
 ORDER SIBLINGS BY DEPTNO, ENAME;  
  
SELECT E.*, LEVEL,
       CASE WHEN LEVEL = 1 THEN ENAME
            WHEN LEVEL = 2 THEN ' -'||ENAME
            WHEN LEVEL = 3 THEN '   -'||ENAME
                           ELSE '     -'||ENAME
       END AS 정렬결과
  FROM EMP E 
 START WITH E.MGR IS NULL
 CONNECT BY PRIOR E.EMPNO = E.MGR
 ORDER SIBLINGS BY DEPTNO, ENAME;

 

 

[문제풀이]

 

SELECT E.*, LEVEL, LPAD('-',(LEVEL-1)*2,' ')||ENAME
  FROM EMP E
 START WITH MGR IS NULL
 CONNECT BY PRIOR EMPNO = MGR
 ORDER SIBLINGS BY DEPTNO, ENAME;