1장 유저 엑세스 제어
SQL 심화2017. 9. 7. 03:16
select *
from user_tables;
■ SQL 교재 구성
- Fun 1 ---> 기본 SQL문
- SELECT 문의 6가지절
- 함수
- 조인
- 서브쿼리
- 집합 연산자
- 레포팅 함수
- DML 문
- TCL 문
- DDL 문
- Database object 5가지
- Fun2 --> 고급 SQL 문
- DCL 문
■ 1장. 유저 엑세스 제어
- 1장 목차
- 유저생성
- 패스워드 변경하는 방법
- 시스템 권한
- 객체 권한
- 롤 사용법
DCL 문(Database Control Language)
Database 의 권한을 제어하는 언어 : grant , revoke
1.1 유저 생성
Create user smith
identified by tiger;
문제379. smith유저로 접속하시오 !
SQL> connect smith/tiger;
ERROR:
ORA-01045: user SMITH lacks CREATE SESSION privilege; logon denied
경고: 이제는 ORACLE에 연결되어 있지 않습니다.
SQL>
접속할 수 있는 권한이 없어서 나는 오류
SQL> connect smith/tiger;
ERROR:
ORA-01045: user SMITH lacks CREATE SESSION privilege; logon denied
경고: 이제는 ORACLE에 연결되어 있지 않습니다.
SQL> connect / as sysdba
연결되었습니다.
문제380. smith 유저가 접속을 할 수 있도록 sys유저에게
smith 유저에게 접속할 수 있는 권한을 주시오
SQL> grant connect to smith;
권한이 부여되었습니다.
문제381. KING 이라는 유저를 생성하고 접속 할 수 있는 권한을 부여하시오
CONNECT / AS SYSDBA
CREATE USER KING
IDENTIFIED BY TIGER;
GRANT CONNECT TO KING;
CONNECT KING/TIGER;
1.2 패스워드를 변경하는 방법
alter user king
identified by oracle1234;
SQL> connect king/oracle1234
연결되었습니다.
SQL> password
KING에 대한 비밀번호를 변경합니다.
이전 비밀번호:
새 비밀번호:
새 비밀번호 다시 입력:
비밀번호가 변경되었습니다.
SQL>
문제382. scott의 password를 복잡하게 변경하시오
connect / as sysdba
alter user scott
identified by oracle1234
1.3 시스템 권한
database 에서 특정 작업을 수행할 수 있는 권한
예: create table, create view, create session
문제383. king으로 접속해서 아래의 테이블을 생성하시오!
create table emp383
( empno number(10),
ename varchar2(20) );
- sys 유저에서 king 에게 create table 권한을 부여하시오
SQL> grant create table to king;
- 나에게 있는 시스템 권한이 무엇인지 확인하는 방법
select *
from session_privs;
문제384. sys 유저가 king에게 create view, create sequence 권한을 부여하고 권한이
잘 들어갔는지 확인하오
SQL> grant create view, create sequence to king;
권한이 부여되었습니다.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
1.4 객체 권한
" 특정 테이블의 데이터를 엑세스 할 수 있는 권한 "
문제385. SCOTT유저에서 KING 유저에게 SCOTT의 EMP 테이블을 SELECT 할 수있는 권한을 주시오
가 > 스콧에게
SQL> grant create public synonym to scott;
권한이 부여되었습니다.
스콧이 > 킹에게
SQL> create public synonym emp for scott.emp;
동의어가 생성되었습니다.
킹이 >
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
문제387. scott 유저가 king에게 scott의 emp 테이블을 update하고 delete 할 수 잇는 권한 (객체권한)
을 주시오
SQL> grant update, delete on emp to king;
권한이 부여되었습니다.
- 권한의 종류 2가지
- 시스템 권한 : DB에서 특정 작업을 수행 할 수 있는 권한
예: create table, create view, create sequene, ...
관련 데이터 사전 : select * from sesscion_privs;
- 객체 권한 : 특정 data를 엑세스하거나 조작 할 수 있는 권한
예: select on emp, update on emp, ...
관련 데이터 사전 : select * from user_tab_privs;
■ 권한 취소 명령어 : revoke
문제388. scott 유저가 king 에게 부여한 emp 테이블을 delete 할 수 있는 권한을 취소하시오
scott>
revoke delete on emp from king;
문제389. sqlplus 프롬프트 창에서 앞에 유저명이 뜨게하려면
어떻게 해야하는거?
glogin.sql 스크립트를 열어서 그 안에 아래의 내용을 추가한다.
Path에서 찾음
C:\app\stu\product\11.2.0\dbhome_1\sqlplus\admin
glogin.sql 파일을 !
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
- 롤사용법
role 이란? 권한의 집합
권한을 하나씩 일일이 주기가 번거로우니까 권한을 모아 놓은
role을 생성해 놓았다가 role을 부여함 권한을 한번에 !!!!!!!!!!!!
문제390. allen 이란 유저를 만들고 allen 유저에게 접속할 수 있는 권한도 주고 테이블을 생성
할 수 있는 권한도 주고, 기본적인 권한들을 부여하고 싶다면 ?
create user allen
identified by tiger;
grant connect, resource to allen;
↑ ↑
role role
호롤롤롤
문제391. jones 라는 유저를 생성하고 jones 유저에게 기본적인 권한을 부여하고 jones 유저로 접혹해서emp 테이블을 생성하고 부서 번호와 부서번호별 토탈 월급을 출력하는데 맨 아래 쪽에 전체토탈월급을
아래와 같이 출력하시오
select decode(deptno,null,'전체토탈',deptno) as deptno, sum(sal) from emp group by grouping sets ((deptno),());
DEPTNO SUM(SAL)
---------------------------------------- ----------
10 8750
20 10875
30 9400
전체토탈 29025
1.6 계정 잠금과 잠금해제
오라클은 설치를 하고 나면 기본적으로 만들어지는 유저들이 sys와 system을 제외하고는 다 잠겨있다.
--> 접속이 안되어있다
- db에 있는 유저들 확인
sys> select username, account_status
from dba_users;
- role 중에 가장 많은 권한이 있는 dba롤을 scott에게 부여
SYS@orcl> grant dba to scott;
권한이 부여되었습니다.
문제392. sh 계정의 패스워드를 sh로 변경하시오
SYS@orcl> alter user sh
2 identified by sh;
사용자가 변경되었습니다.
--------------------------------------------------------------------------------------
SYS@orcl> connect sh/sh
ERROR:
ORA-28000: the account is locked
==> 계정이 잠기었네요 풀어주세요
문제394. sys유저에서 잠긴 sh 계정의 락을 해제 하시오!
SYS@orcl> alter user sh
2 account unlock;
사용자가 변경되었습니다.
2. 유저 gangs를 만드시오
3. gangs로 접속해서 고백 테이블을 만드는데 sys에서 gangs로 권한부여해서 emp테이블의 sal중 가장 높은
사원의 sal을 입력하여 고백테이블을 만드시오.
4. gangs가 wontae에게 권한을 부여하시오
5. wontae로 접속해서 사귐 테이블을 만드는데 고백 테이블을 시타스로해서 생성하시오.(gangs의 권한부여 필요)
문제395. hr의 계정의 락을 해제하고 패스워드를 hr로 변경하시오.
■■ 2장. 스키마 객체 관리
- 2장 목차
- 제약조건추가
- create table 시 인덱스 생성
- 함수 기반 인덱스
- 컬럼삭제 및 컬럼 감추기
- Flash back 타임머신을 타고 과거로 되돌아가는 Flash back 테이블
- external table 사용법 ( 하둡의 hive의 테이블과 비교 )
■ 2.1 제약 조건 추가
- 제약을 사용하는 이유?
"데이터의 품질을 높이기 위해서 "
- 사원테이블에 ename 에 unique 제약을 걸어라!
alter table emp
add constraint emp_ename_un unique(ename);
문제396. 사원 테이블의 월급이 9000 이상의 데이터가 입력 또는 수정
될 수 없게 check제약을 거시오
alter table emp
add constraint emp_sal_ch check (sal < 9000);
문제397. 사원 테이블에 부서번호에 not null 제약을 걸어보세요
alter table emp
add constraint emp_deptno_nn not null(deptno);
설명 : not null 제약은 위와 같은 방법으로는 안되고 아래와 같이 작성해야한다.
alter table emp
modify deptno constraint emp_deptno_nn not null;
update emp set deptno = null;
*
1행에 오류:
ORA-01407: NULL로 ("SCOTT"."EMP"."DEPTNO")을 갱신할 수 없습니다
문제398. 사원 테이블에 ename에 not null 제약을 거시오
alter table emp
modify ename constraint emp_ename_nn not null;
문제399. 사원테이블에 걸린 모든 제약을 다 삭제하시오
SCOTT@orcl> select constraint_name
2 from user_constraints
3 where table_name = 'EMP';
CONSTRAINT_NAME
------------------------------
EMP_ENAME_UN
EMP_SAL_CH
EMP_DEPTNO_NN
EMP_ENAME_NN
PK_EMP
FK_DEPTNO
6 개의 행이 선택되었습니다.
drop co
■ 2.2. create table 시 인덱스 생성
문제400. 사원 테이블에 월급에 인덱스를 생성하시오
create index emp emp_sal
on emp(sal);
select index_name, column_name
from user_ind_columns
where table_name= 'EMP';
문제401. 사원 테이블에 empno에 primary key 제약을 거시오.
select index_name, column_name
from user_ind_columns
where table_name= 'EMP';
SCOTT@orcl> alter table emp
2 add constraint emp_empno_pk primary key(empno);
문제402. 사원 테이블에empno에 걸린 primary key 제약을 삭제하시오
alter table emp
drop constraint emp_empno_pk;
문제403. 사원 테이블에 empno 에 다시 primary key 제약을 거는 데
이번에는 자동으로 생성되는 인덱스 이름이 emp_empno 라고 되게하시오
alter table emp
add constraint emp_empno_pk primary key(empno)
using index ( create index emp_empno
on emp(empno) );
문제404. empno에 primary key 제약을 삭제하면 인덱스도 같이 삭제되나?
같이 삭제 안됨
문제405. 아래의 SQL을 튜닝하시오 !
CREATE INDEX EMP_SAL
ON EMP(SAL);
튜닝전
SELECT ENAME, SAL * 12 연봉
FROM EMP
WHERE SAL * 12 = 36000;
튜닝후
SELECT ENAME, SAL * 12 연봉
FROM EMP
WHERE SAL = 36000 / 12;
문제406. 아래의 SQL을 튜닝하세요
이름이 JACK인 사원의 이름과 월급을 출력하시오
create index emp_ename
on emp(ename);
insert into emp(empno, ename, sal) values (1023, ' jack ', 4000);
튜닝전 :
SELECT ENAME, SAL
FROM EMP
WHERE TRIM(ENAME) = 'JACK';
SELECT ENAME, SAL
FROM EMP
WHERE ENAME LIKE '%JACK%';
튜닝후:
설명 : 튜닝하는 방법은 함수 기반 인덱스를 생성하면 된다.
CREATE INDEX EMP_ENAME_FUN
ON EMP( TRIM(ENAME) );
SELECT ENAME, SAL
FROM EMP
WHERE TRIM(ENAME) = 'JACK';
문제407. 아래의 SQL을 튜닝하시오 ! ( ENAME의 DATA가 대소문자가 섞여있는 상황)
튜닝전 : SELECT ENAME, SAL , DEPTNO
FROM EMP
WHERE UPPER(ENAME) = 'SCOTT';
튜닝후 : CREATE INDEX EMP_ENAME_UPPER
ON EMP ( UPPER(ENAME) );
SELECT ENAME, SAL
FROM EMP
WHERE UPPER(ENAME) = 'SCOTT';
- 2장 목차
- 제약조건추가
- create table 시 인덱스 생성
- 함수 기반 인덱스
- 컬럼삭제 및 컬럼 감추기
문제408. 사원 테이블에 sal 칼럼을 삭제하시오 !
alter table emp
drop column sal;
select * from emp;
문제409. 우리반 테이블을 ctas로 백업하고 우리반 테이블에서
핸드폰번호를 삭제하시오
alter table EMP2
drop column mobile;
- 컬럼 감추는 방법
문제 410. emp 테이블에 ename 컬럼을 감추시오
alter table emp
set unused column ename;
alter table emp
drop unused columns;
■ Flash back 타임머신을 타고 과거로 되돌아가는 Flash back 테이블
Flash back ? ?
특정테이블을 과거로 돌리는 기능
- 1분전에 emp table 에 데이터가 있었는지 무엇이었는지 조회를 해보자!
select *
from emp
as of timestamp(systimestamp - interval '3' minute);
- emp 테이블 6분전으로 되돌리는 작업 수행
alter table emp enable row movement;
설명 : emp 테이블을 flashback 할 수 있게 설정하는 방법
flashback table emp to timestamp
(systimestamp - interval '10' minute);
설명: emp 테이블을 10분전으로 flashback 하는 명령
- emp table 을 drop 하시오!
=> 휴지통 속으로 들어감
- 휴지통을 조회하시오
show recyclebin; ----->cmd창에서
select *
from user_recyclebin; -------> sqlgate에서
flashbak table emp to before drop;
문제411. dept 테이블을 delete 하고 commit 하시오 !
delete from dept;
commit;
문제412. dept 테이블이 지워지기 전 시간이 어떻게 되는지 flashbak query 로 확인해 보시오
- 현재 시간을 확인 해본다.
delete from EMP;
commit;
- flashback query 로 특정 시간의 dept 테이블을 조회해본다.
select *
from dept
as of timestamp to_timestamp('17/03/14 16:13:00', 'RR/MM/DD HH24:MI:SS');
- DEPT 테이블을 FLASHBACK TABLE 해서 17/03/14 16:13:00 으로 되돌리시오~
ALTER TABLE DEPT ENABLE ROW MOVEMENT;
flashback table DEPT TO TIMESTAMP to_timestamp('17/03/14 07:13:06', 'RR/MM/DD HH24:MI:SS');
CMD창에서 FLASHBACK 시간 확인
SHOW PARAMETER UNDO_RETENTION;
SQLGATE에서는
SELECT *
FROM V$PARAMETER
WHERE NAME='undo_retention'; --언두 리텐션을 소문자로 !
■ external table 이란?
↓
외부
대학 등록금 테이블을 생성하려고 csv 파일을 오라클에 입력해서 테이블을 생성했는데 external table은
오라클에 csv data를 입력하지 않고 os 에 csv 파일을 그대로 두고 거기에 링크를 걸어서 data를 엑세스하는 테이블
|
External Table 실습예제 1. emp1.txt 편집(/home/oracle/emp1.txt)
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
SMITH,101,2001/03/15
JOHN,102,2002/04/15
2. Directory 생성
SQL> connect scott/tiger
SQL> create directory emp_dir as '/home/oracle/';
3. External table 생성
SQL> create table ext_emp
(emp_id number(3),
emp_name varchar2(10),
hiredate date)
organization external
(type oracle_loader
default directory emp_dir
access parameters
(records delimited by newline
fields terminated by ","
(emp_name char,
emp_id char,
hiredate date "yyyy/mm/dd") )
location ('emp1.txt') );
4. Select & DML 테스트
SQL> select * from ext_emp;
EMP_ID EMP_NAME HIREDATE
--------- ---------- ------------
101 SMITH 15-MAR-01
102 JOHN 15-APR-02
|
'SQL 심화' 카테고리의 다른 글
| 6장. 서브쿼리에서의 데이터 검색 (0) | 2017.09.07 |
|---|---|
| 5장. 다른 시간대에서의 데이터 관리 (0) | 2017.09.07 |
| 4장 대형 데이터 집합 조작 (0) | 2017.09.07 |
| 3장 데이터 딕셔너리를 통해서 객체 관리 (0) | 2017.09.07 |
| 2장 스키마 객체 관리 (0) | 2017.09.07 |
