17_사용자 관리

[ 사용자 관리 목차 ]

1. 데이터베이스 보안을 위한 권한

  1) 데이터베이스 관리자

  2) 권한의 역할과 종류

2. 사용자 생성하기

3. 권한을 부여하는 GRANT 명령어

  1) 테이블 스페이스 확인하기

  2) WITH ADMIN OPTION

4. 객체 권한

  1) 객체와 권한 설정

  2) 다른 유저의 객체 접근하기

  3) 스키마 알아보기

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

  5) 사용자에게서 권한을 뺏기 위한 REVOKE 명령어

  6) WITH GRANT OPTION

 

 

 

 

[ 1 ] 데이터베이스 보안을 위한 권한

* 권한에 따라서 데이터 사용 범위가 달라진다.

  - 권한 (PRIVILEGES)

     : DB에 저장된 정보를 공유하면서 정보 보안이 이루어지도록 하기 위한 것

     - 시스템 권한 (System Privileges)

     - 객체 권한  (Object Privileges)

        : 스키마의 객체 (TABLE, VIEW, SEQUENCE, 등등..) 에 대한 접근(DML) 여부

  - 역할 (ROLE)

     : 권한의 집합체

 

1. 보안을 위한 데이터베이스 관리자

- 데이터베이스 관리자는 사용자가 데이터베이스의

  객체(테이블, 뷰 등)에 대한 특정 권한을 가질 수 있도록

  함으로써 다수의 사용자가 데이터베이스에 저장된

  정보를 공유하면서도 정보에 대한 보안이 이루어지도록 한다.

- 사용자마다 서로 다른 권한과 롤을 부여함으로써 보안을

  설정 할 수 있다.

 

* DBA (Database Administrator) : 데이터베이스 관리자

  DBA는 권한과 역할을 부여한다.

 

 

2. 권한의 역할과 종류 

- 권한

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

  해당 테이블에 SQL(SELECT/INSERT/UPDATE/DELETE) 문을

  사용할 수 있도록 제한을 두는 것

 

- 데이터베이스 보안을 위한 권한은 두 가지로 나뉜다.

  1) 시스템 권한 (System Privileges)

    : 사용자의 생성과 제거, DB 접근 및 각종 객체를 생성할 수 있는 권한 등

      주로 DBA에 의해 부여되고 그 권한의 수가 80가지가 넘는다.

 

시스템 권한

기능

CREATE USER	새롭게 사용자를 생성하는 권한
DROP USER	사용자를 삭제하는 권한
DROP ANY TABLE	임의의 테이블을 삭제할 수 있는 권한
QUERY REWRITE	함수 기반 인덱스를 생성하는 권한
BACKUP ANY TABLE	임의의 테이블을 백업할 수 있는 권한
CREATE SESSION	데이터베이스에 접속할 수 있는 권한
CREATE TABLE	사용자 스키마에서 테이블을 생성할 수 있는 권한
CREATE VIEW	사용자 스키마에서 뷰를 생성할 수 있는 권한
CREATE SEQUENCE	사용자 스키마에서 시퀀스를 생성할 수 있는 권한
CREATE PROCEDURE	사용자 스키마에서 함수를 생성할 수 있는 권한

[ 대표적인 시스템 권한 종류 ]

 

 

 

  2) 객체 권한 (Object Privileges)

 

 

 

---

 

[ 2 ] 사용자 생성하기

- 오라클 데이터베이스를 설치할 때 자동으로 생성되는

  디폴트 사용자 가운데 시스템 권한을 가진 데이터베이스

  관리자인 DBA는 SYS, SYSTEM이다.

- 사용자 계정을 발급받기 위해서는 시스템 권한을 가진

  SYSTEM으로 접속해야 한다.

 

* SYS, SYSTEM은 DBA 권한이 있다,,?

-- 형식
CREATE USER USER_NAME IDENTIFIED BY PASSWORD;

-- 예
CREATE USER USER10 IDENTIFIED BY USER10;
ALTER USER SYSTEM IDENTIFIED BY MANAGER;  -- 비밀번호 변경하기
ALTER USER SCOTT IDENTIFIED BY TIGER;


-- SYSTEM 계정에서의 코드 --
-- 시스템 권한 : CREATE USER
CREATE USER USER10 IDENTIFIED BY USER10;
-- USER10을 생성하는데 Password가 USER10 이다.
-- USER를 생성하면 권한을 부여해줘야 한다.

GRANT CREATE SESSION TO USER10;

-- 사용자 비밀번호 변경
-- 비밀번호를 까먹었다 그러면 비밀번호 변경해버리면 된다.
ALTER USER USER10 IDENTIFIED BY PWD;
-- 비밀번호를 USER10에서 PWD로 변경했다.

-- 권한을 부여하기 : GRANT
GRANT CREATE SESSION TO USER10; -- USER10에 권한 부여
GRANT CREATE TABLE TO USER10;
GRANT unlimited tablespace to USER10; -- 모든 테이블스페이스에 할당량을 줄 수 있는 권한 부여, 테이블스페이스에 대한 권한 부여
-- 권한을 부여 받아야 테이블을 만들고 그외 다른 객체들을 만들 수 있기 때문에 권한을 받아야한다,,


-- SCOTT 계정에서의 코드 --
CREATE USER USER10 IDENTIFIED BY USER10;
-- 권한이 없어서 안만들어짐

 

* SCOTT 계정에서는 USER 사용자 생성할 수가 없다!

  사용자 생성할 수 있는 권한이 SCOTT 계정에는 없기 때문이다.

  그래서 사용자 생성하는 권한이 있는 SYSTEM 계정에서 사용자를 만들었다.

 

 

[ SCOTT 계정에서는 CREATE USER 권한이 없어서 못만든다. ]

 

 

 

[ SYSTEM 계정에서 CREATE SESSION 권한 부여하기 전 ]

 

 

[ SYSTEM 계정에서 CREATE SESSION 권한 부여한 후 ]

 

[ 테이블 만들려고 하니깐 권한이 없다고 못만듦.. ]

 

[ SYSTEM 계정에서 USER10 계정에 CREATE TABLE 권한 부여해주고 난 후 다시 CREATE TABLE 하니 만들어졌다!! ]

 

 

 

 

[ 실습 : CREATE USER 명령어를 이용한 사용자 생성 ]

- CREATE USER 명령어를 사용하여 사용자명은 USER01,

  암호는 TIGER로 사용자를 생성해보겠다.

 

CONN SYSTEM/MANAGER
SHOW USER;

DROP USER USER01 CASCADE;
CREATE USER USER01 IDENTIFIED BY TIGER;

CONN USER01/TIGER 
-- 접속에 실패한다. 사용자에 아무런 권한이 부여되지 않았기 때문이다.

 

 

 

 

 

---

 

[ 3 ] 권한을 부여하는 GRANT 명령어

- GRANT 명령어

  : 사용자에게 시스템 권한을 부여할 때 사용

 

-- 형식
GRANT PRIVILEGE_NAME, ...
TO USER_NAME;

 

 

[ 실습 : CREATE SESSION 권한 부여하기 ]

- 새로 생성된 USER01에 데이터베이스에 접속할 수 있는

  권한인 CREATE SESSION을 부여한다.

  다시 USER01 사용자로 접속을 시도하면 이번에는

  데이터베이스에 성공적으로 접속된다.

 

CONN SYSTEM/MANAGER
SHOW USER;

DROP USER USER01 CASCADE;
CREATE USER USER01 IDENTIFIED BY TIGER;

GRANT CREATE SESSION TO USER01;

CONN USER01/TIGER

-- SYSTEM 계정에서의 코드 --
-- 권한을 부여하기 : GRANT
GRANT CREATE SESSION TO USER10; -- USER10에 권한 부여
GRANT CREATE TABLE TO USER10;
GRANT unlimited tablespace to USER10; -- 모든 테이블스페이스에 할당량을 줄 수 있는 권한 부여, 테이블스페이스에 대한 권한 부여
-- 권한을 부여 받아야 테이블을 만들고 그외 다른 객체들을 만들 수 있기 때문에 권한을 받아야한다,,

 

 

[ 실습 : CREATE TABLE 권한 부여하기 ]

- 새롭게 생성된 사용자 계정은 테이블을 생성할 권한을 부여받아야만

  CREATE TABLE 명령을 사용할 수 있다.

* CREATE TABLE 권한 회수 REVOKE

  - 형식 : REVOKE CREATE TABLE FROM create_table_name;

 

DROP TABLE EMP01;
CREATE TABLE EMP01(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);

CONN SYSTEM/MANAGER
SHOW USER;

GRANT CREATE TABLE, RESOURCE TO USER01;

CONN USER01/TIGER
SHOW USER;

DROP TABLE EMP01;
CREATE TABLE EMP01(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);

-- 새로 만들어진 사용자 USER10에서의 코드 --
SHOW USER;
-- USER이(가) "USER10"입니다. 출력

-- CREATE TABLE 테이블 생성하기
DROP TABLE EMP01;

CREATE TABLE EMP01(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    DEPTNO NUMBER(2)
);

SELECT * FROM EMP01;

-- 새로 만들어진 사용자 USER10은 CREATE TABLE 권한을 받았기 때문에 테이블을 만들 수 있게 되었다.

 

 

 

 

 

 

1. 테이블스페이스 확인하기 ( 아래의 내용은 개발자보단 DBA가 주로 사용하는 코드들 )

- 테이블스페이스 (Tablespace)

  : 디스크 공간을 소비하는 테이블과 뷰 그리고 그 밖의

    다른 데이터베이스 객체들이 저장되는 장소

 

* C드라이브> prod> oracle> app> oracle> oradata> XE

* 테이블을 여러 개 만들다 보면 저 각각의 DBF에 저장된다..?

 

CONN SYSTEM/MANAGER
SHOW USER;

SELECT USERNAME, DEFAULT_TABLESPACE
FROM DBA_USERS
WHERE USERNAME IN('USER01', 'SCOTT');

-- 테이블 스페이스 쿼터 할당하기
CONN SYSTEM/MANAGER
SHOW USER

ALTER USER USER01
QUOTA 2M ON USERS;

-- CREATE TABLE 생성하기
CONN USER01/TIGER;
SHOW USER;

DROP TABLE EMP01;
CREATE TABLE EMP01(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);

--아래 쉅 내용 코드 --
-- SYSTEM 계정에서의 코드 --
-- 테이블 스페이스 확인하기
SELECT USERNAME, DEFAULT_TABLESPACE
FROM DBA_USERS
WHERE USERNAME IN ('USER10', 'SCOTT');
-- USER 만들 때 테이블스페이스를 지정해서 만들 수 있다.
-- 위의 코드를 실행하면 각각의 계정이 어떠한 테이블을 사용하고 있는 알 수 있다.


-- USER10은 지금 SYSTEM 테이블스페이스로 되어 있다. USERS 테이블스페이스로 변경해보자
-- 테이블 스페이스 변경하기
ALTER USER USER10 DEFAULT TABLESPACE USERS;
ALTER USER USER10 TEMPORARY TABLESPACE TEMP;

-- 테이블스페이스의 쿼터 할당하기
ALTER USER USER10
QUOTA 1000M ON USERS;
-- USERS테이블에서 1기가를 쿼터 할당받아 사용하겠다..?
-- USERS라는 테이블스페이스에 SCOTT계정과 여러 데이터들이 존재한다..

 

 

 

 

[ SYSTEM 계정에서 테이블스페이스 확인한 결과 ]

 

 

[ 테이블스페이스 변경 후 출력한 결과 USER10의 테이블스페이스가 SYSTEM에서 USERS로 변경되었다.  ]

 

 

 

 

 

 

2. WITH ADMIN OPTION (잘 사용 안 함,,)

- 사용자에게 시스템 권한을 WITH ADMIN OPTION과 함께 부여하면

  그 사용자는 데이터베이스 관리자가 아닌데도 불구하고

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

  함께 부여받게 된다.

 

-- WITH ADMIN OPTION을 지정하여 권한 부여하기
CONN SYSTEM/MANAGER
SHOW USER

CREATE USER USER02 IDENTIFIED BY TIGER;
GRANT CREATE SESSION TO USER02 WITH ADMIN OPTION;

CONN USER02/TIGER;
SHOW USER;

GRANT CREATE SESSION TO USER01;

-- WITH ADMIN OPTION을 지정하지 않고 권한 부여하기
CONN SYSTEM/MANAGER
SHOW USER

CREATE USER USER03 IDENTIFIED BY TIGER;
GRANT CREATE SESSION TO USER03;

CONN USER03/TIGER;
SHOW USER;

GRANT CREATE SESSION TO USER01; 
-- 자기가 받은 권한을 다른 사용자에게 부여할 수 없다.

-- SYSTEM 계정에서의 코드 --
-- WITH ADMIN OPTION
CREATE USER USER02 IDENTIFIED BY TIGER;
GRANT CREATE SESSION TO USER02 WITH ADMIN OPTION;
-- CREATE SESSION을 주면서 WITH ADMIN OPTION을 준것,,
-- WITH ADMIN OPTION : CREATE SESSION 권한의 DBA 관리자 권한을 다른 계정에게 부여할 수 있게끔 하는 권한을 부여해준다.

CREATE USER USER03 IDENTIFIED BY TIGER;
GRANT CREATE SESSION TO USER03;
-- USER03은 WITH ADMIN OPTION 권한 없이 CREATE SESSION 권한만 받아서
-- 다른 계정에게 CREATE SESSION 권한을 줄 수 없다.

CREATE USER USER01 IDENTIFIED BY TIGER; -- USER01은 권한 없이 만듦.

 

 

[ USER02에게 WITH ADMIN OPTION 권한을 줬기 때문에 USER02가 다른 계정 USER01에게 CREATE SESSION 권한을 줄 수 있게 되었다. ]

 

 

 

 

---

 

[ 4 ] 객체 권한

 

1. 객체와 권한 설정

- 객체 권한

  : 특정 객체에 조작을 할 수 있는 권한,

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

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

  객체별로 DML문(SELECT, INSERT, DELETE)을

  사용할 수 있는 권한을 설정하는 것이다.

 

-- 형식
GRANT PRIVILEGE_NAME [(COLUMN_NAME)] | ALL 
ON OBJECT_NAME | ROLE_NAME | PUBLIC 
TO USER_NAME;

 

 

 

 

2. 다른 유저의 객체 접근하기

CONN USER01/TIGER;
SHOW USER;
SELECT * FROM EMP; -- 조회할 수 없다.

-- 테이블 객체에 대한 SELECT 권한 부여하기
CONN SCOTT/TIGER;
SHOW USER;
GRANT SELECT ON EMP TO USER01;

CONN USER01/TIGER;
SHOW USER;
SELECT * FROM SCOTT.EMP;

-- SCOTT 계정에서의 코드 --
-- 객체 권한 부여
GRANT SELECT ON EMP TO USER01;

 

 

[ 사용자 USER01객체 권한 부여 전에는 존재하지 않는다고 나옴,, ]

 

 

 

 

[ USER01에게 객체권한 부여 해주고 나니 USER01에서 EMP에 대한 칼럼들 출력할 수 있게 되었다. ]

 

 

 

 

 

 

3. 스키마 알아보기

- 스키마 (SCHEMA)

  : 객체를 소유한 사용자명,

    객체 명 앞에 소속 사용자명을 기술한다.

- 자신이 소유한 객체를 언급할 때 객체 명 앞에

  스키마를 생략할 수 있다.

 

SELECT * FROM SCOTT.EMP;
SELECT * FROM EMP; -- 자신이 소유한 객체를 언급할 때 객체 명 앞에 스키마를 생략할 수 있다.

-- 아래 쉅 내용 코드 --
-- SCOTT 계정에서의 코드
SELECT * FROM SCOTT.EMP; -- 원칙적으로는 이렇게 표현해서 코드를 적어줘야한다. 하지만 아래의 코드처럼 적어도 출력이 된다.
SELECT * FROM EMP;

 

 

 

 

 

 

 

 

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

- 사용자 권한과 관련된 데이터 딕셔너리 중에서

  USER_TAB_PRIVS_MADE 데이터 딕셔너리는

  현재 사용자가 다른 사용자에게 부여한 권한 정보를

  알려준다.

- 만일 자신에게 부여된 사용자 권한을 알고 싶을 때는

  USER_TAB_PRIVS_RECD 데이터 딕셔너리를

  조회하면 된다.

 

CONN SCOTT/TIGER
SELECT * FROM USER_TAB_PRIVS_MADE;
SELECT * FROM USER_TAB_PRIVS_RECD;

-- 아래 쉅 내용 코드 --
-- SCOTT 계정에서의 코드
SELECT * FROM USER_TAB_PRIVS_MADE;  -- 자신이 부여한 객체권한
SELECT * FROM USER_TAB_PRIVS_RECD;  -- 자신이 받은 객체권한

 

[ SCOTT 계정이 부여한 객체 권한 출력 ]

 

 

[ SCOTT 계정이 받은 객체권한 출력 ]

 

 

 

 

5. 사용자에게서 권한을 뺏기 위한 REVOKE 명령어

- REVOKE 명령어

  : 사용자에게 부여한 객체 권한을 데이터베이스 관리자나

   객체 소유자로부터 철회할 때 사용

 

-- 형식
REVOKE {privilege_name | all}
ON object_name
FROM {user_name | role_name | public};

-- 예: 객체 권한 제거하기
SELECT * FROM USER_TAB_PRIVS_MADE;
REVOKE SELECT ON EMP FROM USER01;
SELECT * FROM USER_TAB_PRIVS_MADE;

CONN USER01/TIGER
SELECT * FROM SCOTT.EMP;

-- 아래 쉅 내용 코드 --
-- 객체 권한 제거하기
REVOKE SELECT ON EMP FROM USER01;

 

 

[ USER01에서 EMP 접근할 수 있는 권한을 뺏었더니 USER01에서 SCOTT계정에 있는 EMP 객체에 접근할 수 없게 되었다. ]

 

 

 

 

 

6. WITH GRANT OPTION

- 사용자에게 객체 권한을 WITH GRANT OPTION과 함께

  부여하면 그 사용자는 그 객체를 접근할 권한을 부여받으면서

  그 권한을 다른 사용자에게 부여할 수 있는 권한도 함께

  부여받게 된 것이다.

 

GRANT SELECT ON SCOTT.EMP TO USER02
WITH GRANT OPTION;

CONN USER02/TIGER;
GRANT SELECT ON SCOTT.EMP TO USER01;

-- 아래 쉅 내용 코드 --
-- SCOTT 계정에서의 코드
-- WITH GRANT OPTION
GRANT SELECT ON SCOTT.EMP TO USER02
WITH GRANT OPTION;
-- SCOTT이 가지고 있는 EMP 객체에 접근할 수 있는 권한을 USER02에게 부여함
-- WITH GRANT OPTION 권한을 받았기 때문에 USER02에서도 이제 다른 계정에게 권한을 부여할 수 있게 되었다.

 

 

[ USER02는 SCOTT.EMP에 접근할 수 있는 WITH GRANT OPTION 권한을 부여받았기에 USER02에서도 이제 다른 계정인 USER01에게 SCOTT에 있는 EMP에 접근할 수 있는 권한을 부여할 수 있게 되었다. ]

 

 

[ USER02가 USER01에게 SCOTT에 있는 EMP 접근할 수 있는 권한을 줬기 때문에 USER01에서 EMP 접근해서 EMP를 출력할 수 있게 되었다. ]