본문 바로가기
삵
Oracle Database

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

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

오늘은 지난주에 생성한 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 툴에서 실행한다. DBA 권한이 필요한 작업이므로 SYSTEM 계정 등 적절한 권한을 가진 계정으로 접속해 진행해야 한다.

요청사항

요청사항: 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 부분을 모두 수정해야 하는데, 좀 더 스마트하게 처리해 보자.

Tablespace 생성 스크립트 만들기

아래는 자작 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개가 생성될 것이다.

출력 결과에서 줄이 중간에 끊겨 보이는 것은 SQL*Plus의 표시 폭 설정 때문일 수 있다. 실제로 파일로 저장해 실행할 때는 필요한 경우 set linesize 200처럼 줄 폭을 넉넉히 잡아두면 확인하기 편하다.

계정 생성 스크립트까지 함께 만들기

시작한 김에 아래와 같이 좀 더 손을 보자. 이번에는 Tablespace 생성문, 사용자 생성문, 권한 부여문을 한 번에 만들어 파일로 저장한다.

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

위 스크립트에서 spool은 SQL*Plus 출력 내용을 파일로 저장하는 명령이다. 참고로 SQL 주석은 일반적으로 --를 사용한다. 위 예제의 //는 설명용 구분선처럼 넣은 것이므로, 실행 환경에서 문제가 생긴다면 -- Create Tablespace처럼 바꾸는 편이 안전하다.

이제 위 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

하지만 접속에 실패하였다. 이유는 패스워드 대소문자 구별 때문이다. Oracle 11g부터는 기본적으로 비밀번호의 대소문자를 구분하므로, 생성할 때 지정한 oracle1 그대로 입력해야 한다.

SQL> CONNECT CLASS1_USER1/oracle1
Connected.

소문자로 입력해서 잘 접속되는 것을 확인하였다. 다른 계정도 같은 방식으로 class1_user2/oracle2, class1_user3/oracle3처럼 접속 테스트를 해보면 된다.

참고로 GRANT CONNECT, RESOURCE는 실습 환경에서는 간단히 쓰기 좋지만, 운영 환경에서는 필요한 권한만 최소한으로 부여하는 것이 좋다.

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

관련 글

댓글 2

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

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