[산대특] Oracle10g[Role, Privilege Delegation, SYNONYM, DATA DICTIONARY, MERGE, Trigger, Cursor], MySQL

1008

 

  사용자와 롤(Role), 권한 위임(Privilege Delegation)

  사용자 계정 생성과 계정 잠금, 권한 부여 

사용자들이 가질 수 있는 권한들을 묶어서 여러 롤로 만들어, 각 롤에 사용자를 넣어

해당 롤에 주어진 권한을 해당 롤에 들어있는 사용자들이 사용하게 하는 것.

데이터베이스에서는 ‘사용자를 Role-based로 관리한다’

 

  롤(Role) 

  롤은 ‘CREATE ROLE role1’

생성 시 암호를 부여가능. 

not identified, identified by password, externally 등을 옵션. 

  롤은 관리자가 생성해주는데 사용자를 생성하는 방법과 동일.

 

 Oracle에서의 권한은 

∎ 일반적

connect

resource

dba

∎ User 관련

create user

alter user

drop user

∎ Session 관련

create session

alter session

∎ Table 관련

create table

create any table

alter any table

drop any table

insert any table

update any table

delete any table

select any table

∎ Index 관련

create any index

alter any index

drop any index

∎ 기타

View

Sequence

Synonym

Profile

Role 등

 

  사용자 생성 시 작업 공간을 지정. 

create user tester2 identified by tester2 default tablespace USERS temporary tablespace TEMP; 

사용자를 생성하면서  USERS 테이블스페이스와 TEMP 임시 테이블스페이스를 사용하게 할 수 있다. 

 

  사용자에 대해서 

alter user tester1 identified by tester1;    패스워드를 변경 

alter user tester1 account unlock;           잠긴 계정을 품 

drop user tester1 cascade;                       사용자를 삭제 

 

권한 위임을 통해서 다른 사용자의 테이블을 '사용자.테이블_명'식으로 지정해서 작업하게할 수 있다.

grant connect, resource, dba to test11;

두 개의 권한을 test11 사용자에게 부여한다. 

=>grant select, insert, delete, update on hr.employees to test11; 

hr 사용자의 employees 테이블에 대한 권한을 test11 사용자게 줄 수 있다.  

 

revoke connect from test11;

test11 사용자에게 부여한 권한 중 connect 권한을 뺏는다.

 

  Role 대신 사용자에게 with admin option을 주면 관리자 권한을 가지게 된다.

grant connect, resource(OR all) to mary with admin option; 

connect, resource 권한을 관리자 권한으로 수행 가능. 

 

  SYNONYM(동의어)

  동의어는 테이블 이름을 Alias처럼 별칭으로 다르게 만들어 주는 것, rename과 다르다. 

 

  DATA DICTIONARY

  데이터 딕셔너리는 Oracle 데이터베이스에 내장된 모든 객체의 정보를 가지고 있는 파일.

racle DBMS가 직접 관리해서 저장. 

 

어느 객체의 정보를 얻으려면 이 데이터 딕셔너리를 이용하면 된다. 

데이터 딕셔너리는 데이베이스 생성 시 함께 만들어 지며 

SYSTEM 테이블 스페이스에 Table이나 View 형태로 저장되어 있다.  

 

  데이터 딕셔너리에는 

현재 데이터베이스에 접속한 사용자가 소유한 객체 정보를 가지고 있는 USER,

데이터베이스 관리를 위한 정보를 가지고 있는 DBA,

 그리고 사용자가 접근 가능한 한 모든 스키마 정보를 가지고 있는 ALL 세 가지 종류.

딕셔너리에 저장된 모든 데이터는 대문자. 

 

USER 접두어 딕셔너리 - 사용자에 대한 것

DBA 접두어 딕셔너리 - 관리자에 대한 것

 

  MERGE(병합)

여러 명령어들을 묶어서(Merged) 질의하는 기능

하나 혹은 그 이상의 테이블에 대한 INSERT나 UPDATE 작업을 수행할 때 쿼리 작업이 편리.

INSERT나 UPDATE에서 조건을 추가할 수도 있고 여러 행에 대한 DELETE도 한 번에 수행.

MERGE 문은 자주 사용되므로 잘 알아 두는 것이 좋다. 

 

테이블에 내용을 넣는 것이 아니고 쿼리만 하는 것이라면 USING 절에 dual 테이블을 사용해서 처리. 

 

‘ON 조건’에 일치하면 UPDATE나 DELETE, 불일치하면 INSERT를 하는 쿼리문

'MERGE INTO 테이블_명 USING SELECT ~ ON 조건 

  WHEN MATCHED THEN UPDATE/DELETE ~ 구문

  WHEN NOT MATCHED THEN INSERT ~ 구문 

WHERE ~ 조건'

 

  트리거(Trigger)

쿼리에서 어느 지정한 조건이 만족되면 연관된 다른 쿼리 이벤트를 발생시키는 것. 

 

▪ SQL 실행 시기를 기준

before trigger(실행하기 전에 트리거 실행 됨:)

after trigger(실행하고 난 뒤 트리거 실행 됨)

 

▪ 실행 시 영향을 받는 곳을 기준

row trigger(행: 실행된 트리거가 row(행) 하나하나 마다 실행 됨) 

statement trigger(문장: 실행된 트리거가 INSERT, UPDATE, DELETE 문장에 한 번만 실행 됨) 

 

  트리거는 트랜잭션 제어문(COMMIT, ROLLBACK, SAVEPOINT)을 사용할 수 없다

 

Commit 과 Rollback 의 영향을 받는다.

 

  트리거에서 변수를 선언할 때 DECLARE를 써야 하고, 변수 선언 뒤에는 ;으로 끝내야 한다.

트리거 구문은 보통 

CREATE OR REPLACE TRIGGER 트리거_명

TIMING[BEFORE|AFTER] EVENT[INSERT|UPDATE|DELETE] ON 테이블_명

[FOR EACH ROW]

BEGIN

   ~

END; 

  커서(Cursor) 

쿼리 수행으로 반환되는 여러 값을 처리할 때 사용

사용자가 쿼리를 수행하면 결과 값을 글로벌 영역 내의 저장 공간에 가지고 있다가

원하는 시기에 순차적으로 값을 가져와서 처리한 뒤 결과를 반환하는 기법이다. 

특정 SQL 쿼리의 처리한 결과를 담고 있는 메모리 영역

=> C와 같은 프로그램에서의 포인터(pointer)로 볼 수 있다.

커서를 사용하면 이 ROW에 순차적으로 접근할 수 있다.

 

묵시적(implicit) 커서와 명시적(explicit) 커서

▸ 묵시적 커서는 오라클 내부에서 자동으로 생성, SQL 문장이 실행될 때마다 자동으로 만들어져 실행되는 커서 

▸ 명시적 커서는 사용자가 직접 정의해서 사용하는 커서.

묵시적 커서와 다르게 명시적으로 CURSOR라고 선언하고 사용하기 때문이다. 

 

 

명시적 커서의 속성과 값

a) %ISOPEN :

    최근에 실행된 SQL문의 묵시적 커서의 종료 유무 =>TRUE, FALSE

b) %FOUND : 

    최근에 실행된 SQL문의 결과 행 존재 유무, 결과 값이 있을 때 TRUE를 반환

    =>NULL, TRUE, FALSE, INVALID_CURSOR

c) %NOTFOUND : 

    SQL%FOUND와 반대 =>NULL, FALSE, TRUE, INVALID_CURSOR

d) %ROWCOUNT :

    최근 실행된 쿼리의 행의 개수 반환 =>INVALID_CURSOR 

e) %TYPE :

    데이터 타입 지정 등이 있다. 

 

오라클 11g 이상에서는 데이터베이스를 클라우드에 가상화 데이터베이스로 설치하고 사용할 수 있다.

 

  PS/SQL Programming

=> 파일 주심. 나중에라도 읽어볼 것

 

** SQL Developer(GUI), MySQL command, phpMyAdmin & MySQL WorkBench(GUI)

<=이들을 통합한 GUI 도구가 DBeaver이다. 

   ER-WIN <=데이터베이스 모델링 도구 

 

  MySQL 데이터베이스 

  MySQL 엔진에는 두 가지가 있는데 (인터뷰)

∙ InnoDB는 복잡한 대용량의 데이터베이스를 처리에 사용되므로 무거워서 서버 사양이 좋아야함. 데이터베이스 운영엔진 

∙ MyISAM은 적은 메모리와 디스크를 사용하므로 가벼워서 서버 사양이 낮아도 된다. 데이터 저장엔진

 

  파일에 있는 데이터를 입력받아서 테이블을 완성. 

a) mysql -u root -p --local-infile

비번입력

b) 기존 테이블에서 테이블만 복사해서 신규 테이블을 만듦

create table name_tbl LIKE cent_tbl; 

create table name1_tbl LIKE cent_tbl;

두 개의 빈 테이블을 만들어 둔다.

c) set global local_infile=1; 

d) load data local infile '/home/centos/name.txt' into table name_tbl

             fields terminated by '\t'          ## Tab으로 구별 

             lines terminated by '\n'; 

e) load data local infile '/home/centos/name.txt' into table name1_tbl

             fields terminated by ','          ## , 로 구별 

             lines terminated by '\n'; 

테이블에 데이터가 들어간다.

 

  암호화 데이터 만들기

  테이블에 입력된 데이터를 암호화해서 숨기고 복호화해서 읽을 수 있다. L

TEXT 데이터를 알아볼 수 없게 암호화해서 저장하는 기법으로 단방향과 양방향(인터뷰)

 

∎ 단방향 암호화

DB 사용자 비밀번호가 저장되어 있는 것처럼 저장 이후에 다시는 <평문>으로 볼 수 없는 경우에 주로 사용

일반적으로 MySQL 내에서 보면 대표적으로 md5(32 HEX) 또는 sha1(40 HEX) 등의 함수를 사용.

 

∎ 양방향 암호화

이메일 주소나 전화번호, 전자서명 등과 같이

다시 사용할 가능성이 있어서 암호화 이후 다시 복호화하여 <평문>으로 볼 필요가 있는 데이터를 저장할 때 사용.

양방향 암호화는 AES_ENCRYPT(암호화)와 AES_DECRYPT(복호화) 함수를 이용해서 진행

 

  복호화하는데 역시 AES_DECRYPT() 함수를 사용함

AES_DECRYPT(unhex(암호화된_값), '암호화_키') 형식으로 해준다.

select user_id, AES_DECRYPT(unhex(password), 'a') from encrypt_tbl;

a라는 키만 넣었서 해당하는 a 의 비밀번호만 보이고 나머지 데이터는 null로 보인다. 

만약 ‘암호화_키’ 값이 같으면, unhex(password), ‘같은 암호화_키’ 를 하면,

해당 암호화_키를 갖고 있는 암호들이 함께 복호화된다.