DBMS(오라클) : 롤, 동의어, 계층적 질의문, Export, Import

* 롤 

• 다수 사용자와 다양한 권한을 효과적으로 관리하기 위하여 서로 관련된 권한을 그룹화한 개념
• 활성화 또는 비활성화를 통한 일시적 권한 부여 철회 가능
• 암호 부여 가능
• 사용자 또는 다른 롤에 대한 접근 권한 부여 및 철회 가능
• 자신에 대한 롤 부여나 순환적인 롤 부여는 불가능
• 롤은 특정 소유자나 특정 객체에 속하지 않음

 

* CONNECT 롤 

• 사용자가 데이터베이스에 접속하여 세션을 생성할 수 있는 권한
• 테이블 또는 뷰와 같은 객체를 생성할 수 있는 권한

 

* RESOURCE 롤

• 사용자에게 자신의 테이블, 시퀀스, 프로시저, 트리거 객체 생성
• 사용자 생성 시 : CONNECT롤과 RESOURCE롤을 부여
•  

* DBA 롤

• 시스템 자원의 무제한적인 사용이나 시스템 관리에 필요한 모든 권한
• DBA 권한을 다른 사람에게 부여할 수 있음
• 모든 사용자 소유의 CONNECT, RESOURCE, DBA 권한을 포함한 모든 권한을 부여 및 철회 가능

 

 

* 롤 생성

함호를 지정한 롤과 지정하지 않은 롤 생성

*롤에 권한 부여 또는 롤 부여

• 롤에 시스템 권한이나 객체 권한 또는 다른 롤을 부여 가능
• GRANT 명령문 사용

 

* 롤에 시스템 권한 부여

• DBA 또는 GRANT ANY PRIVILEGE 권한을 가진 사용자는 롤에 시스템 권한 부여 가능

 

hr_mgr 롤에 CREATE SESSION 시스템 권한을 부여

 

 

* 롤에 객체 권한 부여

• 사용자가 롤에 객체를 부여할 수 있는 경우 : 사용자가 객체의 소유자인 경우, WITH GRANT OPTION 옵션과 함께 객체 권한을 부여 받은 경우

 

 

hr 사용자의 student 테이블의 모든 칼럼에 대한 SELECT, INSERT, DELETE 객체 권한을 hr_clerk에 부여

 

 

* 롤 부여

• 롤은 사용자 또는 다른 롤에게 롤 부여
• WITH ADMIN OPTION을 부여 받은 롤은 사용자나 다른 롤에게 해당 롤을 재부여 가능

hr_clerk롤을 hr_mgr롤과 tiger 사용자에게 부여하라

 

 

tiger 유저에 접속 후 hr유저의 student 테이블에 접근하여 조회

 

* 롤 조회

=> role_sys_privs : 롤에 부여한 시스템 권한 조회

=> role_tab_privs, user_role_privs : 롤에 부여한 시스템 권한 조회

user_role_privs

 

role_tab_privs

 

---

* 동의어

• 데이터베이스 객체의 소유권은 해당 객체를 생성한 사용자가 가지므로 사용자가 소유한 객체에 접근하기 위해서는 소유자로부터 접근 권한을 부여받아야 함
• 다른 사용자가 소유한 객체를 조회할 때에는 소유자의 아이디를 객체 이름 앞에 첨부해야 함(Ex. hr.student)
• 객체를 조회할 때마다 객체의 소유자를 일일이 지정하는 것은 번거로움
• 하나의 객체에 대해 다른 이름을 정의하는 방법

 

* 동의어와 별명(ALIAS) 차이점

• 동의어는 데이터베이스 전체에서 사용
• 별명은 해당 SQL 명령문에서만 사용

 

* 전용 동의어 

• 객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어로 해당 사용자만 사용

 

* 공용 동의어

• 권한을 주는 사용자가 정의한 동의어로 누구나 사용
• DBA 권한을 주는 사용자만 생성

 

 

* 전용 동의어 생성

system 사용자 소유의 project 테이블에 my_project 전용 동의어를 생성하여라 - 1

 

system 사용자 소유의 project 테이블에 my_project 전용 동의어를 생성하여라 - 2

 

 

* 공용 동의어 생성

 

 

* 동의어 삭제

전용 동의어 삭제

 

 

공용 동의어 삭제

 

 

 

* 동의어 조회

 

 

 

 

 

 

---

* 계층적 질의문

• 관계형 데이터베이스에서 데이터 간의 부모 관계를 표현할 수 있는 칼럼을 지정하여 계층적인 관계를 표현
• 하나의 테이블에서 계층적인 구조를 표현하는 관계를 순환관계
• SELECT 명령문에서 START WITH와 CONNECT BY 절을 이용
• 계층적 질의문에서는 계층적인 출력 형식과 시작 위치 제어
• 출력 형식은 TOP-DOWN (루트노드부터 먼저 출력) , BOTTOM-UP (단말 노드부터 먼저 출력)

 

 

* TOP-DOWN 방식

 

* BOTTOM-UP 방식

 

 

 

* 레벨별 구분

부서 테이블에서 부서 이름을 검색하여 단대, 학부, 학과 순의 top-down 형식으로 출력해라. 시작 데이터는 '공과대학'이고, 각 레벨별로 우측으로 2칸 이동하여 출력해라

 

 

* 계층 구조에서 가지 제거 방법

• => 계층적 질의문에서 WHERE 절이나 CONNECT BY 절을 이용하여 계층 구조의 일부를 제거하고 나머지 출력
• => WHERE 절은 임의의 가지를 제거
• => CONNECT BY 절은 임의의 가지와 자식 노드까지 동시 삭제

 

* WHERE 절을 이용한 가지 제거 ( 임의의 가지를 제거)

 

 

* CONNECT BY 절을 이용한 가지 제거 (임의의 가지와 자식 노드까지 동시 삭제)

 

* 계층적 질의문 응용

Level이 1인 최상위 로우(루트노드)의 정보를 얻을 수 있다.

 

 

로우의 최하위레벨(Leaf) 여부를 반환한다.

 

 

현재 로우까지의 PATH 정보를 쉽게 얻어올 수 있다.

 

 

leaf node만 전체 PATH 정보가 나오도록 할 수 있다.

 

ORDER BY를 사용하였을 경우, 트리의 구조가 깨진다. 따라서 트리의 구조를 그대로 유지하려면 ORDER SIBLINGSBY를 사용해야 한다.

 

 

ORDER SIBLINGS BY를 사용함으로써 트리의 상관관계를 그대로 유지하며 내부요소를 정렬해준다

 

---

* PL/SQL

• 오라클에서 지원하는 프로그래밍 언어의 특성을 수용한 SQL의 확장
• PL/SQL Block 내에서 SQL의 DML문과 쿼리문(검색문) 그리고 절차향 언어(IF, LOOP) 등을 사용하여 절차적으로 프로그래밍을 가능하게 한 강력한 트랜잭션 언어

---

* 트리거

어떤 사건이 발생했을 때 내부적으로 실행되도록 데이터베이스에 저장된 프로시저

---

DB 죽이기

 

 

DB 접근 시 에러 발생

 

 

DB 부활 시키기

 

DB 접근

---

* Export

 

(1)

Export 실행 화면

 

Export 된 테이블

 

 

(2)

Export 실행 화면

 

Export 된 테이블

 

(3)

hr이 가지고 있는 모든 파일들을 Export

 

Export 된 테이블

 

(4)

Export 실행 화면

 

Export 된 테이블

 

 

* IMPORT

 

(1)

Import 실행

 

system 유저에서 Import 결과 확인

 

(2)

 

Import 하기 전 : 테이블 존재 X

 

Import 수행

 

Import 한 결과 : 테이블 생성 O