오라클 데이터베이스는 다양한 사용자들이 접근하고 객체를 관리해야 하는 환경에서 중요한 보안 및 권한 제어 기능을 제공합니다. 이를 통해 데이터베이스 관리자는 시스템 권한, 객체 권한, 롤, 동의어를 활용하여 사용자들에게 적절한 권한을 부여하고 관리할 수 있습니다. 이 글에서는 오라클 사용자 권한 제어에 대해 알아보겠습니다.
오라클 사용자 권한 제어(시스템 권한, 객체 권한, 롤,
동의어)
시스템 권한
시스템 권한은 데이터베이스를 관리하는 데 필요한 권한입니다. DBA(Database Administrator)는 사용자 생성, 삭제, 객체 생성 및 수정, 데이터베이스 백업 관리 등의 시스템 권한을 가지고 있습니다. 예를 들어, CREATE USER, DROP USER, DROP ANY TABLE, QUERY REWRITE, BACKUP ANY TABLE 등이 있습니다.
시스템 권한 생성
사용법 : GRANT {system_priv|role] [, {system_priv|role} ]. . . TO {사용자|역할|공공} [, {사용자|역할|공공}]. . . [관리 옵션 사용]
PUBLIC : 모든 사용자에게 해당 시스템 권한 부여
WITH ADMIN OPTION : 해당 시스템 권한을 다른 사용자나 롤에 재부여 허용
예) query rewrite 시스템 권한을 scott 사용자와 모든 사용자에게 부여하기
CONNECT sysyem/manager (관리자 접속)
GRANT query rewrite TO scott; (scott에게 부여)
GRANT query rewrite TO PUBLIC; (모든 사용자에게 부여)
시스템 권한 철회
사용법: REVOKE { system_priv|role] [, {system_priv|role} ]. . . FROM {user|role|PUBLIC} [, {user|role|PUBLIC}]. . .
예) scott 사용자에게 부여한 query rewrite 시스템 권한 철회하기
REVOKE query rewrite FROM scott;
객체 권한
일반 사용자는 자신이 생성한 객체를 관리할 수 있는 권한과 내장 프로시저를 관리할 수 있는 권한을 가질 수 있습니다. 예를 들어, CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE 등이 일반 사용자가 가질 수 있는 객체 권한의 예시입니다.
객체 권한부여
객체 권한을 부여하기 전에 tiger 사용자를 생성합니다.
CREATE USER tiger3 IDENTIFIED BY tiger123 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
tiger 사용자에게 데이터베이스에 대한 접속, 객체 관리 및 롤 사용 권한을 부여합니다.
GRANT connect, resource TO tiger3;
scott이 tiger에게 자신이 소유한 dept 테이블을 조회할 수 있는 SELECT 권한을 부여합니다.
GRANT SELECT ON scott.dept TO tiger3;
tiger 사용자로 로그인하여 scott의 dept 테이블을 조회합니다.
sqlplus tiger3/tiger123
SELECT * FROM scott.dept;
scott이 tiger에게 scott 소유의 dept 테이블의 DNAME을 수정할 수 있는 UPDATE 권한을 부여합니다.
sqlplus scott/tiger
GRANT UPDATE (dname) ON dept TO tiger3;
tiger 사용자로 로그인하여 dept 테이블을 수정합니다.
sqlplus tiger3/tiger123
UPDATE scott.dept SET dname=’SALES’ WHERE DEPTNO=30;
객체 권한 철회
scott이 tiger에게 부여한 student 테이블에 대한 SELECT, UPDATE 권한을 철회합니다.
sqlplus scott/tiger
REVOKE UPDATE ON dept FROM tiger3;
REVOKE SELECT ON dept FROM tiger3;
객체 권한 조회 : tiger 사용자로 로그인하여 부여된 사용자 객체 권한을 조회합니다.
sqlplus tiger3/tiger123
SELECT * FROM user_tab_privs_recd; (tiger 사용자에게 부여된 객체 권한과 객체 이름 조회)
tiger 사용자로 로그인하여 부여된 칼럼에 대한 객체 권한과 칼럼 이름을 조회합니다.
SELECT * FROM user_col_privs_recd; (tiger 사용자에게 부여된 칼럼에 대한 객체 권한과 칼럼 이름 조회)
롤(role)
롤은 여러 사용자와 권한을 효과적으로 관리하기 위해 권한을 그룹화한 개념입니다. CONNECT 롤, RESOURCE 롤, DBA 롤 등이 있으며, 사용자에게 롤을 부여하면 해당 사용자의 권한이 자동으로 수정됩니다. 또한 롤은 활성화 또는 비활성화를 통해 일시적인 권한 부여와 철회가 가능하며, 암호를 부여할 수도 있습니다.
롤의 종류
CONNECT 롤
사용자가 데이터베이스에 접속하여 세션을 생성하거나 테이블, 뷰 등의 객체를 생성할 수 있는 권한을 그룹화한 롤입니다.
CONNECT 롤을 부여받은 사용자는 테이블, 뷰, 시퀀스, 동의어 등을 생성할 수 있으며, 다른 데이터베이스에 접속할 수 있는 권한도 가질 수 있습니다.
RESOURCE 롤
사용자에게 자신의 테이블, 시퀀스, 프로시저, 트리거 등의 객체를 생성할 수 있는 권한을 부여한 롤입니다.
주로 사용자 생성 시 CONNECT 롤과 함께 부여되는 롤로, 사용자가 자신의 객체를 생성하고 관리할 수 있도록 합니다.
DBA 롤
시스템 자원의 무제한적인 사용이나 시스템 관리에 필요한 모든 권한과 DBA 권한을 가진 강력한 롤입니다.
DBA 롤은 모든 사용자가 소유하는 CONNECT, RESOURCE, DBA 권한을 포함한 어떠한 권한도 부여하거나 철회할 수 있습니다.
따라서 DBA 롤의 부여는 엄격히 통제되어야 하며, 데이터베이스의 철저한 보안 관리를 위해 사용되어야 합니다.
롤 생성
롤 자체에 대한 보안을 위해 롤 생성 시 암호를 부여할 수 있습니다.
사용법: CREATE ROLE role_name [NOT IDENTIFIED|IDENTIFIED {BY password | EXTERNALLY}]
NOT IDENTIFIED : 롤 활성화 시 암호에 의한 검증 과정 생략
IDENTIFIED : 롤 활성화 시 암호에 의한 검증 과정 필요
BY password : 롤 활성화 시 사용되는 암호 지정
EXTERNALLY : 롤 활성화 시 운영체제 인증을 통한 사용자 검증
예시) sqlplus “/as sysdba”
CREATE ROLE hr_clerk;
롤에 권한 부여
롤에 시스템 권한, 객체 권한, 다른 롤을 부여할 수 있습니다.
사용자에게 권한을 부여하는 것과 유사하게 GRANT 명령문을 사용하여 부여합니다.
롤에 시스템 권한 부여
예시: hr_mgr 롤에 CREATE SESSION 시스템 권한을 부여
GRANT create session TO hr_mgr;
롤에 객체 권한 부여
사용자가 롤에 객체 권한을 부여할 수 있는 경우는 2가지가 있습니다.
사용자가 객체의 소유자인 경우
WITH GRANT OPTION 옵션과 함께 객체 권한을 부여받은 경우
예시: scott 사용자의 dept 테이블에 대한 SELECT, INSERT, DELETE 객체 권한을 hr_clerk 롤에 부여
sqlplus scott/tiger
GRANT select, insert, delete ON dept TO hr_clerk;
롤 부여
롤은 사용자 또는 다른 롤에게 부여할 수 있습니다.
WITH ADMIN OPTION을 부여받은 롤은 해당 롤을 다른 사용자나 롤에게 재부여할 수 있습니다.
예시: hr_clerk 롤을 hr_mgr 롤과 tiger 사용자에게 부여
GRANT hr_clerk TO hr_mgr;
GRANT hr_clerk TO tiger;
롤에 대한 정의 조회:
데이터 딕셔너리를 사용하여 롤에 부여한 시스템 권한과 객체 권한을 조회할 수 있습니다.
롤에 부여한 시스템 권한 조회
SELECT * FROM role_sys_privs;
롤에 부여한 객체 권한 조회:
SELECT * FROM role_tab_privs;
사용자가 부여받은 롤 조회:
SELECT * FROM user_role_privs;
동의어(SYNONYM)
동의어는 객체에 대해 다른 이름을 정의하는 기능으로, 테이블이나 뷰와 같은 객체의 소유자와 이름을 지정하는 번거로움을 해결하기 위해 사용됩니다. 동의어는 개별 사용자를 대상으로 한 전용 동의어와 전체 사용자를 대상으로 한 공용 동의어로 나뉘며, 사용자에게 객체에 대한 접근 권한을 부여받은 경우에만 동의어를 정의할 수 있습니다.
동의어 생성
동의어는 개별 사용자를 대상으로 한 전용 동의어(Private Synonym)와 전체 사용자를 대상으로 한 공용 동의어(Public Synonym)로 구분됩니다.
전용 동의어는 객체에 대한 접근 권한을 부여받은 사용자가 정의한 동의어로 해당 사용자만 사용할 수 있습니다.
공용 동의어는 권한을 주는 사용자가 정의한 동의어로 누구나 사용할 수 있습니다.
공용 동의어는 DBA 권한을 가진 사용자만 생성할 수 있습니다.
동의어 사용법
전용 동의어 생성
CREATE SYNONYM [schema.]synonym FOR [schema.]object;
공용 동의어 생성
CREATE PUBLIC SYNONYM [schema.]synonym FOR [schema.]object;
동의어 삭제
DROP SYNONYM [schema.]synonym;
예시: my_project와 pub_project 동의어 삭제
DROP SYNONYM my_project;
DROP SYNONYM pub_project;
오라클 데이터베이스에서는 시스템 권한, 객체 권한, 롤, 동의어를 통해 사용자들의 권한을 제어할 수 있습니다. 시스템 권한은 데이터베이스 관리를 위한 권한이며, 객체 권한은 사용자가 생성한 객체를 관리하는 권한입니다. 롤은 여러 사용자와 권한을 효과적으로 관리하기 위한 그룹화된 권한을 제공하며, 동의어는 객체에 대해 다른 이름을 정의하여 편리하게 접근할 수 있도록 합니다. 이러한 권한 제어 기능을 통해 데이터베이스 보안을 강화하고 사용자들에게 필요한 권한을 제공할 수 있습니다.
이전 글 – 오라클 테이블 복구(테이블 데이터 복원하기)
이전 글 – 오라클 DML – 데이터 조작 언어(INSERT, UPDATE, DELETE, SELECT)