[Oracle] 사용자 관리

■ 사용자 생성

 -. 사용자를 생성하기 위해서는 시스템 권한을 가지고 있어야 한다.

 -. 오라클 데이터베이스를 설치할 때 자동으로 생성되는 디폴트 사용자 가운데 시스템 권한을 가진 데이터베이스 관리자인 DBA는

    SYS, SYSTEM이다.

 -. 그러므로 사용자 계정을 발급 받기 위해서 시스템 권한을 가진 SYSTEM으로 접속해야 한다.

① SYSTEM 사용자(SYS)로 접속

C:\User\Administrator> sqlplus / as sysdba

SQL> SHOW USER

② 사용자명은 user01 암호는 tiger로 사용자를 생성한다.

    사용자를 생성하기 위해서는 CREATE USER 명령어를 사용한다.

SQL> CREATE USER user01 IDENTIFIED BY tiger;

③ 새롭게 생성된 사용자로 접속

SQL> CONN user01/tiger

경고

새롭게 생성된 사용자로 접속을 시도하게 되면 접속에 실패하게 된다. 이는 사용자만 생성되었을뿐 사용자에게 아무런 권한(PRIVILEGE) 부여가 되지 않았기 때문입니다. 오라클 DB에 접근하려면 최소한 데이터베이스 접근 권한은 부여 받아야 하기 때문입니다.

■ 사용자 권한

 오라클은 다수의 사용자들이 데이터베이스에 저장된 정보를 공유해서 사용한다. 하지만 정보의 유출이나 불법적인 접근을 방지하기 위해서 철저한 보안 대책이 필요하다. 이러한 보안 대책을 위해서 데이터베이스 관리자가 있어야 한다.

 데이터베이스 관리자는 사용자가 데이터베이스의 객체(테이블, 뷰 등)에 대한 특정 권한을 가질 수 있도록 함으로써 다수의 사용자가 데이터베이스에 저장된 정보를 공유하면서도 정보에 대한 보안이 이루어지도록 한다.

 데이터베이스 관리자만이 SYSTEM 사용자 계정으로 로그인 할 수 있습니다. 데이터베이스 관리자는 데이터베이스 객체에 대한 권한을 부여할 수 있다. 권한 크게 시스템 권한과 객체 권한으로 구분된다.

■ 시스템 권한

 -. 사용자를 생성하였지만, SQL*Plus에 접속을 요구하면 데이터 베이스에 접속이 되지 않는다. 또한 접속이 되더라도 테이블이나 뷰, 인덱스 등을 생성 할 수 없다.

 -. 사용자가 이 모든 작업을 할 수 있으려면 권한을 부여해야 한다.

 -. 이러한 권한을 시스템 권한이라고 한다.(총 130개)

① 데이터베이스 관리자가 가지는 시스템 권한

시스템 권한	기능
CREATE USER	-. 새롭게 사용자를 생성하는 권한
DROP USER	-. 사용자를 삭제하는 권한
DROP ANY TABLE	-. 임의의 테이블을 삭제할 수 있는 권한
QUERY REWRITE	-. 질의 재작성을 할 수 있는 권한
BACKUP ANY TABLE	-. 임의의 테이블을 백업할 수 있는 권한

② 일반 사용자에 데이터베이스를 관리하는 권한

시스템 권한	기능
CREATE SESSION	-. 데이터베이스에 속할 수 있는 권한
CREATE TABLE	-. 사용자 스키마에서 테이블을 생성할 수 있는 권한
CREATE VIEW	-. 사용자 스키마에서 뷰를 생성할 수 있는 권한
CREATE SEQUENCE	-. 사용자 스키마에서 시퀀스를 생성할 수 있는 권한
CREATE PROCEDURE	-. 사용자 스키마에서 함수를 생성할 수 있는 권한

01. 사용자에게 시스템 권한 부여하기 위한 GRANT 명령어

 -. 사용자에게 시스템 권한 부여하기 위해서는 GRANT 명령어를 사용한다.

GRANT system_privilege   TO user_name

 ※ 만일 user_name 대신 PUBLIC을 기술하면 모든 사용자에게 해당 시스템 권한이 부여된다.(PUBLIC이란 DB내에 있는 모든 계정을 의미한다.)

① user01에 데이터베이스 접속 권한 부여

SQL> GRANT CREATE SESSION TO user01;

SQL> CONN user01/tiger

② 데이터베이스 접속 권한 부여한 계정 Oracle SQL Developer에 접속하기

-. 접속 이름 : user01  -. 사용자 이름 : user01  -. 비밀번호 : tiger  -. SID : orcl

02. WITH ADMIN OPTION 옵션

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

     다른 사용자에게 부여할 수 있는 권한도 함께 부여받게 된다.

① WITH ADMIN OPTION 옵션을 지정한 경우

SQL> CREATE USER user02 IDENTIFIED BY tiger;

SQL> GRANT CREATE SESSION TO user02          WITH ADMIN OPTION;

SQL> CONN user02/tiger

SQL> GRANT CREATE SESSION TO user01;

② WITH ADMIN OPTION 옵션을 지정하지 않은경우

SQL> CREATE USER user03 IDENTIFIED BY tiger;

SQL> GRANT CREATE SESSION TO user03;

SQL> CONN user03/tiger

SQL> GRANT CREATE SESSION TO user01;

■ 객체 권한

 -. 객체 권한은 테이블이나 뷰나 시퀀스나 함수 등과 같은 객체별로 DML문(SELECT, INSERT, DELETE)을 사용할 수 있는 권한을 설정하는 것

GRANT object_privilege   ON object   TO user_name

 -. 시스템 권한과 차이점이 있다면 ON 옵션이 추가된다는 점이다. ON 다음에 테이블 객체나 뷰 객체등을 기술합니다.

① 새롭게 생성한 user01 객체로 emp 테이블의 내용을 조회

SQL> CONN user01/tiger

SQL> SHOW USER

SQL> SELECT * FROM emp;

오류

특정 객체에 대한 권한은 그 객체를 만든 사용자에게만 기본적으로 주어진다. 그러므로 다음과 같이 SCOTT 사용자로 로그인해서 user01 사용자가 테이블 객체 emp를 조회할 수 있도록 권한 부여를 해야 한다.

② SCOTT 사용자로 로그인하여 user01 사용자가 테이블 객체 emp를 조회할 수 있도록 권한 부여를 해야 한다.

SQL> CONN scott/tiger

SQL> GRANT SELECT ON emp TO user01;

③ 권한이 부여가 되었다면 user01로 로그인하여 emp 테이블에 접속

SQL> CONN user01/tiger

SQL> SELECT * FROM emp;

오류

권한이 부여가 되었는데오 user01은 emp 테이블 객체를 조회할 수 없다.

01. 스키마(SCHEMA)

 -. 스키마(SCHEMA)란 객체를 소유한 사용자명을 의미한다, 객체 명 앞에 소속 사용자명을 기술한다.

① SCOTT 계정에서 emp 테이블 조회

 -. 기존의 emp테이블 객체를 소유한 사용자명 조회할떄 스키마 SCOTT은 emp 테이블 객체를 소유한 사용자명이다.

 -. 지금까지 객체를 기술할 때 에는 스키마를 생략하였던 이유는 사용자가 SCOTT일 경우 자신이 소유한 객체를 언급할 필요가 없기 떄문이다.

SQL> SELECT * FROM emp;

② user01 계정에서 emp 테이블 조회

  -. user01자신은 emp 테이블을 소유하지 않았기 떄문에 에러가 발생한다.

SQL> SELECT * FROM emp;

ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 00942. 00000 -  "table or view does not exist" *Cause:    *Action: 1행, 14열에서 오류 발생

③ user01 계정에서 스키마(SCOTT)를 사용하여 emp 테이블 조회

 -. 자신이 소유한 객체가 아닌 경우에는 그 객체를 소유한 사용자명을 반드시 기술 하여야 한다.

SQL> SELECT * FROM SCOTT.emp;

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

 -. 사용자 권한과 관련된 데이터 딕셔너리 중에서 ser_tab_privs_made 자료사전은 현재 사용자가 다른 사용자에게 부여한 권한 정보를 알려준다.

 -. 만일 자신에게 부여된 사용자 권한을 알고 싶을 때에는 user_tab_privs_recd 데이터 딕셔너리를 조회하면 된다.

① user01에서 부여한 권한과 부여된 권한

SQL> CONN user01/tiger

SQL> SELECT * FROM user_tab_privs_made;

SQL> SELECT * FROM user_tab_privs_recd;

② SCOTTuser01에서 부여한 권한과 부여된 권한

SQL> CONN SCOTT/TIGER

SQL> SELECT * FROM user_tab_privs_made;

SQL> SELECT * FROM user_tab_privs_recd;

03. 사용자에게 권한을 뻇기 위한 REVOKE 명령어

 -. 사용자에게 부여한 객체 권한을 데이터베이스 관리자나 객체 소유자로부터 철회하기 위해서는 REVOKE 명령어를 사용한다.

REVOKE object_privilege   ON object   FROM user_name

 -. REVOKE 명령어 다음에는 철회하고자하는 객체 권한을 기술하고 ON 다음에는 어떤 테이블에 부여된 권한인지 해당 테이블명을 기술하고

    FROM 다음에는 어떤 사용자에게 부여한 권한인지 사용자명을 기술한다.

① SCOTT 사용자의 권한 철회

SQL> CONN SCOTT/TIGER

SQL> REVOKE SELECT ON emp FROM user01;

풀이

-. REVOKE SELECT ON emp FROM user01; 명령문은 user01 사용자에게 부여된 emp 테이블에 대한 SELECT 권한을 철회한다.     권한이 철회되고 나면 자료사전에 객체 권한에 대한 정보도 함께 사라진다.

SQL> SELECT * FROM user_tab_privs_made;

풀이

-. 권한이 철회되고 나면 자료사전에 객체 권한에 대한 정보도 함께 사라진다.

② user01 계정의 스키마를 통한 SCOTT 계정의 emp 테이블 조회

SQL> CONN user01/tiger

SQL> SELECT * FROM SCOTT.emp;

풀이

-. '① SCOTT 사용자의 권한 철회'에서 user01이 emp 테이블을 SELECT문을 사용하도록 객체 권한을 철회한 것이므로     user01 사용자 계정으로 로그인 해서 SCOTT 사용자의 emp 테이블을 사용할 수 없다.

04. WITH GRANT OPTION

 -. 사용자에게 객체 권한을 WITH GRANT OPTION과 함꼐 부여하면 그 사용자는 그 객체를 접근할 권한을 부여 받으면서 그 권한을 다른 사용자에게

    부여할 수 있는 권한도 함께 부여받게 된다.

① SCOTT 사용자로 로그인하여 사용자 user02가 emp 테이블 객체를 SELECT 할 수 있는 권한을 부여 하고 WITH GRANT OPTION 지정

SQL> CONN SCOTT/TIGER

SQL> GRANT SELECT ON SCOTT.emp TO user02          WITH GRANT OPTION;

SQL> CONN user02/tiger

SQL> GRANT SELECT ON SCOTT.emp TO user01;

② SCOTT 사용자로 로그인하여 사용자 user02가 emp 테이블 객체를 SELECT 할 수 있는 권한을 부여 하고 WITH GRANT OPTION 지정하지 않음

SQL> CONN SCOTT/TIGER

SQL> GRANT SELECT ON SCOTT.emp TO user03;

SQL> CONN user03/tiger

SQL> GRANT SELECT ON SCOTT.emp TO user01;