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

Tech Note 정보: 열린기술자 님이 작성하신 글입니다.; 카테고리: [ Oracle Database ]; 게시됨: 02 July 2016; 작성됨: 03 July 2016; 최종 변경: 02 July 2016; 조회수: 5067

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

Oracle 11g XE 설치 방법은 http://sarc.io/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.

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

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

ID 생성

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

sarc.logo

Docker 가상 환경 구축 입문 (by snowball)

MySQL & MariaDB (by kimdubi)

Tech Note

Tech Note 인기 Tags

게시판

메시지함

Sponsor