나의 개발일지

[SQL] 데이터베이스 보안 본문

Oracle SQL

[SQL] 데이터베이스 보안

YoonJuHan 2023. 6. 9. 23:04

데이터베이스 보안

  1. 시스템 보안
    1. 시스템 레벨에서 데이터베이스 액세스 및 사용을 제어하는 것
    2. 사용자 이름 및 암호
    3. 사용자에게 할당된 디스크 공간
    4. 사용자가 수행할 수 있는 시스템 작업 등
  2. 데이터 보안
    1. 데이터베이스 객체에 대한 액세스 및 각 객체에 대해 사용자가 수행할 수 있는 작업을 제어하는 것
  3. DCL (Data Control Language)
    1. 사용자에게 권한을 부여하고 회수하기 위하여 사용하는 SQL
    2. GRANT와 REVOKE

 

데이터베이스 보안을 위한 작업

  1. 데이터베이스 관리자의 업무
    1. 새 사용자를 생성
    2. 권한을 부여하거나 취소하는 작업이 포함
  2. 권한
    1. 데이터베이스에 특정 SQL 문을 실행할 수 있는 권리
      1. 데이터베이스 액세스를 제어하는 시스템 권한
      2. 데이터베이스 객체의 액세스 여부를 제어하는 객체권한
  3. 롤
    1. 관련 권한을 따로 분류하여 명명한 그룹을 의미
    2. 권한 관리를 좀 더 쉽고 동적으로 하기 위하여 사용
    3. 사용자의 권한은 롤을 통해 부여 또는 회수 가능

 

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

  1. 사용자에게 데이터베이스 및 데이터베이스 객체에 대한 액세스 권한을 부여할 수 있는 상위 레벨 사용자
  2. 일반 사용자의 권한 관리 담당

 

새 사용자의 생성

  1. 오라클 사용자는 접속 시 데이터베이스에 계정과 암호를 사용하는 로그인 절차 필요
  2. 데이터베이스 관리자는 CREATE USER  명령문으로 새 사용자를 생성
  3. 구문
CREATE USER user_name IDENTIFIED BY password;

 

시스템 권한

  1. 약 100가지 이상의 시스템 권한 존재
    1. 관리자는 사용자에게 GRANT 명령문을 사용하여 특정 시스템 권한을 부여하고 REVOKE 명령문으로 회수
  2. 구문
    1. 사용자에게 여러 가지 권한을 부여하고자 한다면 GRANT 절에 나열
    2. 대상을 PUBLIC으로 설정하면 해당 권한이 데이터베이스의 모든 사용자에게 부여
GRANT privilege [, privilege...] TO {user | role | PUBLIC}
[WITH ADMIN OPTION];

 

스키마 관련 시스템 권한

  1. 스키마
    1. 데이터베이스에서 특정 사용자가 소유하는 테이블, 뷰, 시퀀스 등과 같은 여러 객체의 모음
    2. 스키마는 해당 사용자와 동일한 이름
    3. 응용 프로그램 개발자는 데이터베이스에 스키마 객체를 생성할 수 있어야 하기 때문에 기본적으로 다음과 같은 시스템 권한이 요구
      1. CREATE SESSION
      2. CREATE TABLE
      3. CREATE SEQUENCE
      4. CREATE VIEW
      5. CREATE SYNONYM

 

  • 암호가 yjc인 새 사용자 sm을 생성, 사용자를 생성하는 작업은 DBA의 권한이므로 관리자로 접속 후 실행
CREATE USER sm IDENTIFIED BY yjc;

 

  • sm 사용자가 접속할 수 있는 새 접속을 생성 후 접속을 시도하면 CREATE SESSION 권한 부족으로 오류 발생

 

  • 관리자 접속 창에서 새 사용자 sm에게 세션, 테이블, 시퀀스 및 뷰를 생성할 수 있는 시스템 권한을 할당
    • 다시 sm에 접속해 보면 접속을 할 수 있다.
GRANT CREATE session, CREATE table, CREATE sequence, CREATE view TO sm;

 

객체 권한

  1. 특정 테이블, 뷰, 시퀀스 또는 프로시저에 대해 특정 작업을 수행할 수 있는 권한을 의미
  2. 객체의 소유자가 자신이 소유한 스키마 객체에 대한 객체 권한을 다른 사용자나 롤에 부여
  3. 구문
GRANT object_권한 ON schema.object_name [(coulumn)]
TO {user | role | PUBLIC}
[WITH GRANT OPTION];

 

스키마 관련 객체 권한 목록

객체 권한 테이블 뷰 시퀀스
ALTER ⭕   ⭕
SELECT ⭕ ⭕ ⭕
INSERT ⭕ ⭕  
UPDATE ⭕ ⭕  
DELETE ⭕ ⭕  
REFERENCE ⭕ ⭕  
INDEX ⭕    
EXECUTE      

 

  • insa 접속에서 사용자 sm에게 y_emp 테이블을 SELECT 할 수 있는 권한을 부여
GRANT SELECT ON insa.y_emp TO sm;

 

  • sm 사용자 접속에서 y_emp의 SELECT와 UPDATE를 수행
    • UPDATE 권한이 없어서 오류
SELECT emp_id, emp_name, salary, position
FROM insa.y_emp
WHERE dept_id = 300;

UPDATE insa.y_emp
SET salary = salary * 1.1
WHERE dept_id = 300;

SELECT
UPDATE

 

  • insa 접속에서 sm에게 y_emp 테이블의 salary 열에 대해서만 수정할 수 있는 권한을 부여
GRANT UPDATE (salary) ON insa.y_emp TO sm;

 

  • sm 사용자가 insa의 y_emp에 대한 UPDATE를 수행
UPDATE insa.y_emp
SET salary = salary * 1.1
WHERE dept_id = 300;
ROLLBACK;

 

롤 사용

  1. 사용자에게 부여할 수 있는 관련 권한을 하나로 묶어 명명한 그룹
    1. 권한 취소 및 유지 관리를 쉽게 수행
    2. 많은 사용자에게 권한을 할당하거나 취소하는 작업을 빠르게 동적으로 수행
    3. 롤 생성은 DBA가 수행
  2. 구문
CREATE ROLE role_name;

 

  • DBA가 암호가 ic인 ic 사용자와 ic_mgr이라는 이름의 롤을 생성
CREATE USER ic IDENTIFIED BY ic;
CREATE ROLE ic_mgr;

 

  • DBA는 롤을 생성한 후 ic_mgr 롤에 권한을 포함시키고 ic 사용자에게 롤을 할당
GRANT CREATE session, CREATE table, CREATE view TO ic_mgr;
GRANT ic_mgr TO ic;

 

  • ic 사용자로 로그인한 후 데이터 딕셔너리 session_privs를 조회하여 현재 사용자가 사용할 수 있는 권한을 확인
SELECT * FROM session_privs;

 

  • insa의 y_emp 테이블에 대한 SELECT 권한을 ic_mgr 롤에 포함
GRANT SELECT ON insa.y_emp TO ic_mgr;

 

  • ic 사용자로 로그인해 권한을 확인
SELECT * FROM insa.y_emp;

 

 

관리자의 지속적인 보안관리

  1. 사용자 암호의 재설정
  2. 사용자 또는 롤에 새로운 권한을 추가하거나 삭제
  3. 더 이상 필요하지 않은 데이터베이스 계정 관리
    1. LOCK을 걸어 두거나 삭제

 

사용자 암호 변경

  1. 해당 사용자 세션 또는 데이터베이스 관리자가 변경
  2. 사용자에게 부여된 권한 확인
    1. 데이터베이스 관리자와 사용자들은 데이터 딕셔너리를 액세스 하여 부여받은 권한 확인 가능
  3. 구문
    1. 사용자의 암호는 대소문자 구분
ALTER USER user_name IDENTIFIED BY new_password;

 

  • DBA가 사용자 ic의 암호를 korea로 변경
ALTER USER ic IDENTIFIED BY korea;

 

  • korea를 KOREA로 입력하면 로그인 거부 (대소문자 구분)

 

  • ic 사용자로 접속하고 user_role_privs를 조회하여 현재 사용자에게 부여된 ROLE과 role_sys_privs를 조회하여 ic 사용자에게 롤을 통하여 부여된 시스템 권한을 확인
SELECT * FROM user_role_privs;
SELECT * FROM role_sys_privs;

user_role_privs
role_sys_privs

 

  • sm 사용자로 접속해 부여된 롤과 롤을 통해 부여된 권한을 확인하고 사용자가 부여받은 시스템 권한도 확인
    • 롤과 롤을 통해 부여된 권한은 없지만 부여받은 시스템 권한은 있다.
SELECT * FROM user_role_privs;
SELECT * FROM role_sys_privs;

SELECT * FROM user_sys_privs;

user_role_privs
role_sys_privs
user_sys_privs

 

미리 정의된 롤

  1. DB 생성 시 미리 정의된 롤
    1. DBA
    2. CONNECT
    3. RESOURCE
  2. 관리자 또는 데이터베이스 접속이나 스키마 객체를 생성해야 하는 사용자를 위해서 DBA가 부여
  3. 다른 권한을 미리 정의된 롤에 추가하거나 삭제 가능

 

미리 정의된 롤의 시스템 권한

롤 이름 포함된 권한
DBA 모든 시스템 권한
CONNECT CREATE SESSION
RESOURCE CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

 

권한의 취소

  1. 사용자에게 부여된 권한을 취소하기 위해 REVOKE 명령문 사용
  2. 구문
REVOKE {privilege [, privilege...] | ALL} [ON object]
FROM {user | role | PUBLIC}
[CASCADE CONSTRAINTS];

 

  • 관리자로 접속해 CONNECT 롤에 CREATE VIEW, CREATE SYNONYM, CREATE DATABASE LINK 권한을 추가
GRANT CREATE view, CREATE synonym, CREATE database link TO connect;

 

  • insa에 접속해 sm 사용자에게 부여되었던 insa의 y_emp  테이블에 관련된 모든 객체 권한을 취소
REVOKE all ON insa.y_emp FROM sm;

 

권한의 전달

  1. 권한 또는 롤을 부여하는 동안 옵션을 추가
    1. 권한을 부여받은 사용자가 해당 권한을 다른 사용자 및 롤에 전달
    2. WITH ADMIN OPTION
      1. 부여하는 권한이 시스템 권한 또는 롤인 경우
    3. WITH GRANT OPTION
      1. 부여하는 권한이 객체 권한인 경우
  2. 구문
GRANT [system_priv | object_priv | role_nema]
TO [user | role_name]
[WITH ADMIN OPTION | WITH GRANT OPTION];

 

  • 관리자로 접속해 SELECT ANY TABLE 시스템 권한을 insa 유저에 WITH ADMIN OPTION과 함께 부여
GRANT select any table TO insa
WITH ADMIN OPTION;

 

  • insa로 접속해 WITH ADMIN OPTION과 함께 부여받은 SELECT ANY TABLE 권한을 insa 유저가 sm 유저에게 부여
GRANT select any table TO sm;

 

  • sm 사용자로 접속해 권한이 부여된 것을 확인
SELECT * FROM session_privs;

 

  • insa로 접속해 객체 권한을 부여받은 사용자가 다른 사용자에게 해당 권한을 부여할 수 있도록 WITH GRANT OPTION을 함께 지정하여 sm 사용자에게 y_loc 조회 권한을 부여
GRANT select ON insa.y_loc TO sm
WITH GRANT OPTION;

 

  • sm 사용자로 접속해 ic 사용자에게 y_loc 조회 권한 부여
GRANT select ON insa.y_loc TO ic;

 

  • ic 사용자로 접속해 insa.y_loc를 조회
SELECT * FROM insa.y_loc;

 

권한 취소

  1. WITH ADMIN OPTION은 취소 시 지정된 사용자에게서만 권한 취소
  2. WITH GRANT OPTION은 취소하면 부여된 모든 권한이 연쇄적으로 취소

 

  • 관리자로 접속해 WITH ADMIN OPTION을 포함하여 부여된 SELECT ANY TABLE 권한을 insa에게서 취소
REVOKE select any table FROM insa;

 

  • insa로 접속해 권한이 회수된 것을 확인
SELECT * FROM session_privs;

 

  • sm 사용자로 접속해 권한이 회수되지 않고 유지됨을 확인
    • WITH ADMIN OPTION이 있는 SELECT ANY TABLE 권한은
    • DBA > insa > sm 순으로 전달이 되었고 insa에게서 권한을 회수했으나 sm은 권한이 유지된다.
SELECT * FROM session_privs;

 

  • insa로 접속해 WITH GRANT OPTION을 포함한 insa.y_loc에 대한 SELECT 권한을 sm으로부터 회수
REVOKE select ON insa.y_loc FROM sm;

 

  • sm과 ic로 각각 접속해 insa.y_loc를 조회해 보고 권한이 연쇄적으로 취소되었음을 확인
    • WITH GRANT OPTION이 있는 insa.y_loc에 대한 SELECT 권한은
    • insa > sm > ic 순으로 전달이 되었고 sm에게서 권한을 회수하자 ic까지 연쇄적으로 회수당함
SELECT * FROM insa.y_loc;

 

.

동의어 (SYNONYM)

  1. 테이블이나 뷰, 시퀀스, 프로시저와 같은 데이터베이스 객체에 대하여 일종의 별칭과 같은 정의된 다른 이름
  2. 유형
    1. 전용 동의어 (Private Synonym)
    2. 공용 동의어 (Public Synonym)
  3. 용도
    1. SQL 성능을 향상
      1. 긴 이름의 객체를 짧은 이름의 동의어로 대신함으로써 코딩을 줄여줌
    2. 데이터베이스에 투명성을 제공하여 보안 기능 개선
      1. 참조하고 있는 객체에 대한 소유자, 실제 객체의 이름 등을 모르는 사용자가 객체를 동의어를 통해 액세스

 

전용 동의어 (PRIVATE SYNONYM)

  1. 동의어에 대한 정의가 특정 사용자의 스키마에 저장되어 해당 사용자만 액세스 가능
    1. 주로 긴 객체 이름을 짧게 만들어 객체 액세스를 단순화하는 것이 주된 용도
      1. 뷰와 같은 긴 객체 이름을 사용할 때 유용
      2. 다른 사용자 소유의 테이블을 쉽게 참조
  2. 구문
CREATE SYNONYM synonym_name
FOR object_name;

 

  • 관리자로 접속해 DBA가 사용자 insa, sm, ic에게 SYNONYM을 생성할 수 있도록 CONNECT 롤에 create synonym 권한을 부여하고 CONNECT 롤을 sm, ic에게 부여
GRANT create synonym TO connect;
GRANT connect TO sm, ic;

 

  • insa로 접속해 insa 사용자가 dname_tot_vu에 d_tot라는 이름의 전용 동의어를 생성한다.
CREATE SYNONYM d_tot
FOR dname_tot_vu;

 

  • 동의어는 기본 객체의 액세스와 동일한 방법으로 사용, d_tot는 뷰의 동의어이므로 쿼리를 할 수 있다.
SELECT * FROM d_tot;

 

  • ic 사용자로 접속해 insa.y_emp 테이블에 대한 동의어 emp를 생성
CREATE SYNONYM emp
FOR insa.y_emp;

 

  • ic 사용자가 insa.y_emp와 emp를 각각 조회하면 같은 결과를 볼 수 있다.
SELECT * FROM insa.y_emp;
SELECT * FROM emp;

 

PUBLIC 권한과 공용 동의어

  1. 공용 동의어 (PUBLIC SYNONYM)
    1. 데이터베이스의 모든 사용자가 사용해야 하는 객체에 대하여 개별적인 권한부여 작업 없이 객체를 액세스 할 수 있도록 하기 위한 목적으로 생성
    2. 사용자에게 기본 객체의 소유자 및 원본 이름을 공개하지 않음으로 데이터베이스 보안 유지
    3. 데이터베이스의 모든 사용자가 액세스 할 수 있도록 객체에 대한 PUBLIC 액세스 권한이 먼저 부여되어야 함
  2. 구문
CREATE PUBLIC SYNONYM synonym_name
FOR object_name;

 

  • insa로 접속해 insa.y_loc 테이블에 대하여 zip_code, address 열만 포함하는 addr_list_vu라는 이름의 뷰를 만든다.
CREATE VIEW addr_list_vu
AS 
SELECT zip_code, address
FROM y_loc;

 

  • 공용 동의어 생성을 위해 해당 객체에 PUBLIC 권한이 필요하므로 select on insa.addr_list_vu 권한을 PUBLIC에게 부여
GRANT select on insa.addr_list_vu TO PUBLIC;

 

  • insa 유저는 CREATE PUBLIC SYNONYM 권한 또는 DBA 권한이 없기 때문에 공용 동의어 생성을 시도하면 오류
CREATE PUBLIC SYNONYM addr_list
FOR insa.addr_list_vu;

 

  • DBA로 접속해 addr_list라는 이름의 공용 동의어를 생성
CREATE PUBLIC SYNONYM addr_list
FOR insa.addr_list_vu;

 

  • 이제 데이터베이스의 모든 사용자는 addr_list라는 이름으로 insa.y_loc 테이블의 zip_code와 address를 조회할 수 있다. ic, sm 사용자로 접속해 조회
SELECT * FROM addr_list;

 

  • 데이터 딕셔너리 user_synonyms를 통해 스키마의 동의어 관련 정보를 볼 수 있다. insa로 접속해 insa 유저가 소유하는 동의어 관련 정보를 조회
SELECT * 
FROM user_synonyms;

 

  • PUBLIC SYNONYM 관련 정보는 SYSTEM과 같은 DBA 권한의 사용자가 데이터 딕셔너리 dba_synonyms를 통해 볼 수 있다. 관리자로 접속해 dba_synonyms를 통해 PUBLIC SYNONYM의 addr_list 관련 정보를 조회
SELECT *
FROM dba_synonyms
WHERE synonym_name = 'ADDR_LIST';

 

동의어의 삭제

  1. DROP SYNONYM 명령문으로 삭제
  2. 공용 동의어는 DBA가 DROP PUBLIC SYNONYM 명령문으로 삭제
  3. 테이블 삭제 시
    1. 테이블에 정의된 제약 조건과 인덱스는 함께 삭제
    2. 관련 뷰와 동의어는 존재
  4. 구문
DROP [PUBILC] SYNONYM synonym_name;

 

  • insa로 접속해 dname_tot_vu 뷰를 삭제해도 동의어는 삭제되지 않는 것을 확인한다. 동의어는 삭제되지 않아도 사용할 수는 없음
DROP view dname_tot_vu;

SELECT * FROM user_synonyms;

SELECT * FROM d_tot;

 

  • insa로 접속해 ic_mgr 롤에서 select on insa.y_emp 권한을 회수
REVOKE select ON insa.y_emp FROM ic_mgr;

 

  • ic 사용자는 동의어 emp를 사용할 수 없다. 하지만 ic 사용자 소유의 객체로 emp 동의어는 여전히 존재한다.
SELECT * FROM emp;

SELECT * FROM user_synonyms;

 

  • insa로 접속해  DROP SYNONYM 명령문을 사용하여 insa의 d_tot 동의어를 삭제
DROP SYNONYM d_tot;

 

  • 관리자로 접속해 DROP PUBLIC SYNONYM 명령문을 사용하여 공용 동의어 addr_list를 삭제
DROP PUBLIC SYNONYM addr_list;

 

롤의 삭제

  1. DROP ROLE 명령문으로 정의된 롤을 삭제
  2. 롤을 부여받은 사용자는 롤이 삭제되면서 롤을 통해 부여받은 권한은 즉시 회수
  3. 구문
DROP ROLE role_name;

 

  • ic로 접속해 session_roles를 조회하여 사용 가능한 롤을 조회
SELECT * FROM session_roles;

 

  • 관리자로 접속해 ic_mgr롤을 삭제
DROP ROLE ic_mgr;

 

  • ic사용자 접속을 해제한 후 다시 접속하여 사용 가능한 롤을 조회
SELECT * FROM session_roles;

 

사용자의 삭제

  1. 사용자의 삭제는 DBA의 권한이 필요
  2. 현재 접속 중인 사용자는 삭제 불가
  3. 사용자가 삭제되면 사용자 스키마가 함께 삭제되기 때문에 소유하고 있던 모든 객체가 함께 삭제되므로 주의
  4. 구문
    1. 스키마 객체를 소유하고 있는 사용자는 CASCADE 옵션을 사용
DROP USER user_name [CASCADE];

 

  • 관리자가 sm, ic 사용자를 삭제 (삭제하려는 사용자의 접속을 해제하고 실행)
    • sm 사용자는 소유하는 스키마 객체가 없다.
    • ic 사용자는 동의어 emp를 소유하고 있어서 삭제 시 CASCADE 옵션이 필요하다.
DROP USER sm;
DROP USER ic CASCADE;

저작자표시 (새창열림)

'Oracle SQL' 카테고리의 다른 글

[SQL] 시퀀스  (1) 2023.06.07
[SQL] 뷰  (0) 2023.06.05
[SQL] 인덱스  (0) 2023.06.02
[SQL] 제약 조건  (0) 2023.06.02
[SQL] 테이블 삭제  (0) 2023.06.02
'Oracle SQL' Related Articles more
Comments