KTDS_자바&파이썬 웹애플리케이션전문가 양성과정교육_24일차(DB)

자바&파이썬 웹애플리케이션 전문가 양성과정 채용연계형 교육과정 24일차

KT 자바교육 후기 (2021-07-22 ~ 2021-12-03) 

2021-08-25 (참고, 8월 16일 ~ 9월 3일 비대면 주간, 4단계 거리두기로 인하여)

SQL Oracle 데이터 딕셔너리, 시스템 뷰 ,USER 뷰,DBA 뷰,TAB,테이블 (TABLE),뷰 (VIEW) ,시퀀스 (SEQUENCE),
인덱스 (INDEX) ,동의어(SYNONYM) ,USER 생성 삭제 권한, 롤,

---

[오전]

  복습 및 쿼리 예제 풀이!

 

---

[오후]

데이터 딕셔너리(Data Dictionary)

SELECT * FROM DICT;

DICT는 DICTIONARY의 약자 

권한이 있는 데이터사전에 대한 데이터사전명과 설명 정보를 제공 ▪ 데이터베이스에 있는 모든 스키마 OBJECT들의 정의내용

 

 

 

SELECT * FROM DICT WHERE table_name = 'USER_OBJECTS';

스키마 오브젝트들과 이들이 데이터베이스에서 차지하는 공간 등에 대한 정보 

테이블의 컬럼들에 대한 디폴트 값 정보 

제약조건

모든 오라클 사용자들에 대한 정보 

각 사용자에게 부여된 권한 정보 

감사(auditing) 정보 

기타 일반적인 데이터베이스에 대한 정보

 

 

 

Data Dictionary의 종류 

기준 테이블 및 참고 가능한 뷰 

시스템 뷰 

ALL 뷰

 - ALL로 시작되는 뷰

 - 자식 계정의 소유이거나 접근 권한을 부여 받은 타 계정의 객체 조회

 - ALL_OBJECTS

 - ALL_TABLES

 - ALL_CONSTRAINTS …

 

DBA 뷰

 - DBA로 시작되는 뷰

 - 데이터베이스 관리자를 위한 정보를 제공하는 뷰들

 - 일부 사용자(관리자)들만 접근 가능

 - DBA_OBJECTS

 - DBA_TABLES

 - DBA_CONSTRAINTS …

 

USER 뷰

 - USER로 시작되는 뷰

 - 로그인한 사용자가 소유자가 되는 스키마 정보를 갖고 있음

 - USER_OBJECTS

 - USER_TABLES

 - USER_CONSTRAINTS, …

 

 

데이터 딕셔너리(Data Dictionary)

• USER_TABLES(TABS) : 테이블에 대한 정보 
• USER_VIEWS : 뷰에 대한 정보 
• USER_SEQUENCES(SEQ) : 시퀀스 
• USER_CONS_COLUMNS : 제약 조건을 가진 열에 대한 정보 
• USER_COL_COMMENTS : 컬럼에 대한 주석 
• USER_IND_COLUMNS : 인덱스열에 대한 정보
• USER_DB_LINKS : 데이터베이스 링크에 관련된 정보
• USER_INDEXES(IND) : 인덱스에 관한 정보
• USER_TAB_COMMENTS : 테이블/뷰에 대한 주석 
• USER_CONSTRAINTS : 제약조건 
• USER_SYNONYMS(SYN) : 동의어 
• USER_TAB_COLUMNS(COLS) : 컬럼에 대한 정보
• USER_TRIGGERS : 트리거 
• USER_SOURCE : 프로시저, 함수 및 패키지 
• USER_ERRORS : 코드 오류 
• USER_TABLESPACES : 테이블스페이스 
• USER_USERS : 사용자 
• USER_TAB_PRIVS : 테이블 권한 
• USER_COL_PRIVS : 열 권한 
• USER_SYS_PRIVS : 시스템 권한

 

기타 Data Dictionary 

TAB

 - TABLE의 약자로 해당 사용자가 소유하고 있는 데이블의 정보를 알려주는 DICTIONARY

 - [구문]

SELECT * FROM TAB;

 

---

OBJECT 종류

객체	의미
TABLE	데이터 저장소
VIEW	여러 테이블의 데이터를 조회하는 가상 테이블
SEQUENCE	자동 채번 객체
SYNONYM	객체의 별칭
INDEX SQL	성능 향상

 

각종 참조 시스템 뷰 

ALL_OBJECTS

ALL_INDEXES 

ALL_VIEWS 

ALL_SYNONYMS

ALL_SEQUENCES, …

 

 

---

테이블 (TABLE)

가장 기본이 되는 객체, 데이터 저장소 

Row와 column으로 구성

CREATE TABLE table_name (
  column_name1 column_name1_type [[NOT] NULL], 
  column_name2 column_name2_type [[NOT] NULL],
  … )
TABLESPACE tb_name
PCTUSED 0
PCTFREE 10
INITRANS 1 
MAXTRANS 255 ….. ;

 

 

 

뷰 (VIEW) 

뷰는 하나의 가상 테이블 

뷰는 실제 데이터가 저장 되는 것은 아니지만 뷰를 통해 데이터 관리 가능

데이터 보안, 쿼리 관리 

Read-Only 뷰, Updatable 뷰

CREATE [OR REPLACE] VIEW [schema.] view_name
AS SELECT clause …
[WITH CHECK OPTION] [WITH READ ONLY]
DROP VIEW [schema.] view_name;

 

 

 

시퀀스 (SEQUENCE)

자동 채번 객체로 데이터가 자동 증가하는 속성의 컬럼에 많이 사용

NEXTVAL, CURRVAL 의사컬럼

DROP SEQUENCE “schema.seq_name”;

ALTER SEQUENCE sequence_name
  [INCREMENT BY n]
  [MAXVALUE n | NOMAXVALUE] 
  [MINVALUE n | NOMINVALUE] 
  [CYCLE | NOCYCLE]
  [CACHE | NOCACHE]

 

 

 

인덱스 (INDEX) 

 테이블의 데이터를 조회 성능을 향상시키는 객체 

 SQL문의 처리 속도 향상

 인덱스 데이터가 별도로 저장됨 

 저장된 인덱스를 통해 데이터 조회 

 단일 인덱스, 복합 인덱스 

 UNIQUE 인덱스, NON-UNIQUE 인덱스 

 B-TREE 인덱스, BITMAP 인덱스 

 기타

CREATE [UNIQUE] INDEX [schema.] index_name
	ON [schema.] table_name (column_name1, [column_name2, column_name3 …]); 
DROP INDEX [schema.] index_name;

 

 

 

 B-TREE 인덱스는 B-tree(binary search tree)를 기반 

 인덱스의 물리적 구조가 좌우 대칭 구조를 이루고 있어 Balance-tree라 

 B-tree 인덱스는 컬럼안에 유일한 데이터가 많을 때 효과적

 

 

 

동의어(SYNONYM) 

객체의 별칭 (ALIAS), 일회성이 아님. 

테이블 이름이 너무 길어 사용이 불편한 경우 간단하고 짧은 이름을 부여하고자 할 때 사용 

PUBLIC : 지정하지 않으면 생성자만 사용가능

CREATE [OR REPLACE] [PUBLIC] SYNONYM ‘[schema.] syn_name’
	FOR ‘[schema.] object_name’;

DROP [PUBLIC] SYNONYM ‘schema.syn_name’;

 

---

사용자의 사용자의 생성과 권한의 설정

USER 란

데이터베이스에 접속하여 데이터를 관리하는 계정

 

 

 

데이터베이스 스키마란 

데이터베이스에서 데이터 간 관계, 데이터 구조, 제약 조건 등

데이터를 저장 및 관리하기 위해 정의한 데이터베이스 구조의 범위

 

 

 

USER 생성 

DBA가 CREATE USER 명령어로 USER를 생성 

ALL_USERS

CREATE USER user01 IDENTIFIED BY user01;

CREATE USER user IDENTIFIED {BY password | EXTERNALLY}
  [DEFAULT TABLESPACE tablespace] 
  [TEMPORARY TABLESPACE tablespace] [PASSWORD EXPIRE]
  [ACCOUNT {unlock/lock}] 
  [PROFILE profile]
  [QUOTA {integer [K|M] | UNLIMITED} ON tablespace ]

 

 

 

USER 삭제 

DBA가 DROP USER 명령어로 USER 스키마를 삭제 

CASCADE - 사용자 이름과 관련된 모든 데이터베이스 스키마가 데이터 사전으로부터 삭제

  - 모든 스키마 객체들 또한 물리적으로 삭제

DROP USER user01 [CASCADE];

 

 

 

권한(PRIVILEGE) 

특정 타입의 SQL문을 실행하거나 데이터베이스나 데이터 베이스 객체에 접근할 수 있는 권리 

시스템 권한(System Privileges)

  - 사용자가 데이터베이스에서 특정 작업을 수행할 수 있는 권리 

객체 권한(Object Privileges) - 사용자가 특정 객체를 액세스 및 조작할 수 있는 권리 

GRANT 명령은 사용자 또는 Role에 대해서 권한을 부여 

REVOKE 명령은 권한을 삭제

 

 

시스템 권한(SYSTEM PRIVILEGE)

GRANT privilege TO [user| role | PUBLIC] [WITH GRANT OPTION];

REVOKE privilege FROM [user | role | PUBLIC ]

시스템 권한의 종류(100개 이상)

 - CREATE SESSION : 데이터 베이스를 연결할 수 있는 권한

 - CREATE ROLE : 오라클 데이터베이스 역할을 생성할 수 있는 권한

 - CREATE VIEW : 뷰의 생성 권한 - ALTER USER : 생성한 사용자의 정의를 변경할 수 있는 권한

 - DROP USER : 생성한 사용자를 삭제 시키는 권한

시스템 권한을 취소 할 때는 연쇄적으로 취소 되지 않는다.

GRANT create session, create table TO user01 ; 
REVOKE create session, create table FROM user01 ;

 

 

객체 권한(OBJECT PRIVILEGE) 

기본적으로 유저가 소유한 오브젝트에 대한 모든 권한은 자동적으로 획득

Object 소유자는 다른 사용자에게 특정 Object Privileges를 부여 가능 

PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 해야 함 

GRANT privilege ON object TO [user| role | PUBLIC] [WITH GRANT OPTION]; 

REVOKE privilege ON object FROM [user| role | PUBLIC]

  [WITH GRANT OPTION

  [CASCADE CONSTRAINTS];

WITH GRANT OPTION을 사용하여 부여한 객체 권한을 취소하면 취소 작업이 연쇄적으로 수행

GRANT select, insert On emp TO user01 ; 
REVOKE select, insert On emp FROM user01 ;

 

 

권한관련 데이터 사전

 

 

 

롤(ROLE)

 롤은 사용자에게 허가 할 수 있는 권한들의 집합

 

 

롤(ROLE) 부여순서

SQL>CREATE ROLE manager -- role을 생성

SQL>GRANT create session, create table TO manager -- role에 권한을 부여

SQL>GRANT manager TO scott, test; -- 권한이 부여된 role을 user나 role에 부여

 

 

 

롤(ROLE)관련 데이터 사전

데이터 사전	설 명
ROLE_SYS_PRIVS	Role에 부여된 시스템 권한
ROLE_TAB_PRIVS	Role에 부여된 테이블 권한
USER_ROLE_PRIVS	현재 사용자가 ACCESS할 수 있는 ROLE
USER_TAB_PRIVS_MADE	현재 사용자의 객체에 부여한 객체 권한
USER_TAB_PRIVS_RECD	현재 사용자의 객체에 부여받은 객체 권한
USER_COL_PRIVS_MADE	현재 사용자 객체의 특정 컬럼에 부여한 객체 권한
USER_COL_PRIVS_RECD	현재 사용자 객체의 특정 컬럼에 부여받은 객체 권한

 

 

 

롤(ROLE)

오라클 데이터베이스를 생성하면 기본적으로 생성되는 롤(Role)

ALL_ROLES 

CONNECT : 오라클에 접속 할 수 있는 세션 생성 및 테이블을 생성하거나 조회 할 수 있는 가장 일반적인 권한

 - SELECT grantee, privilege FROM DBA_SYS_PRIVS

       WHERE grantee = 'CONNECT'; 

RESOURCE : Store Procedure나 Trigger와 같은 PL/SQL을 사용할 수 있는 권한

 - SELECT grantee, privilege FROM DBA_SYS_PRIVS WHERE grantee = 'RESOURCE';

 

 

---