[Oracle] 31.사용자 관리

in DB on Oracle


권한의 역할과 종류



권한은 사용자가 특정 테이블을 접근할 수 있도록 하거나

해당 테이블에 SQL(SELECT/INSERT/UPDATE/DELETE) 문을 사용할 수 있도록 제한을 두는 것을 말합니다.

데이터베이스 보안을 위한 권한은 시스템 권한(System Privileges)과 객체 권한(Object Privileges)으로 나뉩니다.

시스템 권한은 사용자의 생성과 제거, DB 접근 및 각종 객체를 생성할 수 있는 권한 등 주로 DBA에 의해 부여되며

그 권한의 수가 80 가지가 넘기에 대표적인 시스템 권한만 정리하고 넘어가겠습니다.


image



데이터베이스를 관리하는 권한으로 다음과 같은 것이 있습니다.

이러한 권한은 시스템 관리자가 사용자에게 부여하는 권한입니다.


image





객체 권한은 객체를 조작할 수 있는 권한입니다.

객체는 우리가 학습한 것 중에서 테이블, 뷰 등을 예로 들 수 있고,

이미 학습한 시퀀스, 인덱스 등과 앞으로 배울 동의어가 모두 객체에 해당됩니다.


사용자 생성



사용자 계정을 발급 받기 위해서 시스템 권한을 가진 SYSTEM으로 접속해야 합니다!

사용자 계정을 생성하려면 다음과 같이 합니다. 


-- SYSTEM 계정

CREATE user user01 identified by 1234;



user01이 생성되었다고 해서 바로 Oracle에 접속할 수 있는 것은 아닙니다~

데이터베이스에 접근할 수 있게 다음과 같이 접속 권한을 주어야합니다. 


-- SYSTEM 계정
-- 사용자 접속 권한 줌 : 세션 권한 안주면 접속 불가

GRANT CREATE SESSION TO user01;



사용자에게 시스템 권한을 WITH ADMIN OPTION과 함께 부여하면 그 사용자는 데이터베이스 관리자가 아닌데도 불구하고

부여받은 시스템 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받게 됩니다. 


--SYSTEM 계정
GRANT CREATE SESSION TO user01 WITH ADMIN OPTION;



그러면 user01이 다른 사용자에게 데이터베이스 접근 권한을 줄 수 있습니다. 


--user01 계정

GRANT CREATE SESSION TO user02;




시스템 권한을 다시 빼앗으려면 다음과 같이합니다. 


--SYSTEM 계정
REVOKE CREATE SESSION FROM user01;




테이블 생성 권한



사용자 user01에게 테이블 생성 권한을 주려면 다음과 같이 합니다! 


--SYSTEM 계정
--테이블 생성 권한 줌 (그래도 테이블 생성 불가-> tablespace 권한도 추가해주어야함) 

GRANT CREATE TABLE TO user01; 

-- 테이블 스페이스 권한도 추가해주어야함 -> 테이블 삭제, 생성, 변경 가능 

GRANT UNLIMITED TABLESPACE TO user01;




객체 권한



객체 권한은 특정 객체에 조작을 할 수 있는 권한입니다.

객체의 소유자는 객체에 대한 모든 권한을 가집니다.

다음은 객체와 권한 설정할 수 있는 명령어를 매핑시켜 놓은 표입니다!


image



객체 권한은 테이블이나 뷰나 시퀀스나 함수 등과 같은 객체별로

DML문(SELECT, INSERT, DELETE)을 사용할 수 있는 권한을 설정하는 것입니다!

객체 권한은 다음과 같이 부여합니다(테이블 소유주 또는 SYSTEM 계정) 


-- HR 계정--
--USER01에게 EMP 테이블 조회 권한을 줌

GRANT SELECT ON emp TO user01;



권한을 받은 user01은 HR 계정의 emp 테이블을 다음과 같이 조회할 수 있습니다. 


-- user01 계정 --

SELECT *
FROM hr.emp;



위와 같이 hr은 테이블을 소유한 사용자명인데, 이를 스키마라고 합니다!

자신이 소유한 객체가 아닌 경우에는 그 객체를 소유한 사용자명(스키마)을 반드시 기술해야 합니다.

다음과 같이 속성에 대한 권한만 줄 수도 있습니다. 


-- HR 계정 --

GRANT UPDATE(salary) on emp to user01;




사용자에게 부여된 권한 조회



이제 사용자와 관련된 권한을 조회해보도록 합시다!

user_tab_privs_made 데이터 딕셔너리는 현재 사용자가 다른 사용자에게 부여한 권한 정보를 알려줍니다! 


--HR계정
--테이블에 대해 준 권한

SELECT
    *
FROM
    user_tab_privs_made;




image



아까 user01에게 준 EMP 테이블 조회 권한이 보입니다!

컬럼에 대해 준 권한은 user_col_privs_made 데이터 딕셔너리에서 조회 가능합니다! 


--HR계정
--컬럼에 대해 준 권한

SELECT
    *
FROM
    user_col_privs_made;




image





그렇다면 받은 권한을 조회할 때는 어떻게 할까요?

user_tab_privs_recd 데이터 딕셔너리와 user_col_privs_recd 데이터 딕셔너리를 이용합니다! 


--user01 계정
--테이블에 대해 받은 권한

SELECT
    *
FROM
    user_tab_privs_recd;




image




--user01 계정
--컬럼에 대해 받은 권한

SELECT
    *
FROM
    user_col_privs_recd;




image




권한 철회



권한을 다시 철회할 때는 REVOKE 문을 이용합니다! 


--HR 계정

REVOKE SELECT ON emp FROM user01;



테이블에 대한 모든 권한을 철회할 때는 다음과 같이 합니다. 


--HR 계정

REVOKE ALL SELECT ON emp FROM user01;




WITH GRANT OPTION



사용자에게 시스템 권한을 WITH ADMIN OPTION과 함께 부여하면 그 사용자는 데이터베이스 관리자가 아닌데도 불구하고

부여받은 시스템 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받게 된다고 했습니다.

객체 권한에서는 WITH GRANT OPTION이 같은 기능을 합니다! 


GRANT SELECT ON emp TO user01 WITH GRANT OPTION;