2012.01.27 Admin 8일차(3) (사용자관리, Privilege) ; 유저만들때 고려해야할 점(tablespace), System privilege, Object privilege

용어정리
Schema : 특정 사용자가 만든 모든 것(A named collection of objects)
(Username과 살짝 의미는 다르지만 혼용해서 사용하기도 한다.) 

Users

유저만들기
*시작하기전에
주의사항 : Default tablespace과 temporary tablespace를 지정해줘야 한다.(지정할 tablespace는 미리 만들어저 있어야함)
그림참조

USER 만드는 예시

CREATE USER koo
IDENTIFIED BY oracle_1
DEFAULT TABLESPACE koo
TEMPORARY TABLESPACE kootemp
QUOTA UNLIMITED ON koo
QUOTA 10M on users
 

예시설명
1. koo 사용자를 만든다.
2. 비밀번호는 oracle_1
3. koo 사용자로 로그인 한 후 table만들때 따로 tablespace를 지정하지 않으면 default tablespace는 koo tablespace이다.
4. 위와같이 default temporary tablespace는 kootemp이다.
5. koo tablespace의 용량은 무제한으로 사용할 수 있다.
6. users tablespace의 용량을 10M 사용할 수 있다.
* 참고사항 : QUOTA행을 써주지 않으면 tablespace용량을 사용하지 못한다.
                     ---> 보통 quota쓰지 않고, 다음에 나올 Privilege중 UNLIMITED TABLESPACE 를 계정에다가 할당한다.


USER 설정 변경예시

ALTER USER koo
QUOTA UNLIMITED ON koo
QUOTA 0M on users;
 

설명 : koo tablespace의 용량을 무제한 사용, users tablespace를 사용못하게 함
(비밀번호 변경, 디폴트테이블스페이스 변경도 다 동일한 방식으로 하면 됩니다.)


USER 삭제(drop) 하기
DROP USER user_name [CASCADE];

*CASCADE옵션은 유저가 생성한 object까지 모두 삭제
* 현재 유저가 접속하고 있으면 삭제할 수 없습니다.

SQL> drop user koo;
drop user koo
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected 

USER 관련 딕셔너리
* DBA_USERS, DBA_TS_QUOTAS


--------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------- 
-------------------------------------------------------------------------------------------------------- 

해당부분 참고사이트 ( http://www.oracleclub.com )

Privilege : ~해도 좋다 관련 권한
관련 딕셔너리
DBA_SYS_PRIVS
SESSION_PRIVS : 어떤 privs가 존재하는지 보여주는 딕셔너리
DBA_TAB_PRIVS
DBA_COL_PRIVS
2종류가 있다.
 

1. 시스템권한(System Privileges) (http://www.oracleclub.com/lecture/1008)

- 시스템권한은 사용자가 데이터베이스에서 특정 작업을 수행 할 수 있도록 한다
 

- 권한의 ANY 키워드는 사용자가 모든 스키마에서 권한을 가짐을 의미 한다.
(DROP ANY TABLE 권한을 가지게 되면, 그 사용자는 모든 사용자의 TABLE을 DROP할 수 있게된다.)

- 주로 DDL과 연관 

- SYSDBA, SYSOPER은 Linux에서 root에 맞먹는 최고권한(SYSDBA권한이 SYSOPER권한+with admin option+추가권한) 
 

- GRANT 명령은 사용자 또는 ROLE에 대해서 권한을 부여 할 수 있다.
사용법 : GRANT CREATE SESSION TO user_name [WITH ADMIN OPTION];  
(여기서 CREATE SESSION은 DB에 접속할 수 있는 권한)
(WITH ADMIN OPTION으로 받은 권한은 다른 사용자에게도 GRANT가능)

 

- REVOKE 명령은 권한을 회수 한다.
사용법 : REVOKE CREATE SESSION FROM user_name;
(CREATE SESSION권한을 user_name사용자로부터 회수)

WITH ADMIN OPTION을 사용하여 시스템권한 취소

WITH ADMIN OPTION을 사용하여 시스템권한을 부여했어도 시스템권한을 취소 할 때는 연쇄적으로 취소 되지 않는다.

시나리오

• 1. DBA가 STORM에게WITH ADMIN OPTION을 사용하여 CREATE TABLE 시스템권한을 부여 한다.
• 2. STORM이 테이블을 생성 한다.
• 3. STORM이 CREATE TABLE 시스템권한을 SCOTT에게 부여 한다.
• 4. SCOTT가 테이블을 생성 한다.
• 5. DBA가 STORM에게 부여한 CREATE TABLE 시스템권한을 취소 한다.

결과

• - STORM의 테이블은 여전히 존재하지만 새 테이블을 생성할 수 있는 권한은 없다.
• - SCOTT는 여전히 테이블과 새로운 테이블을 생성 할 수 있는 CREATE TABLE권한을 가지고 있다.

2. 객체권한(Object Privileges)  (http://www.oracleclub.com/lecture/1009)

- 테이블이나 뷰, 시퀀스, 프로시저, 함수, 또는 패키지 중 지정된 한 객체에 특별한 작업을 수행 할 수 있게 한다.

- 객체 소유자는 다른 사용자에게 특정 객체권한을 부여 할 수 있다.

- PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 해야 한다.

- 기본적으로 소유한 객체에 대해서는 모든 권한이 자동적으로 획득된다.

- WITH GRANT OPTION 옵션은 ROLE에 권한을 부여할 때는 사용할 수 없다.
- 주로 DML과 연관 
 

(관련 딕셔너리 : 
SER_TAB_PRIVS : 객체권한의 소유자, 객체권한 부여자, 객체권한 피부여자를 볼수 있음

USER_TAB_PRIVS_MADE : 사용자가 부여한 모든 객체권한

USER_TAB_PRIVS_RECD : 사용자가 부여받은 모든 객체권한

USER_COL_PRIVS : 객체권한의 소유자, 객체권한 부여자, 객체권한 피부여자의 컬럼의 객체권한

USER_COL_PRIVS_MADE : 사용자가 부여한 객체 컬럼에 대한 모든 객체권한

USER_COL_PRIVS_RECD : 사용자가 부여받은 객체 컬럼에 대한 모든 객체권한

객체에 따른 권한 목록 예

객체권한	테이블	뷰	시퀀스	프로시저
ALTER	○		○
DELETE	○	○
EXECUTE				○
INDEX	○
INSERT	○	○
SELECT	○	○	○

객체권한 부여 문법

위의 표에서 맨 왼쪽에 있는 ALTER, DELETE, EXECUTE.. 등등은 object_privilege란에 오면 되고, 맨 윗줄에 있는 테이블, 뷰, 시퀀스, 프로시저 등등은 ON 다음에 있는 object에 입력하면 된다.

• - object_privilege : 부여할 객체권한의 이름
• - object : 객체명
• - user, role : 부여할 사용자 이름과 다른 데이터 베이스 역할 이름
• - PUBLIC : 객체권한, 또는 데이터베이스 역할을 모든 사용자에게 부여할 수 있다.
• - WITH GRANT OPTION : 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로 부여할 수 있게 된다.

객체권한 부여 예제

	
-- scott USER에게 emp테이블을 SELECT, INSERT할 수 있는 권한을 부여했다.
-- scott USER도 다른 USER에게 그 권한을 부여 할 수 있다.
SQL>GRANT SELECT, INSERT
    ON emp
    TO scott
    WITH  GRANT  OPTION;
 권한이 부여되었습니다.
	

객체권한의 회수

• - 객체 권한의 철회는 그 권한을 부여한 부여자만이 수행할수 있다.
• - CASCADE CONSTRAINTS : 이 명령어의 사용으로 참조 객체 권한에서 사용 된 참조 무결성 제한을 같이 삭제 할 수 있다.
• - WITH GRANT OPTION으로 객체 권한을 부여한 사용자의 객체 권한을 철회하면, 권한을 부여받은 사용자가 부여한 객체 권한 또한 같이 철회되는 종속철회가 발생한다.

객체권한 회수 예제

 
-- scott USER에게 부여한 emp 테이블에 대한 SELECT, INSERT 권한 회수 예제
-- 만약 scott USER가 다른 사용자에게 SELECT, INSERT권한을 부여했으면
     그 권한들도 같이 회수가 된다.
SQL>REVOKE SELECT, INSERT
    ON emp
    FROM scott;
 권한이 회수되었습니다. 

 

WITH GRANT OPTION을 사용하여 객체권한 회수

WITH GRANT OPTION을 사용하여 부여한 객체 권한을 취소하면 취소 작업이 연쇄적으로 수행 된다.

시나리오

• 1. SCOTT가 STORM에게 WITH GRANT OPTION을 사용하여 emp 테이블의 SELECT 권한을 부여 한다.
• 2. STORM이 emp 테이블의 SELECT 권한을 TEST에게 부여 한다.
• 3. SCOTT가 STORM에게 부여한 emp 테이블의 SELECT 권한을 취소 한다.

결과

• - SCOTT가 STORM에게 부여한 emp 테이블에 대한 SELECT 권한을 취소하면, TEST USER가 emp 테이블을 SELECT할 수 있는 권한도 자동으로 취소가 된다.