User 생성과 관리

=====================================================================================

실습 1.1 User 조회 (필)

=====================================================================================

  <?XML:NAMESPACE PREFIX = O />

SQL> desc dba_users;

Name Null? Type

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

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

PASSWORD VARCHAR2(30)

ACCOUNT_STATUS NOT NULL VARCHAR2(32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2(30)

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)

EXTERNAL_NAME VARCHAR2(4000)

 

SQL> select username, default_tablespace, temporary_tablespace, account_status, profile

2 from dba_users

3 order by 1;

 

USERNAME DEFAULT_TA TEMPORARY_ ACCOUNT_STATUS PROFILE

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

DBSNMP SYSAUX TEMP EXPIRED & LOCKED DEFAULT

DIP SYSTEM TEMP EXPIRED & LOCKED DEFAULT

OUTLN SYSTEM TEMP OPEN DEFAULT

SYS SYSTEM TEMP OPEN DEFAULT

SYSTEM SYSTEM TEMP OPEN DEFAULT

TSMSYS SYSTEM TEMP EXPIRED & LOCKED DEFAULT

 

SQL> save user.sql

Created file user.sql

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

 

=====================================================================================

실습 1.2 User 생성 (필)

=====================================================================================

 

SQL> @dd

 

TABLESPACE_NAME BYTES FILE_NAME

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

SYSAUX 104857600 /app/ora10g/oradata/disk3/sysaux01.dbf

SYSTEM 314572800 /app/ora10g/oradata/disk3/system01.dbf

UNDO 10485760 /app/ora10g/oradata/disk3/undo01.dbf

 

SQL> create tablespace te ← User를 생성하기 전에 반드시 사용할 tablespace를 먼저 생성한다.

2 datafile

3 '$ORACLE_BASE/oradata/disk1/te01.dbf' size 3M,

4 '$ORACLE_BASE/oradata/disk2/te02.dbf' size 3M;

Tablespace created.

 

SQL> create tablespace st

2 datafile

3 '$ORACLE_BASE/oradata/disk2/st01.dbf' size 3M,

4 '$ORACLE_BASE/oradata/disk1/st02.dbf' size 3M;

Tablespace created.

 

SQL> create tablespace indx

2 datafile

3 '$ORACLE_BASE/oradata/disk1/indx01.dbf' size 2M,

4 '$ORACLE_BASE/oradata/disk2/indx02.dbf' size 2M;

Tablespace created.

 

SQL> @dd

 

TABLESPACE_NAME BYTES FILE_NAME

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

INDX 2097152 /app/ora10g/oradata/disk1/indx01.dbf

INDX 2097152 /app/ora10g/oradata/disk2/indx02.dbf

ST 3145728 /app/ora10g/oradata/disk1/st02.dbf

ST 3145728 /app/ora10g/oradata/disk2/st01.dbf

SYSAUX 104857600 /app/ora10g/oradata/disk3/sysaux01.dbf

SYSTEM 314572800 /app/ora10g/oradata/disk3/system01.dbf

TE 3145728 /app/ora10g/oradata/disk1/te01.dbf

TE 3145728 /app/ora10g/oradata/disk2/te02.dbf

UNDO 10485760 /app/ora10g/oradata/disk3/undo01.dbf

 

SQL> @dt

 

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN

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

INDX ONLINE PERMANENT LOCAL AUTO

ST ONLINE PERMANENT LOCAL AUTO

SYSAUX ONLINE PERMANENT LOCAL AUTO

SYSTEM ONLINE PERMANENT LOCAL MANUAL

TE ONLINE PERMANENT LOCAL AUTO

TEMP ONLINE TEMPORARY LOCAL MANUAL

UNDO ONLINE UNDO LOCAL MANUAL

 

SQL> create user te

2 identified by te

3 default tablespace te

4 temporary tablespace temp

5 quota unlimited on te

6 quota 2M on st

7 quota 2M on indx;

User created.

 

SQL> create user st0

2 identified by st0

3 default tablespace st

4 temporary tablespace temp;

User created.

 

SQL> create user st1

2 identified by st1

3 default tablespace st

4 temporary tablespace temp

5 quota unlimited on st

6 quota 2M on indx;

User created.

 

SQL> create user st2

2 identified by st2

3 default tablespace st

4 temporary tablespace temp

5 quota unlimited on st

6 quota 2M on indx

7 account lock;

User created.

 

SQL> grant connect, resource to te, st1, st2;

Grant succeeded.

 

SQL> @user

 

USERNAME DEFAULT_TA TEMPORARY_ ACCOUNT_STATUS PROFILE

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

DBSNMP SYSAUX TEMP EXPIRED & LOCKED DEFAULT

DIP SYSTEM TEMP EXPIRED & LOCKED DEFAULT

OUTLN SYSTEM TEMP OPEN DEFAULT

ST0 ST TEMP OPEN DEFAULT

ST1 ST TEMP OPEN DEFAULT

ST2 ST TEMP LOCKED DEFAULT

SYS SYSTEM TEMP OPEN DEFAULT

SYSTEM SYSTEM TEMP OPEN DEFAULT

TE TE TEMP OPEN DEFAULT

TSMSYS SYSTEM TEMP EXPIRED & LOCKED DEFAULT

 

SQL> select username, tablespace_name, max_bytes, max_blocks from dba_ts_quotas

2 where lower(username) in ('te', 'st0', 'st1', 'st2')

3 order by 1;

 

USERNAME TABLESPACE_NAME MAX_BYTES MAX_BLOCKS

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

ST1 ST -1 -1

ST1 INDX 2097152 256

ST2 ST -1 -1

ST2 INDX 2097152 256

TE TE -1 -1

TE ST 2097152 256

TE INDX 2097152 256

 

SQL> select grantee, granted_role from dba_role_privs

2 where lower(grantee) in ('te', 'st0', 'st1', 'st2')

3 order by 1;

 

GRANTEE GRANTED_ROLE

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

ST1 CONNECT

ST1 RESOURCE

ST2 CONNECT

ST2 RESOURCE

TE CONNECT

TE RESOURCE

 

SQL> select grantee, privilege from dba_sys_privs

2 where lower(grantee) in ('te', 'st0', 'st1', 'st2')

3 order by 1;

 

GRANTEE PRIVILEGE

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

ST1 UNLIMITED TABLESPACE

ST2 UNLIMITED TABLESPACE

TE UNLIMITED TABLESPACE

 

 

SQL> select grantee, owner, table_name, grantor, privilege

2 from dba_tab_privs

3 where lower(grantee) in ('te', 'st0', 'st1', 'st2')

4 order by 1;

no rows selected

 

SQL> conn st1/st1

Connected.

 

SQL> conn st2/st2

ERROR:

ORA-28000: the account is locked

 

Warning: You are no longer connected to ORACLE.

 

SQL> conn st0/st0

ERROR:

ORA-01045: user ST0 lacks CREATE SESSION privilege; logon denied

 

Warning: You are no longer connected to ORACLE.

 

SQL> conn / as sysdba

Connected.

 

SQL> grant create session to st0;

Grant succeeded.

 

SQL> alter user st2

2 account unlock;

User altered.

 

SQL> conn st0/st0

Connected.

 

SQL> conn st2/st2

Connected.

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

 

 

=====================================================================================

실습 1.3 Quota와 User

=====================================================================================

 

 

SQL> select username, tablespace_name, max_bytes, max_blocks from dba_ts_quotas

2 where lower(username) in ('te', 'st0', 'st1', 'st2')

3 order by 1;

 

USERNAME TABLESPACE_NAME MAX_BYTES MAX_BLOCKS

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

ST1 ST -1 -1

ST1 INDX 2097152 256

ST2 ST -1 -1

ST2 INDX 2097152 256

TE TE -1 -1

TE ST 2097152 256

TE INDX 2097152 256

 

SQL> select u.username, u.default_tablespace, max_bytes, max_blocks

2 from dba_users u, dba_ts_quotas q

3 where u.username = q.username

4 and u.default_tablespace = q.tablespace_name;

 

USERNAME TABLESPACE_NAME MAX_BYTES MAX_BLOCKS

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

ST1 ST -1 -1

ST2 ST -1 -1

TE TE -1 -1

 

SQL> conn te/te

Connected.

 

SQL> create table te (no number);

Table created.

 

SQL> select table_name,tablespace_name from user_tables;

 

TABLE_NAME TABLESPACE_NAME

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

TE TE

 

SQL> conn st0/st0

Connected.

 

SQL> create table st0 (no number);

create table st0 (no number)

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL> conn /as sysdba

Connected.

 

SQL> select grantee, privilege from dba_sys_privs

2 where grantee = 'ST0';

 

GRANTEE PRIVILEGE

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

ST0 CREATE SESSION

 

SQL> grant create table to st0;

Grant succeeded.

 

SQL> select grantee, privilege from dba_sys_privs

2 where grantee = 'ST0';

 

GRANTEE PRIVILEGE

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

ST0 CREATE TABLE

ST0 CREATE SESSION

 

SQL> conn st0/st0

Connected.

 

SQL> create table st (no number);

create table st (no number)

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'ST'

 

SQL> conn /as sysdba

Connected.

 

SQL> select username, default_tablespace from dba_users

2 where username='ST0';

 

USERNAME DEFAULT_TA

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

ST0 ST

 

SQL> alter user st0

2 quota unlimited on st;

User altered.

 

SQL> select username, tablespace_name, max_bytes, max_blocks from dba_ts_quotas

2 where username = 'ST0';

 

USERNAME TABLESPACE_NAME MAX_BYTES MAX_BLOCKS

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

ST0 ST -1 -1

 

SQL> conn st0/st0

Connected.

 

SQL> create table st (no number);

Table created.

 

SQL> select table_name, tablespace_name from user_tables;

 

TABLE_NAME TABLESPACE_NAME

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

ST0 ST

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

 

 

=====================================================================================

실습 1.4 User 삭제 (필)

=====================================================================================

 

SQL> select owner, table_name from dba_tables

2 where owner = 'ST0';

 

OWNER TABLE_NAME

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

ST0 ST

 

SQL> drop user st0;

drop user st

*

ERROR at line 1:

ORA-01922: CASCADE must be specified to drop 'ST0'

 

SQL> drop table st0.st;

Table dropped.

 

SQL> drop user st0;

User dropped.

 

SQL> drop user te cascade;

User dropped.

 

SQL> @user