13일차 oracle(6)

Q 123.
관리자가 잠시 자리를 비울경우 =>계정생성  [with admin option]
                                          자기가 받은 권한을 다른 계정에게 줄 수있는 권한
                                                         (=권한 위임)
시나리오
 관리자->1.aaa,bbb,ccc계정을 생성(암호는 계정명과 동일하게)
            2.각자 로그인해서 자원을 생성할 수 있는 권한 부여
                                               테이블 생성
                             connect,resource
                         ->ccc (로그인 X)
            3.bbb계정->다른 계정에게 권한을 임시 줄 수 있는 권한
                                ccc

SQL> conn sys/sys12345 as sysdba
연결되었습니다.

SQL> create user aaa identified by aaa;
사용자가 생성되었습니다.

SQL> create user bbb identified by bbb;
사용자가 생성되었습니다.

SQL> create user ccc identified by ccc;
사용자가 생성되었습니다.

SQL> grant connect, resource to aaa;
권한이 부여되었습니다.

SQL> grant connect, resource to bbb with admin option;
권한이 부여되었습니다.

SQL> conn ccc/ccc
ERROR:
ORA-01045: user CCC lacks CREATE SESSION privilege; logon denied
 ->로그인 권한이 부족->인증 거부

SQL> conn bbb/bbb
연결되었습니다.

SQL> grant connect, resource to ccc;
권한이 부여되었습니다.
-> admin 권한을 받았기 때문에 bbb가 ccc에게 권한 부여 가능함

SQL> conn aaa/aaa
연결되었습니다.

SQL> grant connect, resource to ccc;
grant connect, resource to ccc
*
1행에 오류:
ORA-01031: 권한이 불충분합니다
-> admin 권한이 없기 때문에 ccc에게 권한 부여 불가능

SQL> conn ccc/ccc
연결되었습니다.
-> bbb가 ccc에게 권한을 줬기 때문에 연결 가능함

SQL> create table abc(id number);
테이블이 생성되었습니다.

SQL> insert into abc values(1);
1 개의 행이 만들어졌습니다.

SQL> select * from abc;
        ID
----------
         1


Q 124.
관리자가 bbb 권한을 뺏으면 bbb가 권한준 ccc도 같이 권한이 회수 되는지

관리자->bbb(with admin option)->ccc(connect,resource)
관리자=>bbb(권한 회수(revoke))=>ccc의 계정의 권한?
            connect,resource 회수          같은 회수? or 회수X

SQL> conn sys/sys12345 as sysdba
연결되었습니다.

SQL> revoke connect, resource from bbb;
권한이 취소되었습니다.

SQL> conn ccc/ccc
연결되었습니다.

SQL> create table abc2(id varchar2(10));
테이블이 생성되었습니다.

********************************************************************************************************
정리) 관리자가 계정을 생성할때 with admin option
 ->생성된 계정은 자기가 부여받은 권한 범위내에서 권한을 다른 계정에게 위임이 가능하다.
->권한을 회수당해도 위임받은 계정에게는 영향이 없다. (그대로 권한행사)
********************************************************************************************************


Q 125.
오라클의 객체 권한(테이블,시컨스,시노님,,,)
=>계정우선(소유자)->권한주기,권한회수
객체의 권한종류를 조회할 수 있는 SQL?

시스템권한->system_privilege_map
객체권한->  table_privilege_map

SQL> conn sys/sys12345 as sysdba
연결되었습니다.

SQL>select * from table_privilege_map;
 PRIVILEGE NAME
---------- ----------------------------------------
         0 ALTER
         1 AUDIT
         2 COMMENT =>주석을 달때 사용(모델링)
         3 DELETE=>삭제
         4 GRANT ->권한부여
         5 INDEX
         6 INSERT ->테이블의 insert권한
         7 LOCK
         8 RENAME ->이름 변경(테이블 이름,필드명 변경)
         9 SELECT ->테이블의 select 부여
        10 UPDATE ->수정하는 권한

 PRIVILEGE NAME
---------- ----------------------------------------
        11 REFERENCES
        12 EXECUTE (실행) 권한
        16 CREATE =>생성
        17 READ
        18 WRITE
        20 ENQUEUE
        21 DEQUEUE
        22 UNDER
        23 ON COMMIT REFRESH
        24 QUERY REWRITE
        26 DEBUG

 PRIVILEGE NAME
---------- ----------------------------------------
        27 FLASHBACK=>데이터 복구에 관련된 명령어
                                 (flashback 기술)
          =========================
        28 MERGE VIEW
        29 USE
        30 FLASHBACK ARCHIVE
26 개의 행이 선택되었습니다.


Q 126.
권한줄때 ->테이블(DML(insert,update,delete),DQL(select),뷰(execute)
                                                                    함수,프로시저
객체권한->소유자가 권한부여,권한회수<->with admin option (시스템 권한)

형식)grant 권한종류,,, on 적용대상자(=테이블,뷰,,,) to 계정명,, [with grant option] 위임
               test2
               test3
               test4
                          b_dept2(scott)(소유자)

 ** 관리자 **
  create user test2 identified by test2;
  create user test3 identified by test3;
  create user test4 identified by test4;
                   (로그인)  
SQL> grant create session, create table to test2, test3, test4;
권한이 부여되었습니다.
------------------------------------------

b_dept2 테이블의 권한=>scott

conn scott/tiger

      test2->insert
      test3->with grant option->b_dept2 (insert,update,select)
      test4->select,update

scott->test3에게 준다.(양도권한)

SQL> grant select, insert, update on b_dept2 to test3 with grant option;
권한이 부여되었습니다.

SQL> conn test3/test3;
연결되었습니다.

SQL> grant insert on scott.b_dept2 to test2;
권한이 부여되었습니다.
->test3 가 test2 에게 권한을 부여

SQL> grant select, update on scott.b_dept2 to test4;
권한이 부여되었습니다.
->test3 가 test4 에게 권한을 부여

SQL> conn test4/test4;
연결되었습니다.

SQL> select * from scott.b_dept2;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        80 TESTING3       TEST
        50 SUPPORT        GWANGJU
        60 TESTING        SEOUL
        70 TESTING2       BUSAN
        90 TESTING        TEST2
9 개의 행이 선택되었습니다.

SQL> update scott.b_dept2 set loc = 'DALLAS' where deptno = 60;
1 행이 갱신되었습니다.
-> test3 에게 update 권한을 받은 test4라서 update가 가능함


Q 127.
scott->test3에게 부여한 권한을 회수 한다면
          test3로부터 권한을 양도받은 test4의 권한유지?

SQL> conn scott/tiger
연결되었습니다.

SQL> revoke select, insert, update on b_dept2 from test3;
권한이 취소되었습니다.

SQL> conn test4/test4
연결되었습니다.

SQL> select * from scott.b_dept2;
select * from scott.b_dept2
                        *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

********************************************************************************************************
소유자(scott)가 test3(select,insert,update)->test4라는 계정에게 부여받은 권한의 일부(select,update)을 
줘도 자기 권한(test3)을 회수당하면 양도한 계정(test4)도 같이 회수를 당한다.
********************************************************************************************************
============================================================================
 롤권한과 동일하다. <--->시스템 권한과 반대
============================================================================


Q 128.
데이터 복원 -> 10g이후부터 가능(delete,truncate,drop)

데이터를 삭제? delete구문,truncate구문의 차이점,공통점을 기술하시오
       단답형=>데이터를 삭제시키는 기본적인 SQL구문 2가지 서술하시오 
                   delete구문, truncate구문

SQL> conn scott/tiger
연결되었습니다.

SQL> select count(*) from b_emp3;
  COUNT(*)
----------
        14

SQL> delete from b_emp3;
14 행이 삭제되었습니다.

SQL> select count(*) from b_emp3;
  COUNT(*)
----------
         0

SQL> rollback;
롤백이 완료되었습니다.
-> commit을 안했기 때문에 가능함

SQL> select count(*) from b_emp3;
  COUNT(*)
----------
        14

SQL> truncate table b_emp3;
테이블이 잘렸습니다.

SQL> rollback;
롤백이 완료되었습니다.

SQL> select count(*) from b_emp3;
  COUNT(*)
----------
         0
-> truncate 는 롤백을 해도 복구가 안되기 때문에 카운트가 0으로 뜸


Q 129.
데이터를 삭제=>drop문
형식)drop table 삭제시킬 테이블명->DDL문
       테이블을 삭제(그 내용도 같이 삭제)=> 복구 안된다

오라클 버전10g이후 flashback 기술=> 복구가 가능하다 (항상 복구되는것이 아니다)(휴지통에 임시저장)

SQL> create table b_emp6 as select * from emp;
테이블이 생성되었습니다.

SQL> drop table b_emp6;
테이블이 삭제되었습니다.

SQL> rollback;
롤백이 완료되었습니다.

SQL> select * from b_emp6;
select * from b_emp6
              *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
-> 롤백 해도 복구가 안됨


Q 130.
삭제된 테이블이 휴지통에 들어가 있는상태인지 아닌지 조회

SQL>desc user_recyclebin  -> 데이터사전->뷰로작성
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME      (테이블 삭제후 이름)     NOT NULL VARCHAR2(30)
 ORIGINAL_NAME    (테이블 삭제전 이름)      VARCHAR2(32)
 OPERATION                                          VARCHAR2(9)
 TYPE       (오라클 객체종류->테이블)           VARCHAR2(25)
 TS_NAME                                            VARCHAR2(30)
 CREATETIME                                         VARCHAR2(19)
 DROPTIME   (삭제된 시간)                        VARCHAR2(19)
 DROPSCN                                            NUMBER
 PARTITION_NAME                                  VARCHAR2(32)
 CAN_UNDROP                                       VARCHAR2(3)
 CAN_PURGE                                          VARCHAR2(3)
 RELATED                                   NOT NULL NUMBER
 BASE_OBJECT                               NOT NULL NUMBER
 PURGE_OBJECT                              NOT NULL NUMBER
 SPACE                                              NUMBER

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
B_EMP6           BIN$GSS7H2s+Q5m2hYVQ5d+80Q==$0 TABLE        2023-05-09:10:35:22
-> 휴지통에 들어있는 테이블 확인 가능
    DROP TIME 을 통해 언제 삭제 됐는지 확인 가능

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$GSS7H2s+Q5m2hYVQ5d+80Q==$0 TABLE
-> 오라클 10g 이후 부터 drop된 테이블은 위 같이 뜬다.


Q 131.
휴지통에 들어가 있는 테이블->복원

형식)flashback table 복원테이블명(원본테이블명) to before drop

SQL> flashback table b_emp6 to before drop;
플래시백이 완료되었습니다.

SQL> select * from b_emp6;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK
....
14 개의 행이 선택되었습니다.


Q 132.
만약에 같은 테이블을 여러개 삭제시킨경우 어떻게 복원?

SQL> create table t1(a number);

테이블이 생성되었습니다.

SQL> drop table t1;

테이블이 삭제되었습니다.

SQL> create table t1(a varchar2(10);
create table t1(a varchar2(10)
                             *
1행에 오류:
ORA-00907: 누락된 우괄호

SQL> create table t1(a number);
테이블이 생성되었습니다.

SQL> drop table t1;
테이블이 삭제되었습니다.

SQL> create table t1(a varchar2(10));
테이블이 생성되었습니다.

SQL> drop table t1;
테이블이 삭제되었습니다.

SQL> create table tq(a date);
테이블이 생성되었습니다.

SQL> drop table tq;

테이블이 삭제되었습니다.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1               BIN$hu+8uRmUTDSNuWLaKSu0hA==$0 TABLE        2023-05-09:10:49:22
T1               BIN$h0vvuxGTQIG96nzXccawxQ==$0 TABLE        2023-05-09:10:49:04
TQ               BIN$bj3NtS9nT2WUwZi7mudrZA==$0 TABLE        2023-05-09:10:49:43

SQL> flashback table t1 to before drop;  -> 가장 최근에 삭제된 테이블 순서대로 복원이 됨
플래시백이 완료되었습니다.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1               BIN$h0vvuxGTQIG96nzXccawxQ==$0 TABLE        2023-05-09:10:49:04
TQ               BIN$bj3NtS9nT2WUwZi7mudrZA==$0 TABLE        2023-05-09:10:49:43

SQL> flashback table t1 to before drop;
flashback table t1 to before drop
*
1행에 오류:
ORA-38312: 원래 이름이 기존 객체에 의해 사용됨
-> 이미 t1 이란 테이블명이 존재하기 때문에 복구가 안됨


Q 133.
이미 복원 시킬 테이블이 존재하는 경우(동일한 테이블)어떻게 복원?
복원을 시킬때 이름을 변경시켜서 복원하는 방법

형식)flashback table "휴지통의 테이블이름" to before drop
       rename to 새로운 테이블명;

SQL> flashback table "BIN$h0vvuxGTQIG96nzXccawxQ==$0" to before drop rename to t2;
플래시백이 완료되었습니다.
-> 같은 t1이라 복구가 안되니 t2로 바꿔서 복구함

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TQ               BIN$bj3NtS9nT2WUwZi7mudrZA==$0 TABLE        2023-05-09:10:49:43

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$bj3NtS9nT2WUwZi7mudrZA==$0 TABLE
BONUS                          TABLE
BUSER                          SYNONYM
B_DEPT2                        TABLE
B_EMP2                         TABLE
B_EMP3                         TABLE
B_EMP6                         TABLE
DEPT                           TABLE
EMP                            TABLE
EMP_DEPTNO                     TABLE
SALGRADE                       TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
V_SEQUENCE_SEQ                 VIEW
ZIPCODE                        TABLE

15 개의 행이 선택되었습니다.