SQL_사용자와 권한관리, 데이터 사전, PL/SQL, 함수

권한

 

• 특정 SQL문을 실행할 수 있는 권리

 

권한의 종류

 

시스템권한

• 데이터베이스를 엑세스 할 수 있는 권한
• 데이터베이스 관리자만 시스템권한을 부여/회수 할 수 있다

 

객체권한

• 데이터베이스 객체의 내용을 조회/조작할 수 있는 권한
• 데이터베이스 객체를 소유하고 있는 다른 사용자가 다른 사용자에게 객체권한을 부여/회수 할 수 있다

 

객체와 객체에 따른 권한

 

객체권한	테이블	뷰	시퀀스	프로시저
ALTER	O		O
INDEX	O
EXECUTE				O
DELETE	O	O
INSERT	O	O
SELECT	O	O	O
UPDATE	O	O

• 사용자는 시스템권한과 객체권한이 필요하다

 

롤(Role)

• 사용자에게 부여할 수 있는 여러 권한을 하나로 묶어서 그룹화하고 이름을 부여한 것
• 롤을 사용하면 권한의 부여, 회수, 유지관리가 쉬워진다

 

대표적인 롤

 

CONNECT 롤

• 오라클 데이터베이스에 접속할 수 있는 권한(CREATE SESSION 시스템권한)을 포함할 수 있는 롤

 

RESOURCE 롤

• 데이터베이스 객체를 생성/수정/삭제할 수 있는 권한을 포함하고 있는 롤
• 일반적으로 가장 많이 사용되는 시스템 권한을 포함하고 있는 롤이다 (일반 사용자는 CONNECT 롤, RESOURCE롤을 포함한다)

 

DAB 롤

• 데이터베이스를 관리하는 시스템 권한을 대부분 포함하고 있는 롤

 

권한 부여와 회수

 

시스템 권한의 부여

 GRANT 시스템권한, 시스템권한, ...
TO 사용자명;

GRANT 롤, 롤, ...
TO 사용자명;

 

 

시스템 권한의 회수

REVOKE 시스템권한, 롤, ...
FROM 사용자명;

 

 

객체 권한의 부여

GRANT 객체권한(컬럼명, 컬럼명, ...), 객체권한, ...
ON 객체명
TO 사용자명;

 

 

객체 권한의 회수

REVOKE 객체권한, 객체권한, ...
ON 객체명
FROM 사용자명;

 

 

사용자 관리

 

사용자 생성

CREATE USER 사용자명 INDETIFIED BY 비밀번호;

• 사용자명은 대소문자를 구분하지 않는다
• 비밀번호는 대소문자를 구분한다

 

사용자 삭제

DROP USER 사용자명;

 

 

사용자 비밀번호 변경

ALTER USER 사용자명 INDENTIFIED BY 새비밀번호;

 

 

사용자 생성 및 권한 부여/회수 예시

 

 

 

스키마

• 테이블, 뷰, 시퀀스 등과 같은 여러 객체들의 집합
• 데이터베이스 사용자가 스키마를 소유하고 있으며, 사용자의 이름과 동일한 이름을 갖는다 
• 다른 사용자가 소유하고 있는 객체를 엑세스할 때는 반드시 스키마를 명시해야 한다

 

스키마 작성예

/*
HTA 사용자가 HR이 소유하고 있는 EMPLOYEES를 조회하기
*/

SELECT *
FROM HR.EMPLOYEES

 

 

 

데이터 사전(Data Dictinary)

 

• 데이터 사저은 데이터베이스 전반에 대한 정보를 제공하는 읽기전용 테이블 및 뷰들의 집합
• 오라클은 데이터 사전을 읽어서 객체의 존재여부, 사용자에게 적합한 엑세스 권한이 있는지 등을 확인
• 오라클은 데이터베이스 구조, 사용자권한 등의 변경이 있을 때마다 데이터 사전을 갱신

 

데이터 사전이 포함하고 있는 정보

사용자 정보

권한과 롤 정보

데이터베이스 스키마 객체 (테이블, 뷰, 인덱스, 시노님, 시퀀스 등) 정보

데이터베이스 구조 정보

 

 

데이터 사전의 분류

ALL_XXX	ALL_ 로 시작하는 데이터사전으로, 한 특정 사용자가 조회가능한 모든 데이터 사전을 의미
USER_XXX	USER_ 로 시작하는 데이터 사전으로 한 특정 사용자에게 종속되어 있고,  그 사용자가 조회가능한 데이터 사전 뷰, ALL_XXX 데이터 사전의 부분집합
DBA_XXX	DBA 권한을 가진 사용자만이 조회할 수 있는 데이터 사전으로 모든 오라클 데이터베이스 객체에 대한 정보를 제공

 

 

데이터 사전 예시

 

 

PL/SQL

 

• Proceural Language extension to SQL의 약자
• Procedual Laugage는 절차적 프로그래밍 언어 ( 절차적 프로그래밍 언어는 변수정의, 조건처리(IF), 반복처리(LOOF, WHILE, FOR)등을 지원
• SQL을 절차적 프로그램 언어로 확장시킨 것

 

PL/SQL의 특징

• DBMS 내부에서 실행되는 절차적 프로그램이다
• BEGIN/END의 블록구조로 프로그램을 모듈화
• 조건문, 반복문을 사용해서 연속적인 SQL 작업을 수행할 수 있다

 

PL/SQL의 종류

 

프로시저(Procedure)

• 특정 작업을 수행하는 이름이 있는 PL/SQL 블록
• 매개변수를 받을 수 있고, 재사용할 수 있는 PL/SQL 블록
• 연속성을 가지는 작업 혹은 실행단계가 복잡한 트랜잭션(SQL작업)을 수행하기 위해서 사용한다

 

함수(Function)

• 프로시저와 동일하게 PL/SQL을 이용해서 일련의 SQL 작업을 처리하고, 그 처리결과를 단일값으로 반환하는 PL/SQL 프로그래밍

 

트리거(Trigger)

• 특정 테이블에 데이터의 추가/수정/삭제 등의 데이터변경 이벤트가 발생하면 DBMS에서 자동으로 실행되도록 PL/SQL로 구현한 프로그램

 

 

프로시저 형식

CREATE ON REPLACE PROCEDURE 프로시저명
(변수명 IN 데이터타입, 
변수명 IN 데이터타입,
변수명 OUT 데이터타입, ...)
IS
-- 변수선언
변수명 데이터타입;
변수명 데이터타입 := 값;
BEGIN
SQL 구문 혹은 PL/SQL 제어문자
...
END;

 

 

사용예시

 

 

 

함수 형식

CREATE OR REPLACE FUNCTION 함수명
(변수명 IN 데이터타입,
변수명 IN 데이터타입, ...)
-- 함수가 반환되는 반환값의 타입을 정의한다
RETURN 데이터타입
IS
변수명 변수타입;
BEGIN
PL/SQL 구문;
        
-- 값을 반환하는 구문이 반드시 있어야 함
RETURN 반환값;
END;

 

함수 예시