0208 오라클 DB 입문 - 제약 조건 및 사용자, 권한, 롤 관리

1. 제약조건 (CONSTRAINT / NOT NULL(빈값 불가) / UNIQUE (중복 불가) / PRIMARY 키)

1) CONSTRAINT

--제약조건 이름 변경
ALTER TABLE TABLE_NOTNULL2
RENAME CONSTRAINT TBLNN_TEL_NN TO TBLNN2_TEL_NN;

바뀐 제약조건 이름

--변경된 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;

TBLNN2_TEL_NN으로 변경된 모습

--삭제된 제약조건 확인
DESC TABLE_NOTNULL2

2) UNIQUE

--중복 허용하지 않음 = UNIQUE
CREATE TABLE TABLE_UNIQUE(
 LOGIN_ID VARCHAR2 (20) UNIQUE,
 LOGIN_PWD VARCHAR2(20) NOT NULL,
 TEL VARCHAR2(20)
);
SELECT * FROM TABLE_UNIQUE;
DESC TABLE_UNIQUE;
--변경된 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;

SYS_C0011119는 UNIQUE로 설정된 모습

U : UNIQUE

C : NOT NULL OR CHECK 

P : PRIMARY KEY

R : FOREIGN KEY

Y : Y는 아무것도 아님

--중복되는 데이터 추가
SELECT * FROM TABLE_UNIQUE;
INSERT INTO TABLE_UNIQUE(LOGIN_ID,LOGIN_PWD,TEL)
VALUES('TEST_ID_01','PWD01','010-1234-5678');
SELECT * FROM TABLE_UNIQUE;
INSERT INTO TABLE_UNIQUE(LOGIN_ID,LOGIN_PWD,TEL)
VALUES('TEST_ID_01','PWD01','010-1234-5678');
--아이디가 중복되는 값은 추가할 수 없음. (제약조건 UNIQUE)

- 중복되는 값을 못 넣음

- PWD 널은 입력못하고 중복값은 가능함

제약 조건 위배

--중복되는 데이터 추가
SELECT * FROM TABLE_UNIQUE;
INSERT INTO TABLE_UNIQUE(LOGIN_ID,LOGIN_PWD,TEL)
VALUES('TEST_ID_02','PWD01','010-1234-5678');
SELECT * FROM TABLE_UNIQUE;

중복되는 데이터 추가하기 (아이디만 다르게)

--빈 값 추가
INSERT INTO TABLE_UNIQUE(LOGIN_ID,LOGIN_PWD,TEL)
VALUES(NULL,'PWD01','010-1234-5678');
SELECT * FROM TABLE_UNIQUE;

NULL값을 추가한 모습

--아이디가 중복되는 값으로 변경
SELECT * FROM TABLE_UNIQUE;
UPDATE TABLE_UNIQUE
SET LOGIN_ID = 'TEST_ID_01'
WHERE LOGIN_ID IS NULL;
--아이디가 중복되는 값은 변경 안됨 (제약조건 UNIQUE)

UNIQUE라는 조건 때문에 변경도 안됨.

--제약조건 이름 지정
CREATE TABLE TABLE_UNIQUE2(
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLNQ2_LGNID_UNQ UNIQUE,
LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLNQ2_LGNPW_NN NOT NULL, 
TEL VARCHAR2(20)
);
--변경된 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;

UNQ와 NN의 제약조건이 입력된 모습

3) MODIFY

--제약조건 추가
ALTER TABLE TABLE_UNIQUE
MODIFY(TEL UNIQUE);
--제약조건 확인
DESC TABLE_UNIQUE;
--중복되는 값이 있어 추가 안됨

중복키가 있다고 설명

--빈 값으로 변경
UPDATE TABLE_UNIQUE
SET TEL = NULL;
SELECT * FROM TABLE_UNIQUE;
--제약 조건 추가
ALTER TABLE TABLE_UNIQUE
MODIFY (TEL UNIQUE);
--제약조건 확인
DESC TABLE_UNIQUE;

NULL값으로 변경된 모습

TEL에 1이 생겨서 UNIQUE가 적용 됐다는 것을 알 수 있음.

4) 제약조건 확인

--추가된 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_UNIQUE%';
--제약조건 이름 지정하여 추가 
ALTER TABLE TABLE_UNIQUE2
MODIFY(TEL CONSTRAINT TBLUNQ_TEL_UNQ UNIQUE);
--이름 지정된 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_UNIQUE%';

추가 전

추가 후

--제약조건 이름 변경
ALTER TABLE TABLE_UNIQUE2
RENAME CONSTRAINT TBLUNQ_TEL_UNQ TO TBLUNQ2_TEL_UNQ;
--변경된 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_UNIQUE%';

2로 변경된 모습

--제약조건 삭제
ALTER TABLE TABLE_UNIQUE2
DROP CONSTRAINT TBLUNQ2_TEL_UNQ;
--삭제된 제약조건 확인 
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_UNIQUE%';

삭제된 제약조건의 모습

5) 제약조건 테이블 추가

--PRIMARY KEY = NOT NULL + UNIQUE = 유일하게 하나만 있는 값
CREATE TABLE TABLE_PK(
LOGIN_ID VARCHAR2(20) PRIMARY KEY,
LOGIN_PWD VARCHAR2 (20) NOT NULL,
TEL VARCHAR2(20)
);

DESC TABLE_PK;
--제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_PK%';

TABLE_PK의 확인된 모습

6) PRIMARY KEY 생성

--PRIMARY KEY 생성하면 INDEX도 자동 생성됨
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_PK%';
--제약 조건 이름 지정
CREATE TABLE TABLE_PK2(
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLPK2_LGNID_PK PRIMARY KEY,
LOGIN_PWD VARCHAR2 (20) CONSTRAINT TBLPK2_LGNPW_NN NOT NULL,
TEL VARCHAR2(20)
);
DESC TABLE_PK2;
--제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_PK%';

NOT NULL과 Y를 구분할 줄 알아야 함

--중복되는 데이터 추가
SELECT * FROM TABLE_PK;
INSERT INTO TABLE_UNIQUE(LOGIN_ID,LOGIN_PWD,TEL)
VALUES('TEST_ID_01','PWD01','010-1234-5678');
SELECT * FROM TABLE_PK;
INSERT INTO TABLE_UNIQUE(LOGIN_ID,LOGIN_PWD,TEL)
VALUES('TEST_ID_01','PWD02','010-2345-5678');
--PRIMARY KEY = 중복 불가 + 빈값 불가

구문 두 개다 PRIMARY KEY에 위반 되어 적용이 안되는 모습

* 정리

--제약조건 지정하는 두가지 방식
--1) 개별적으로 열이름, 데이터타입, 제약조건 = 인라인, 열 레벨 제약조건 정의
--2) 열이름, 데이터타입부터 먼저 작성 후 마지막에 CONSTRAINT 으로 시작하여 지정 =
--아웃오브라인, 테이블 레벨 제약조건 정의
--단, NOT NULL 제약조건은 제외

 

7) 암시적 입력과 명시적 입력

--빈 값 추가
--명시적
INSERT INTO TABLE_UNIQUE(LOGIN_ID,LOGIN_PWD,TEL)
VALUES(NULL,'PWD01','010-1234-5678');
--암시적
INSERT INTO TABLE_UNIQUE(LOGIN_ID,TEL)
VALUES('PWD02','010-2345-5678');
--PRIMARY KEY = 중복 불가 + 빈값 불가

8) 외래키 (REFERENCES)

--외래키 = 다른 테이블의 기본키를 참조
--제약조건 확인
--제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP','DEPT');
--테이블 조회
SELECT * FROM EMP;
SELECT DISTINCT DEPTNO FROM EMP;
SELECT * FROM DEPT;

FK_DEPTNO에서 PK_DEPT는 1번째 줄 P의 PK_DEPT를 참조한다는 뜻

R은 외래키를 의미함

 

--외래키 열에 존재하지 않은 값 추가
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(9999,'홍길동','CLERK','7788',TO_DATE('2017/04/30','YYYY/MM/DD'),
1200,NULL,50);
--참조할 수 있는 값이 없기 때문에 추가 안됨

제약 조건 때문에 삽입이 불가능

--실습 테이블 생성
--외래키 지정하기 위한 기본키 지정 (DEPT)
CREATE TABLE DEPT_FK(
DEPTNO NUMBER (2) CONSTRAINT DEPTFK_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
DESC DEPT_FK;
--외래키 지정(EMP)
CREATE TABLE EMP_FK(
EMPNO NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK (DEPTNO));
DESC EMP_FK;

참조 대상의 DEPT_FK 테이블

제약조건을 정의한 테이블

--사원 정보 입력
INSERT INTO EMP_FK
VALUES (9999,'TEST_NAME','TEST_JOB',NULL,TO_DATE('2001/01/01','YYYY/MM/DD'),
3000, NULL,10);
--부서 정보에 참조할 부서 번호 10번이 없기 때문에 입력이 안됨
--부서 정보 입력
INSERT INTO DEPT_FK
VALUES(10, 'TEST_DNAME','TEST_LOC');
SELECT * FROM DEPT_FK;
--참조할 수 있는 부서번호 값이 먼저 있어야 함

사원 정보를 입력하려 했지만 부모키가 없다고 뜸.

부서 정보를 입력하고 확인한 결과

* 참조키의 경우 : 부서 정보를 입력 후 -> EMP 테이블에 데이터 집어 넣기

--사원 정보 재입력
INSERT INTO EMP_FK
VALUES (9999,'TEST_NAME','TEST_JOB',NULL,TO_DATE('2001/01/01','YYYY/MM/DD'),
3000, NULL,10);
--부서 정보에 참조할 부서 번호 10번이 존재하기 때문에 입력이 됨
--생성 순서
--1) 참조할 기본 키 생성 (예, DEPT_FK)
--2) 기본키 참조할 참조키 생성 (예, EMP_FK)
--3) 참조할 기본키에 데이터 입력 (예, 부서번호 10번)
--4) 기본키 참조할 참조키에 데이터 입력(예, 10번 부서 소속의 TEST_NAME이라는 사원)

참조가 가능함

--삭제
DELETE FROM DEPT_FK
WHERE DEPTNO = 10;
--EMP 테이블에서 참조하고 있기 때문에 삭제가 안됨

--삭제 순서
--1) 기본키 참조할 참조키에 데이터 삭제(예, 10번 부서 소속의 TEST_NAME이라는 사원)
--2) 참조할 기본키에 데이터 삭제(예, 부서번호 10번)
--다른 방법
--1) 기본키 참조할 참조키에 데이터 변경(예, 10번 부서 소속의 TEST_NAME이라는 사원의 부서 번호를
--다른 번호로 변경하거나 빈 값으로 변경)
--2)  참조할 기본키에 데이터 삭제(예, 부서번호 10번)

--참조키 제약조건 해제가 번거로울 때, 제약조건 삭제 옵션 지정 가능
--1) 참조하고 있는 데이터 삭제 : 예, CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK (DEPTNO)
--ON DELETE CASCADE
--2) 참조하고 있는 데이터 빈 값으로 변경 : 예, CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK (DEPTNO)
--ON DELETE SET NULL

참조하는 데이터는 삭제가 안됨.

9) CHECK

--값의 범위나 패턴을 정의할 때 사용 = CHECK
--3글자 초과만 가능
CREATE TABLE TABLE_CHECK(
 LOGIN_ID VARCHAR2 (20) CONSTRAINT TBLCK_LOGINID_PK PRIMARY KEY,
 LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLCK_LOGINPW_CK CHECK (LENGTH(LOGIN_PWD)>3),
 TEL VARCHAR2(20)
);
DESC TABLE_CHECK;
--CHECK 조건에 맞지 않는 데이터 입력
INSERT INTO TABLE_CHECK
VALUES ('TEST_ID','123','010-1234-5678');
--CHECK 조건에 맞는 데이터 입력
INSERT INTO TABLE_CHECK
VALUES ('TEST_ID','1234','010-1234-5678');
SELECT * FROM TABLE_CHECK;
--제약조건 확인
SELECT OWNER, CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TABLE_CHECK';

데이터 입력한 값

비밀번호가 123인데 제약 조건이 3글자 초과만 가능하기 때문에 위배 됨.

입력이 된 결과

제약조건 확인한 결과 값

10) DEFAULT

CREATE TABLE TABLE_DEFAULT(
 LOGIN_ID VARCHAR2 (20) CONSTRAINT TBLCK2_LOGINID_PK PRIMARY KEY,
 LOGIN_PWD VARCHAR2(20) DEFAULT '1234',
 TEL VARCHAR2(20)
);
DESC TABLE_DEFAULT;
--명시적 입력
INSERT INTO TABLE_DEFAULT VALUES ('TEST_ID', NULL, '010-1234-5678');
SELECT * FROM TABLE_DEFAULT ;
--암시적 입력
INSERT INTO TABLE_DEFAULT (LOGIN_ID,TEL) VALUES ('TEST_ID2', '010-1234-5678');
SELECT * FROM TABLE_DEFAULT;

NULL 값이 들어갔음

암시적으로 입력할 경우 제약조건에 있는 1234이 그대로 적용 됨.

--실무에서 여러가지 테스트하는 경우가 많은데, 제약조건으로 불편한 경우가 많음
--제약조건 활성화 / 비활성화를 통해 사용 가능함
--ALTER TABLE 테이블 이름 ENABLE CONSTRAINT 제약조건 이름; (활성화)
--ALTER TABLE 테이블 이름 DISABLE CONSTRAINT 제약조건 이름; (활성화)

 

2. 사용자, 권한, 롤 관리

0) 정의

* 사용자

 

1) 사용자 관리

--사용자 관리
--토드에서 SCOTT 계정으로 실행
CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;
--SQLPLUS에서 SYSTEM/ORACLE로 실행
--SQLPLUS SYSTEM/ORACLE
CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;
--데이터베이스 관리자 권한으로 사용자 생성
CONN ORCLSTUDY/ORACLE;
--접속이 안됨, 접속 권한이 없음

사용자 생성이 됨

ORACLE 접속 권한이 없음

--접속 권한 부여
--SQLPLUS에서 system/oracle 로 실행
CONN system/oracle;
GRANT CREATE SESSION TO ORCLSTUDY;

연결 후 권한 부여

CONN ORCLSTUDY/ORACLE;

이번에는 연결이 됨.

--사용자 정보 조회
SELECT * FROM ALL_USERS
WHERE USERNAME = 'ORCLSTUDY';
SELECT * FROM DBA_USERS
WHERE USERNAME = 'ORCLSTUDY';
SELECT * FROM DBA_OBJECTS
WHERE OWNER = 'ORCLSTUDY';
--데이터베이스 관리 권한이 없으면 조회 불가
--SQLPLUS에서 system/oracle로 실행

CMD, TOAD에서 둘 다 되는 모습.

권한이 없어서 못봄

CMD에서 CONN을 통해 SYSTEM 계정 접속 후 DBA가 실행이 되는 모습

레코드가 없다는 모습

1-2) 사용자 관리 - 비밀번호 변경, 삭제

--사용자 비밀번호 변경
ALTER USER ORCLSTUDY
IDENTIFIED BY ORCL;
--사용자 삭제
DROP USER ORCLSTUDY;
--사용자와 객체 모두 삭제
DROP USER ORCLSTUDY CASCADE;

비밀번호 변경된 모습

사용자가 삭제된 모습

2) 권한 관리

--권한 관리
CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;

CONN system/oracle;

--권한 관리
CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;
CONN system/oracle;
GRANT RESOURCE, CREATE SESSION, CREATE TABLE TO ORCLSTUDY;

권한 부여

3) 생성 및 조회까지

--테이블 생성, 데이터 추가, 조회
CREATE TABLE TEMP1 (
COL1 VARCHAR2 (20),
COL2 VARCHAR2 (20));
INSERT INTO TEMP1 VALUES ('USER','TEST');
SELECT * FROM TEMP1;
--권한이 있기 때문에 잘 실행됨

4) 권한 취소

--SQLPLUS에서 system/oracle로 실행
--RESOURCE 롤과 CREATE TABLE 권한 취소
CONN system/oracle;
REVOKE RESOURCE, CREATE TABLE FROM ORCLSTUDY;

--SQLPLUS에서 system/oracle로 실행
--계정 연결
CONN ORCLSTUDY/ORACLE;
--테이블 생성
CREATE TABLE TEMP1 (
COL1 VARCHAR2 (20),
COL2 VARCHAR2 (20));
--연결은 되지만 RESOURCE 롤과 테이블 생성할 수 있는 권한이 없어
--생성할 수 없음

권한이 취소 되어 테이블 값을 입력 할 수 없는 모습

5) 객체 권한

--객체 권한
--SQLPLUS에서 scott/tiger 로 실행
--테이블 생성, 조회
CONN scott/tiger
CREATE TABLE TEMP (
COL1 VARCHAR2 (20),
COL2 VARCHAR2 (20));
SELECT * FROM TEMP;

행이 없어서 레코드는 실행 안되는 모습 (테이블 입력은 됨)

6) 권한 부여

--ORCLSTUDY 계정에게 권한 부여
--SELECT 권한
GRANT SELECT ON TEMP TO ORCLSTUDY;
--INSERT 권한
GRANT INSERT ON TEMP TO ORCLSTUDY;
--동시에 부여 가능
GRANT SELECT, INSERT ON TEMP
TO ORCLSTUDY;

권한이 부여 된 모습

7) 객체 권한 부여

--SQLPLUS에서 ORCLSTUDY/ORACLE로 실행
--계정 연결
CONN ORCLSTUDY/ORACLE;
--SELECT, INSERT 실행
SELECT * FROM SCOTT.TEMP;
INSERT INTO SCOTT.TEMP VALUES ('TEXT','FROM ORCLSTUDY');
SELECT * FROM SCOTT.TEMP;
--권한이 있어 실행 가능

객체 권한 부여 모습

8) 객체 권한 취소 및 확인 - SCOTT으로 들어가서

--SQLPLUS에서 scott/tiger로 실행
--ORCLSTUDY 계정의 권한 취소
CONN scott/tiger
REVOKE SELECT, INSERT ON TEMP FROM ORCLSTUDY;

--SQLPLUS에서 CONN ORCLSTUDY/ORACLE로 실행
--계정 연결 
CONN ORCLSTUDY/ORACLE;
--SELECT 실행
SELECT * FROM SCOTT.TEMP;

권한 취소 후

권한 취소 후 확인

9) 롤 관리

* 정의

- 권한을 일일이 부여해줘야 하는 번거로움이 있음.

- 사전 정의된 롤(RESOURCE), 사용자 정의롤로 정의 됨. 

- CONNECT = CREATE SESSION

- RESOURCE : 여래개의 객체를 생성할 수 있는 기본 시스템

- DBA롤 : 최상위 시스템 권한

 

* 계정 연결 후 롤 관리, 생성, 권한 부여 및 롤 부여

--SQLPLUS에서 CONN ORCLSTUDY/ORACLE로 실행
--계정 연결 
CONN ORCLSTUDY/ORACLE;
--SELECT 실행
SELECT * FROM SCOTT.TEMP;

--롤 관리
--SQLPLUS에서 system/oracle로 실행
--롤 생성
CONN system/oracle;
CREATE ROLE ROLESTUDY;
--권한 부여
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE SYNONYM
TO ROLESTUDY;
--롤 부여 
GRANT ROLESTUDY TO ORCLSTUDY;

롤 생성 및 권한 부여

* 계정 연결 후 부여된 롤과 권한 확인

--계정 연결
CONN ORCLSTUDY/ORACLE
--부여된 롤과 권한 확인
SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

 

- ADM(admin) : 권한 부여 옵션

- def(default) : 고정값

- OS : 시스템 관리자

 

* 롤 취소후 확인까지 

--SQLPLUS에서 system/oracle 로 실행
--롤 취소
CONN system/oracle;
REVOKE ROLESTUDY FROM ORCLSTUDY;
--취소된 내용 확인
--SQLPLUS에서 ORCLSTUDY/ORACLE 로 실행
--계정 연결
CONN ORCLSTUDY/ORACLE;
--부여된 롤 확인
SELECT * FROM USER_ROLE_PRIVS;
--롤이 취소됨

권한 취소 후 레코드 확인까지

* 롤 삭제

--SQLPLUS에서 system/oracle 로 실행
--롤 삭제
CONN system/oracle;
DROP ROLE ROLESTUDY;

롤 삭제까지

 

* 정리 - 권한의 종류

1. 시스템 권한 : ON을 써서 구체적인 테이블을 명시해야 함.

2. CREATE USER, GRANT, REVOKE

3. CONSTRAINT 의 방법 알기 : 개별적으로 열이름, 데이터타입, 제약조건 = 인라인, 열 레벨 제약조건 정의

4. 참조키(외래키)의 경우 : 부서 정보를 입력 후 -> EMP 테이블에 데이터 집어 넣기 // 참조할 부모키가 있어야 함.