12. 데이터 무결성
12-1) 데이터 무결성 제약의 개념
– 데이터의 정확성과 일관성 보장
0 데이터 무결성 제약 조건의 이점
– 테이블 생성 시 Constraint 정의 가능
– 데이터 딕셔너리에 테이블이 정의되어 저장되기 때문에
애플리케이션에 입력된 모든 데이터에 동일하게 적용됩니다.
– 제한을 활성화 또는 비활성화하는 유연성
0 – 무결성 제약 유형
– NOT NULL : 열에 NULL이 포함되지 않아야 합니다.
– CHECK : 컬럼에 저장할 수 있는 데이터 값의 범위나 조건을 지정
– 고유 키: 테이블의 행에서 고유한 값을 갖는 열 또는 열 조합을 식별합니다.
– 기본 키: 해당 열 값이 존재하고 고유해야 합니다.
잘못된 UNIQUE NOT NULL 제약 조건
– 외래 키: 참조 테이블의 열과 열 사이에 외래 키 관계를 생성하고 적용합니다.
① 기본 키 무결성 제약
② 고유 키 무결성 제약
③ NOT NULL 제약
④ CHECK 무결성 제약 조건
⑤ 참조 키 무결성 제약 조건
12-2) 데이터 무결성 제약의 종류
0- NOT NULL 무결성 제약
– 열 값이 NULL이 될 수 없도록 정의하는 제약 조건입니다.
– 테이블 수준이 아닌 열 수준에서만 지정할 수 있음
– NOT NULL 제약 조건으로 지정된 열에는 값이 포함되어야 합니다.
생략하면 오류
– 기존 데이터를 NULL로 변경해도 오류 발생
0 – 고유 키에 대한 무결성 제약
– 테이블 내의 어떤 열도 동일한 값을 가질 수 없도록 정의하는 제약 조건
– 열 또는 테이블 수준에서 정의 가능
– 고유 키 제약 조건을 위반하지 않으므로 NULL 입력 가능
(NULL은 항상 고유함)
– 고유 키 제약 조건을 정의하면 해당 제약 조건과 동일한 이름의 인덱스가 자동으로 테이블에 생성됩니다.
0- 기본 키 무결성 제약
– 기본 키는 테이블에 저장된 행을 고유하게 식별할 수 있는 식별자로 사용됩니다.
관계형 데이터 모델에서 테이블은 행을 요소로 하는 집합으로 정의됩니다.
집합의 모든 행을 식별하는 식별자가 필요합니다.
– 기본 키 제약 조건은 테이블의 각 행을 하나 이상의 열로 구분하는 데 사용됩니다.
식별자 정의 조건
– high-key 제약 조건과 NOT NULL 제약 조건을 결합한 개념
0 – 참조 무결성에 대한 제약
– 자신 또는 다른 테이블의 열 값 중 하나와 일치하도록 한 테이블의 열 값에 대한 제약
– 하위 테이블: 다른 테이블의 열 값을 참조하는 테이블
– 상위 테이블: 다른 테이블이 참조하는 테이블
– 외래 키: 상위 테이블의 열 값을 참조하는 하위 테이블의 열입니다.
– 참조 키: 자식 테이블이 참조하는 부모 테이블의 열
0- CHEK 무결성 제약
– 열에 허용되는 데이터 범위 또는 조건을 지정합니다.
– 데이터 입력 및 변경 시 실수로 잘못된 값 입력 방지
– 열에 대해 여러 CHECK 제약 조건을 지정할 수 있습니다.
– CURRVAL, NEXTVAL과 같은 가상 컬럼이나 SYSDATE, USER와 같은 함수는 사용할 수 없습니다.
12-3) Constraints 생성 방법
0- 무결성 제약 조건을 만드는 방법
– 테이블 생성과 동시에 정의
– 테이블을 생성한 후 테이블을 추가하거나 삭제할 수 있습니다.
– 제약조건 이름을 지정하지 않으면 자동으로 SYS_Cn 형태로 생성됨(프라이머리 키에 이름을 추가해야 함)
– Column Level: Column 정의 시 각 Column 별로 설정
– 테이블 수준: 제약 조건은 하나 이상의 열을 참조하고 지정합니다.
– 사용하는 방법
CREATE TABLE (schema.) table
(column datatype(DEFAULT expression)
(column_constraint),
(table_constraint)(,…));– 열 수준 제한 정의
column_constraint : column-level 제약 조건을 정의할 때
– 테이블 수준 제약 조건 정의
table_constraint : 테이블 수준 제약 조건을 정의합니다.
0- 제약 조건 생성 문의 키워드
– CASCADE에서 삭제
: 상위 테이블에서 외래 키가 참조하는 기본 키 또는 고유 키를 포함하는 행을 삭제할 때
또한 하위 테이블의 외래 키가 있는 행을 삭제하십시오.
– 색인 사용
: 기본 키 또는 고유 키 제약 조건 생성 시 암시적으로 생성되는 인덱스에 대한 스토리지 매개 변수를 정의합니다.
– 연기할 수 없습니다.
: DML 문이 처리될 때마다 제약 조건 위반 여부를 확인하며, defer_spec이 지정되지 않은 경우 기본값입니다.
-가능한
: 트랜잭션이 끝날 때까지 트랜잭션 내의 모든 DML 문에 대한 제약 조건 확인을 연기합니다.
– 먼저 즉시
: DML 문 끝에서 제한 사항 확인
– 원래 지연됨
: 거래 종료 시에만 제한 사항 확인
13. 인덱스 관리
13-1) 인덱스 개념
– SQL 문 처리 속도 향상을 위해 컬럼에 대해 생성된 객체
– 주기를 사용하여 테이블에 지정된 데이터에 대한 임의 액세스에 사용됩니다.
0- 인덱스 유형
– 고유 인덱스
– 고유하지 않은 인덱스
– 단일 인덱스
– 필수 디렉토리
– 셧다운 인덱스
– 기능 기반 인덱스
13-2) 인덱스 구조
– 인덱스 내부 구조는 B-tree 형태
13-3) 인덱스를 효율적으로 사용하는 방법
0- 인덱스가 효율적인 경우
– WHERE 절 또는 조인 조건에서 자주 사용되는 열
– 전체 데이터의 10~15% 이내의 데이터를 검색하는 경우
– WHERE 절이나 조인 조건에서 2개 이상의 컬럼을 자주 사용하는 경우
– 스프레드시트에 저장된 데이터에 대한 드문 변경
– 열에 많은 null 값이 포함된 경우 열에 광범위한 값이 포함된 경우
0- 인덱스 생성
CREATE (UNIQUE) INDEX index
ON table (column1(ASC|DESC) (, column2(ASC|DESC),…));– UNIQUE: 고유 인덱스 포인트
– ASC|DESC : 인덱스 키를 오름차순 또는 내림차순으로 정렬
13-4) 인덱스 종류 및 생성 방법
0- 고유 인덱스
고유 인덱스는 고유한 값을 가진 열에 대해 생성된 인덱스입니다.
각 인덱스 키는 테이블의 행과 연결됩니다.
0- 고유하지 않은 인덱스
– 중복 값이 있는 컬럼에 인덱스 생성
인덱스 키는 테이블의 여러 행과 연관될 수 있습니다.
0- 단일 인덱스
– 하나의 컬럼으로만 구성된 인덱스
0- 바인딩 인덱스
– 두 개 이상의 컬럼을 결합하여 생성된 인덱스
0- 종료 인덱스
– 칼럼별로 정렬 순서를 별도로 설정하여 복합 인덱스를 생성하는 방식
0- 함수 기반 인덱스
– 컬럼 연산이나 함수의 계산 결과를 인덱스로 생성 가능
– UPPER(column_name) 또는 LOWER(column_name) 키워드로 정의된 기능 기반 인덱스 사용
검색은 대소문자를 구분하지 않습니다.
함수 기반 인덱스는 INSERT 및 UPDATE 중에 인덱스에 새 값을 추가합니다.
13-5) 인덱스 실행 경로 확인
– 실행 경로: 내부적으로 명령어가 처리되는 경로
– SQL 문 실행 시 조회 결과만 반환되므로 실행 경로는 인덱스를 사용
임의 접근을 했는지, 전체 테이블을 검색했는지 프로그래머가 확인하기 어렵다.
13-6) 인덱스 관리
0- 색인 정보 조회
– USER_INDEXES: 인덱스 이름 및 고유성 확인
– USER_IND_COLUMNS: 인덱스명, 테이블명, 컬럼명, 인덱스가 생성된 위치 등을 확인한다.
– 인덱스 재구성: 인덱스가 정의된 테이블의 컬럼 값이 자주 변경되는 경우,
인덱스 내 불필요한 노드 정리 작업
ALTER INDEX (schema.) index REBUILD
(TABLESPACE tablespace)==========================================
14. 사용자 권한 제어
14-1) 데이터베이스 보안
0- 다중 사용자 환경
– 불법적인 접근 및 유출을 방지하기 위한 보안조치 필요
Oracle에서 사용자는 자신이 만든 개체를 소유합니다.
데이터를 조작하거나 볼 수 있음
– 다른 생성자가 소유한 개체는 소유자의 허가 없이 액세스할 수 없습니다.
– 다중 사용자 환경에서 데이터베이스 관리자의 비밀번호는 철저하게 관리됩니다.
0- 중앙 집중식 데이터 관리
– 기존 분산 파일 시스템에 비해 보안이 취약하여 철저한 보안대책 필요
0- 시스템 보안
– 시스템 관리자 수준에서 데이터베이스 자체에 대한 접근 권한 관리
– 데이터베이스 관리자가 사용자 계정과 암호를 관리하고 사용자별로 디스크 공간을 할당합니다.
0- 데이터 보안
– 사용자가 객체를 조작하기 위한 액션 관리
– 데이터베이스 개체에 대한 액세스 권한 관리
14-2) 권한
– 데이터베이스 시스템을 관리하거나 개체를 사용하는 사용자의 권한
0- 권한 유형
– 시스템 권한
: 시스템 수준의 자원 관리 또는 사용자 스키마 객체 관리 등
데이터베이스 관리 작업을 수행할 수 있는 권한
: 오라클에서는 테이블, 뷰, 롤백 세그먼트, 프로시저 등의 객체를 생성
삭제 및 수정과 관련된 140여종의 시스템 권한 지원
– 개체 허가
: 테이블, 뷰, 시퀀스, 함수 등의 객체를 조작할 수 있는 권한
: 개체 유형에 따라 다른 유형의 개체 권한
: 각 테이블 열에 대한 삽입, 업데이트 및 참조와 같은 권한 세부 정보
0 – 데이터베이스 관리자 시스템 권한
– 사용자 생성 및 삭제, 사용자 계정의 개체 생성 또는 수정, 데이터베이스 백업 관리
CREATE USER : 사용자 생성 권한
DROP USER : 사용자를 삭제할 수 있는 권한
DROP ANY TABLE : 모든 테이블을 삭제할 수 있는 권한
QUERY REWRITE: 함수 기반 인덱스 생성 권한
BACKUP ANY TABLE : 내보내기 유틸리티를 사용하여 모든 테이블을 백업할 수 있는 권한
0- 일반 사용자가 가질 수 있는 시스템 권한
– 사용자 생성 개체 및 저장 프로시저 관리
CREATE SESSION: 데이터베이스에 접근할 수 있는 권한
CREATE TABLE : 사용자 스키마에 테이블을 생성할 수 있는 권한
CREATE SEQUENCE: 사용자 스키마에 시퀀스를 생성할 수 있는 권한
CREATE VIEW : 사용자 스키마에 뷰를 생성할 수 있는 권한
CREATE PROCEDURE : 사용자의 스키마에 프로시저, 함수, 패키지를 생성할 수 있는 권한입니다.
0- 시스템 권한 부여
– 시스템 권한은 특정 사용자 또는 모든 사용자에게 부여할 수 있습니다.
– 역할에 시스템 권한 부여 가능
– GRANT 문을 사용
0- 세션_PRIVS
– 현재 세션에서 사용자 및 역할에 부여된 시스템 권한을 쿼리할 수 있습니다.
0- 시스템 권한 취소
– 데이터베이스 관리자 또는 권한을 부여한 사용자
부여할 시스템 권한을 철회할 수 있음
– REVOKE 문 사용
0- 개체 권한
– 테이블, 뷰, 시퀀스, 함수 등 객체별 사용 권한
0- 개체 권한 부여
– 데이터베이스 관리자 또는 개체 소유자의 사용자 및 역할에 부여됨
– GRANT 문으로 권한 부여
: ALL : 사용자에게 모든 개체 권한을 부여합니다.
: PUBLIC : 이 개체에 대한 모든 사용자 권한 부여
: WITH ADMIN OPTION : 다른 사용자에게 시스템 권한을 부여하거나
역할 재부여 허용
0- 개체 권한 취소
– REVOKE 문으로 해지
: CASCADE CONSTRAINTS : REFERENCES 또는 ALL 권리를 철회할 때
관련 참조 무결성 제약 조건도 삭제합니다.
14-3) 던지기
– 여러 사용자와 다양한 권한을 효과적으로 관리하기 위해
관련 권리 그룹화의 개념
– 일반 사용자의 데이터베이스 사용을 위한 공유 권한
(데이터베이스 접근 권한, 테이블 생성, 수정, 삭제, 쿼리 권한,
빌드 권한 표시)
– 이러한 권한을 그룹화하고 역할로 정의함으로써
액세스 권한을 할당하면 효과적으로 권한을 관리할 수 있습니다.
0-Roll 부여
– 임시 승인은 활성화 또는 비활성화로 취소될 수 있습니다.
– 비밀번호 지정 가능
– 사용자 또는 기타 역할에 대한 액세스 권한을 부여 및 취소할 수 있습니다.
– 자녀에게 역할이나 순환 할당을 할당할 수 없습니다.
– 역할은 특정 소유자 또는 개체에 속하지 않습니다.
0- 역할 유형
– 미리 정의된 역할
: 사용자 접근, 리소스 생성, DBA
: 미리 정의된 역할 할당 및 철회
– 사용자 지정 역할
– CONNECT 역할
– 자원 롤
-DBA 역할
===========================
계층 구조 출력 유형
하향식 : 위에서 아래로
아래에서 위로: 부모님만 아래에서 위로 볼 수 있습니다.