[DB][SQL][국비교육] Day 30

Ga02·2023년 2월 8일
dbsql국비교육

국비교육

목록 보기
29/82

🔍 DCL, Data Control Language, 데이터 제어어

DBMS에 대한 설정(제어)에 관한 구문

  • 트랜잭션 제어(TCL)
    • COMMIT : 작업내역 영구 반영
    • ROLLBACK : 작업내역 되돌리기
  • 권한 설정
    • GRANT : 권한 부여
    • REVOKE : 권한 회수

➰ 권한, Privilege

  1. DB객체에 대한 접근, 관리
  2. DB객체에 사용할 수 있는 SQL구문의 종류
  • 👆🏻 위 2종류에 대해 사용자계정마다 차이를 두는 것을 권한이라 함 👉🏻 시스템 권한, 객체 권한

🔍 시스템 권한

DB시스템 작업에 대한 권한 👉🏻 DDL권한들

✔ 대표적인 시스템 권한
CREATE SESSION : DB에 대한 접속(접근) 권한
CREATE TABLE : 테이블 생성 권한
UNLIMITED TABLESPACE : 모든 테이블 스페이스 이용권한

  • 모든 유저의 테이블에 대한 권한이 부여됨
    CREATE ANY TABLE
    ALTER ANY TABLE
    DROP ANY TABLE
    SELECT ANY TABLE
    UPDATE ANY TABLE
    DELETE ANY TABLE

SYSDBA : 데이터베이스 관리 최고권한 👉🏻 뭐든지 다 가능
➡ GRANT로 부여하는 일이 거의 없으며 conn sys as sysdba 정도로 사용

➰ 시스템 권한을 부여하는 구문

  • WITH ADMIN OPTION : 부여받은 시스템 권한을 다른 사용자계정에 부여할 수 있도록 함
    👉🏻 해당 권한에 대한 관리자가 계정급으로 인정됨
  • 시스템 권한이 부여된 현황 관련 자료사전
    user_sys_privs : 일반 사용자 계정으로 부여한 시스템 권한
    dba_sys_privs : 관리자 계정으로 부여한 시스템 권한
--  privs_test/1234 사용자 계정 생성
CREATE USER privs_user IDENTIFIED BY 1234;  --에러, 권한 불충분


--  [system 계정 이용]
--  scott계정에게 CREATE USER 시스템 권한 부여
GRANT
    CREATE USER
TO scott;

--  scott계정에게 CREATE SESSION 시스템 권한을 ADMIN OPTION으로 부여
GRANT
    CREATE SESSION
TO scott
WITH ADMIN OPTION;


--  [scott 계정 이용]
--  privs_user에 CREATE SESSION 권한 부여
GRANT
    CREATE SESSION
TO privs_user;


--  [system 계정 이용]
--  scott에게 부여한 CREATE SESSION 회수하기
REVOKE
    CREATE SESSION
FROM scott;


--	자료사전 확인
SELECT * FROM dba_sys_privs
WHERE grantee = upper('scott');		--관리자 계정으로 scott에게 부여된 권한 확인

SELECT * FROM user_sys_privs;		--사용자 계정으로 본인의 권한 확인

➰ 롤, Role

권한들을 모아놓는 객체 / 권한 집합

  • 사용자 계정에 권한을 부여하듯이 롤 객체에 권한을 부여할 수 있음
  • 롤을 사용자 계정에 권한을 부여하듯이 부여할 수 있음 👉🏻 롤에 부여된 권한들을 한꺼번에 사용자계정에 부여할 수 있음
  • 롤 관련 자료사전
    user_role_privs : 일반 사용자 계정의 부여받은 롤
    dba_role_privs : 관리자 계정으로 부여한 롤
  • CONNECT, RESOURCE는 기본적인 권한의 롤
--  롤 자료사전 조회
SELECT * FROM user_role_privs;  --CONNECT, RESOURCE 두 롤을 부여받고 있음

--  [system 계정 이용]
SELECT * FROM dba_sys_privs
--WHERE grantee = upper('scott'); --SCOTT의 부여받은 롤 확인
WHERE grantee IN ('CONNECT', 'RESOURCE');   --롤 확인


--  롤을 포함하여 사용자의 모든 권한을 조회하는 구문
SELECT * FROM dba_sys_privs
WHERE grantee = 'SCOTT'     --SCOTT에게 직접 부여된 권한
UNION
SELECT * FROM dba_sys_privs
WHERE grantee IN (          
    SELECT granted_role FROM dba_role_privs     --특정 사용자에게 부여된 롤
    WHERE grantee = 'SCOTT'
)
ORDER BY 1,2;


--  기본 롤 부여
GRANT CONNECT, RESOURCE
TO privs_user;

--  롤 권한 회수
REVOKE CONNECT, RESOURCE
FROM privs_user;

🔍 객체 권한

db객체에 작업(CRUD)을 수행할 수 있는 권한

  • 소유자(OWNER)는 생성한 객체에 대한 모든 객체권한을 가지고 있음
  • 객체권한 관련 자료사전
    user_tab_privs : 일반 사용자 계정의 테이블 객체권한
    dba_tab_privs : 관리자 계정의 테이블 객체권한

✔ 대표적인 시스템 권한
SELECT
INSERT
UPDATE
DELETE

ALTER : 객체 스키마 변경 권한
RENAME : 객체 이름 변경 권한
COMMENT : 객체에 코멘트를 작성할 수 있는 권한

--  [system 계정 이용]
REVOKE SELECT ANY TABLE
FROM privs_user;

--  [scott 계정 이용]
GRANT SELECT
ON dept
TO privs_user;

GRANT SELECT, INSERT
ON emp
TO privs_user;

REVOKE INSERT
ON emp
FROM privs_user;

🔍 PL/SQL, Procedural Language extention to SQL

오라클 기본 SQL에 절차적 언어 특성을 확장(추가)한 문법 👉🏻 SQL에 프로그래밍 처리가 추가된 형식
❗ 구문의 출력 결과를 모니터에 보이도록 설정 : SET SERVEROUTPUT ON;
👉🏻 SQL Developer를 껐다 켤 때마다 실행해야 함

  • 기본 SQL에서 제공하지 않는 변수, 조건문, 반복문 등을 지원
  • 기본 코드 구조
    • BEGIN, END ➡ 필수요소
    • DECLARE, EXCEPTION ➡ 선택요소
    • ; : 문장 하나의 끝
    • / : 하나의 PL/SQL 구문이 완료되었음을 나타냄 👉🏻 /는 단독으로 사용!
    • := : 값 대입
  • 문장 출력코드 👉🏻 자바의 system.out.plintln()과 같은 역할
    • DBMS_OUTPUT.PUT() : 모니터에 데이터를 출력 / 개행, FLUSH 안함
    • DBMS_OUTPUT.NEW_LINE() : 출력된 데이터를 FLUSH하고 개행함
    • DBMS_OUTPUT.PUT_LINE() : PUT() + NEW_LINE() 두 함수를 합친 동작
  • PL/SQL의 정수 표현방식
    • NUMBER
    • INTEGER
    • INT
    • SMALLINT
      👉🏻 세 타입은 ANSI 표준으로 지정된 자료형 키워드 ➡ 오라클타입 NUMBER(38)로 대체됨
    • BINARY_INTEGER 👉🏻 PL/SQL에서만! 사용 가능
DECLARE
    num1 NUMBER;
    num2 NUMBER := 10;
BEGIN
    DBMS_OUTPUT.PUT_LINE('num1 = ' || num1);
    DBMS_OUTPUT.PUT_LINE('num2 = ' || num2);
    
    --값대입
    num1 := 555;
    num2 := 666;
    
    DBMS_OUTPUT.PUT_LINE('num1 = ' || num1);
    DBMS_OUTPUT.PUT_LINE('num2 = ' || num2);
    
END;
/


--  SELECT 구문을 조회한 결과를 변수에 저장하기 / 변수의 데이터타입을 테이블 정보로 지정하기
DECLARE 
    v_dname dept.dname%TYPE := upper('&input');   --키보드 입력받아 설정하기
    
    --deptno을 저장할 변수 선언
    v_deptno dept.deptno%TYPE;
    
BEGIN
    SELECT deptno
    INTO v_deptno
    FROM dept
    WHERE dname = v_dname;
    
    DBMS_OUTPUT.PUT_LINE(v_dname || '팀의 부서번호 : ' || v_deptno);
    
END;
/

➰ PL/SQL 제어문

  • 조건문
DECLARE
    v_num NUMBER := #
BEGIN
    IF v_num > 0 THEN
        DBMS_OUTPUT.PUT_LINE('양수입니다.');
    ELSIF v_num < 0 THEN
        DBMS_OUTPUT.PUT_LINE('음수입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('0입니다.');
    END IF;
END;
/
  • 반복문
  1. 무한반복
BEGIN
--  무한반복되다가 버퍼 메모리 부족으로 강제 중단됨
    LOOP
    DBMS_OUTPUT.PUT_LINE('HI');
    END LOOP;
END;
/
  1. 인덱스를 이용한 FOR LOOP 반복문
  • 인덱스 변수가 시작값부터 종료값까지 1씩 증가하면서 반복
  • IN 키워드 뒤에 REVERSE 키워드를 추가하면 종료값부터 시작값까지(역순으로) 진행됨
  • FOR에서 사용된 인덱스는 END LOOP;이후에 사용할 수 없음
BEGIN
    FOR i IN 1..10
    LOOP
        --  i변수 이용
        i := i * 10;
        
        DBMS_OUTPUT.PUT(i || ' ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE();
    
    --  FOR에서 사용된 인덱스는 END LOOP; 이우에 사용할 수 없음
--    DBMS_OUTPUT.NEW_LINE(i);
END;
/


--  REVERSE FOR LOOP 반복문
BEGIN
    --  반복구간을 역순으로 지정하면 FOR LOOP가 반복되지 않음
--    FOR i IN 10..1

    FOR i IN REVERSE 1..10
    
    LOOP
        DBMS_OUTPUT.PUT(i || ' ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE();
END;
/
  1. SELECT 구문의 결과를 이용한 반복 👉🏻 자바의 for each 구문과 비슷
BEGIN
    FOR dept_row IN (
        SELECT * FROM dept
        ORDER BY deptno DESC
    )
    LOOP 
  --  에러, 조회결과 변수를 바로 사용할 수 없음 -> dept_row에 행의 모든 데이터가 들어가있으므로 각 컬럼에 맞게 하나씩 꺼내줘야 함
--     DBMS_OUTPUT.PUT_LINE(dept_row);

     DBMS_OUTPUT.PUT(dept_row.deptno);
     DBMS_OUTPUT.PUT(chr(9));
     DBMS_OUTPUT.PUT(dept_row.dname);
     DBMS_OUTPUT.PUT(chr(9));
     DBMS_OUTPUT.PUT_LINE(dept_row.loc);
     
    END LOOP;
END;
/
  1. 조건을 이용한 반복문
DECLARE
    i NUMBER := 0;  --초기식
BEGIN
    WHILE i<5   --조건식
    LOOP
        i := i + 1; --증감식
        
        DBMS_OUTPUT.PUT(i || ' ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE();

END;
/
  • 기타 제어문
    조건에 만족하면 반복문 종료
--  무한반복 LOOP + EXIT구문
DECLARE
    i BINARY_INTEGER := 0;   --BINARY_INTEGER : PL/SQL 전용 INT
BEGIN
    LOOP
        EXIT WHEN i >= 10;   --조건식
        
        --  수행코드
        DBMS_OUTPUT.PUT('HI' || ' ');
        
        i := i + 1; --증감식
    END LOOP;
    DBMS_OUTPUT.NEW_LINE();
END;
/
profile
Ga02
IT꿈나무 댓츠미
이전 포스트

[DB][SQL][국비교육] Day 29

다음 포스트

[DB][SQL][국비교육] Day 31

0개의 댓글