삵
Oracle Database

새로운 접속 계정을 생성하기 위한 여러 과정들

열열린기술자·2016년 7월 2일·조회 5,995

오늘은 지난 주에 생성한 DB에 계정을 생성하는 방법을 알아보자.

Oracle 11g XE 설치 방법은 /index.php/oracledatabase/506-linux-oracle-database-11g-express-edition-oracle-xe-11-2-0-1-0-x86-64 에 정리하였다. 참고바란다.

이어지는 작업은 SQL*Plus 툴 상에서 실행한다.

 

요청사항 : 1반의 9명의 학생에게 계정을 만들어주시오.

이제 만들 계정 이름은 class1_user{X}, 즉 class1_user1부터 class1_user9까지이다. 그런데 계정을 만드려면 계정마다 Default Tablespace를 지정해야 한다. 현재 어떠한 Tablespace가 있는지 확인해 보자.

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

아무래도 각 계정별로 Tablespace를 따로 만드는 것이 좋을 것 같다.

  • 만들 Tablespace의 이름은 ts_class1_user{X}로 정했다. 즉 ts_class1_user1부터 ts_class1_user9까지이다.
  • Tablespace는 /u01/app/oracle/oradata/CLASS1 아래에 저장시키려고 한다.
  • 그리고 이 Tablespace들은 automatic segment-space management 방식으로 만드려고 한다.

그렇다면 보통 이렇게 만들게 될 것이다.

CREATE TABLESPACE ts_class1_user1 DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user1.dbf' SIZE 10M 
  EXTENT MANAGEMENT LOCAL 
  SEGMENT SPACE MANAGEMENT AUTO;

그런데 만들 사용자가 총 9명이다. CREATE TABLESPACE문을 9줄 만들어서 user1부분을 다 수정해야 하는데.. 좀 더 스마트하게 처리해 보자.

아래는 자작 SQL이다. cr_tbs9.sql라고 이름 붙였다.

set serveroutput on
begin
  for seq in 1..9 loop
    dbms_output.put_line('CREATE TABLESPACE ts_class1_user'||to_char(seq)||chr(10)||'  DATAFILE ''/u01/app/oracle/oradata/CLASS1/ts_class1_user'||to_char(seq)||'.dbf'' SIZE 10M'||chr(10)||'  EXTENT MANAGEMENT LOCAL'||CHR(10)||'  SEGMENT SPACE MANAGEMENT AUTO;');
  end loop;
end;
/

돌려보자!

SQL> @cr_tbs9.sql
CREATE TABLESPACE ts_class1_user1
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user1.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE ts_class1_user2
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user2.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE ts_class1_user3
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user3.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE ts_class1_user4
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user4.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE ts_class1_user5
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user5.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE ts_class1_user6
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user6.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE ts_class1_user7
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user7.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE ts_class1_user8
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user8.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE ts_class1_user9
  DATAFILE '/u01/app/oracle/oradata/CLASS1/ts_class1_user9.dbf' SIZE 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE
MANAGEMENT AUTO;

PL/SQL procedure successfully completed.

아시겠지만 위 작업은 실제 Tablespace를 만들어주는 작업이 아니고 Tablespace를 만들기 위한 스크립트를 뽑아주는 것 뿐이다. 저기 나온 CREATE TABLESPACE들을 긁어서 실행하면 비로소 Tablespace 9개가 생성될 것이다.

시작한 김에 아래와 같이 좀 더 손을 보자.

set heading off
set echo off
set term off
set feedback off
set pagesize 150
set pagesize 500

spool cr_user9_result.sql

set serveroutput on
begin
  for seq in 1..9 loop
    dbms_output.put_line(chr(10)||'// Create Tablespace');
    dbms_output.put_line('CREATE TABLESPACE ts_class1_user'||to_char(seq)||chr(10)||'  DATAFILE ''/u01/app/oracle/oradata/CLASS1/ts_class1_user'||to_char(seq)||'.dbf'' SIZE 10M'||chr(10)||'  EXTENT MANAGEMENT LOCAL'||CHR(10)||'  SEGMENT SPACE MANAGEMENT AUTO;');
    dbms_output.put_line('// Create User');
    dbms_output.put_line('CREATE USER class1_user'||to_char(seq)||chr(10)||'  IDENTIFIED BY oracle'||to_char(seq)||chr(10)||'  DEFAULT TABLESPACE ts_class1_user'||to_char(seq)||chr(10)||'  TEMPORARY TABLESPACE temp;');
    dbms_output.put_line('// Grant Privilge');
    dbms_output.put_line('GRANT CONNECT, RESOURCE TO class1_user'||to_char(seq)||';');
  end loop;
end;
/

spool off

이제 위 SQL을 실행해 보자.

SQL> @cr_user9.sql
SQL>

아무 반응이 없는 것 같지만, 아래와 같은 cr_user9_result.sql라는 파일이 생성된다.

// Create Tablespace
CREATE TABLESPACE ts_class1_user1
  DATAFILE
'/u01/app/oracle/oradata/CLASS1/ts_class1_user1.dbf' SIZE 10M
  EXTENT
MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;
// Create User
CREATE USER class1_user1
  IDENTIFIED BY oracle1
  DEFAULT TABLESPACE
ts_class1_user1
  TEMPORARY TABLESPACE temp;
// Grant Privilge
GRANT CONNECT, RESOURCE TO class1_user1;

// Create Tablespace
CREATE TABLESPACE ts_class1_user2
  DATAFILE
'/u01/app/oracle/oradata/CLASS1/ts_class1_user2.dbf' SIZE 10M
  EXTENT
MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;
// Create User
CREATE USER class1_user2
  IDENTIFIED BY oracle2
  DEFAULT TABLESPACE
ts_class1_user2
  TEMPORARY TABLESPACE temp;
// Grant Privilge
GRANT CONNECT, RESOURCE TO class1_user2;

(중간생략)

// Create Tablespace
CREATE TABLESPACE ts_class1_user8
  DATAFILE
'/u01/app/oracle/oradata/CLASS1/ts_class1_user8.dbf' SIZE 10M
  EXTENT
MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;
// Create User
CREATE USER class1_user8
  IDENTIFIED BY oracle8
  DEFAULT TABLESPACE
ts_class1_user8
  TEMPORARY TABLESPACE temp;
// Grant Privilge
GRANT CONNECT, RESOURCE TO class1_user8;

// Create Tablespace
CREATE TABLESPACE ts_class1_user9
  DATAFILE
'/u01/app/oracle/oradata/CLASS1/ts_class1_user9.dbf' SIZE 10M
  EXTENT
MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;
// Create User
CREATE USER class1_user9
  IDENTIFIED BY oracle9
  DEFAULT TABLESPACE
ts_class1_user9
  TEMPORARY TABLESPACE temp;
// Grant Privilge
GRANT CONNECT, RESOURCE TO class1_user9;

그럼 지금 막 생성된 cr_user9_result.sql을 실행해 보자.

SQL> @cr_user9_result.sql
SQL>

모든 작업이 잘 완료되었을지 확인해 보자. 먼저 Tablespace 생성 여부다.

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

FILE_NAME						     TABLESPACE_NAME
------------------------------------------------------------ ---------------
/u01/app/oracle/oradata/XE/users.dbf			     USERS
/u01/app/oracle/oradata/XE/sysaux.dbf			     SYSAUX
/u01/app/oracle/oradata/XE/undotbs1.dbf 		     UNDOTBS1
/u01/app/oracle/oradata/XE/system.dbf			     SYSTEM
/u01/app/oracle/oradata/CLASS1/ts_class1_user1.dbf	     TS_CLASS1_USER1
/u01/app/oracle/oradata/CLASS1/ts_class1_user2.dbf	     TS_CLASS1_USER2
/u01/app/oracle/oradata/CLASS1/ts_class1_user3.dbf	     TS_CLASS1_USER3
/u01/app/oracle/oradata/CLASS1/ts_class1_user4.dbf	     TS_CLASS1_USER4
/u01/app/oracle/oradata/CLASS1/ts_class1_user5.dbf	     TS_CLASS1_USER5
/u01/app/oracle/oradata/CLASS1/ts_class1_user6.dbf	     TS_CLASS1_USER6
/u01/app/oracle/oradata/CLASS1/ts_class1_user7.dbf	     TS_CLASS1_USER7
/u01/app/oracle/oradata/CLASS1/ts_class1_user8.dbf	     TS_CLASS1_USER8
/u01/app/oracle/oradata/CLASS1/ts_class1_user9.dbf	     TS_CLASS1_USER9

9개의 새로운 Tablespace가 잘 생성되었다. 다음은 계정이다.

SQL> SELECT USERNAME, DEFAULT_TABLESPACE, TO_CHAR(CREATED, 'YYYY-MM-DD') CREATED FROM DBA_USERS;

USERNAME		       DEFAULT_TABLESPACE	      CREATED
------------------------------ ------------------------------ ----------
SYS			       SYSTEM			      2011-08-28
SYSTEM			       SYSTEM			      2011-08-28
ANONYMOUS		       SYSAUX			      2011-08-28
CLASS1_USER1		       TS_CLASS1_USER1		      2016-07-02
CLASS1_USER2		       TS_CLASS1_USER2		      2016-07-02
CLASS1_USER3		       TS_CLASS1_USER3		      2016-07-02
CLASS1_USER4		       TS_CLASS1_USER4		      2016-07-02
CLASS1_USER5		       TS_CLASS1_USER5		      2016-07-02
CLASS1_USER6		       TS_CLASS1_USER6		      2016-07-02
CLASS1_USER7		       TS_CLASS1_USER7		      2016-07-02
CLASS1_USER8		       TS_CLASS1_USER8		      2016-07-02
CLASS1_USER9		       TS_CLASS1_USER9		      2016-07-02
APEX_PUBLIC_USER	       SYSTEM			      2011-08-28
APEX_040000		       SYSAUX			      2011-08-28
OUTLN			       SYSTEM			      2011-08-28
XS$NULL 		       SYSTEM			      2011-08-28
FLOWS_FILES		       SYSAUX			      2011-08-28
MDSYS			       SYSAUX			      2011-08-28
CTXSYS			       SYSAUX			      2011-08-28
XDB			       SYSAUX			      2011-08-28
HR			       USERS			      2011-08-28

2016년 7월 2일에 생성된 9개의 계정이 잘 확인되었다.

이제 마지막으로 계정 접속 테스트를 해보자.

SQL> CONNECT CLASS1_USER1/ORACLE1
ERROR:
ORA-01017: invalid username/password; logon denied

하지만 접속에 실패하였다. 이유는 패스워드 대소문자 구별 때문이다. 

SQL> CONNECT CLASS1_USER1/oracle1
Connected.

소문자로 해서 잘 되는 것을 확인하였다.

 

위와 같이 계정 생성 방법을 알아보았다.

댓글 2

로그인 후 댓글을 남길 수 있습니다.

  • 앤앤· 2016년 7월 4일
    요런 스크립트 만들때 pl/sql을 별로 안써봤는데 보니까 괜찮네요. 저도 활용해 보겠습니다.^^
  • durecatdurecat· 2016년 7월 6일
    완전 유용한 스크립트네요. 감사합니다.