롤과 권한에 대한 실습

작성자: 강명규
OS: Linux 7.0(Kernel 2.4.10)
Oracle: Enterprise Edition 8.1.7


강명규,임꺽정,홍길동 3명의 사용자를 생성한다.
강명규가 테이블의 소유자로 지정할 것이고, 나머지 임꺽정,홍길동은
강명규가 소유한 테이블을 access하는 사용자로 지정할 것이다.

의적이라는 롤을 생성한다.
의적이라는 롤에게 강명규가 소유한 테이블들에 대한 select권한을 부여할 것이다.
의적이라는 롤은 임꺽정,홍길동에게 부여한다.

결론적으로 임꺽정,홍길동은 강명규가 가진 테이블에 대한 select권한을 가질 것이다.


SQL> connect system/xxxxxx@dev2
연결되었습니다.
SQL> create user 강명규 identified by aa
  2  default tablespace users
  3  temporary tablespace temp
  4  quota 10m on users;

사용자가 생성되었습니다.

SQL> create user 임꺽정 identified by bb
  2  default tablespace users
  3  temporary tablespace temp
  4  quota 0m on users;

사용자가 생성되었습니다.

SQL> create user 홍길동 identified by cc
  2  default tablespace users
  3  temporary tablespace temp
  4  quota 0m on users;

사용자가 생성되었습니다.


여기서 잠깐)
패스워드는 사용자이름과 달리 한글로 지정할 수 없다.


생성된 사용자에게 권한부여
강명규는 기본적으로 사용되는 connect, resource롤을 부여한다.
connect, resource롤은 아래와 같은 시스템권한을 가지고 있다.

SQL> select grantee, privilege from dba_sys_privs
  2  where grantee in('CONNECT', 'RESOURCE');

GRANTEE         PRIVILEGE
--------------- --------------------
CONNECT         ALTER SESSION
CONNECT         CREATE CLUSTER
CONNECT         CREATE DATABASE LINK
CONNECT         CREATE SEQUENCE
CONNECT         CREATE SESSION
CONNECT         CREATE SYNONYM
CONNECT         CREATE TABLE
CONNECT         CREATE VIEW
RESOURCE        CREATE CLUSTER
RESOURCE        CREATE INDEXTYPE
RESOURCE        CREATE OPERATOR
RESOURCE        CREATE PROCEDURE
RESOURCE        CREATE SEQUENCE
RESOURCE        CREATE TABLE
RESOURCE        CREATE TRIGGER
RESOURCE        CREATE TYPE

16 개의 행이 선택되었습니다.


SQL> grant connect, resource to 강명규;

권한이 부여되었습니다.


임꺽정,홍길동은 연결만 가능하도록 하는 create session 시스템권한만 준다.

SQL> grant create session to 임꺽정,홍길동;

권한이 부여되었습니다.

SQL> create role 의적;

롤이 생성되었습니다.

SQL> grant 의적 to 임꺽정,홍길동;

권한이 부여되었습니다.



강명규로 로그인하여 '회원' '주소' 2개의 테이블을 생성하여
이 테이블들에 대한 select권한을 의적이라는 롤에 부여한다.

SQL> connect 강명규/aa@dev2
연결되었습니다.
SQL> create table 회원
  2  (
  3  아이디 varchar(10),
  4  이름 varchar(10),
  5  나이 number(3)
  6  );

테이블이 생성되었습니다.

SQL> create table 주소
  2  (
  3  아이디 varchar(10),
  4  주소 varchar(50)
  5  );

테이블이 생성되었습니다.

SQL> alter table 회원
  2  add constraint 회원_pk primary key(아이디)
  3  using index tablespace users;

테이블이 변경되었습니다.

SQL> alter table 주소
  2  add constraint 주소_fk foreign key(아이디)
  3  references 회원(아이디);

테이블이 변경되었습니다.

SQL> grant select on 회원 to 의적;

권한이 부여되었습니다.

SQL> grant select on 주소 to 의적;

권한이 부여되었습니다.

강명규가 사용가능한 테이블스페이스 할당량
SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
------------------------------ ---------- ---------- ---------- ----------
USERS                                   0   10485760          0       1280

강명규가 가진 시스템권한
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE            ADM
------------------------------ -------------------- ---
강명규                         UNLIMITED TABLESPACE NO

강명규가 가진 객체권한
SQL> select * from user_tab_privs;

GRANTEE         OWNER      TABLE_NAME           GRANTOR    PRIVILEGE            GRA
--------------- ---------- -------------------- ---------- -------------------- ---
의적            강명규     회원                 강명규     SELECT               NO
의적            강명규     주소                 강명규     SELECT               NO

강명규가 다른 사용자에게 부여한 객체권한
SQL> select * from user_tab_privs_made;

GRANTEE         TABLE_NAME           GRANTOR    PRIVILEGE            GRA
--------------- -------------------- ---------- -------------------- ---
의적            주소                 강명규     SELECT               NO
의적            회원                 강명규     SELECT               NO

강명규가 다른 사용자로부터 받은 객체권한
SQL> select * from user_tab_privs_recd;

선택된 레코드가 없습니다.


이제 임꺽정으로 로그인하여 사용자 정보를 보자
홍길동은 임꺽정과 동일하므로 생략하겠다.

SQL> connect 임꺽정/bb@dev2
연결되었습니다.
SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
------------------------------ ---------- ---------- ---------- ----------
USERS                                   0          0          0          0

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE            ADM
------------------------------ -------------------- ---
임꺽정                         CREATE SESSION       NO

임꺽정은 의적이라는 롤을 부여받았으므로 강명규가 소유한 회원테이블을 select할 수 있다.
SQL> select * from 강명규.회원;

선택된 레코드가 없습니다.

아래의 예에서 
임꺽정이 강명규에 대한 테이블을 select할 수 있음에도 불구하고 0개의 row가 리턴되는 이유는 
select 권한이 의적이라는 롤에게 부여되었고, 의적 롤은 임꺽정에게 부여되었으므로 
해당사용자에 대한 직접적으로 select 객체권한을 부여받은 것이 아니기 때문이다.

SQL> select * from user_tab_privs;

선택된 레코드가 없습니다.

SQL> select * from user_tab_privs_made;

선택된 레코드가 없습니다.

SQL> select * from user_tab_privs_recd;

선택된 레코드가 없습니다.

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
임꺽정                         의적                           NO  YES NO

SQL> connect system/xxxxxx@dev2
연결되었습니다.

SQL> select * from dba_tab_privs
  2  where grantee in('의적');

GRANTEE         OWNER      TABLE_NAME           GRANTOR    PRIVILEGE            GRA
--------------- ---------- -------------------- ---------- -------------------- ---
의적            강명규     회원                 강명규     SELECT               NO
의적            강명규     주소                 강명규     SELECT               NO



강명규가 가진 테이블에 대한 select권한을 임꺽정, 의적 둘 다에 부여한다.
롤에게서 권한을 revoke했을때, 사용자는 계속 강명규가 가진 테이블에 대한 select권한을 가진다.
따라서 롤/사용자에게 부여되는 권한이 중복되지 않게 부여하는 것이 관리적인 면에서 중요하다.


SQL> connect 강명규/aa@dev2
연결되었습니다.
SQL> grant select on 회원 to 임꺽정;

권한이 부여되었습니다.

SQL> grant select on 주소 to 임꺽정;

권한이 부여되었습니다.

SQL> revoke select on 회원 from 의적;

권한이 취소되었습니다.

SQL> revoke select on 주소 from 의적;

권한이 취소되었습니다.

SQL> connect 임꺽정/bb@dev2
연결되었습니다.
SQL> select * from 강명규.주소;

선택된 레코드가 없습니다.


cf)
grant옵션(객체권한과 관련)과 admin옵션(시스템권한과 관련)에 따른 grant,revoke는 를 참조한다.