230718 Oracle SQL 17 [객체권한, 권한관리, 복구 (Flashback)]

[7월 18일 점심시간 문제] 다음의 서브쿼리를 with 절로 변경하세요.

select  substr(address, 1, 3 ) 주소, count(*)
       from  emp17
       group  by  substr(address, 1, 3 )
       having    count(*) > (  select  avg( count(*) )
                                  from  emp17
                                  group  by  subsgtr(address, 1, 3 )   ) ;

⬇️

with address_cnt as ( select substr(address, 1,3) 주소, count(*) as cnt
                       from emp17
                       group  by  substr(address, 1,3) )
 select 주소, cnt
 from address_cnt
 where cnt > ( select avg(cnt)
                     from address_cnt ) ;

---

기출문제 모델링X 긴거X

복습

🟥 권한의 종류

• 시스템권한 : create, drop, truncate 와 같이 db에서 객체 (object) 에 대해서 행할 수 있는 권한들
  (-> 객체 : table, view, index, sequence, synonym)
  cf) select any table (객체권한처럼 보이지만 시스템 권한임)
• 객체권한 : 특정 데이터를 select, update, delete, merge 할 수 있는 권한들

문제 654. (객체권한) 내가 가지고 있는 유저 리스트가 뭐가 있는지 c## 으로 시작하는 유저이름을 조회하시오

select *
 from dba_users
 where username like 'C##%' ;

문제 655. (객체권한)
c##king2 라는 유저를 생성하고 connect 할 수 있는 권한만 부여하시오

create user c##king2
 identified by tiger;

 grant connect to c##king2;

문제 656. (객체권한)
c##king2 유저에게 c##scott 유저의 emp 테이블을
select, update, delete, insert, merge 할 수 있는 권한을 부여하시오

grant select, update, delete, insert, alter on emp to c##king2
or
grant all on emp to c##king2;

✔️ merge 는 insert + update 라서 저거 두개 있으면 따로 안넣어도됨

문제 657. (객체권한)
c##king2 유저로 접속해서 c##scott 유저의 emp 테이블을 select 하시오

connect c##king2/tiger

select * from c##scott.emp;

✔️public synonym 등록해놔야 그냥 emp 만 써도 조회된다.

문제 658. (객체권한)
c##scott 유저에서 c##king2 유저에게 c##scott 유저의 dept 테이블을
select, delete 할 수 있는 권한을 주시오

grant select, delete on dept to c##king2;

문제 659. (객체권한)
c##king2 유저로 접속해서 c##scott 유저의 dept테이블의
부서번호 10번의 부서위치를 seoul 로 변경하시오

update dept
 set loc='seoul'
 where deptno = 10;

문제 660. (객체권한)
c##king2 유저에서 c##scott의 dept 테이블을 delete 하시오

delect from dept;

모든 계정에 권한 부여 (public)

문제 661. (객체권한)
c##scott 유저에서 salgrade 테이블에 대해서 select 할 수 있는 권한을 db에 있는 모든 유저들에게 다 부여하시오

grant select on salgrade to public;

✔️ 모든 계정들에게 권한이 들어감!

✔️ public 으로 전체 주는건 주의해서 사용해야함 (오라클에서 권장 X)
현업에서는 public 사용을 자제하고 계정별로 개별적으로 부여하는게 바람직하다.

update 권한 문법

✅ 모든 컬럼에 대해서 update 할 수 있는 권한

grant update  on employees  to demo;

✅ 특정컬럼만 선택해서 update 할 수 있게

grant update (department_name, location_id) -> 컬럼명
on dept -> 테이블이름
to demo, manager; -> role 이름, 유저이름

---

with grant option

➡️ 권한을 줄 수 있는 권한

c##scott                    c##king3                   c##king4

                                                     select * from c##scott.emp;
                                                     
create user c##king3 identified by tiger;
create user c##king4 identified by tiger;
grant connect to c##king3, c##king4;
grant select on emp to c##king3 with grant option;
➡️ king3 로 접속한다음에 king4 한테 select 권한 부여가능


                          select * from c##scott.emp;
                          grant select on c##scott.emp to c##king4;

🔎 c##scott 유저가 c##king3에게 준 권한을 회수한다면 king4는?

revoke select on emp from c##king3

-> 제일 처음 준 사람한테만 (king3) 회수해도 나머지 다 같이 (king4) 회수된다.

❗with grant option 을 써서 권한을 부여하고,
내가 권한을 줬던 계정의 권한을 회수하면 나머지 계정도 다같이 회수가 되는 것

---

내가 가지고 있는 권한의 종류 2가지 확인하는 방법

✔️ 테이블권한 먼저주기
c##scott 유저에서 c##king4 유저에게 create table 권한을 줍니다.

grant create table to c##king4;

grant select on emp to c##king4;
grant update on emp to c##king4;

시스템 권한 확인하는 방법

select * from session_privs;

객체 권한 확인하는 방법 ★

❗user_tab_privs_recd 기억해놓기

col table_name for a10
col grantor for a10
col privilege for a15
(가로 길이 정해주는?)

select table_name, grantor, privilege
 from user_tab_privs_recd;

✅ user_xxx : 내가 소유한 객체에 대한 정보
✅ all_xxx : 내가 소유한 객체 + 권한을 받은 객체에 대한 정보

문제 662. (객체권한)
dba 인 c##scott 이 db감사를 준비해야해서 db에 있는 유저들에 대해서 어떠한 객체 권한이 부여되었는지 엑셀로 정리해서 뽑아내야하는 업무가 생겼습니다. 어떻게 해야 효율적으로 할 수 있을까요

• 고급dba -> SQL+파이썬+PL/SQL 사용해서 자동으로 뽑아냅니다.
• 초급DBA -> SQL을 단발성으로 쿼리해서 하나씩 결과를 뽑아서 복사해서 엑셀에 수기로 입력

➡️ 개발자들이 dba 인 나에게 자주 물어보는 질문들이 뭔지 평상시 노트하고,
관련 쿼리를 목차로 만들어서 정리해놓고 파이썬, 리눅스 쉘, PL/SQL 등을 이용해서 자동화 해놓고 빠르게 알려줄 수 있도록 합니다.

권한 취소 하는 방법 (revoke)

revoke

문법

revoke  select on emp from c##king4;
          객체권한             유저명
revoke  create table from c##king4;
          시스템권한           유저명

문제 663. c##scott 유저에서 c##king4 유저에게 부여된 객체 권한을 확인하고 전부 revoke 시키시오
(sql developer 에서 c##scott 유저로 조회)

select table_name, grantor, grantee, privilege
 from all_tab_privs_made
 where grantor='C##SCOTT';

➡️ c##scott 유저가 부여한 모든 권한들이 나옴

select table_name, grantor, grantee, privilege
 from all_tab_privs_made
 where grantee='C##KING4';

➡️ KING4 에 부여된 객체권한만 나옴

revoke select on emp from c##king4;
revoke update on emp from c##king4;

revoke 완료~

문제 664. c##scott 유저가 public 으로 부여한 객체 권한들을 전부 revoke 시키시오

select table_name, grantor, grantee, privilege
 from all_tab_privs_made
 where grantee='PUBLIC';

 revoke select on salgrade from public;

✔️ 데이터 보안을 위해서 public 으로 준 권한들은 회수하는게 바람직합니다.
(공공기관 dba 는 필수. 네이버카카오토스당근쿠팡등은 보안 ISO 인증을 받으려면 이런 권한 관리에 대한 서류를 제출해야 하는데 이떄 위의 작업이 필요합니다.)

---

권한 관리 정리

1. DCL문 : grant, revoke
2. 유저생성
3. 롤(role) 생성
4. 권한의 종류 : 1) 시스템 권한 2) 객체 권한
5. 부여한 권한을 확인하는 데이터 딕셔너리(사전) : 1. sesstion_privs 2. user_tab_privs_made
6. 권한 회수 (revoke)

---

관련 OCP 문제.

grant all on emp, dept to c##king2;;

❌

grant all on emp to c##king2;
grant all on dept to c##king2;

이렇게 따로 줘야! = 답d

• grant create table to user1, user2 이렇게 두명 동시에 주는건 되나방

시스템 권한을 주는데 맞는거 고르기

A. on 은 안넣어도됨 ❌
B. alter table 은 객체권한이라 ❌
C. 유저 두명에게 동시에 줄수 있다 ⭕
D. all 아니고 public 사용임 ❌

---

099. 실수로 지운 데이터 복구하기 1(FLASHBACK QUERY)

SQL200제 (257p)

FLASHBACK QUERY
특정 테이블의 과거의 데이터를 확인할 때 사용하는 쿼리문 (특정 시점의 데이터)

예제 1. 현재 시간을 확인합니다.

select systimestamp  from dual;

예제 2. emp 테이블을 전부 delete 하시오

delete from emp;

commit;

커밋까지 해벌임

예제 3. emp 테이블의 데이터가 지워지기전 10분전의 상태를 확인하시오

select *
 from emp
 as of timestamp (to_timestamp('2023/07/18:14:00:00','RRRR/MM/DD:HH24:MI:SS');

✔️ 시간은 2시가 아니라 14시로 적어야하고, HH24 로 형태 적어줘야한다.

select *
 from emp
 as of timestamp (systimestamp - interval '10' minute);

✔️ 밑에도 확인은 가능하나,시간은 계속 변하기 때문에 명확한 시간을 짚을 수 없다.

✅ 시간을 통해 테이블 복구

예제 4. emp 테이블을 '2023/07/18:14:00:00' 시간으로 복구하시오

• 먼저, emp 테이블을 flachback 이 가능한 상태로 변경한다.

alter  table emp enable row movement ;

• emp 테이블을 2023/07/18:14:00:00 으로 flashback 시킵니다.

flashback table emp to timestamp to_timestamp('2023/07/18:14:00:00',
                                               'RRRR/MM/DD:HH24:MI:SS');

문제 665. DEPT 테이블에 dname 컬럼을 전부 null 로 변경하고 commit 하시오

update dept
 set dname=null;

 commit;

문제 666. dept 테이블에 dname 에 data 가 있었던 시점으로해서 flashback query 를 하시오

✔️ 먼저 n분전 데이터 검색해본다

select *
from dept
as of timestamp to_timestamp('23/07/18:14:10:27','RR/MM/DD:HH24:MI:SS');

문제 607. dept 테이블을 '23/07/18:14:10:27' 로 flashback table 시키시오

alter  table dept enable row movement ;

➡️ enable 먼저 시켜주고,

select table_name, row_movement
 from user_tables
 where table_name='DEPT';

확인!

flashback table dept to timestamp to_timestamp('23/07/18:14:10:27',
                                              'RR/MM/DD:HH24:MI:SS');

➡️ 복구한다!

✔️ 현업에서 사용할떄는 복구하기 전에 우녕ㅇ팀에게 특정시간에 데이터를 먼저 보여주고 이 시간으로 복구해도 되겠는지 의견을 물어보고 복구 하세여

✅ flashback table 전에 백업을 미리 해놓습니다.

create table dept_backup
as
 select *
 from dept
 where 시간..

문제 608. emp테이블에서 KING 의 월급을 0으로 변경하시오
그리고 commit 하시오

update emp
 set sal = 0
 where ename='KING';

 commit;

문제 609. 현재 시간에서 5분전에 KING의 월급을 확인하시오

select systimestamp from dual;

23/07/18 14:30:24.591000000 +09:00

select ename, sal
 from emp
 as of timestamp to_timestamp('23/07/18 14:24:57','RR/MM/DD HH24:MI:SS')
 where ename='KING';

5분전의 킹의 월급이 나온다

예제2.
emp 테이블의 sal 에 인덱스를 생성하시오

create index emp_sal on emp(sal);

예제3.
emp 테이블을 전부 지우고 commit 합니다

delete from emp;
commit;

❓테이블이 지워진줄 모르고 다른 유저들이 insert 를 한다면..?

insert 는 되긴하나, 실제로 들어가거나 확인되지는 않는다.

예제4.
현재 시점에서 5분전으로 emp 테이블을 flashback 하시오

select  systimestamp from dual;

23/07/18 14:55:25.911000000 +09:00

alter table emp enable row movement;

select *
 from emp
 as of timestamp to_timestamp('23/07/18 14:47:25','RR/MM/DD HH24:MI:SS');

떄는 insert 된 jane 이 뜨지 않아서, 일단 백업은 해둔다. (52분껄로)

create table emp_backup7
as
 select *
 from emp
 as of timestamp to_timestamp('23/07/18 14:52:25','RR/MM/DD HH24:MI:SS');

⬇️ 그리고 47분 테이블로 flashback 시킨다.

select *
 from emp
 as of timestamp to_timestamp('23/07/18 14:47:25','RR/MM/DD HH24:MI:SS');

예제 6.
백업밥ㄷ은 emp_backup7 에 있는 데이터를 emp 테이블에 입력하시오

insert into emp
 select *
 from emp_backup7

문제 610.
위와같이 flashback table 로 복구했는데 emp 테이블에 걸려있는 인덱스는 어떻게 되었는지 확인하시오

select index_name
 from user_indexes
 where table_name='EMP';

⬇️ STATUS 확인

select index_name, status
 from user_indexes
 where table_name='EMP';

101. 실수로 지운 데이터 복구하기 3(FLASHBACK DROP)

테이블을 DROP 했을 때 복구하는 방법입니다.

예제 1.
EMP 테이블에 걸려있는 인덱스를 확인하세요

select index_name, status
 from user_indexes
 where table_name='EMP';

예제 2. emp 테이블을 drop 하시오

drop table emp;

예제3. emp 테이블에 걸려있는 인덱스를 다시 확인하시오
-> 사라졌다.

✔️ 테이블을 drop 하면 index 도 삭제된다. (+제약도)

예제 4. 휴지통 속에 emp 테이블이 있는지 확인합니다.

show recyclebin;
혹은
select * from user_recyclebin;

휴지통 속 테이블 복구

예제 5. 휴지통 속에 있는 emp 테이블을 복구하시오

flashback table emp to before drop;

복구한 테이블의 인덱스 복구하기

예제 6. emp테이블에 걸려있던 인덱스를 확인하시오

select index_name
 from user_indexes
 where table_name='EMP';

✔️ 휴지통속에서 빠져나온 테이블의 인덱스는 이름이 이상해진다.
어느 컬럼의 인덱스인지 알수가 없다.

예제 7. 위의 이상해진 인덱스가 어느 컬럼에 걸린 인덱스인지 확인합니다.

select index_name, column_name
 from user_ind_columns
 where table_name='EMP';

컬럼이름 사전을 통해 sal 인덱스라는 것을 알 수 있다!

예제 8. 인덱스의 이름을 원래의 이름으로 변경하시오

alter index "BIN$icD6Vnx5QZCBddf7+yN6FQ==$0" rename to emp_sal;

✔️ 만약 휴지통에서 테이블을 복구했다면, 테이블과 관련한 인덱스 이름들을 위와같이 변경해주어야 합니다.

문제 611. 휴지통을 비우시오

purge recyclebin;

show recyclebin;

✅ 휴지통 안거치고 바로 지우기 (잘 안씀)

문제 612. 이 상태에서 emp 테이블을 drop 하시오
purge 옵션 사용 : 휴지통속에 안들어가고 아예 지워지는것

drop table emp purge;

• 진짜 쓸데없고 지워도 되는 아주 큰 대용량 테이블을 drop 할때 사용

102. 실수로 지운 데이터 복구하기 4(FLASHBACK VERSION QUERY)

❗특정테이블이 그동안 어떻게 변경되어 왔는지 그 이력정보를 확인하는 쿼리문

1. 먼저 현재 시간을 확인합니다.

   select systimestamp from dual;

   -> 23/07/18 15:50:58.254000000 +09:00

2. 현재 scn 번호를 확인합니다.
   scn 번호란 ? system change number 의 약자로, commit 할 때 부여하는 번호

   select current_scn
     from v$database;

   실행할때마다 바뀜

3. scn 가지고 시간 확인하기 / 시간 가지고 scn 확인하기

   select scn_to_timestamp('3180430')
    from dual;

   

   select timestamp_to_scn('23/07/18 15:50:58')
    from dual;

   

✔️ 아까 시간 가지고 테이블 복구 했었는데, scn 번호를 가지고도 복구할 수 있다.
(복구할 때는 scn 번호로 하는게 더 복구가 잘된다.)

4. emp 테이블의 KING 의 월급을 9000으로 변경하시오

   update emp
    set sal = 9000
    where ename='KING';
   
    commit;

5. emp 테이블의 KING 의 부서번호를 30번으로 수정하시오

    update emp
     set deptno = 30
     where ename='KING';

6. emp 테이블의 KING 의 데이터를 지우시오

   delete from emp
    where ename='KING';

데이터 이력정보 확인하기

7. emp 테이블의 KING 의 데이터가 그동안 어떻게 변경되어 왔는지 그 이력정보를 확인하시오

    select ename, sal, deptno, versions_starttime, versions_endtime, versions_operation
     from emp
     versions between timestamp to_timestamp('23/07/18 15:50:58',
                                             'RR/MM/DD HH24:MI:SS' )
              and maxvalue
     where ename='KING';

   
   23/07/18 15:57:53 로 가보면 KING 의 원래 데이터를 볼 수 있게따!

8. 맨 아래에 나온 시간에 10초전 시간의 KING 의 데이터를 flashback query 로 확인하시오

   select * from emp
    as of timestamp to_timestamp('23/07/18 15:57:43' ,
                                  'RR/MM/DD HH24:MI:SS' )
    where ename='KING';

   
   데이터 확인 가능

문제 613. '23/07/18 15:57:43' 으로 emp 테이블을 flashback table 하시오

alter table emp enable row movement;

flashback table emp to timestamp to_timestamp('23/07/18 15:57:43','RR/MM/DD HH24:MI:SS');

---

103. 실수로 지운 데이터 복구하기 5(FLASHBACK TRANSACTION QUERY)

❗특정 테이블에 대해서 그동안 변경해왔던 이력정보를 가지고
다시 과거로 되돌리기 위한 DML문 을 보여주는 쿼리문

SQL200제 책 265p 참고.
(실습이 어려움 - DB내리고, 아카이브모드로 바꾸고.. 등등)

이 작업은 DB 관리 단원에서 flashback database 배울 때 진행하도록 하겠습니다.

✏️ 책 내용 참고해서 보기!!

OCP 문제.

a. 트리거 : emp 변경되면 emp_backup 도 변경되게 하는 옵션(?)같은거. drop ❌
b. 데이터 지워지면 공간 유지 되지 않는다. ❌
c. truncate 롤백 ㄴㄴ ❌
d. truncate 해도 제약은 유지된다. ⭕
e. 데이터지워지고 공간회수 해 가지만, index 는 남아있다. ⭕
f. truncate 하면 flashback table 되지 않는다. ❌

---

문제 613. (SQL 마지막 문제)
emp 테이블을 truncate 하고 emp 테이블을 10분전으로 flashback table 해보시오

truncate table emp;

alter table emp enable row movement;

flashback table emp to timestamp to_timestamp('23/07/18 16:25:43','RR/MM/DD HH24:MI:SS');