Oracle 사용자 권한 제어, 롤, 동의어
아 어렵네 ㅜㅜ
사용자 권한 제어
다중 사용자 환경에서는, 불법적 접근 및 유출 방지를 위하여 보안 대첵이 필요하다. 따라서 "접근권한, 소유권, 암호"를 철저히 관리해야 한다.
또한, 중앙 집중적인 데이터 관리는 분산적으로 관리되는 것보다 보안이 취약하다는 점에 주의해야 한다.
- 권한: 사용자가 데이터베이스 시스템(시스템 권한)을 관리하거나 객체(객체 권한)를 이용할 수 있는 권리.
- 데이터베이스 관리자가 가지는 시스템 권한: 사용자 생성 및 삭제, 사용자 계정에서 객체 생성 및 수정, 데이터베이스 백업 관리
- 일반 사용자가 가지는 시스템 권한: 사용자가 생성한 객체 관리 및 내장 프로시저 관리
시스템 권한은 특정 사용자나 모든 사용자에게 부여할 수 있다.
Query rewrite 시스템 권한을 부여하는 예시를 들어보자.
```
- conn system/manager
- grant query rewrite to scott;
- grant query rewrite to public;
- conn scott/tiger
- select * from user_sys_privs;
``` - 시스템 권한이므로, 'sys'나 'system'으로 접속하여야 Grant가 가능하다.
- scott에게 'query rewrite'라는 권한을 부여한다.
- public, 즉 모든 사용자에게 'query rewrite'라는 권한을 부여한다.
- 권한을 확인하기 위해 scott 계정으로 연결한다.
- user_sys_privs는 현재 세션(지금은 scott)에서 사용자와 롤에 부여된 시스템 권한을 조회할 수 있다.
참고로 user_sys_privs는 이렇게 구성되어있다.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_
---------- -------------------- ------
PUBLIC QUERY REWRITE NO
HR CREATE VIEW NO
HR UNLIMITED TABLESPACE NO
HR CREATE DATABASE LINK NO
HR CREATE SEQUENCE NO
HR CREATE SESSION NO
HR ALTER SESSION NO
HR CREATE SYNONYM NO
권한 철회하는 방법은 아래와 같다.
REVOKE query rewrite FROM scott;
유저를 생성한 후, 권한을 부여해보자.
tiger라는 유저를 생성한 후, scott의 객체에 맘껏 접근할 수 있도록 권한을 부여해보자.
CREATE USER tiger IDENTIFIED BY tiger123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;tiger(비밀번호는 tiger123)라는 유저를 생성한 후, Default(디폴트) 테이블 스페이스는 users, Temporary(임시) 테이블 스페이스는 temp로 정의한다.
테이블 스페이스는 실제 데이터를 저장하는 공간이다. temp에 임시로 저장했다가 commit하면 users에 저장된다고 생각하면 된다.
잠깐, temp는 기본적으로 있는 테이블 스페이스일텐데, users라는 테이블 스페이스는 사용자 정의니까 따로 정의해야 하는건가?
맞다. 아래와 같이 테이블 스페이스를 생성하면 된다.
SQL> conn system/manager
Connected.
SQL> create tablespace test
2 datafile 'C:\oraclexe\app\oracle\oradata\XE\TEST.dbf' size 100m;system으로 접속한 뒤 지정한 경로에 "TEST.dbf" 파일을 생성하게 된다.
다시 돌아가서, 새로운 유저 tiger에게 '아주 기본적인' 권한을 부여하자.
GRANT connect, resource TO tiger;데이터베이스에 접근하는 권한을 부여한다. 이걸 안하면 tiger로 접속이 아예 안된다 ㅎ
다음으로 scott이 직접 tiger에게 '나의 salgrade 테이블 접근 권한을 줄게!'하는 상황을 만들어보자.
connect scott/tiger -- scott 계정에 연결
GRANT SELECT ON scott.salgrade TO tiger;
GRANT UPDATE ON scott.salgrade(hisal) TO tiger;'tiger'에게 자신의 salgrade 테이블에 대해 'SELECT'와 'UPDATE' 권한을 부여했다!
우리는 select, update 권한을 부여할 때 뒤에 'ON 대상'을 붙여야 한다는 것을 알 수 있다.
connect tiger/tiger123
SELECT * FROM scott.salgrade;
UPDATE scott.salgrade
SET hisal = 9000
WHERE grade = 5;위처럼 tiger가 scott의 테이블에 select, update를 자유롭게 하는 것을 볼 수 있다.
물론 scott이 tiger에게 '나 너에게 이런 권한을 줬어!'라고 직접 이야기 했다면 좋겠지만, 직접 말하지 않는 이상 tiger는 '나에게 무슨 권한이 있다는 거야?'라고 생각할 거다.
그럴때, 다음을 통해 권한을 조회할 수 있다.
SELECT * FROM user_tab_privs_made;
- 자신이 타인에게 부여한 객체 권한을 조회
SELECT * FROM user_tab_privs_recd;
- 자신이 타인에게 받은 객체 권한을 조회
SELECT * FROM user_col_privs_made;
- 자신이 타인에게 부여한 칼럼 권한을 조회
SELECT * FROM user_col_privs_recd;
- 자신이 타인에게 받은 칼럼 권한을 조회
롤
- 다수의 사용자와 다양한 권한을 효과적으로 관리하기 위해서, 서로 관련된 권한을 '그룹화'한 개념
다음과 같은 특징이 있다.
- 활성화-비활성화 가능
- 암호 부여 가능
- '자신'에 대한 롤 부여나 순환적 부여 불가능
- 이게 무슨소리냐면, 자기 스스로 '나는 다른 유저의 테이블에 접근할 수 있다!'...라고 할 수 없다는 말이다.
- 순환적 부여는 'A가 B에게, B가 A에게 권한 부여'로, 권한 부여가 사이클을 가지는 경우를 이야기한다.
- 롤은 특정 소유자나 객체에 속하지 않음
- 관리자가 정하는 '규칙'이니까, 사용자가 맘대로 할 수 없다는 의미다.
사전에 정의된 롤 vs 사용자가 정의하는 롤
- 사전에 정의된 롤에는 connect, resource, dba가 있다.
- connect: DB에 접속과 세션 및 객체를 생성할 수 있는 권한
- resource: 더 다양한 객체를 생성할 수 있는 권한
- dba: '관리자 권한'으로, 시스템 자원의 무제한적인 사용이나 시스템 관리에 필요한 모든 권한으로 dba 권한을 받은 사람이 또 dba 권한을 다른 사람에게 부여할 수 있다(그만큼 중요하다는 소리)
사용자가 정의하는 롤은 다음과 같이 구성한다.
CREATE ROLE role [IDENTIFIED BY 비밀번호]롤을 만들었으면 부여해야 하지 않겠나?
- 롤에 권한 부여
GRANT create session TO role_name;
2. 롤에 롤 부여
GRANT role1 TO role2;
다음은 '롤'을 조회하는 방법이다.
select * from role_sys_privs;
select * from role_tab_privs;
select * from user_role_privs;원하는 방식으로 조회하면 된다.
동의어
- 하나의 객체에 대해 다른 이름을 정의하는 방법으로, 데이터베이스 전체에서 사용.
동의어에는 '전용' 동의어와 '공용' 동의어가 있다.
쉽게 이야기하자면, public의 유무 차이다.
CREATE [PUBLIC] SYNONYM syn_name FOR object;아, 동의어를 영어로 하면 synonym이다.
그러면 동의어를 사용하는 예시를 들어보자.
create synonym my_project for project;
select * from my_project;아! 또 깜박했다. synonym 만드는 것도 권한이 필요하다!
conn system/manager
GRANT create synonym to scott;동의어 삭제는 다음과 같다.
DROP synonym my_project;
'공용' 동의어의 활용 방식은 다음과 같다. 일단 scott에게 system 소유의 project 테이블 조회(select) 권한이 있다고 가정하자.
CREATE PUBLIC SYNONYM pub_project FOR system.project;
conn scott/tiger
select * from pub_project;공용 동의어 삭제할 때는, 'public'을 명시해야 한다.
DROP PUBLIC synonym pub_project;
지금까지 사용자 권한 제어, 롤, 동의어에 대하여 정리하였다.