가전제품 이것저것 이야기

1. 사용자 계정 생성

CREATE USER LEE
IDENTIFED BY LEE_PASS
DEFAULT TABLESPACE USERS
QUOTA 2M ON USERS

: USERS - 오라클 설치 시 기본적으로 생성되는 테이블스페이스

2. SYSTEM 계정에서 LEE 에게 권한 부여

GRANT CREATE SESSION TO LEE;
GRANT CREATE TABLE TO LEE;

: CREATE SESSION - 데이터베이스 접속(로그인) 권한

: CREATE TABLE - 테이블 생성 권한

3. 오라클에서는 with grant option과 wit admin option 을 둘 다 지원한다.

WITH GRANT OPTION : 객체권한(DML)을 다른 사용자에게 전파할 수 있는 권한

WITH ADMIN OPTION : 시스템 권한을 전파할 때 사용

4. 오라클의 기본적인 롤(role) 종류

1) 롤 부여

2. 롤 생성하고 부여하기

3. 사용자로부터 롤을 회수

DBMS는 허가되지 않은 사용자, 즉 권한이 없는 사용자로부터의 데이터 접근을 차단한다.

1. 데이터베이스에서의 사용 권한

: 사용자가 데이터베이스 내의 특정 객체에 대해 특정 연산을 수행할 수 있는 권리

1) 특정 객체 - 테이블, 뷰, 필드, 저장 프로시저 등 데이터베이스의 구성 요소

2)특정 연산 - DML(SELECT, DELETE, INSERT, UPDATE) , DDL(CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX)

2. 권한에 따른 사용자 분류

1) 데이터베이스 관리자(DBA)

2) 객체 소유자(owner) : 생성한 객체에 대한 모든 권한 소유 (해당 객체에 대한 권한 부여나 회수도 가능)

3) 기타 사용자 : 기본적으로는 다른 사용자의 객체에 대한 어떠한 권한도 없음, 다만 객체 소유자나 DBA로부터 권한을 부여받을 수 있음

< SQL에서의 권한 제어 >

3. GRANT

: 권한을 부여하는 명령

1) 형식

<권한리스트> : SELECT, DELETE, UPDATE, INSERT, REFERENCES

2) 예시

3)

- REFERENCE 권한이란?

: 테이블을 외래키로 참조할 수 있는 권한 

: 권한이 없으면 student 테이블은 department 테이블을 참조할 수 없다.

- REFERENCE 권한 부여

: dept_id 는 테이블 department 의 기본키이다. 기본키만을 외래키로 참조할 수 있다.

4) 모든 사용자에게 권한 부여

5) 모든 종류의 권한을 하나의 명령으로 부여하는 방법

6) 부여받은 권한을 다른 사용자에게 전파할 수 있는 옵션

단, chang 은 더 이상 다른 사용자에게 권한을 부여할 수 없음

4. REVOKE

: 다른 사용자에게 부여한 권한을 회수(취소)하기 위한 명령

: 이 경우, kim 뿐만 아니라 권한을 부여한 chang으로부터 자동적으로 권한을 회수함

5. ROLE(롤)

: 권한별로 사용자 그룹을 만들어 그룹에 권한을 부여하는 방법

: 롤은 데이터베이스 관리자만 생성 가능하다.

1) 롤 생성 형식 및 예시

2) 롤 배정 형식 및 예시

3) 롤에 권한 부여 ( GRANT문의 형식과 동일 )

4) 롤에 부여된 권한 회수

5) 배정된 롤에서 사용자 배제

6) 롤 삭제

6. 뷰를 이용한 권한 제어

: 특정 테이블에서 일부 필드 혹은 일부 레코드들에 대해서 접근을 허용할 경우 이 부분들을 뷰로 정의

: kim은 junior 에 대한 접근은 가능하지만, student 테이블 전체에 대한 접근은 불가하다.

1. 무결성 제약(integrity constraint) 또는 무결성 규칙(integrity rule)

: 데이터베이스에 저장된 데이터가 현실 세계에 존재하는 정보들을 모순 없이 반영하는 성질이다.

: 데이터베이스에 저장된 데이터가 갖추어야 할 제약 조건을 항상 만족하도록 보장하는 성질이다.

예시 )

2. 무결성 제약 조건을 충족시키기 위한 방법

1) 데이터를 삽입, 삭제, 수정할 때마다 사용자, 즉 응용 프로그램이 직접 주어진 무결성 제약의 만족 여부를 검증

2) 데이터베이스 설계자가 데이터베이스가 지켜야 할 무결성 제약들을 정의

: DBMS가 자동적으로 제약 조건을 검증하여 무결성을 보장해 줌

3. 무결성 제약의 유형

[ 기본적 무결성 제약 ]

1) 기본키 무결성 제약(primary key integrity constraint)

: 기본키는 null 값을 가질 수 없으며, 기본키의 값이 동일한 레코드가 하나의 테이블에 동시에 두 개 이상 존재할 수 없다.

일반적인 방법 )

-> 사용자가 기본키를 정의하여 테이블을 생성하면 기본키 무결성 제약은 DBMS에 의해 자동적으로 검증된다.

간단한 방법 )

- 기본키가 하나의 필드로 구성될 때만 가능하다.

- 제약에 이름이 부여되지 않아 추후에 기본키를 취소하거나 변경할 수 없다는 단점이 있다.

2) 참조 무결성 제약

: 한 테이블의 레코드가 다른 테이블을 참조할 때, 참조되는 테이블에 해당 레코드가 반드시 존재하거나 null 값을 가져야 한다.

형식 ) 

예시 )

Q) 만약 테이블 생성 당시 외래키를 설정하지 않았고, 추가적으로 설정하고 싶다면?

A) ALTER 문으로 수정 가능

Q) 외래키 제약을 삭제하고 싶을 때에는?

A) alter table ~ drop constraint 문을 사용

[ 테이블의 무결성 제약 ]

- not null

- unique

- check

- default

1) NOT NULL

: 특정 필드에 대해 null 값 입력을 금지해야 하는 경우

참고 ) 기본키는 원래부터 null 값을 허용하지 않으므로 기본키로 정의된 필드에 대해서는 Not null 조건을 입력하지 않아도 됨

2) UNIQUE

: 해당 필드가 테이블 내에서 중복된 값을 갖지 않고 반드시 유일한 값을 갖도록 하는 제약 조건

** 두 개 이상의 필드에 동시 설정 가능하다.

: 두 필드 각각의 값이 유일해야 한다는 의미가 아니다. 아래의 경우에 이름과 성이 동시에 같은 경우에 대해 중복을 허용하지 않는다는 의미이다.

- unique 제약을 추가하거나 해제할 때 alter table 를 이용한다.

3) CHECK

: 각 필드의 값은 정의된 도메인에 속한 값만 허용한다.

: 필드를 정의할 때 주어진 데이터 타입 이외에도 좀 더 세부적으로 허용할 수 있는 값의 범위를 지정할 수 있다.

** 제약에 이름을 부여하지 않거나 각 필드에 대한 조건식을 분리하여 명시 가능하다.

-> 

- chek 제약을 추가하거나 해제할 때

4) DEFAULT

: 레코드를 삽입할 때, 필드에 대한 값이 정해지지 않을 경우 사전에 정해놓은 값이 입력되도록 디폴트 값을 설정

- default 제약을 추가 혹은 해제하고 싶을 때

- 오라클만의 default 설정 및 해제 방법

** 지나치게 많은 제약조건이 존재할 경우 많은 데이터가 입력될 때 문제가 생길 수 있다.

< 기타 무결성 제약 >

1) Assertion (주장)

2) 응용 프로그램에서의 무결성 제약

: 개발자가 응용 프로그램에서 직접 무결성을 보장하는 코드 작성

1. 뷰(View) 란?

기존 테이블로부터 생성(정의)되는 가상의 테이블

- 기반 테이블들에 대한 하나의 질의문(query)으로 정의된다.

( but 실제로 질의를 실행하는 것은 아님 ! 질의 그 자체가 뷰임. 물리적인 저장공간 없음)

- 뷰는 기반 테이블과 동등하게 사용된다.

< 기능 >

- 특정 사용자에게 테이블 내용 중 일부를 숨길 수 있기 때문에 데이터에 대한 접근 제한과 보안성을 높일 수 있다.

- 질의의 결과를 뷰로 만들어서 사용하면 복잡한 질의를 간단히 표현할 수 있다.

2. 뷰 생성 방법

- <SELECT문> : 뷰 생성에 사용될 질의문

- OR REPLACE 를 추가하면 <뷰이름> 과 같은 뷰가 이미 존재하는 경우, 기존의 뷰를 삭제하고 새로 생성한다.

3. 뷰 생성 권한

: 대부분의 DBMS에서 일반 사용자 계정에서는 뷰 생성 권한이 없다.

: 관리자가 특정 사용자에게 권한을 부여해야 뷰를 생성할 수 있다.

4. 직접 실습해보기

- SQLDevelper 에서 실습해 보기로 하자

1) localhost(관리자 계정) 에서 scott(사용자 계정)으로 뷰 생성 권한을 부여한다.

2) V_TAKES 라는 이름의 뷰 생성하기

참고) TAKES 테이블은 이렇게 생겼다.

3) v_takes 뷰를 이용하여 특정 학번의 학생 정보를 검색

질의결과)

5) INSERT문도 실행시켜보기

실행결과)

Q) 원래 TAKES 테이블에도 값이 추가가 될까?

A) Yes! 추가되지 않은 GRADE 열의 값은 null 이 들어간다. 아래 결과 확인

5. 읽기 전용 뷰

: INSERT, DELETE, UPDATE 와 같은 데이터 조작 언어(DML)의 사용을 막고 싶을 때에는 

WITH READ ONLY 키워드를 추가해준다. (SELECT 는 가능)

6. 뷰 삭제

7. 실습해보기 두번째

- 컴퓨터공학과 학생들의 정보만 추출하여 뷰를 생성한다.

** 문제발생) 기본키인 dept_id 가 select 되지 않았기 때문에 insert문을 실행시킬 수 없다.

1. 기본 개념

: SQL문 안에 SQL문을 중첩하여 사용하는 질의이다.

: 다음과 같은 연산자와 함께 사용한다.

- in, not in

- =some, <some, >some, <=some, >=some, <>some

- =all, <all, <=all, >all, >=all, <>all

- exists, not exists

-> From , where 절에 위치할 수 있다.

2. IN / NOT IN

select title

from course

where course_id not in ('C301', 'C302', 'C401');

라고도 쓸 수 있다.

3. SOME / ALL

= SOME : 지정된 필드의 값이 부-질의 검색 결과에 존재하는 임의의 값과 같은지를 나타낸다. (IN과 같은 의미)

<= SOME : 지정된 필드의 값이 부-질의 검색 결과에 존재하는 임의의 값보다 작거나 같은지를 나타낸다.

= ALL : 지정된 필드의 값이 부-질의 검색 결과에 존재하는 모든 값과 같은지를 나타낸다. 

<= ALL : 지정된 필드의 값이 부-질의 검색 결과에 존재하는 모든 값보다 작거나 같은지를 나타낸다.

4. EXISTS / NOT EXISTS

exists : 최소한 한 개의 레코드가 존재하면 참이 되고 그렇지 않으면 거짓이 된다.

not exists : 부-질의의 결과에 레코드가 하나도 없으면 참이 되고, 하나라도 있으면 거짓이 된다.

[ 집합 연산 ]

1. UNION : 두 연산의 결과를 모두 출력한다.

-> 중복을 제거하고 출력한다.

만약) 중복을 제거하지 않고 출력하고 싶다면,

UNION ALL 연산자를 사용하면 된다.

2. INTERSECT : 두 연산결과의 교집합을 구한다.

3. MINUS : 앞의 연산결과에서 뒤의 연산결과를 제외하고 연산한다.

* 주의 *

select 하는 필드는 동일해야 한다.

[ 외부 조인 ]

두 테이블의 레코드에서 모두 존재하지 않더라도, 가져오고 싶을 때 사용한다.

1) 왼쪽 외부 조인 : 왼쪽에 있는 모든 레코드를 가져온다.

2) 오른쪽 외부 조인 : 오른쪽에 있는 모든 레코드를 가져온다.

3) 완전 외부 조인

[ 집계 함수 ]

- SELECT와 HAVING 절에서만 사용 가능하다.

- sum, avg는 숫자형 데이터 타입일 때만 사용 가능하다.

- NULL은 계산에서 제외된다.

1) COUNT

: 해당 필드에 값이 몇 개 있는지 출력한다.

2) SUM

3) AVG

4) MIN, MAX

1. GROUP BY

* 주의 *

group by절에 포함되지 않은 필드는 select 절에도 포함될 수 없다.

2. HAVING

: 그룹에 대한 조건을 명시할 때 사용한다.