사용자 관리 - user 생성, user 정보 확인, profile 관리, privilege 관리

 

-- 실전 오라클 백업과 복구(생능출판사, 서진수 지음), p.180
-- 사용자 관리
-- 1. user 생성하기
-- 2. user 정보 확인하기
-- 3. profile 관리하기
-- 4. 권한(privilege) 관리하기

-- 1. user 생성하기
SQL> create tablespace ts_sms datafile '/u01/app/oracle/oradata/orcl/ts_sms01.dbf' size 10M;

SQL> create temporary tablespace temp_sms tempfile '/u01/app/oracle/oradata/orcl/temp_sms01.dbf' size 10M;

SQL> create user smsuser
identified by smspwd
default tablespace ts_sms
temporary tablespace temp_sms
quota unlimited on ts_sms
quota 0M on system;

SQL> grant resource, connect to smsuser;

SQL> conn smsuser/smspwd;

-- 2. user 정보 확인하기
SQL> set line 200
SQL> col default_tablespace for a10
SQL> col temporary_tablespace for a10
SQL> select username, default_tablespace "Default TS", temporary_tablespace "Temp TS" from dba_users where username='SMSUSER';

-- 3. profile 관리하기
SQL> create profile sample_prof limit
failed_login_attempts 3
password_lock_time 5
password_life_time 15
password_reuse_time 15;

SQL> create profile re_sample_prof limit
cpu_per_session 1000
connect_time 480
idle_time 10;

SQL> select username "사용자명", profile "적용 프로파일" from dba_users where username='SMSUSER';

SQL> set line 200
SQL> col profile for a13
SQL> col resource_name for a30
SQL> col resource for a10
SQL> col limit for a10
SQL> select * from dba_profiles
where profile='SMAPLE_PROF';

SQL> col profile for a15

SQL> select * from dba_profiles
where profile='RE_SAMPLE_PROF';

SQL> alter user smsuser profile sample_prof;

SQL> alter user smsuser profile re_sample_prof;

SQL> select username, profile
from dba_users
where username='SMSUSER';

-- 여러 개의 프로파일을 적용시킬 수 없다.

SQL> drop profile re_sample_prof;

SQL> drop profile re_sample_prof cascade;

SQL> select username, profile
from dba_users
where username='SMSUSER';

-- 4. 권한(privilege) 관리하기
SQL> grant create table, create session to scott;
revoke create table from scott;

SQL> select * from dba_sys_privs where grantee='SCOTT';

SQL> grant select on smsuser.smstest to scott;

SQL> grant update on smsuser.smstest to scott with grant option;

SQL> revoke select on smsuser.smstest from scott;

SQL> create role trole;

SQL> grant create session, create table to trole;

SQL> grant trole to scott;

SQL> select * from dba_role_privs where grantee='SCOTT';

SQL> select * from dba_sys_privs where grantee='CONNECT';

SQL> select * from dba_sys_privs where grantee='RESOURCE';