-
객체권한
-
table, view, sequence, synonym, index
-
<문1>
-
객체의 권한 종류를 조회하는 SQL?
-> table_privilege_map(테이블)
-
시스템 권한종류?
-> system_privilege_map
SQL> conn sys/sys12345 as sysdba
연결되었습니다.
SQL> select * from table_privilege_map;
PRIVILEGE NAME
---------- ----------------------------------------
0 ALTER
1 AUDIT
2 COMMENT // 주석(테이블 설계 => exerd)
3 DELETE // 삭제권한
4 GRANT // 권한부여
5 INDEX
6 INSERT // DML권한
7 LOCK
8 RENAME // 이름변경(테이블이름, 필드명 변경)
9 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 // 데이터 복구에 관련된 명령어
28 MERGE VIEW
29 USE
30 FLASHBACK ARCHIVE
26 개의 행이 선택되었습니다.
권한을 줄때 -> 테이블(DML(insert, update, delete, select, view))
<문2>
관리자가 test4/test4, test5/test5, test6/test6 계정생성을 하고 connect, resource, create table권한을 부여하고test5가 test6에게 권한을 줄 수있도록 위임
형식) grant 권한종류,, on 적용테이블명 to 계정명 [with grant option]
SQL> create user test4 identified by test4;
SQL> create user test5 identified by test5;
SQL> create user test6 identified by test6;
SQL> grant connect, resource to test4, test5, test6;
SQL> create user test4 identified by test4;
사용자가 생성되었습니다.
SQL> create user test5 identified by test5;
사용자가 생성되었습니다.
SQL> create user test6 identified by test6;
사용자가 생성되었습니다.
SQL> grant connect, resource to test4, test5, test6;
권한이 부여되었습니다.
테이블의 컬럼수(행) 확인할때는 select * from 테이블명 하지 말고 select count(*) from 테이블명으로 할것
실무에서 방대한 자료를 *로 불러내는 것은 어리석은 행동이다.
test5에게 권한부여
SQL> grant select, insert, update on b_dept2 to test5 with grant option;
SQL> grant select, insert, update on b_dept2 to test5 with grant option;
권한이 부여되었습니다.
test5가 test4에게는 insert test6에게는 select, update 권한을 부여하기
SQL> conn test5/test5
SQL> grant insert on scott.b_dept2 to test4;
SQL> grant select, update on scott.b_dept2 to test6;
SQL> conn test5/test5
연결되었습니다.
SQL> grant insert on scott.b_dept2 to test4;
권한이 부여되었습니다.
SQL> grant select, update on scott.b_dept2 to test6;
권한이 부여되었습니다.scott 주의할 것 scott의 테이블을 사용하는 것이기 때문에 소유자명을 반드시 입력해야한다.
부여받은 권한 확인하기
SQL> conn test6/test6
SQL> select * from scott.b_dept2;
SQL> conn test6/test6
연결되었습니다.
SQL> select * from scott.b_dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
90 TESTING4 SEOUL4
50 SUPPORT KWANGJU
60 TESTING SEOUL
70 TESTING2 SEOUL2
80 TESTING3 SEOUL3
9 개의 행이 선택되었습니다.
수정도 가능하다
SQL> update scott.b_dept2 set loc='DALLS' where deptno=60;
SQL> update scott.b_dept2 set loc='DALLS' where deptno=60;
1 행이 갱신되었습니다.
SQL> select * from scott.b_dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
90 TESTING4 SEOUL4
50 SUPPORT KWANGJU
60 TESTING DALLS
70 TESTING2 SEOUL2
80 TESTING3 SEOUL3
9 개의 행이 선택되었습니다.
<문3> 위임권한 회수
scott이 test5에게 부여한 권한을 회수
SQL> conn scott/tiger
SQL> revoke select, update, insert on b_dept2 from test5;
SQL> conn scott/tiger
연결되었습니다.
SQL> revoke select, update, insert on b_dept2 from test5;
권한이 취소되었습니다.
권한 회수 확인
SQL> conn test5/test5
연결되었습니다.
SQL> select * from scott.b_dept2;
select * from scott.b_dept2
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다SQL> conn test6/test6
연결되었습니다.
SQL> select * from scott.b_dept2;
select * from scott.b_dept2
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다test5가 권한을 회수당하면 test5가 test6에게 부여한 권한도 같이 회수 당한다. <---> 시스템 권한과 반대다.
<문4> 데이터 삭제
-
delete
-
truncate
-
delete from 테이블명 (delete * from 아니다 주의)
= 테이블의 내부데이터 삭제
delete from 테이블명 where 조건식
= 회원탈퇴에 사용
SQL> select count(*) from b_emp3;
COUNT(*)
----------
14
SQL> delete from b_emp3;
14 행이 삭제되었습니다.
SQL> rollback; 하면 (DML)데이터 복구가 가능하다 commmit 전이라면
SQL> rollback;
롤백이 완료되었습니다.
SQL> select count(*) from b_emp3;
COUNT(*)
----------
14
2. truncate table 삭제시킬테이블명;
= 테이블 내의 데이터를 삭제
SQL> truncate table b_emp3;
SQL> truncate table b_emp3;
테이블이 잘렸습니다.
SQL> rollback;
롤백이 완료되었습니다.
SQL> select * from b_dept3;
select * from b_dept3
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다데이터 복원이 안된다.
-
delete, truncate 공통점
-
둘다 테이블내의 데이터를 삭제(테이블의 구조는 그대로 유지)
-
-
delete, truncate 차이점
-
delete: rollback으로 데이터 복원 가능
DML계열
-
truncate: rollbakc으로 데이터 복원 불가능
DDL 계열 => 자동으로 commit
-
DDL계열과 DCL계열은 자동으로 commit 되므로 주의해서 사용해야 한다.
<문5> 데이터 삭제 drop
-
drop 문
-
drop table 삭제시킬 데이블명
테이블삭제 => 데이터삭제, 구조삭제(폴더 삭제) , 복원불가, DDL계열
SQL> drop table b_emp6;
SQL> select count(*) from b_emp6;
SQL> rollback;
sql>drop table b_emp6;
테이블이 삭제되었습니다.
SQL> select count(*) from b_emp6;
select count(*) from b_emp6
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
sql>rollback
SQL> select count(*) from b_emp6;
select count(*) from b_emp6
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
8i,9i 복구 불가 -> 10g이후부터 drop table으로 삭제해도 복구시키면 존재
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$zLbQG9V7RdGDDCBX32bq8A==$0 TABLE
==>삭제된 테이블을 보여준다.(휴지통에 보관된 상태)
BONUS TABLE
BUSER SYNONYM
B_DEPT2 TABLE
B_EMP2 TABLE
B_EMP3 TABLE
B_EMP4 TABLE
<문6> 삭제된 테이블이 휴지통에 들어가 있는 상태에서 조회
SQL> desc user_recyclebin
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
구분해주는 고유번호(system change 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
<문7> 휴지통에 들어가 있는 테이블을 복원
형식) flashback table 복원시킬 테이블명 to before drop
SQL> flashback table b_emp6 to before drop;
sql>flashback table b_emp6 to before drop;
플래시백이 완료되었습니다.
sql>select count(*) from b_emp6;
COUNT(*)
----------
14
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
BUSER SYNONYM
B_DEPT2 TABLE
B_EMP2 TABLE
B_EMP3 TABLE
B_EMP4 TABLE
B_EMP6 TABLE
<문8> 영구삭제시키는 방법 => 휴지통 거치지 않고 삭제
flashback 기술이 적용되지 않는다.
형식) drop table 삭제시킬 테이블명 [purge]
SQL> drop table b_emp3 purge;
SQL> show recyclebin
SQL> flashback table b_emp3 to before drop;
SQL> drop table b_emp3 purge;
테이블이 삭제되었습니다.
SQL> show recyclebin; // 보여지는 것이 없다. 휴지통에 들어간게 애초에 없다.
SQL> flashback table b_emp3 to before drop;
flashback table b_emp3 to before drop
*
1행에 오류:
ORA-38305: 객체가 RECYCLE BIN에 없음
<문9> 만약 같은 테이블을 여러개 삭제시킨경우 복원
SQL> create table t1(a number);
SQL> create table t1(a number);
테이블이 생성되었습니다.
SQL> drop table t1;
SQL> drop table t1;
테이블이 삭제되었습니다.
다음 테이블을 시간차 두고 생성 삭제 해보기
SQL> create table t1(a varchar2(10));
SQL> drop table t1;
SQL> create table t1(a date);
SQL> drop table t1;
SQL> create table t1(a number);
SQL> drop table t1;
SQL> create table t1(a varchar2(10));
테이블이 생성되었습니다.
SQL> drop table t1;
테이블이 삭제되었습니다.
SQL> create table t1(a date);
테이블이 생성되었습니다.
SQL> drop table t1;
테이블이 삭제되었습니다.
SQL> create table t1(a number);
테이블이 생성되었습니다.
SQL> drop table t1;
테이블이 삭제되었습니다.
SQL> select * from tab; 로 확인
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$BCs+HUhgSAy8kkC+6ouBGw==$0 TABLE
BIN$K1Bh6xXBR6GysMWJ7yEZvQ==$0 TABLE
BIN$n+R2VWHeQnW901GDN+NIdQ==$0 TABLE
BIN$zGQh+rDnQaC3Uo1dld0DJA==$0 TABLE
BONUS TABLE
BUSER SYNONYM
B_DEPT2 TABLE
B_EMP2 TABLE
B_EMP4 TABLE
DEPT TABLE
DEPT30 VIEW
----------------------------------------------------
이하 생략
SQL> show recyclebin; 로 확인
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$n+R2VWHeQnW901GDN+NIdQ==$0 TABLE 2020-07-10:12:27:19
T1 BIN$BCs+HUhgSAy8kkC+6ouBGw==$0 TABLE 2020-07-10:12:27:01
T1 BIN$zGQh+rDnQaC3Uo1dld0DJA==$0 TABLE 2020-07-10:12:25:49
T1 BIN$K1Bh6xXBR6GysMWJ7yEZvQ==$0 TABLE 2020-07-10:12:23:52삭제된 시간 확인이 가능하다.
SQL> flashback table t1 to before drop;
SQL> show recyclebin;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$n+R2VWHeQnW901GDN+NIdQ==$0 TABLE 2020-07-10:12:27:19
T1 BIN$BCs+HUhgSAy8kkC+6ouBGw==$0 TABLE 2020-07-10:12:27:01
T1 BIN$zGQh+rDnQaC3Uo1dld0DJA==$0 TABLE 2020-07-10:12:25:49
T1 BIN$K1Bh6xXBR6GysMWJ7yEZvQ==$0 TABLE 2020-07-10:12:23:52
SQL> flashback table t1 to before drop;
플래시백이 완료되었습니다.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$BCs+HUhgSAy8kkC+6ouBGw==$0 TABLE 2020-07-10:12:27:01
T1 BIN$zGQh+rDnQaC3Uo1dld0DJA==$0 TABLE 2020-07-10:12:25:49
T1 BIN$K1Bh6xXBR6GysMWJ7yEZvQ==$0 TABLE 2020-07-10:12:23:52가장 최근에 삭제된 데이터순으로 복구된다.
<문10> 이미 복원시킬 테이블이 존재하는 경우(동일한 테이블, 동일명 테이블)
(위의 문제에서 t1테이블을 만들고 삭제하고 반복하였다 그리고 이미 t1의 이름을 가진 테이블이 복원된 상태 이고 추가적으로 복원을 하려고하는데 t1으로는 불가하다)
어떻게 복원? => 복원을 시킬때 이름을 변경시켜 구분하여 복원시킨다.
형식) flashback tabel "휴지통의 테이블이름(RECYCLEBIN NAME)" to before drop rename to 새로운 테이블명
SQL> flashback table "BIN$BCs+HUhgSAy8kkC+6ouBGw==$0" to before drop rename to imsitest;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$BCs+HUhgSAy8kkC+6ouBGw==$0 TABLE 2020-07-10:12:27:01
T1 BIN$zGQh+rDnQaC3Uo1dld0DJA==$0 TABLE 2020-07-10:12:25:49
T1 BIN$K1Bh6xXBR6GysMWJ7yEZvQ==$0 TABLE 2020-07-10:12:23:52
SQL> flashback table "BIN$BCs+HUhgSAy8kkC+6ouBGw==$0" to before drop rename to imsitest;
플래시백이 완료되었습니다.
SQL> desc imsitest;
이름 널? 유형
----------------------------------------- -------- ----------------------------
A DATE
'KIC > ORACLE' 카테고리의 다른 글
| [Oracle] 추가내용(관리자입장) (0) | 2020.10.12 |
|---|---|
| [Oracle] 동의어, 권한 (0) | 2020.10.12 |
| [Oracle] 트랜잭션 (0) | 2020.10.12 |
| [Oracle] 인덱스, 시퀀스 (0) | 2020.10.12 |
| [Oracle] 뷰 (0) | 2020.10.12 |