[10g] 사용자 및 권한/롤 관리 - ver.dbrang

/****************************************************************************************
-- Title : [10g] 사용자 및 권한/롤 관리 - ver.dbrang
-- Key word : user privilege role
****************************************************************************************/

-- 테이블스페이스 확인
select a.tablespace_name, a.status, a.contents, b.default_ts "DEFAULT"
     , a.logging, a.allocation_type, a.plugged_in
     , a.extent_management -- 9i 이상부터 사용
     , a.segment_space_management -- 9i 이상부터 사용
     , a.bigfile -- 10g 이상부터 사용
     , to_char(a.initial_extent/1024, '999,999.00') "initial_extent(KB)"
     , to_char(a.next_extent/1024, '999,999.00') "next_extent(KB)"
     , to_char(a.min_extents/1024, '999,999.00') "min_extents(KB)"
     , to_char(a.max_extents/1024/1024, '999,999.00') "max_extents(MB)"
from dba_tablespaces a
left outer join (select property_value as ts_nm
                      , 'DEFAULT' as default_ts
                 from database_properties
                 where property_name
                 in ('DEFAULT_TEMP_TABLESPACE','DEFAULT_PERMANENT_TABLESPACE')
                ) b
on a.tablespace_name = b.ts_nm;

/**************************
-- 사용자 관리
**************************/

/*
-- 사용자/권한/롤 관련 뷰
*/
-- 사용자 관련 뷰
select username, account_status, default_tablespace, temporary_tablespace, profile
from dba_users;

-- 사용자 할당 테이블스페이스 뷰
select username, tablespace_name, bytes, max_bytes, dropped
from dba_ts_quotas;

-- 권한 관련 뷰
select * from dba_sys_privs;
select * from dba_tab_privs;
select * from dba_col_privs;

-- 롤 확인 뷰
select * from dba_roles;
select * from dba_role_privs;

-- 사용자별 테이블스페이스 할당 뷰
select * from dba_ts_quotas;

select u.username, u.default_tablespace, q.max_bytes, q.max_blocks
from dba_users u, dba_ts_quotas q
where u.username = q.username
and u.default_tablespace = q.tablespace_name;

 

/*
-- 사용자 생성 및 TS 할당량 관리
*/
-- 사용자 생성
create user fff identified by fff;

create user ggg identified by ggg
default tablespace TS_DATA0
temporary tablespace TEMP;

create user ttt identified by ttt
default tablespace TS_DATA1
temporary tablespace TEMP
quota 0 on SYSTEM
quota 0 on SYSAUX;

-- 사용자 계정 확인
select username, account_status, default_tablespace, temporary_tablespace, profile
from dba_users where username in ('FFF', 'GGG', 'TTT');

-- 기본 권한 설정
grant create session to fff, ggg, ttt;
grant create table to fff, ggg, ttt;

-- 테이블 생성(각 사용자별로 로긴 후 테스트)
SQL_fff> create table tbl1( a number);
SQL_ttt> create table tbl1( a number);
SQL_ggg> create table tbl1( a number);
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

-- 사용자에 QUOTA 할당
alter user fff quota 1M on USERS;
alter user ggg quota 2M on TS_DATA0;
alter user ttt quota 3M on TS_DATA1;

-- 사용자 할당 TS 확인
select username, tablespace_name, bytes, max_bytes, dropped
from dba_ts_quotas where username in ('FFF', 'GGG', 'TTT');

-- 테이블 생성(각 사용자별로 로긴 후 테스트)
create table t1 (a number);
insert into t1 values (111);
insert into t1 values (222);
commit;

/*
-- 사용자 락 설정/해제
*/
-- 사용자 락 설정
alter user fff account lock;

-- 사용자 접속 테스트
oracle> sqlplus fff/fff

-- 사용자 락 해제
alter user fff account unlock;

-- 사용자 접속 테스트
oracle> sqlplus fff/fff

/*
-- 사용자 삭제
*/
-- 사용자 삭제(오류)
drop user fff;

-- CASCADE 사용
drop user fff cascade;
drop user ggg cascade;
drop user ttt cascade;

/**************************
-- 권한 관리
**************************/
/*
-- 권한 부여
*/
-- 사용자 생성
create user ttt identified by ttt quota 1m on users;
create user ggg identified by ggg quota 1m on users;

-- 연결 확인(오류)
oracle> sqlplus ttt/ttt

-- 시스템 권한(연결 권한) 부여
grant create session to ttt, ggg;
grant create any table to ttt;
grant create table to ggg;

-- 시스템 권한 확인
select * from dba_sys_privs
where grantee in ('TTT','GGG','FFF');

-- 연결 확인(각 사용자별로)
oracle> sqlplus ttt/ttt

-- 테이블 생성
SQL TTT> create talbe t1(a number);
SQL TTT> create table ttt.t2(a number);
SQL TTT> create table ggg.t3(a number);  -- ggg 스키마 생성
SQL GGG> create table ggg.t4(a number);
SQL GGG> create table ttt.t5(a number);  -- 생성 불가

-- 읽기 확인
SQL TTT> select * from t1;
SQL TTT> select * from ttt.t2;
SQL TTT> select * from ggg.t3; 
SQL GGG> select * from ggg.t4;

/*
-- WITH ADMIN/GRANT OPTION 설정
*/
-- 오브젝트 권한 부여
SQL ggg> grant select on ggg.t3 to ttt;

-- 읽기 확인
SQL ttt> select * from ggg.t3;

-- 뷰 생성
create or replace view ttt.vw_ttt
as select * from ggg.t3;

-- 사용자 추가
create user fff identified by fff
quota 1m on users;

-- 오브젝트 권한 설정
grant create session to fff;
grant select on ttt.t1 to fff;
grant select on ttt.vw_ttt to fff;

-- 오브젝트 권한 확인
select * from dba_tab_privs
where grantee in ('TTT','GGG','FFF');

-- 뷰 읽기
SQL fff> select * from ttt.t1;
SQL fff> select * from ttt.vw_ttt;

-- with grant option 추가
grant select on ggg.t3 to ttt with grant option;

-- 뷰 읽기
SQL fff> select * from ttt.vw_ttt;

-- 권한 해제
revoke select on ggg.t3 from ttt;

-- 읽기 확인
SQL ttt> select * from ggg.t3;
SQL fff> select * from ttt.vw_ttt;

-- 권한 초기화
revoke create session from ttt, fff, ggg;
revoke create table from ggg;
revoke create any table from ttt;
revoke select on ttt.t1 from fff;
revoke select on ttt.vw_ttt from fff;

/**************************
-- 롤 관리
**************************/
/*
-- 롤 관리 기본
*/
-- 롤 생성
create role rl_qq1;
create role rl_qq2
identified by qq2;
create role rl_qq3
identified by qq3;

-- 롤 확인
select * from dba_roles
where role in ('RL_QQ1', 'RL_QQ2', 'RL_QQ3');

-- 롤 수정
alter role rl_qq3
not identified;

-- 롤 확인
select * from dba_roles
where role in ('RL_QQ1', 'RL_QQ2', 'RL_QQ3');

-- 롤 지정
grant create session, create table to rl_qq1;
grant rl_qq1 to ttt, ggg;

-- 롤 지정 확인 뷰
select * from dba_sys_privs    /* 롤에 지정된 권한 확인 */
where grantee in ('RL_QQ1');

select * from dba_role_privs   /* 롤에 지정된 사용자 확인 */
where grantee in ('TTT', 'GGG');

-- 연결 확인(각 사용자)
oracle> sqlplus ttt/ttt;
oracle> sqlplus ggg/ggg;

-- 롤 삭제
drop role rl_qq1;
drop role rl_qq2;
drop role rl_qq3;

/*
-- 기본 설정 롤(Default Template)
-- RESOURCE롤 부여시 자동으로 UNLIMITED 권한 부여됨.
*/
-- CRUD 설정(기본 개체 소유자+개발자)
grant connect, resource to ttt;

-- DBA 롤 지정
grant dba to fff;

-- 사용자 생성
create user u1 identified by u1   /* WIPS Template */
default tablespace TS_DATA1
temporary tablespace TEMP
quota 0 on SYSTEM
quota 0 on SYSAUX;

create user u2 identified by u2   /* WIPS Template */
default tablespace TS_DATA1
temporary tablespace TEMP
quota 0 on SYSTEM
quota 0 on SYSAUX;

create user u3 identified by u3   /* WIPS Template */
default tablespace TS_DATA1
temporary tablespace TEMP
quota 0 on SYSTEM
quota 0 on SYSAUX;

-- 사용자 할당 테이블스페이스 뷰
select username, tablespace_name, bytes, max_bytes, dropped
from dba_ts_quotas where username in ('U1','U2','U3');

-- 권한/롤 설정
grant create session, create table to u1;
grant connect, resource to u2;
grant dba to u3;

-- 사용자 할당 테이블스페이스 뷰
select u.username, u.default_tablespace, q.max_bytes, q.max_blocks
from dba_users u, dba_ts_quotas q
where u.username = q.username
and u.default_tablespace = q.tablespace_name
and u.username in ('U1','U2','U3');

-- 권한 확인
select * from dba_role_privs
where grantee in ('U1','U2','U3');

select * from dba_sys_privs -- UNLIMITED TABLESPACE 자동 생성 확인
where grantee in ('U1','U2','U3')
order by grantee;

-- 사용자 삭제
drop user u1 cascade;
drop user u2 cascade;
drop user u3 cascade;