[Oracle SQL]21일차_23.07.18

망구씨·2023년 7월 18일
FLASHBACKFLASHBACK DROPFLASHBACK QUERYFLASHBACK TABLEFLASHBACK VERSION QUERYpurgerevoke객체권한권한관리권한취소시스템권한오라클 휴지통 비우기
0

Oracle SQL

목록 보기
21/21
post-thumbnail

오늘의 TIL

  1. 객체권한
  2. with grant option
  3. 내가 가지고있는 권한 확인하기(시스템,객체)
  4. 권한 취소하기(revoke)
  5. 실수로 지운 데이터 복구하기 1(FLASHBACK QUERY)
  6. 실수로 지운 데이터 복구하기 2(FLASHBACK TABLE)
  7. 실수로 지운 데이터 복구하기 3(FLASHBACK DROP)
  8. 휴지통 비우기(purge)
  9. 실수로 지운 데이터 복구하기 4(FLASHBACK VERSION QUERY)
  10. 실수로 지운 데이터 복구하기 5(FLASHBACK TRANSACTION QUERY) - 나중에 제대로

[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_count as (select substr(address, 1, 3) address , count(*) as count
                          from emp17
                          group  by  substr(address, 1, 3 ))
   select address, count    
    from address_count
    where count > (select avg(count) from address_count);

복습

권한의 종류

  1. 시스템 권한 : create, drop, truncate와 같이 db에서 객체(object)에 대해서 행할 수 있는 권한들
  2. 객체 권한 : 특정 데이터를 select, update, delete, merge 할 수 있는 권한들
    ✔️ 객체(object) table, view, index, sequence, synonym이 있다.

문제 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 할 수 있는 권한을 부여하시오 !

1. grant select, update, delete, insert, alter on emp to c##king2;

2. grant all on emp to c##king2;

문제 657. [객체권한] c##king2 유저로 접속해서 c##scott유저의 emp 테이블을 select 하기

select * from c##scott.emp;

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

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

문제 659. [객체권한] c##king2 유저로 접속해서 스캇 유저의 dept테이블의 부서번호 10번의 부서위치를 서울로 변경하시오

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

✅update권한을 부여하지 않았기때문에 에러가 납니다.

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

delete from dept; // (c##scott.dept)

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

dba            개발팀1          개발팀2         개발팀3         개발팀4
               (카드)           (보험)          (예금)         (대출)
공통테이블       c##king2         c##allen        c##james      c##martin
grant select on salgrade to public;


❗public은 되도록 사용하지 말자. 보안상 주의해야함!
그냥 이런 방법이 있다 라는 것만 알고있자. 현업에서는 계정별로 개별적으로 부여하는것이 바람직하다.

권한 부여 참고(특정 컬럼만)


아래처럼 모든 컬럼에 대해서 업데이트할 수 있는 권한을 줄 수 있지만

grant update on dept to c##king2;

특정 컬럼만 업데이트할 수 있는 권한을 부여할 수도 있다.

grant update(dname, loc) on dept to c##king2;

with grant option

c##scott             
  ↓
grant select on emp to c##king3; // with grant option 붙여야함
c##king3             
  ↓
  select * from c##scott.emp;
  grant select on c##scott.emp to c##king4;

king3이 king4에게 권한부여하려고 하면 이거 안된다. grant 할 수 있는 권한을 scott한테 못받았기 때문!

✏️ 실습 !


위처럼 scott 계정에서 king3에게 king4에게 권한을 부여할 수 있도록 권한부여를 했다.

💡king4가 emp테이블을 select 할 수 있도록 king3이 권한을 부여했다.(scott한테 권한부여 권한을 부여받았기 때문)


💡 king4에서 select가 잘 되는 것을 확인!

❓만약 scott 계정에서 king3에게 부여했던 권한을 revoke(회수)하면, c##king4 에서는 emp테이블이 보일까?

  1. scott에서 king3에 부여했던 권한 회수
  2. king3계정에서는 안보이고
  3. king4에서도 안보인다!

    ✅ with grant option을 써서 권한을 부여하면, 내가 처음 권한을 줬던 계정의 권한을 회수하게된다면 나머지 계정도 다 같이 회수가 된다.

내가 가지고있는 권한 확인하기(시스템,객체)

✏️ 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; // 객체권한

1. 내가 가지고있는 시스템 권한 확인하기

select * from session_privs;

2. 내가 가지고있는 객체 권한 확인하기

select * from session_privs;


✅ 위 col~~~ for a어쩌구는 그냥 예쁘게 보려고 설정해준 것

위는 그냥 grantor 다시 설정해준 것.(참고만) 아무튼 권한은 이렇게 조회할 수 있다!! 개발자들이 이계정이 가지고있는 권한이 무엇인지, 공공기관 같은 경우는 감사때 권한 리스트를 줘야할 수 있다. 이 쿼리문장 잘 기억하기!

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

고급 dba --> SQL + 파이썬 + PL/SQL 사용해서 자동으로 뽑아냅니다.

초급 dba --> SQL 을 단발성으로 쿼리해서 하나씩 결과를 뽑아서 복사해서
엑셀에 수기로 입력해서 고생고생 합니다.
✅

권한 취소하기(revoke)

문법

revoke select on emp from c##king4;

select on emp - 객체권한
c##king4 - 유저명

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

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

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

revoke하기 !

revoke select on emp from c##king4; // select , update 한번에 써도됨
revoke update on emp from c##king4;

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

select grantor, grantee, table_name, 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. 권한의 종류 : 시스템권한, 객체권한
  5. 부여한 권한을 확인하는 데이터 딕셔너리 : session_privs, user_tab_privs_made

관련 ocp 문제1

B - insert 어쩌고 해도되는데 all on emp .. 이런식 사용 가능
정답 - D

위처럼 emp, dept처럼 따로따로 하라는 말

관련 ocp 문제2
A - ON은 객체권한에 붙인다.
B - ALTER는 객체권한
D - 모든 계정에 다 주고싶으면 ALL이 아닌 PUBLIC !
정답 - C (유저 두명에게 동시에 줄 수 있다.)

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

오라클 영문 교재 FUN2 --> sg1 --> p.183 / 교재 SQL200제 (p.257)

💡특정 테이블의 과거 데이터를 확인할 때 사용하는 쿼리문

예제1. 현재 시간 확인하기

select systimestamp from dual;

예제2. emp 테이블 전부 delete gkrl

delete from emp;
 commit;

예제3. emp테이블이 데이터가 지워지기 전에 10분전 상태를 확인하기(flashback query)

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

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

✅ 위 방법이 더 정확하다.
✅ 14시라고 해야한다. 02시 하면 새벽 2시의 데이터를 보는 것

예제4. emp table을 2023/07/18:14:00:00 시간으로 복구하기
(emp 테이블을 플래시백이 가능한 상태로 변경해놓기

1. 플래시백이 가능하도록 설정하기

alter tale emp enable row movement;

2. 2023/07/18:14:00:00으로 클래시백 시키기.

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

문제 605.dept테이블에 dname 컬럼을 전부 널로 변경하고, 커밋하기

update dept
  set dname = null;

commit;

문제 606. dept테이블 dname 에 data가 있었던(null이 아니던) 시점으로 해서 flashback query를 하기

1. 지금 현재 시간 확인해보고, 되돌릴 시간 정하기(같이 의논하여)

select systimestamp from dual;

2. 해당 시간에 데이터가 어떻게 되어있었나 확인

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

3. flashback이 가능한 상태로 만들어주기

alter table dept enable row movement;


❗원래는 enable이 아니고 디스에이블이 디폴트이다. 이것을 바꿔준 것 !

4. flashback 하기

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

✅ 현업에서 사용할 때, 복구하기 전에 운영팀에게 특정 시간에 데이터를 먼저 보여주고 이 시간으로 복구해도 괜찮을지 의견을 물어본 후 복구해야한다.

💡 tip! 혹시 flashback이 실패할 수 있다. 이럴경우 다시 현재로 돌아올 수 없으니, flashback 결과로 백업을 미리 해놓는다. 

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

문제 608. emp테이블에서 KING의 월급을 0으로 변경 후 커밋!

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

commit;

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

1. select systimestamp from dual; // 현재 시간 확인
2. 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분전 KING의 월급 확인

실수로 지운 데이터 복구하기 2(FLASHBACK TABLE)

✔️ 테이블을 특정 시점으로 되돌리는 복구기능!

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

create index emp_sal on emp(sal);

예제3. emp 테이블을 전부 지우고 commit!

delete from emp ;
commit;

만약, 내가 실수로 emp테이블을 지운 상태에서 다른사람들이 이 사실을 모르고 insert을 하면 ?!

(이렇게 insert는 잘 되고, update는 안될것이다..!)

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

select systimestamp from dual;
SELECT *
    FROM emp
    AS OF TIMESTAMP to_timestamp('23/07/18 14:48:57', 'RR/MM/DD    HH24:MI:SS');


  • 48분은 내가 삭제하기 전, 53분은 다른사람이 'jane'데이터 넣은 시점 -> 내가 결정해야한다. 의논 !!! 해야하고, 날라간 데이터는 따로 insert를 해주어야 한다. 
create table emp_backup7 // jane 데이터 들어있는거 백업하고
as
 SELECT *
 FROM emp
 AS OF TIMESTAMP to_timestamp('23/07/18 14:53:57', 'RR/MM/DD HH24:MI:SS');
flashback table emp to timestamp to_timestamp('23/07/18 14:48:57', 'RR/MM/DD HH24:MI:SS'); 
//flashback 해서 원래 데이터 복구 후에
insert into emp
 select *
 from emp_backup7; // 아까 jane들어있는 백업테이블을 insert 한다.
alter table EMP enable row movement;

문제 610. 위와같이 플레시백 테이블로 복구했는데 emp테이블에 걸려있는 인덱스는 어떻게 되었는지 확인 -> 잘 나온다 !

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

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

✔️ 테이블을 drop했을 때 복구하는 방법
예제1. emp 테이블의 걸려있는 인덱스 확인!

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

1. show recyclebin;
2. select * from user_recyclebin;

휴지통속에 있는 테이블 복구

예제 5. 휴지통속에 있는 emp테이블을 복구 (가장 최근의 emp)

 flashback table emp to before drop;

예제 6. emp 테이블에 걸려있는 인덱스 확인해보기

 select index_name, status
  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$d3cmCGPCSU2bWdLYzzA9vQ==$0" rename to emp_sal;

✅ 더블 쿼테이션 꼭 두르기 !

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


✅ 다시 이름이 emp_sal로 바뀌었다.

휴지통 비우기(purge)

✏️ 정리
만약 휴지통에서 테이블을 복구했다면, 테이블과 관련한 인덱스 이름들을 위와같이 변경해줘야 한다.

문제 611. 휴지통 비우기

purge recyclebin;
show recyclebin;

문제 612. 이 상태에서 emp 테이블을 drop하는데 아래와같이 purge 옵션을 써보기

drop table emp purge;

✅ 이렇게하면 휴지통에 들어가지 않는다!
-> 진짜 지워도 되는 아주 큰 대용량 테이블을 drop할 때 위처럼 하면 휴지통에 들어가지 않고 한번에 드롭된다.

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

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

  • demp, dept 초기화하기

  1. 먼저 현재 시간을 확인하기

    select systimestamp from dual;

  2. 현재 scn번호를 확인하기
    ✏️ scn번호란, system change number의 약자로 commit할 때 부여하는 번호

    select current_scn
  from v$database;


    위 번호는 계속 바뀐다. 우리는 커밋을 하지 않지만 내부적으로 계속 뭔가를 하고있음..

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

    select scn_to_timestamp('3106669')
  from dual;

    select timestamp_to_scn('23/07/18 15:52:42')
  from dual;

  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';

  commit;

  6. emp 테이블의 KING의 데이터 지우기

    delete from emp 
  where ename = 'KING';

  commit;

  7. emp테이블의 KING의 데이터가 그동안 어떻게 변경되어왔는지 이력정보를 확인하기 (SQL200제 p.263)

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


    맨 처음(아래)에 나온 시간의 전 시간의 10초전 시간에 KING의 데이터를 flashback query
    (23/07/18 15:57:13)

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


    ❗ 맨 처음의 KING데이터가 잘 나온다!!

문제 613. 위 시간으로 emp 테이블을 플래시백 시키기

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


✅ 잘 복구가 되었다 !

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

✔️ 특정 테이블에 대해서 그동안 변경해왔던 이력정보를 가지고 다시 과거로 되돌리기 위한 DML문을 보여주는 쿼리문
💡책에 alter database open 이라는 명령어가 빠져있다. 추가로 써주기!!(알아두기)

아카이브 모드로 변경해야하는데, 이작업이 지금하기에는 조금 애매해서 DB관리 단원에서 flashback database 배울 때 진행할 예정!!

(OCP문제)

A - 트리거 해도 남아있는다(오답)
B - 공간유지 안된다. 구조만 남는다(오답)
C - rollback 안된다.
D - 테이블과 관련된 제약들은 유지된다(맞다)
E - 테이블과 관련된 인덱스 유지된다(맞다)
F - ⭐TRUNCATE하면 FLASHBACK 안된다.⭐

문제 614. (SQL마지막 문제) EMP테이블을 truncate하고, emp테이블을 10분전으로 flashback table해보기!

truncate table emp;

select systimestamp from dual;

select * 
    from emp
    as of timestamp to_timestamp('23/07/18 16:32:15', 'RR/MM/DD HH24:MI:SS');
  
alter table emp enable row movement;

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

select * from emp;


16:32:37에서 10분전으로 flashback 하기

안됨!

profile
망구씨
Slow and steady wins the race.
이전 포스트

[Oracle SQL]20일차_23.07.17

1개의 댓글

comment-user-thumbnail
happy
2023년 7월 18일

아주 유용한 정보네요!

답글 달기