[8i] 사용자 관리(OLN)

/********************************************************************************************
-- Title : [8i] 사용자 관리(OLN)
-- Reference : OLN
-- Key word : user role
********************************************************************************************/

/****************************************************************************************
-- 사용자 모니터링
****************************************************************************************/

-- 테이블스페이스 할당량
SQL> SELECT tablespace_name, blocks, max_blocks, bytes, max_bytes
  2  FROM dba_ts_quotas
  3  WHERE username = 'SCOTT';
 
TABLESPACE_NAME      BLOCKS    MAX_BLOCKS    BYTES     MAX_BYTES
------------------ --------    ----------  -------   -----------
DATA01                   10            -1    20480            -1

1 row selected.

-- 사용자 계정 상태
SELECT username, account_status, temporary_tablespace
FROM dba_users;

USERNAME  ACCOUNT_STATUS  TEMPORARY_TABLESPACE
--------- --------------- ---------------------
SYS       OPEN            TEMP
SYSTEM    OPEN            TEMP
OUTLN     OPEN            SYSTEM
DBSNMP    OPEN            SYSTEM
TRACESVR  OPEN            SYSTEM
SCOTT     OPEN            SYSTEM
ADAMS     OPEN            SYSTEM
JONES     OPEN            SYSTEM
CLARK     OPEN            SYSTEM
SCOTT2    OPEN            SYSTEM
BLAKE     OPEN            SYSTEM
KJK       OPEN            SYSTEM

12 rows selected.

/****************************************************************************************
-- 관리용
****************************************************************************************/
-- user.sql
select username, user_id, account_status, default_tablespace "DEFAULT_TS"
     , temporary_tablespace "TEMP_TS", profile
from dba_users
/

/****************************************************************************************
-- 새로운 사용자 생성(서버 인증)
****************************************************************************************/
SQL> CREATE USER mapbak
  2  IDENTIFIED BY akqkr
  3  DEFAULT TABLESPACE users
  4  TEMPORARY TABLESPACE temp
  5  QUOTA 15m ON users
  6  PASSWORD EXPIRE;

User created.

/****************************************************************************************
-- 계정 잠금과 암호 제어
****************************************************************************************/
SQL> GRANT create session
  2  TO mapbak;

Grant succeeded.

SQL> conn mapbak/akQkr
Changing password for mapbak
New password: mapbak
Retype new password: ******
Connected.

/****************************************************************************************
-- 테이블스페이스에 대한 사용자 할당량 변경
****************************************************************************************/
SQL> ALTER USER mapbak
  2  QUOTA 0 ON users;  -- 0인경우 더 이상 새로운 익스텐트가 할당 될 수 없다.
 
User altered.

/****************************************************************************************
-- 사용자 삭제
****************************************************************************************/
SQL> DROP USER mapbak
  2  CASCADE;         -- CASCADE : 스키마가 오브젝트를 포함하고 있을 때

User dropped.