[OCP 문제] create table 권한을 받으면 create index 권한도 같이 자동으로
들어가게 됩니다.
복습: 권한의 종류 :
1. 시스템 권한 : create, drop, truncate 와 같이 db 에서 .
객체(object) 에 대해서 행할 수 있는 권한들
1. table
2. view
3. index
4. sequence
5. synonym
select any table 권한 --> 시스템 권한
2. 객체 권한 : 특정 데이터를 select, update, delete, merge
할 수 있는 권한들문제654.[객체권한] 내가 가지고 있는 유져 리스트가 뭐가 있는지
c## 으로 시작하는 유져이름을 조회하시오 !
select *
from dictionary
where table_name like '%USERS';
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;
아니면 그냥 emp 테이블에 대해서 수행할 수 있는 객체권한을 다 넣고 싶으면
grant all on emp to c##king2;
문제657.[객체권한] c##king2 유져로 접속해서 c##scott 유져의 emp 테이블을
select 하시오 !
create public synonym emp
for c##scott.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 로 변경하시오 !
SQL> update c##scott.dept
2 set loc='seoul'
3 where deptno = 10;
update dept
- 1행에 오류:
ORA-01031: 권한이 불충분합니다
문제660.[객체권한] c##king2 유져에서 c##scott 의 dept 테이블을 delete 하시오
delete from 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; **--> 새로 만들어진 계정에도 부여가 되는 구만!**
create user c##abc
identified by tiger;
grant connect to c##abc;
SQL> show user;
USER은 "C##ABC"입니다
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999※ 현업에서는 public 사용을 자제하고 계정별로 개별적으로 부여하는게
바람직합니다.
- dept 테이블의 모든 컬럼에 대해서 update 할 수 있는 권한
grant update on dept to c##king2; - dept 테이블의 특정 컬럼에 대해서만 update 할 수 있는 권한
grant update (dname, loc) on dept to c##king2;
▣ with grant option : 부여하고 있는 권한을 다른 계정에게 줄 수 있는 권한까지 같이 부여하는 옵션
c##scott -> c##king3 -> c##king4
만약 scott이 king3에게 준 권한을 회수 한다면(with grant option),
king3가 king4에게 준 권한은 유지될까 회수 될까?
c##scott c##king3 c##king4
↓ ↓ ↓
create user c##king3 identified by tiger; select * from c##scott.emp;
create user c##king4 identified by tiger;
grant connect to c##king3, c##king4;
grant select on emp to c##king3 with grant option;
select * from c##scott.emp;
grant select on c##scott.emp to c##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;
→ grant create table to c##king3, c##king4; 같이 써도 된다.
→ grant select, update on emp to c##king4; 같이 써도 된다.
1. 내가 가지고 있는 시스템 권한 확인하는 방법 ?
- c##king4 에서 확인
SQL> select * from session_privs;
PRIVILEGE
-------------------------------------------------------------
CREATE SESSION
CREATE TABLE
SET CONTAINER
SQL> select * from user_sys_privs
where username = 'C##KING4';
USERNAME PRIVILEGE ADMIN_ COMMON INHERI
---------- -------------------- ------ ------ ------
C##KING4 CREATE TABLE NO NO NO
2. 내가 가지고 있는 객체 권한 확인하는 방법 ?
col table_name for a10
col grantor for a8
col privilege for a15
select table_name, grantor, privilege
from **user_tab_privs_recd**;
TABLE_NAME GRANTOR PRIVILEGE
---------- ---------- ----------
EMP C##SCOTT SELECT
EMP C##SCOTT UPDATE
가장 중요한 권한 확인!!!
USER_SYS_PRIVS → 시스템 권한
USER_TAB_PRIVS_MADE → 객체 권한
USER_TAB_PRIVS_RECD → 객체 권한
문제662.[객체권한] dba 인 c##scott 이 db 감사를 준비를 해야해서
db 에 있는 유져들에 대해서 어떠한 객체 권한이 부여되었는지
정리해서 엑셀로 뽑아내야하는 업무가 생겼습니다. 어떻게 해야
효율적으로 할 수 있을까요 ?
고급 dba --> SQL + 파이썬 + PL/SQL 사용해서 자동으로 뽑아냅니다.
초급 dba --> SQL 을 단발성으로 쿼리해서 하나씩 결과를 뽑아서 복사해서
엑셀에 수기로 입력해서 고생고생 합니다.
개발자들이 dba 인 나에게 자주 물어보는 질문들이 뭔지 평상시 노트 하고
관련 쿼리를 목차로 만들어서 정리해 놓고 파이썬, 리눅스 쉘, PL/SQL등을
이용해서 자동화 해놓고 빠르게 알려줄 수 있도록 합니다.
▣ 권한 취소 하는 방법
문법: revoke select on emp from c##king4;
revoke create table from c##king4;어디 서버에 들어가야하는데 ? ip주소랑 DB이름 좀 알려주세요~
문제663. c##scott(DBA) 유져에서 c##king4 유져에게 부여된 객체 권한을 확인하고
전부 revoke 시키시오 ! ( sqldeveloper 에서 c##scott 유져에서 조회)
select grantor, grantee, table_name, privilege
from all_tab_privs_made
where grantee='C##KING4';
C##SCOTT C##KING4 EMP SELECT
C##SCOTT C##KING4 EMP UPDATE
user_xxx : 내가 소유한 객체에 대한 정보
all_xxx : 내가 소유한 객체 + 권한을 받은 객체에 대한 정보
revoke select on emp from c##king4;
revoke update on emp from c##king4;
select grantor, grantee, table_name, privilege
from all_tab_privs_made
where grantee='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 인증을
받으려면 이런 권한 관리에 대한 서류를 제출해야하는데 이때 위의 작업이
필요합니다. )
점심시간 문제: with 절 문제
[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 substr(address, 1, 3 ) ) ;
정답 :
with address_cnt as ( select substr(address, 1, 3) as 주소, count(*) as 갯수
from emp17
group by substr(address, 1, 3))
select 주소, 갯수
from address_cnt
where 갯수 > ( select avg(갯수)
from address_cnt);▣ 099 실수로 지운 데이터 복구하기 1(FLASHBACK QUERY)
교재: SQL200제(P 257)
" 특정 테이블의 과거의 데이터를 확인할 때 사용하는 쿼리문 "
예제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');
※ 14시로 해야지 02시 하면 안됩니다. 그러면 새벽2시의 데이터를 보는것입니다.
select *
from emp
as of timestamp ( systimestamp - interval '10' minute );
예제4. emp 테이블을 2023/07/18:14:00:00 시간으로 복구 하시오 !
- emp 테이블을 flashback 이 가능한 상태로 변경합니다. 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'); select * from emp;
문제605. dept 테이블에 dname 컬럼을 전부 null 로 변경하고 commit 하시오!
update dept
set dname=null;
commit;
문제606. dept 테이블에 dname 에 data 가 있었던 시점으로해서 flashback query
를 하시오 !
select systimestamp from dual;
select *
from dept
as of timestamp to_timestamp('23/07/18 14:10:27','RR/MM/DD HH24:MI:SS');
※ tip : flashback table 전에 백업을 미리 해놓습니다.
create table dept_backup
as
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;
select table_name, row_movement
from user_tables
where table_name='DEPT';
TABLE_NAME ROW_MOVEMENT
---------- ----------------
DEPT ENABLEDflashback table dept to timestamp
to_timestamp('23/07/18 14:10:27', 'RR/MM/DD HH24:MI:SS');
select * from dept;
문제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:29:57
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';
▣ 100 실수로 지운 데이터 복구하기 2(FLASHBACK TABLE)
"테이블을 특정 시점으로 되돌리는 복구 기능 "
예제1. emp 와 dept 를 초기화 시킵니다.
SQL> @init_emp.sql
예제2. emp 테이블에 sal 에 인덱스를 생성하시오 !
create index emp_sal on emp(sal);
예제3. emp 테이블을 전부 지우고 commit 합니다.
delete from emp;
commit;
다른 개발자가 c##scott 세션에서 데이터를 update하고 commit 해놓습니다.
update emp
set sal = 9000
where ename = 'KING';
다른 c##scott 세션에서 데이터를 insert 하고 commit 해놓습니다.
insert into emp(empno, ename,sal)
values(1111,'jane', 5000);
commit;
예제4. 현재 시점에서 5분전으로 emp 테이블을 flashback 하시오!
select systimestamp from dual;
23/07/18 14:52:57
alter table emp enable row movement ;
select *
from emp
as of timestamp to_timestamp('23/07/18 14:47:57', 'RR/MM/DD HH24:MI:SS');
create table emp_backup7
as
select *
from emp
as of timestamp to_timestamp('23/07/18 14:52:57', 'RR/MM/DD HH24:MI:SS');
→ 현재 가지고 있는 데이터를 백업을 해놓는다!
예제5. 23/07/18 14:47:57 으로 emp 테이블을 flashback table 시킵니다.
flashback table emp to timestamp to_timestamp('23/07/18 14:47:57', 'RR/MM/DD HH24:MI:SS');
select * from emp;
예제6. 백업받은 emp_backup7 에 있는 데이터를 emp 테이블에 입력하시오!
insert into emp
select *
from emp_backup7;
commit;
문제610. 위와 같이 flashback table 로 복구 했는데 emp 테이블에 걸려있는
인덱스는 어떻게 되었는지 확인하시오 !
select index_name, status
from user_indexes
where table_name='EMP';
INDEX_NAME STATUS
---------------------------------------- ----------------
EMP_SAL VALID▣ 101 실수로 지운 데이터 복구하기 3(FLASHBACK DROP)
"테이블을 DROP 했을 때 복구하는 방법입니다. "
예제1. emp 테이블의 걸려있는 인덱스를 확인하시오 !
select index_name, status
from user_indexes
where table_name='EMP';
INDEX_NAME STATUS
---------------------------------------- ----------------
EMP_SAL VALID예제2. emp 테이블을 drop 하시오 !
drop table emp;
예제3. emp 테이블에 걸려있는 인덱스를 확인하시오 !
select index_name, status
from user_indexes
where table_name='EMP';
선택된 레코드가 없습니다. <--- 테이블 drop 하면 인덱스도 사라집니다.
또한 제약도 사라집니다.
예제4. 휴지통 속에 emp 테이블이 있는지 확인합니다.
show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------- ------------------------------ ----------- -------------------
EMP_SAL BIN$nFFJ5liPQYaSvNpZ9KtiHQ==$0 INDEX 2023-07-18:15:26:00
EMP BIN$VxBZdte3Q6y+bnR7fWG1ag==$0 TABLE 2023-07-18:15:26:00
DEPT BIN$AKlQudJMQ5Sj4bYIo4zGJA==$0 TABLE 2023-07-18:15:51:14
EMP BIN$JNP9/lL5QxKTJWYOiSFoRQ==$0 TABLE 2023-07-18:17:09:33
DEPT BIN$54bOC7lsQ3eqf/7QL6fa6g==$0 TABLE 2023-07-18:17:09:33
EMP_SAL BIN$fMmztUgRRp+s5mc5U137bQ==$0 INDEX 2023-07-18:17:27:59
EMP BIN$zo+KWZ/FTYuLUG6TfseM5Q==$0 TABLE 2023-07-18:17:27:59
select * from user_recyclebin;
이름 널? 유형
-------------- -------- -------------
OBJECT_NAME NOT NULL VARCHAR2(128)
ORIGINAL_NAME VARCHAR2(128)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(128)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
BIN$nFFJ5liPQYaSvNpZ9KtiHQ==$0 EMP_SAL DROP INDEX USERS 2023-07-18:14:48:35 2023-07-18:15:26:00 2964758 NO YES 75312 75312 75313 8
BIN$VxBZdte3Q6y+bnR7fWG1ag==$0 EMP DROP TABLE USERS 2023-07-18:14:48:13 2023-07-18:15:26:00 2964760 YES YES 75312 75312 75312 8
BIN$AKlQudJMQ5Sj4bYIo4zGJA==$0 DEPT DROP TABLE USERS 2023-07-18:14:48:13 2023-07-18:15:51:14 2967039 YES YES 75311 75311 75311 8
BIN$JNP9/lL5QxKTJWYOiSFoRQ==$0 EMP DROP TABLE USERS 2023-07-18:15:51:14 2023-07-18:17:09:33 2971910 YES YES 75316 75316 75316 8
BIN$54bOC7lsQ3eqf/7QL6fa6g==$0 DEPT DROP TABLE USERS 2023-07-18:15:51:14 2023-07-18:17:09:33 2971923 YES YES 75315 75315 75315 8
BIN$fMmztUgRRp+s5mc5U137bQ==$0 EMP_SAL DROP INDEX USERS 2023-07-18:17:09:39 2023-07-18:17:27:59 2972573 NO YES 75319 75319 75320 8
BIN$zo+KWZ/FTYuLUG6TfseM5Q==$0 EMP DROP TABLE USERS 2023-07-18:17:09:33 2023-07-18:17:27:59 2972575 YES YES 75319 75319 75319 8예제5. 휴지통 속에 있는 emp 테이블을 복구하시오 !
flashback table emp to before drop;
select * from emp;
예제6. emp 테이블에 걸려있는 인덱스를 확인하시오 !
SQL> select index_name, status
2 from user_indexes
3 where table_name = 'EMP';
INDEX_NAME STATUS
---------------------------------------- ----------------
BIN$dHU3taRIT5G5Kcbga/F/Gw==$0 VALID
BIN$dHU3taRIT5G5Kcbga/F/Gw==$0 <--- 테이블이 휴지통에서 빠져나오면
인덱스 이름이 이렇게 생깁니다.예제7. 위의 인덱스가 어느 컬럼에 걸린 걸린 인덱스인지 확인합니다.
select index_name, column_name
from user_ind_columns
where table_name='EMP';
예제8. 위의 인덱스의 이름을 원래의 이름으로 변경하시오 !
alter index "BIN$dHU3taRIT5G5Kcbga/F/Gw==$0" rename to emp_sal;
→ “ “ 따옴표를 해줘야한다!
select index_name, column_name
from user_ind_columns
where table_name='EMP';
※ 만약 휴지통에서 테이블을 복구했다면 테이블과 관련한 인덱스 이름들을
위와 같이 변경해줘야 합니다.
문제611. 휴지통을 비우시오
purge recyclebin;
show recyclebin;
문제612. 이 상태에서 emp 테이블을 drop 하는데 아래와 같이 purge 옵션을
써서 drop 하시오 !
drop table emp purge;
show recyclebin;
설명: 진짜 지워도 되는 아주 큰 대용량 테이블 drop 할때 위와 같이 drop하면
휴지통에 안들어가고 한번에 drop 됩니다.
▣ 102 실수로 지운 데이터 복구하기 4(FLASHBACK VERSION QUERY)
특정 테이블이 그동안 어떻게 변경되어 왔는지 그 이력정보를 확인하는 쿼리문
-
emp 와 dept 를 초기화 합니다.
-
먼저 현재 시간을 확인합니다.
select systimestamp from dual;
-
현재 scn 번호를 확인 합니다.
※ scn 번호란 ? system change number 의 약자로 commit 할때 부여하는 번호
select current_scn
from v$database;
53223482-
scn 가지고 시간 확인하기와 시간을 가지고 scn 확인하기
select scn_to_timestamp('53223482')
from dual;23/07/18 15:53:17
select timestamp_to_scn('23/07/18 15:53:17')
from dual;→ scn이 살짝 다르다.
-
emp 테이블의 KING 의 월급을 9000으로 변경하시오 !
update emp
set sal = 9000
where ename='KING';commit;
-
emp 테이블의 KING 의 부서번호를 30번으로 수정하시오 !
update emp
set deptno = 30
where ename='KING';commit;
-
emp 테이블의 KING 의 데이터를 지우시오 !
delete from emp
where ename='KING';commit;
-
emp 테이블의 KING 의 데이터가 그동안 어떻게 변경되어왔는지 그 이력정보를
확인하시오 ! (SQL200제의 263페이지)```sql select ename, sal, versions_starttime, versions_endtime, versions_operation from emp versions between timestamp to_timestamp('23/07/18 15:51:46', 'RR/MM/DD HH24:MI:SS') and maxvalue where ename='KING'; ENAME SAL VERSIONS_STARTTIME VERSIONS_ENDTIME VE ---------- ---------- ------------------------------ ------------------------------ -- KING 9000 23/07/18 15:59:02 D KING 9000 23/07/18 15:58:20 23/07/18 15:59:02 U KING 9000 23/07/18 15:57:16 23/07/18 15:58:20 U KING 5000 23/07/18 15:57:16 ``` -
맨아래에 나온 그 시간의 10초전 시간에 KING 의 데이터를 flashback query
로 확인하시오 !```sql select * from emp as of timestamp to_timestamp('23/07/18 15:57:06', 'RR/MM/DD HH24:MI:SS') where ename='KING'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- ------------------ ---------- -------- ---------- ---------- ---------- 7839 KING PRESIDENT 81/11/17 5000 10 ```
문제613. 23/07/18 15:57:08 으로 emp 테이블을 flashback table 하시오 !
alter table emp enable row movement;
flashback table emp to timestamp to_timestamp('23/07/18 15:57:08',
'RR/MM/DD HH24:MI:SS');
select ename, sal, deptno
from emp
where ename='KING';
▣ 103 실수로 지운 데이터 복구하기 5(FLASHBACK TRANSACTION QUERY)
특정 테이블에 대해서 그동안 변경해왔던 이력정보를 가지고
다시 과거로 되돌리기 위한 DML 문을 보여주는 쿼리문
이 작업은 DB 관리 단원에서 flashback database 배울때 진행하도록 하겠습니다.
문제614. (SQL 마지막 문제) emp 테이블을 truncate 하고 emp 테이블을 10분전으로 flashback table 해보시오 !
마지막 문제 올리시고 나머지 시간은 자유롭게 자습 또는 스터디하시면 됩니다.
truncate table emp;
Table EMP이(가) 잘렸습니다.
select systimestamp from dual;
-- 23/07/18 16:34:12.775000000 +09:00
alter table emp enable row movement;
Table EMP이(가) 변경되었습니다.
flashback table emp to timestamp to_timestamp('23/07/18 16:24:12', 'RR/MM/DD HH24:MI:SS');
명령의 6 행에서 시작하는 중 오류 발생 -
flashback table emp to timestamp to_timestamp('23/07/18 16:24:12', 'RR/MM/DD HH24:MI:SS')
오류 보고 -
ORA-01466: 테이블 정의가 변경되었습니다 데이터를 읽을 수 없습니다
01466. 00000 - "unable to read data - table definition has changed"
*Cause: Query parsed after tbl (or index) change, and executed
w/old snapshot
*Action: commit (or rollback) transaction, and re-execute
