[kh정보교육원 44일차] DCL - GRANT, REVOKE / TCL / DB-Object-data dictionary,stored view, Sequence, Index

 

DCL

Data Control Language 데이터 제어어

권한을 부여/회수하는 명령어 (grant / revoke)

TCL

Transaction Control Language 를 포함한다. (commit / rollback)

 

 

 

GRANT / REVOKE

grant 권한 or 롤 to 사용자 or 롤 or public [ with admin option]

권한 previliage - create session, create table , select on table, ....( ~할 수 있는)

롤 role 권한 묶음 - connect , resource, dba, ...

with admin option 부여받은 권한 or 롤을 다시 다른 사용자에게 부여할 수 있는 권한

 

qwerty 사용자생성

qwerty에게 kh.table을 조회 / 추가 / 수정 / 삭제 권한 부여

 

(관리자로 실행 시작)

일반사용자 (12c부터) c##qwerty, C##QWERTY처럼 생성해야 한다.

그리고 qwerty 생성시

CREATE SESSION 권한이 없어서 실패.

grant connect to qwerty;
grant resource to qwerty;

alter user qwerty 
quota unlimited on users; -- default tablespace 

 

connect 롤 안에 create session 권한 포함

resource 롤 안에 create table 권한 포함

(없을 경우, ORA-01031 : insufficient privileges (테이블 만들 권한이 없다고 나옴))

alter user qwerty

quota unlimited on users; -- 저장할 공간 형성. 재접속이 필요함!!

 

접속 성공!

 

테이블 만들기 성공!

 

부여된 권한과 롤 조회

select * from dba_sys_privs where grantee = 'QWERTY'; -- 권한
select * from dba_role_privs where grantee = 'QWERTY'; -- 롤 connect, resource
select * from dba_sys_privs where grantee = 'CONNECT'; -- connect롤안에 권한 조회
select * from dba_sys_privs where grantee = 'RESOURCE'; -- resource롤안에 권한 조회

 

 

 

 

 

다시 kh계정에 접속해서, coffee 테이블을 생성한다.

create table coffee(
    name varchar2(50),
    price number,
    company varchar2(50),
    constraint pk_coffee_name primary key(name)
);

insert into coffee values ('맥심', 3000, '동서식품');
insert into coffee values ('카누', 5000, '동서식품');
insert into coffee values ('네스카페', 4000, '네슬레');

select * from coffee;

 

값이 잘 입력된 것을 확인하고, commit 한다.

 

coffee테이블의 소유주인 kh가 조회권한을 qwerty에게 부여하는 코드(kh에서 작성)

grant select on kh.coffee to qwerty; --접속사용자가 kh일경우 생략 가능.

 

qwerty에서 확인

select*from kh.coffee;

 

 

이처럼 데이터 추가, 수정, 삭제 권한 부여

grant insert, update, delete on kh.coffee to qwerty;
grant all on kh.coffee to qwerty; --한번에 추가

 

데이터 추가, 수정, 삭제 권환 회수

revoke insert, update, delete on kh.coffee from qwerty;
revoke all on kh.coffee from qwerty; -- 한번에 회수

 

데이터 조회권한 회수

revoke select on kh.coffee from qwerty;

 

이렇게 기본적으로 grant, revoke를 통해 권한을 주어지고 회수하는 방법을 확인해보았다.

(자주 쓰이진 않음)

 

 

 

TCL

Transaction Control Language 트랜잭션 제어어

Commit Rollback Savepoint

 

트랜잭션이란 : 한꺼번에 처리되어야 할 최소의 작업단위. 논리적 작업 단위 (Logical Unit of Work)

*트랜잭션 하위의 작업은 반드시 모두 성공 또는 모두 실패해야 한다.

🔽

ex ) 계좌이체 트랜잭션.

홍길동이 신사임당에게 10000원을 이체할 때,

1. update account set balance = balance - 10000 where id = 'honggd';
2. update account set balance = balance + 10000 where id = 'sinsa';

1 O O X X

2 O X O X

둘다 성공한 경우에만 commit 가능하고, 나머지는 rollback 해야한다.

 

 

 

DATABASE OBJECT 1

 

DATA DICTIONARY

DB를 효율적으로 관리하기 위해 DB객체에 대한 메타정보를 조회하는 가상 테이블

관리자의 테이블을 일반사용자가 읽기전용 접근

db객체 생성, 수정, 삭제 할때도 data dictionary는 자동으로 갱신된다.

 

user_xxx : 사용자 소유의 객체를 조회

all_xxx : 사용자 소유, 타 사용자(관리자)로 부터 사용권한을 부여받은 객체를 조회

dba_xxx : 관리자만 접근 가능한 Data Dictionary. 모든 사용자의 모든 객체정보를 조회

 

모든 dd조회

select * from dict;
select * from dictionary;

 

계정이 소유한 객체정보 user_xxx(복수형)

select * from user_tables;

 

계정의 권한 조회

select * from user_sys_privs;
select * from user_role_privs;
select * from role_sys_privs; -- 사용자가 부여받은 롤안의 실제 권한

 

 

계정의 제약조건 조회

select * from user_constraints;

 

계정의 view 조회

select * from user_views;

 

사용권한이 있는 모든 객체에 대한 Data Dictionary테이블

select * from all_tables;

 

 

View

select * from all_views;

 

관리자용 DD(관리자만 이용 가능)

select * from dba_tables;
select * from dba_tables where owner = 'CHUN';
select * from kh.employee;
select * from chun.tb_student;

select * from dba_users;

 

 

kh로 접근시 접근 불가.

system으로 접근시 접근가능.

 

특정 사용자 권한 확인

select * from dba_sys_privs where grantee = 'KH';
select * from dba_role_privs where grantee = 'KH';

 

테이블 권한

select * from dba_tab_privs where owner = 'KH';

 

 

STORED VIEW

하나이상의 테이블에서 원하는 데이터를 선택하여 보여주는 가상테이블(링크개념)

다른 테이블을 정보를 보여줄 뿐, 실제 데이터를 가지고 있지 않다.

데이터에 대한 저장공간을 차지하지 않는다.

view가 허용한 부분에 한해서만 table을 볼 수 있음.

 

system에서 grant create view to kh;를 실행하고, 다시 kh로 돌아와서 실행한다.

 

create or place : 존재하지 않으면 create, 존재하면 replace

drop 명령어 ) drop view view_emp

 

ex)employee의 일부 정보를 확인할 수 있는 view_emp , 사번 사원명 이메일 부서명 직급명

-- drop view view_emp
create or replace view view_emp
as
select
    emp_id,
    emp_name,
    email,
    (select dept_title from department where dept_id = e.dept_code) dept_title,
    (select job_name from job where job_code = e.job_code) job_name,
    decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') gender
from 
    employee e;

-- view_emp 뷰/테이블 조회
select
    *
from
    view_emp;

 

 

qwerty에게 employee의 제한된 정보 view_emp조회 권한

grant select on kh.view_emp to qwerty;

 

qwerty에서

select * from kh.employee; -- ORA-00942: table or view does not exist
select * from kh.view_emp;

 

kh.employee는 조회 권한을 받지 못해 존재하지 않는다고 뜨고, view_emp는 조회 권한을 받아 뜨게 된다.

 

 

 

SEQUENCE

정수값을 순차적으로 발행하는 객체. 채번기. 대부분 테이블의 pk컬럼의 고유한 식별값을 사용한다.

create sequence 시퀀스명 ---(필수)

start with 시작값(1)

increment by 증감값(1)

maxvalue (최대값) | nomaxvalue (기본값)

minvalue (최소값) | nominvalue (기본값)

cycle | nocycle (기본값)

cache 숫자(20) | nocache --메모리상에서 관리될 번호 개수.

* 메모리상의 번호가 유실되는 경우가 있지만, 대부분 pk로써 고유하기만 하면 문제되지 않음)

 

시퀀스의 start with값이 변경이 불가능하다.

번호를 수정하고 싶은 경우에는 시퀀스 객체를 삭제 후에 재생성해야한다.

increment by는 수정이 가능하다.

create table tb_member(
    no number primary key,       ------------- 회원 pk (고유번호)
    id varchar2(20) unique not null ------------- 회원 id
);

create sequence seq_tb_member_no
start with 1
increment by 1
nomaxvalue
nominvalue
nocycle
cache 20;

insert into tb_member values(seq_tb_member_no.nextval, 'honggd');
insert into tb_member values(seq_tb_member_no.nextval, 'sinsa');
insert into tb_member values(seq_tb_member_no.nextval, 'sejong');
insert into tb_member values(seq_tb_member_no.nextval, 'yoos');
insert into tb_member values(seq_tb_member_no.nextval, 'happyworld');

select * from tb_member;

 

나는 drop table tb_member; 를 두번 했다가 다시 값을 추가했더니 20+20+1 부터 NO가 시작되었다.

 

-- sequence객체 현재번호(마지막에 발급된) 조회 (1)
select 
    seq_tb_member_no.currval,
    seq_tb_member_no.nextval
from 
    dual;

-- dd에서 조회
select * from user_sequences;

 

(1)문장을 1번 실행할때마다 값이 증가했다.

 

 

ex) 주문번호 생성하기. kh-20220428-1234

create table kh_order(
    no varchar2(30),
    user_id varchar2(20),
    product_id varchar2(20),
    cnt number default 1,
    order_date date default sysdate,
    constraint pk_kh_order_no primary key(no)
);

 

create sequence seq_kh_order_no;

insert into 
    kh_order
values (
    'kh-' || to_char(sysdate, 'yyyymmdd') || '-' || to_char(seq_kh_order_no.nextval, 'fm0000'),
    'honggd', 'iphone13', 10, default
);

select * from kh_order;

 

 

 

 

INDEX

색인

sql명령어 처리속도 향상을 위해 특정 테이블 특정 컬럼에 대해서 생성하는 오라클 객체

key-value형태로 관리. key에는 컬럼값, value에는 레코드 주소.

테이블데이터와 별도로 색인데이터를 관리한다. (별도의 저장공간이 필요하다)

 

장점

1. 검색 속도가 빨라짐
2. 시스템 부하가 줄고 정체적인 성능이 향상됨

단점

1. 별도의 저장공간 필요, 인덱스 수정/생성/삭제에 별도 시간 소요
2. 테이블 데이터의 성격상 insert/update/delete가 잦은 경우 index에 의한 성능 저하 발생 가능

 

INDEX를 사용해야하는 컬럼은?

1. 선택도(selectivity)가 좋은 컬럼 (= 중복값이 적은 데이터, 고유값을 많이 가지는 데이터)
   1. 좋은 예 : 사번(pk) , 주민번호(uq), email(uq) > name --> pk/uq컬럼은 자동으로 생성
   2. 보통 예 : 부서코드, 직급코드
   3. 나쁜 예 : 퇴사여부, 성별, 결혼여부 등
2. where 절에 자주 사용되는 컬럼, Join 조건절에 자주 사용되는 컬럼
3. 테이블 데이터가 많은 경우 인덱스 사용이 필수.

 

INDEX를 사용 자제해야하는 컬럼은?

1. 선택도가 나쁜 컬럼
2. null값이 많은 컬럼
3. 테이블당 너무 많은 인덱스를 생성하면 성능저하 가능성이 있음.

 

실행계획을 통한 성능비교 (F10누르기)

emp_id가 더 성능이 좋은 것을 알 수 있다. ( cost 0 !)

 

 

인덱스 생성

create index idx_employee_emp_name on employee(emp_name);

 

이름으로도 인덱스가 생성되었다.

 

인덱스 사용시 주의점

1. 인덱스 컬럼에 변형이 가해진 경우 인덱스처리되지 않는다.

2. is null 비교하면 인덱스처리되지 않는다.

3. not 비교하면 인덱스처리되지 않는다.

4. 인덱스컬럼과 비교하는 값의 타입이 다른 경우 인덱스처리되지 않는다.

5. 오라클내부 cost-based optimizer의 실행계획에 의해 변경된 경우(힌트로 설정변경)

 

 

 

1. 인덱스 컬럼에 변형이 가해진 경우, 인덱스 처리되지 않는다.

select * from employee where substr(emp_no, 8, 1) = '1';

 

 

2. is null 비교하면 인덱스 처리되지 않는다.

select * from employee where emp_name is null;

 

 

3. not 비교하면 인덱스 처리되지 않는다.

select * from employee where emp_name != '송종기';

 

 

4. 인덱스컬럼과 비교하는 값의 타입이 다른 경우에는 인덱스 처리되지 않는다.

select * from employee where emp_id = 201;

 

 

 

 

 

5. 오라클내부 cost-based optimizer의 실행계획에 의해 변경된 경우(힌트로 설정변경)