[oracle] 오라클 설치 후 사용자 계정 만들기/권한 부여

오라클이 설치된 이후 system 계정으로 접속하여 테이블스페이스, 사용자를 만들고 권한을 부여 한다.

필요할 경우 테이블스페이스생성

CREATE TABLESPACE [테이블스페이스명]
DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\TEST.DBF' -- 경로
SIZE 2048M -- 최초 사이즈
AUTOEXTEND ON -- 사이즈를 다 사용했을경우 자동 확장
NEXT 4M MAXSIZE UNLIMITED -- 4M 씩 확장하고 사이즈 제한 없음
LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
BLOCKSIZE 8K 
SEGMENT SPACE MANAGEMENT MANUAL 
FLASHBACK ON; 

 

사용자등록 및 비밀번호, 권한부여

-- 현재 등록된 사용자 조회
select * from all_users;

-- 사용자 추가
-- 테이블스페이스는 따로 만든 명을 입력하거나, 만들지 않는다면, USERS를 입력
-- 임시테이블스페이스는 TEMP
create user [사용자명] identified by [비밀번호]
default tablespace [테이블스페이스명] -- 테이블스페이스 지정 ex.USERS
temporary tablespace [임시테이블스페이스명]; -- 임시 테이블스페이스 지정 ex.TEMP

-- 필요시 비밀번호 변경
alter user [사용자명] identified by [비밀번호];

-- 필요시 테이블 스페이스 변경
-- tablespace는 USERS, 임시테이블스페이스는 TEMP
alter user [사용자명] default tablespace USERS TEMPORARY tablespace TEMP;

-- 상세한 오라클 권한 부여
grant 
  create user, 
  select any table, 
  create any table, 
  create session, 
  create table, 
  create view, 
  create procedure, 
  create sequence,
  resource
  sysdba, 
  sysoper 
  to [사용자명];

-- 심플하게 권한을 부여 할 수도 있다.
-- RESOURCE : 개체를 생성, 변경, 제거 할 수 있는 권한 ( DDL, DML사용이가능함)
-- CONNECT : 데이터베이스에 연결할 수 있는 권한
-- DBA : 데이터베이스 관리자 권한
grant RESOURCE, CONNECT to [사용자명];

-- 지금까지 작업한 내용 커밋 (적용)
commit;  

-- 이렇게 생성 후 db접속 툴로 접속 시 connect as를 sysdba로 하지 않으면 접속되지 않는다.
-- connect as normal로 접속 해야하므로 sysdba, sysoper 권한을 회수
revoke sysdba, sysoper from [사용자명];

-- 사용자의 권한 조회
select * from dba_sys_privs where grantee=['사용자명']; -- 유저명은 대문자로

 

생성한 계정으로 접속 후 테이블 생성 (오류 발생할 경우 - ORA-01950)

-- 테이블 생성
create table test_table (
  id varchar2(100 char) not null,
  name varchar2(100 char),
  email varchar2(100 char)
);

-- ORA-01950: no privileges on tablespace 'SYSTEM'
-- 테이블 스페이스 사용에 대한 권한이 없어서 발생하는 오류
-- 권한 부여 grant 시 resource가 빠져 있을 경우 발생
grant resource to [사용자명]

기존에 사용하던 데이터베이스를 export 한 경우 import

-- 기존에 사용하던 데이터베이스 export한 sql
-- 열어보면 아래와 비슷하게 생긴 sql 파일 (ex. 파일명 all_ddl.sql)
set define off
spool all_ddl.log

prompt
prompt Creating table AXISSOAPDEFINITION
prompt =================================
prompt
create table AXISSOAPDEFINITION


-- 위 sql 파일을 import 할 경우 sqlplus로 접속한다.
-- sysdba로 접속 후 사용할 사용자로 변경해주고, import할 sql파일을 @@로 실행한다.

sqlplus / as sysdba

SQL> conn 아이디/비밀번호
Connected.
SQL> @@C:\Users\kisspa\Downloads\db_script\all_ddl.sql
...
...
...
...