09-2 권한, 테이블, INSERT, UPDATE, DELETE

■ 유저관리
권한(Privilege)
- 특정한 SQL문을 수행할 수 있는 권리
- 시스템권한 : 데이터베이스에 영향을 줄 수 있는 권한
- 객체권한 : 객체(테이블)를 사용할 수 있는 권한
- ROLE(롤) : 유저에게 부여할 수 있는 권한을 모아 놓은 객체, 관리에 대한 편리성

# 내가 dba로부터 직접 받은 시스템 권한을 확인
SELECT * FROM user_sys_privs;

# 내가 부여한 객체권한, 내가 받은 객체권한을 확인
SELECT * FROM user_tab_privs;

# 내가 받은 롤에 대한 정보를 확인
SELECT * FROM session_roles;

# 내가 받은 롤안에 시스템 권한 확인
SELECT * FROM role_sys_privs;

# 내가 받은 롤안에 객체 권한 확인
SELECT * FROM role_tab_privs;

# 내 정보 확인
SELECT * FROM user_users;



■ 유저생성(SYS계정에서 수행)
SHOW USER

# db에 생성되어 있는 유저 정보 확인
SELECT * FROM dba_users;

# db에 생성되어 있는 데이터파일 정보 확인
SELECT * FROM dba_data_files;

# db에 생성되어 있는 temporary file 정보 확인
SELECT * FROM dba_temp_files;

SELECT * FROM dba_segments;


CREATE USER insa            -- 유저명 생성
IDENTIFIED BY oracle        -- 비번 설정
DEFAULT TABLESPACE users    -- 논리적 영역 부여
TEMPORARY TABLESPACE temp   -- 임시 디스크영역 부여
QUOTA 10M ON users;         -- 10메가 사용가능하게 부여

SELECT * FROM dba_users;

SELECT * FROM dba_ts_quotas;    -- 유저별 사용가능 공간 확인 / -1 : 무한



■ 유저수정(SYS)
ALTER USER insa             -- 유저 수정
--IDENTIFIED BY oracle        -- 비번 설정
--DEFAULT TABLESPACE users    -- 논리적 영역 부여
--TEMPORARY TABLESPACE temp   -- 임시 디스크영역 부여
--QUOTA 10M ON users;         -- 10메가 사용가능하게 부여

SELECT * FROM dba_users;
SELECT * FROM dba_ts_quotas;    -- 유저별 사용가능 공간 확인 / -1 : 무한

ALTER USER insa             -- 유저 수정
QUOTA UNLIMITED ON users;   -- 용량무한으로 수정


# 계정 감금
ALTER USER insa ACCOUNT LOCK;   -- LOCKED
SELECT * FROM dba_users;

# 계정 복구
ALTER USER insa ACCOUNT UNLOCK; -- OPEN
SELECT * FROM dba_users;

 

■ 권한
DCL(Data control Language)
- GRANT : 권한부여
- REVOKE : 권한회수

# 시스템 권한 부여(SYS 계정)
GRANT 시스템권한, 시스템권한, ... TO 유저, 롤;

# CREATE SESSION 권한이 없어서 오류 발생

<SQL Command Line>

SQL> conn insa/oracle
    ERROR:
    ORA-01045: user INSA lacks CREATE SESSION privilege; logon denied

GRANT CREATE SESSION TO insa;	-- SYS에서 권한부여

SQL> conn insa/oracle
    Connected.

 

# dba입장에서 insa계정에 어떤 권한을 부여했는가

SELECT * FROM dba_sys_privs WHERE grantee = 'INSA';

'INSA' : 딕셔너리 내에서는 대문자로 되어있음

 

# 시스템 권한 회수(SYS 계정)
REVOKE CREATE SESSION FROM insa;
SELECT * FROM dba_sys_privs WHERE grantee = 'INSA';    -- insa 계정의 권한 확인


# 객체 권한 부여(SYS, 객체소유자)
GRANT 객체권한 ON 객체 TO 유저;

GRANT SELECT ON hr.employees TO insa;
SELECT * FROM dba_tab_privs WHERE grantee = 'INSA';



# 객체 권한 부여(hr session)
GRANT SELECT ON hr.departments TO insa;
GRANT INSERT, UPDATE, DELETE ON hr.employees TO insa;
SELECT * FROM user_tab_privs;


# 객체 권한 회수(hr)
REVOKE SELECT ON hr.departments FROM insa;
REVOKE DELETE ON hr.employees FROM insa;
SELECT * FROM user_tab_privs;


■ 유저삭제
DROP USER insa CASCADE;
-- session 먼저 끊고 유저가 만든 객체정리(CASCADE) 후 삭제


■ TABLE
- 행(ROW)과 열(COLUMN)으로 구성되어 있는 데이터의 저장구조

테이블을 생성하려면 두가지 체크해야한다.

1. 테이블을 생성할 수 있는 권한

CREATE TABLE 시스템권한

<sys session>
GRANT CREATE TABLE TO insa;

<insa session>
SELECT * FROM user_sys_privs;   -- 직접받음
SELECT * FROM role_sys_privs;
SELECT * FROM session_privs;

UNLIMITED TABLESPACE 시수템권한 : dbdp 생성되어 있는 모든 테이블스페이스를
                               \ 사용할 수 있는 권한
<sys>
GRANT UNLIMITED : 시스템권한

2. 테이블을 저장할 수 있는 테이블스페이스 권한
<insa session>
SELECT * FROM user_ts_quotas;

 

■ 테이블 이름, 컬럼이름, 유저이름, 다른객체이름, 제약조건 이름
- 문자시작(한글은 한글폰트가 있는 환경에서)
- 문자의 길이 1 ~ 30
- 문자, 숫자,특수문자(_, $, #) 가능하다.
- 대소문자 구별하지 않습니다.
- 동일한 유저가 소유한 객체이름은 중복할 수 없다.
- 예약어는 사용할 수 없다.

 

■ 컬럼 타입
- number(p,s) = 가변길이 숫자 타입, p: 전체자리수, s : 소수점 자리수
- varchar2(4000) : 가변길이 문자 타입
- char(2000) : 고정길이 문자 타입
- date : 날짜 타입
- clob : 가변길이 문자 타입, 4GBYTE
- blob : 가변길이 이진데이터 타입, 4GBYTE
- bfile : 외부 파일에 저장된 이진 데이터 타입, 4GBYTE

 

■ 테이블 생성
CREATE TABLE 신규테이블명 (칼럼명 데이터타입(크기), 칼럼명 데이터타입(크기), ... )

<insa session>

CREATE TABLE emp(
		id number(4),
		name varchar2(30),
		day date default sysdate)   -- 입력값이 없으면 기본값 지정
TABLESPACE users;			-- 생략가능. 지정하지 않으면 유저 생성시에 지정한 DEFAULT값으로 저장

desc emp		-- 데이터 타입 확인


DROP TABLE emp PURGE;	-- 테이블 삭제

 

* TABLESPACE 
- 테이블스페이스를 지정하지 않으면 유저 생성시에 지정한 DEFAULT TABLESPACE users에 저장된다 -> 생략가능
SELECT * FROM user_users;        -- 지정여부 확인

 

■ 테이블 삭제
DROP TABLE 테이블명 PURGE;

 

■ DML(Data Manipulation Language)
- INSERT
- UPDATE
- DELETE
- MERGE

 

■ TCL(Transaction Control Language)
- COMMIT : DML작업을 영구히 데이터베이스에 저장
- ROLLBACK : DML작업을 영구히 데이터베이스에서 취소
- SAVEPOINT : ROLLBACK기능을 도와주는 표시자

★ Transaction : 논리적으로 DML을 하나로 묶어서 처리하는 작업 단위

 

■ INSERT
- 테이블에 새로운 행을 입력하는 SQL문
INSERT INTO 소유자.테이블(컬럼, 컬럼, ...)
VALUES(데이터, 데이터, ...)

<insa session>

INSERT INTO insa.emp(id, name, day)	-- transaction 시작
VALUES(1, '홍길동', to_date('2023-10-18','yyyy-mm-dd'));	-- 기본양식

INSERT INTO insa.emp(id, name)		-- day칼럼에는 default값을 선언했기에 default값이 입력 됨
VALUES(2, '박찬호');

INSERT INTO insa.emp(id, name)		-- 칼럼 2개
VALUES(3, '윤건', sysdate);		-- 데이터 3개
					-- 오류 : 컬럼이 지정되어 있지 않는 곳에 데이터를 입력할 수 없다.
INSERT INTO insa.emp(id, name, day)
VALUES(3, default, default);		-- day칼럼에 default라고 수행하면 default값이 입력된다.
					-- name칼럼은 defualt값 지정이 안되어 null값으로 입력된다.
INSERT INTO insa.emp(id, name, day)
VALUES(4, '나얼', null);		-- day컬럼에 default값이 선언되어 있어도 null을 수행하면 null값으로 입력된다.


SELECT * FROM insa.emp; 		-- 미리보기


ROLLBACK;				-- transaction 시작시점까지 취소, transaction 종료
또는
commit;					-- transaction 시작시점까지 저장, transaction 종료

SELECT * FROM insa.emp; 		-- 데이터 확인

 

 

■ UPDATE
- 특정한 필드값을 수정하는 SQL문
UPDATE 소유자.테이블
SET 컬럼 = 새로운값, 컬럼 = 새로운값, ...
WHERE 조건;

UPDATE insa.emp			-- transaction 시작 시점
SET id = 100;			-- id칼럼 데이터 전부 100으로 수정 

SELECT * FROM insa.emp;		-- 미리보기, 다른곳에선 기존값 유지중


ROLLBACK;			-- transaction 시작시점까지 취소, transaction 종료
또는
COMMIT;				-- transaction 시작시점까지 저장, transaction 종료

 

# UNDO TABLESPACE 역할
DML 작업시에 이전값을 UNDO 공간에 저장한다.
1. ROLLBACK
2. 읽기 일관성

SELECT * FROM insa.emp WHERE ID = 2;	-- 수정할 데이터 확인

UPDATE insa.emp				-- 수정
SET name = '제임스', day = to_date('2001-01-01','yyyy-mm-dd')
WHERE ID = 2;

SELECT * FROM insa.emp;			-- 미리보기

COMMIT;					-- 저장

 

# day칼럼의 값을 default값으로 수정

UPDATE insa.emp
SET day = default
WHERE ID = 2;

SELECT * FROM insa.emp;

COMMIT;

 

# day칼럼의 값을 null값으로 수정

UPDATE insa.emp
SET day = null
WHERE ID = 2;

SELECT * FROM insa.emp;

COMMIT;

 

■ DELETE 문
- 행을 삭제하는 SQL문
DELETE FROM 소유자.테이블;                        -- 테이블 전체 행을 삭제
DELETE FROM 소유자.테이블 WHERE 조건;  -- 테이블의 조건절에 해당하는 행만 삭제

# 테이블 전체 행 삭제 , 구조 유지

DELETE FROM insa.emp;		-- transaction 시작 시점
SELECT * FROM insa.emp;

ROLLBACK;			-- transaction 시작시점까지 취소, transaction 종료
또는
COMMIT;				-- transaction 시작시점까지 저장, transaction 종료

 

# 특정 행 삭제

DELETE FROM insa.emp WHERE ID = 2;	-- transaction 시작
SELECT * FROM insa.emp;

ROLLBACK;				-- transaction 시작시점까지 취소, transaction 종료
또는
COMMIT;					-- transaction 시작시점까지 저장, transaction 종료

 

 

■ 테이블 복제 CTAS (시타스)
- 테이블 구조, 행(데이터), 제약조건에서는 NOT NULL 만 복제 된다.

CREATE TABLE hr.emp
AS
SELECT * FROM hr.employees;

SELECT * FROM hr.emp;

 

# 칼럼 선택하여 복제

CREATE TABLE hr.emp
-- TABLESPACE users		-- 미지정 시 default값 적용
AS
SELECT employee_id as id, upper(last_name||' '||first_name) name, salary * 12 SAL
FROM hr.employees;

SELECT * FROM hr.emp;


DROP TABLE hr.emp PURGE;	-- hr.emp 테이블 삭제

 

>> CATS 사용시에 TABLESPACE 절을 생략하게되면 DEFAULT TABLESPACE 로 지정되어 있는 TABLESPACE 에 저장된다.
SELECT * FROM user_users;   -- DEFAULT TABLESPACE 확인

# 테이블의 구조만 복제

CREATE TABLE hr.emp
AS
SELECT *
FROM hr.employees
WHERE 1 = 2;		-- 조건에 해당하는 값이 없기에 데이터가 없음

SELECT * FROM hr.emp;


DROP TABLE hr.emp PURGE;	-- hr.emp 테이블 삭제

 

 

■ INSERT SUBQUERY

# 테이블 구조 일치하에 데이터 복제

INSERT INTO hr.emp
SELECT * FROM hr.employees; 

SELECT * FROM hr.emp;


ROLLBACK;	-- 취소
또는
COMMIT;		-- 저장

 

# 데이터 부분 복제

CREATE TABLE hr.test(
                id number(4),
                name varchar2(50),
                sal number,
                dept_id number)
TABLESPACE users;	-- test 테이블 생성

desc hr.test		-- 데이터 타입 확인

INSERT INTO hr.test(id, name, sal, dept_id)				-- 4개 칼럼
SELECT employee_id, last_name||' ' ||first_name, salary, department_id	-- 4개 데이터
FROM hr.employees
WHERE hire_date BETWEEN to_date('2006-01-01','yyyy-mm-dd')
                AND to_date('2006-12-31','yyyy-mm-dd');

SELECT * FROM hr.test;

COMMIT;			-- 영구저장