Oracle SQL 기본_13

새로운 사용자 계정(USER) 만들기, 권한 부여하기(GRANT), 새 계정에 접속 만드는 방법 : cmd창 sqlplus 비밀번호 입력이 안 되는 문제 해결

---

오라클 SQL의 기능을 배울 때 꿈에 나오도록 scott, hr 연습계정을 사용하게 됩니다. 하지만 언젠가는 연습계정을 벗어나 hr이나 scott 같은 새로운 계정을 만들어 사용해야 할 것입니다.

​

본 포스팅에서는 오라클 SQL 디벨로퍼에 hr이나 scott 계정처럼 사용할 수 있는 새로운 계정, 'andrea'를 만들 것입니다. 계정 생성 과정 흐름은 다음과 같이 진행됩니다.

​

1. 계정 생성(cmd)

2. 권한 부여(cmd)

3. 권한 확인(cmd)

4. 접속 생성(Oracle SQL Developer)

 

 

 

1. 계정 생성(cmd)

​

먼저, 계정을 생성하기 위해서 '명령 프롬프트(cmd)' 창을 열어 줍니다.

​

1) 이슈 : 비밀번호 입력이 안 되는 문제 해결

​

(1) cmd 창 'sqlplus' 모드에서 '비밀번호 입력(Enter password)' 창에 숫자나 문자가 입력이 안 되는 경우

 

어떤 경우에는, 가이드를 따라서 cmd창에 들어가 'sqlplus' 입력+Enter 후 사용자명(username)을 입력했는데, 비밀번호 입력이 안 됩니다. 원인을 알 수 없고 엔터 외에는 듣지 않아서 비밀번호 자체를 입력할 수 없고, 붙여넣기도 안 됩니다. 이 경우에는 다음 '(2)'와 같이 관리자 권한으로 접속해야 합니다.

 

 

(2) 명령 프롬프트에서 관리자 권한으로 SQL 접속하기

 

sqlplus / as sysdba

 

명령 프롬프트에서 위 문장을 입력한 후 엔터(Enter)를 눌러 실행하면, 관리자 권한으로 접속됩니다.

 

(3) 새로운 사용자 계정 생성 :

CREATE USER

​

이제 사용자 계정을 만들 수 있습니다. 원하는 사용자이름(username), 비밀번호(password)를 지정하여 다음 구문을 완성해 줍니다. 본 포스팅에서는 andrea 라는 사용자 계정을 만들었습니다.

 

create user 사용자이름 identified by 비밀번호;

 

 

'사용자가 생성되었습니다' 라는 메시지가 나타나면 andrea 계정이 생성된 것입니다.

​

​

(4) 생성된 사용자 계정 조회하기

​

'(3)'에서 만든 사용자 계정(andrea)이 무사히 만들어졌는지 확인하고 싶다면, 명령 프롬프트 SQLPLUS 관리자모드에 접속한 상태로, 다음의 코드를 입력한 후 실행합니다.

 

select * from ALL_users;

명령 프롬프트의 상단 USERNAME에 'ANREA'가 추가되어 있습니다. 중간쯤에 연습계정인 hr과 scott도 보입니다.

 

 

2. 권한 부여(cmd)

​

(1) 권한 부여의 필요성

​

여기까지의 단계에서는 아직 오라클SQL 디벨로퍼에서 andrea 계정에 접속을 생성하지 못합니다.

서버에 접속할 수 있는 세션 권한, 테이블을 만들 수 있는 권한이 아직 부여되지 않았기 때문입니다.

 

만약 '접속 만들기' 창에서 이 상태로 andrea 계정의 접속을 생성하려고 하면, 위처럼 오류가 발생하며 테스트에 실패합니다.

​

오류(ORA-01045) :

user ANDREA lacks CREATE SESSION privilege : login denied.

​

따라서, 명령 프롬프트 창에서 GRANT 명령어를 사용하여 '권한 부여'를 해 주어야 합니다.

 

 

(2) 명령 프롬프트 창에 관리자 권한으로 접속,

GRANT 명령어로 새 계정에 권한 부여하기

 

sqlplus / as sysdba

(* 만약 명령 프롬프트를 종료했다면, 다시 실행+위 문장을 실행하고 SQLPLUS 관리자 권한 상태로 만들어 줍니다)

grant create table, create session to andrea;

위 문장은 andrea 계정에 테이블을 생성할 수 있는 권한, 서버에 접속할 수 있는 세션 권한을 준다는 의미입니다.

​

위 문장을 cmd 창에 입력 후 실행(Ctrl+Enter)하면, 다음과 같이 '권한이 부여되었습니다' 라는 메시지가 나타납니다.

 

※ 기존의 상태로 위 GRANT 권한부여 문장을 cmd 창이 아닌 오라클SQL 디벨로퍼에서 실행할 경우, 다음과 같은 오류가 발생함에 유의합니다.

​

오류 보고 -

ORA-01031: 권한이 불충분합니다

01031. 00000 - "insufficient privileges"

*Cause: An attempt was made to perform a database operation without

the necessary privileges.

*Action: Ask your database administrator or designated security

administrator to grant you the necessary privileges

 

 

3. 사용자 계정에 부여된 권한 확인하기(cmd)

​

andrea 계정에 부여된 권한을 확인하고 싶다면, 다음 문장을 명령 프롬프트 창(여전히 sqlplus / as sysdba 상태)에 입력합니다.

 

select * from dba_sys_privs
where grantee='ANDREA';

 

* grantee(권한을 부여받은 계정) 이름은 반드시 대문자로 써 줍니다.

​

WHERE 조건절로 grantee(권한을 부여받은 계정)을 지정해 주는 이유는, 필터링 없이 모든 사용자의 모든 권한을 조회할 경우 수백 개가 넘는 권한들이 조회되기 때문입니다. (저의 경우에는 초기 상태에서 조회된 권한이 800개가 넘었습니다.)

 

GRANT 명령어로 새 계정에 부여한 CREATE SESSION, CREATE TABLE 권한이 조회되었습니다.

 

 

4. 접속 생성(Oracle SQL Developer)

​

cmd창에서 새 계정 생성과 권한 부여까지 마쳤다면, 이제 새로 만들어진 계정(andrea)에 대해 접속 생성, 테이블 생성, 각종 SQL 쿼리문 작성이 가능해집니다.

​

접속 탭에서 '새 접속 만들기' 창에 접속 후,

​

- name 입력 (andrea@localhost)

- 사용자 이름과 비밀번호에 계정 정보를 입력(* cmd 창에서 CREATE USER로 설정한 사용자이름과 비밀번호)

- SID 입력 (오라클 설치 시 입력했던 SID 입력, 저의 경우 ORCL 이었습니다)

 

'테스트' 버튼을 누르면, 처음 시도했을 때와 달리 '성공'메시지가 나타납니다.

이제 '저장' 버튼을 누르면 andrea 계정에의 접속이 새로 생성됩니다.

 

오라클 SQL 디벨로퍼 좌측 접속 탭에 'andrea@localhost' 계정이 새로 추가되었습니다!

* GRANT / REVOKE 명령어로 권한을 부여하거나 회수하는 기능과 관련해서는 별도의 포스팅으로 다룹니다.

 

 

 

GRANT (1) : 사용자 계정에 시스템 관련 각종 권한 부여하기(세션, 테이블 생성, 각종 수정 및 삭제 권한)

---

1. GRANT(권한 부여)의 이해와 표현

​

오라클에서는 계정을 생성한 후에도 바로 접속 생성과 작업이 가능한 것이 아니라 서버 접속, 테이블 생성, 다른 사용자 계정에의 간섭 가능 정도와 같이 "권한"을 지정해 주어야만 합니다.

 

(1) 사용자K에 권한A, 권한B, ... 부여하기

​

cmd창에서 sqlplus / as sysdba 접속 후, 아래와 같은 구조의 식을 입력하여 실행합니다.

grant 권한이름A, 권한이름B, ... to '사용자이름K';

 

(2) 사용자K에게 부여된 권한 목록 조회

​

cmd 창에서 sqlplus / as sysdba 접속한 상태로, 다음 식을 사용하여 조회합니다.

select * from dba_sys_privs
where grantee='사용자이름K';

* 이 때, 사용자이름K는 반드시 대문자로 입력해야 합니다.

 

 

 

2. 시스템 관련 주요 권한 목록

​

* ANY : '소유자에 상관 없이'

세션(SESSION), 서버 접속 관련
CREATE SESSION	서버에 접속할 수 있도록 세션을 생성할 수 있는 권한
ALTER SESSION	서버에 접속한 상태에서 세션 옵션 변경 가능 권한
RESTRICTED SESSION	제한된 상태로 공개된 데이터베이스에 접속 가능

테이블(TABLE) 관련
CREATE TABLE	자신의 계정에 테이블 생성 가능
CREATE ANY TABLE	다른 사용자 계정에도 테이블 생성(CREATE TABLE) 가능	테이블 소유자, 사용자 계정에 에 상관 없는 모든 테이블에 적용
ALTER ANY TABLE	모든 테이블에 ALTER 가능
DROP ANY TABLE	모든 테이블 삭제(DROP) 가능
INSERT ANY TABLE	모든 테이블에 데이터 입력(INSERT) 가능
UPDATE ANY TABLE	모든 테이블의 데이터 갱신(UPDATE) 가능
DELETE ANY TABLE	모든 테이블의 데이터 삭제(DELETE) 가능

인덱스(INDEX) 관련
CREATE ANY INDEX	모든 인덱스 생성(CREATE) 가능	테이블 소유자, 사용자 계정에 에 상관 없는 모든 테이블에 적용
ALTER ANY INDEX	모든 인덱스 수정(ALTER) 가능
DROP ANY INDEX	모든 인덱스 삭제(DROP) 가능

테이블스페이스(TABLESPACE) 관련
CREATE TABLESPACE	테이블스페이스 생성 가능
ALTER TABLESPACE	테이블스페이스 변경 가능
DROP TABLESPACE	테이블스페이스 삭제 가능
UNLIMITED TABLESPACE	테이블스페이스 무제한 허용

 

 

3. 예제 : 사용자 계정에 대한 권한 부여 방법

​

1) cmd 프롬프트 창에서 관리자 권한으로 SQLPLUS 접속

 

sqlplus / as sysdba

 

2) 예제 : GRANT

andrea 계정에 세션접속 / 테이블 생성 권한 부여하기

​

cmd 창에 sqlplus / as sysdba 가 실행된 상태로 아래의 문장을 실행합니다.

 

grant create table, create session to andrea;

위 문장을 실행하고, 명령 프롬프트 창에 '권한이 부여되었습니다' 라는 메시지가 나타나면 andrea에 권한이 부여된 것입니다.

 

 

3) 예제 : andrea 계정에 부여된 권한 목록 조회

​

명령프롬프트 창에서 sqlplus / as sysdba 가 실행된 상태로 다음의 문장을 입력하여 andrea 계정에 부여된 권한을 조회할 수 있습니다.

select * from dba_sys_privs
where grantee='ANDREA';

* 사용자 이름은 반드시 대문자로 입력해야 합니다.

 

andrea 계정에 부여된 CREATE SESSION, CREATE TABLE 권한이 cmd 창에 조회되고 있습니다.

 

 

 

GRANT (2) : 다른 계정의 특정 테이블에 SELECT, INSERT, UPDATE, DELETE,... 할 수 있는 테이블 편집권한 부여하기

---

오라클 SQL 디벨로퍼에서는 여러개의 계정과 접속을 가질 수 있고, 각자의 접속은 테이블, 인덱스, 뷰 등의 오브젝트를 소유할 수 있습니다.

​

그런데 사용자A가 사용자B가 보유하고 있는 어떤 테이블에 대하여 SELECT, INSET, UPDATE, ...를 해야 하는 경우가 발생할 수 있습니다. 이 때에는 GRANT를 사용하여 특정 테이블, 특정 명령어에 한정한 권한만을 부여해줄 수도 있습니다.

 

 

1) 다른 계정의 특정 테이블에 대한 특정 명령어를 쓸 수 있는 권한 부여하기

 

grant 명령어K on 사용자이름A.테이블이름X to 사용자이름B;

- 사용자A가 가진 테이블X에 대해

- 명령어K를 쓸 수 있는 권한을 (SELECT, UPDATE, DELETE, INSERT, ...)

- 사용자 B에게 부여합니다.

​

* 만약 사용자B가 자신이 부여받은 권한을 다른 사용자C, D,...에게 줄 수 있게끔 하고 싶다면 :

​

with grant option을 마지막에 추가하여 줍니다.

 

grant 명령어K on 사용자이름A.테이블이름X to 사용자이름B with grant option;

 

 

2) 사용자B에게 부여된 테이블 권한 조회하기

 

select * from dba_tab_privs
where grantee='사용자이름B';

* 이하의 예제에서는 임의로 만든 사용자 계정인 ANDREA 을 사용합니다.

 

 

 

2) 예제 : ANDREA 계정에 SCOTT 계정의 DEPT테이블을 SELECT 할 수 있는 권한 부여하기

 

1) 명령 프롬프트(cmd)에서 관리자 권한으로 SQLPLUS에 접속하기

​

다음을 입력 후 실행(Enter)합니다.

 

sqlplus / as sysdba

 

(2) ANDREA에 권한 부여하기

​

명령 프롬프트에 아래의 명령어를 입력합니다.

grant select on SCOTT.DEPT to ANDREA;

grant select on SCOTT.DEPT to ANDREA with grant option;

 

(3) ANDREA에 부여된 테이블 권한 조회하기

​

ANDREA 계정에 다른 계정의 테이블에 대하여 부여된 권한을 조회합니다.

 

select * from dba_tab_privs
where grantee='ANDREA';

ANDREA 계정에 scott 연습계정의 dept 테이블에 대해 SELECT할 수 있는 권한이 부여되었습니다.

 

 

(4) 테스트 : ANDREA 계정에서 scott 연습계정의 dept 테이블 조회하기

​

오라클SQL 디벨로퍼로 돌아가서, andrea 계정의 접속에서 다음의 SQL 쿼리를 입력 후 실행합니다.

select * from scott.dept;

​

 

andrea@localhost 에서 scott 연습계정이 가진 dept 테이블의 내용이 SELECT 되었습니다.

​

물론, SELECT 외의 권한을 부여하지 않았으므로, SELECT만 가능할 뿐입니다.

INSERT나 UPDATE도 허가하려면 마찬가지의 방법으로 새로이 권한을 부여해줘야 합니다.

 

 

REVOKE : 사용자 권한 해제 (권한 회수, 권한 취소)

---

GRANT 를 사용하여 오라클 SQL의 각 계정에 세션 접속 / 테이블 생성 / 다른 테이블 접근 및 편집권한 등을 부여할 수 있었습니다. 반대로, REVOKE 명령어는 각 계정에 부여되어 있는 권한을 해제합니다.

 

1. REVOKE : 권한 해제 방법

​

명령 프롬프트(cmd) 창에서 sqlplus / as sysdba로 접속하여, 다음과 같이 입력하여 줍니다.

​

(1) 기본식 : 사용자A게서 권한K 해제하기

revoke 권한이름K from 사용자이름A;

 

(2) 기본식 : 사용자B에게서 사용자A의 테이블X에 대해 명령어K가 가능했던 권한 해제하기

revoke 명령어K on 사용자이름A.테이블이름X from 사용자이름B;

* 이하의 예제는 CREATE SESSION, CREATE TABLE, SELECT ON SCOTT.DEPT 권한을 가진 임의의 계정 ANDREA를 사용하고 있습니다.

 

2. 예제 : ANDREA 계정으로부터 권한 해제하기

먼저, 명령 프롬프트 창(cmd)에 다음을 입력하여 SQL 프롬프트로 만들어 줍니다.

sqlplus / as sysdba

 

 

1) ANDREA 계정에서 CREATE TABLE 권한 해제하기

​

(1) 권한 해제

revoke create table from ANDREA;

위 문장을 입력하고 실행한 뒤, '권한이 취소되었습니다' 라는 메시지가 나타나면 권한이 해제된 것입니다.

 

 

(2) ANDREA 계정에서 CREATE TABLE 권한이 해제된 것을 확인하기 :

데이터 딕셔너리(레퍼런스) 이용

​

dba_sys_privs는 현재 DBA가 가진 시스템 권한목록을 보여줍니다. ANDREA의 시스템 권한을 조회하는 다음의 소스코드를 입력 후 실행하면, ANDREA의 권한 중에서 CREATE TABLE이 지워지고 CREATE SESSION만 남아있습니다.선택된 레코드가 없습니다. 즉, 현재 ANDREA가 가지고 있는 테이블 관련 권한이 없는 것입니다.

 

select * from dba_sys_privs
where grantee='ANDREA';

이제 ANDREA로는 세션 접속은 가능하지만, 테이블 생성은 (다시 권한을 할당하기 전까지는) 허용되지 않습니다.

 

2) ANDREA 계정에서 SELECT ON SCOTT.DEPT 권한 해제하기

​

ANDREA 계정은 현재 SCOTT 계정의 DEPT 테이블을 SELECT할 수 있는 권한을 가지고 있습니다. 이것을 이제 해제할 것입니다.

​

(1) 권한 해제

​

명령 프롬프트에 sqlplus / as sysdba 가 실행된 상태에서, 다음을 입력하고 실행합니다.

revoke select on SCOTT.DEPT from ANDREA;

'권한이 취소되었습니다' 메시지가 나타나면 해당 권한이 해제된 것입니다.

 

(2) 테이블 SELECT 권한 해제 상태 확인하기

​

dba_tab_privis 데이터 딕셔너리는 DBA에 존재하는 테이블 관련 권한을 보여 줍니다. ANDREA에게 부여된 테이블 관련 권한을 조회하려면 다음을 실행해야 합니다.

 

select * from dba_tab_privs
where grantee='ANDREA';

선택된 레코드가 없습니다. 즉, 현재 ANDREA가 가지고 있는 테이블 관련 권한이 없는 것입니다.

 

 

ROLE(롤) : 권한 그룹 관리 - 롤 생성, 롤 부여, 롤 회수, 롤 삭제 방법 (feat. GRANT)

---

1. ROLE의 이해와 사용 방법

​

1) ROLE의 개념과 그 필요성

​

오라클 SQL 에서의 ROLE(롤, 역할)이란, 여러개의 권한을 그룹으로 묶은 권한의 묶음​입니다. 여러 개의 권한 그룹을 만들 수 있습니다. 어떤 권한을 행사할 수 있느냐가 모여서 역할이 됩니다.

​

ROLE을 사용자에게 부여하면, ROLE에 속한 권한들을 한꺼번에 사용자 계정에 부여하거나 회수할 수 있습니다. 따라서, 자주 사용하거나 필수적인 권한들은 ROLE로 설정하면 작업 시간을 단축​할 수 있습니다.

 

 

2) ROLE(롤)을 사용자 계정에 부여하는 과정

​

(1) ROLE 생성 (CREATE ROLE)

​

(2) ROLE에 권한 할당 (GRANT)

​

(3) 사용자에 ROLE 부여 (GRANT)

​

(4) 사용자에게 부여된 ROLE 확인 (데이터 딕셔너리 dba_role_privs 사용)

 

 

* 참고 : 이하부터는 ROLE 과 관련된 작업을 명령 프롬프트(cmd) 창에서 진행합니다.

만약 오라클 SQL 디벨로퍼 내에서, 일반적인 상태의 scott 연습계정에서 롤 생성(CREATE ROLE)을 시도할 경우, 다음의 오류가 발생합니다.

​

명령의 1 행에서 시작하는 중 오류 발생 -

create role samprole01

오류 보고 -

ORA-01031: 권한이 불충분합니다

01031. 00000 - "insufficient privileges"

*Cause: An attempt was made to perform a database operation without

the necessary privileges.

*Action: Ask your database administrator or designated security

administrator to grant you the necessary privileges

 

 

3) ROLE(롤) 생성, 부여, 확인, 회수, 삭제 방법

​

먼저 명령 프롬프트(cmd) 창에서 sqlplus / as sysdba로 접속해 주세요.

 

sqlplus / as sysdba

 

(1) 기본식 : ROLE 생성 방법

​

롤A를 생성하는 방법은 다음을 실행하는 것입니다.

create role 롤이름A;

 

(2) 기본식 : ROLE에 권한 할당

​

롤A에 1개 이상의 권한 X, Y, ...를 할당합니다.

grant 권한이름X, 권한이름Y, ... to 롤이름A;

 

(3) 사용자에 ROLE 부여

​

사용자 계정 K에 롤A(+에 포함된 권한X, 권한Y, ...)를 부여합니다.

grant 역할이름A to 사용자이름K;

 

(4) 사용자의 ROLE 확인

​

사용자K가 어떤 롤을 가지고 있는지 조회합니다.​

* 이 때, 사용자이름K는 반드시 대문자로 입력해 주세요.

select * from dba_role_privs
where grantee='사용자이름K';

 

 

(5) ROLE에 포함된 권한 확인

​

롤A에 포함된 권한 X, Y, ...를 조회합니다.

select * from dba_sys_privs
where grantee='롤이름A';

 

(6) ROLE 해제

​

사용자 계정 K에게 부여된 롤A를 회수합니다. 롤A와 그 안에 포함되어 있었던 권한이 회수(해제, 취소)됩니다.

revoke 롤이름A from 사용자이름K;

 

 

(7) ROLE 삭제

​

롤A를 삭제합니다.

drop role 롤이름A;

 

* 이하의 예제에서는 임의로 생성한 사용자 계정인 ANDREA를 사용합니다.

​

2. 예제 : ROLE의 생성과 부여, 회수, 삭제

​

먼저 명령 프롬프트(cmd)를 열어, 다음을 실행합니다.

 

sqlplus / as sysdba

 

1) ROLE 생성 : 임의의 롤 'sampriole01' 생성하기

​

cmd창에 아래의 소스코드를 입력 후 실행합니다.

create role samprole01;

 

'롤이 생성되었습니다' 라는 메시지가 나타나면, samprole01 롤이 만들어진 것입니다.

​

 

2) ROLE에 권한 할당 : 'samprole01' 롤에 CREATE SESSION, CREATE TABLE, INSERT ANY TABLE 권한 할당하기

GRANT CREATE를 이용하여, samprole01 롤에 3개의 권한을 할당합니다.

 

grant create session, create table, insert any table to samprole01;

'권한이 부여되었습니다' 라는 메시지가 나타나면, samprole01 롤에 3개의 권한이 잘 할당된 것입니다.

​

 

3) 사용자에 ROLE 부여 : andrea 계정에 samprole01 롤 부여하기

​

롤을 사용자 계정에 부여하는 경우에도 GRANT 를 사용합니다.

 

grant samprole01 to andrea;

andrea 계정에 samprole01 롤의 권한들이 부여되었습니다.

 

4) 사용자의 ROLE 확인 : andrea 계정에 부여된 롤 확인하기

​

dba_role_privs라는 데이터 딕셔너리(레퍼런스)를 사용하면 andrea 계정이 가진 롤을 확인할 수 있습니다.

단, 이 때 grantee 다음의 계정 이름은 꼭 "대문자"로 작성해야 합니다.

(* 소문자로 계정 이름을 입력할 경우, '레코드가 없다'는 식의 답변을 받을 수 있습니다.)

 

select * from dba_role_privs
where grantee='ANDREA';

- GRANTEE : 사용자 계정

- GRANTED_ROLE : 사용자 계정에 부여된 롤 이름

​

andrea 계정에 samprole01 롤이 잘 부여되어 있는 것을 확인했습니다.

 

 

5) ROLE에 포함된 권한 확인하기

​

롤에 포함된 권한을 확인할 때도 데이터 딕셔너리를 씁니다. 이번에는 dba_sys_privs 를 사용하는데, grantee에 롤 이름을 입력해야 합니다.

단, 이 때 grantee 다음의 계정 이름은 꼭 "대문자"로 작성해야 합니다.

(* 소문자로 계정 이름을 입력할 경우, '레코드가 없다'는 식의 답변을 받을 수 있습니다.)

 

select * from dba_sys_privs
where grantee='SAMPROLE01';

samprole01 롤에 부여된 권한(PRIVILEGE)들이 나타났습니다. '예제1)'에서 부여한 3개의 권한이 조회되고 있습니다.

 

 

6) ROLE 해제 : andrea 계정에 부여된 samprole01 권한을 해제하기

​

권한 해제는 'REVOKE" 를 사용합니다.

revoke samprole01 from andrea;

 

'권한이 취소되었습니다' 메시지가 나타난다면, andrea 계정에게 부여된 samprole01 롤의 권한들을 취소(회수, 해제)시킨 것입니다.

 

 

7) ROLE 삭제

​

samprole01 롤을 삭제합니다.

drop role samprole01;

samprole01이 삭제되었습니다. ​

 

 

Transaction(트랜잭션) : 작업 단위, 커밋, 롤백 개념

---

1. 트랜잭션(Transaction)의 이해

​

1) 트랜잭션, 커밋, 롤백

​

오라클 SQL에서 트랜잭션(Transaction)이란, 작업이 이루어지는 단위입니다. COMMIT(커밋) 또는 ROLLBACK(롤백)을 사용하여 트랜잭션 단위로 작업을 확정하거나, 트랜잭션 내의 모든 DML 명령어를 취소할 수도 있습니다.

​

즉, 작업 확정/취소의 단위가 되는 것이 트랜잭션입니다.

만약에 같은 트랜잭션 내에서 문제가 발생하여, DML 명령문 중의 하나가 확정되지 못한다면 같은 트랜잭션의 다른 명령문도 마찬가지로 취급됩니다.

 

 

명령어	의미
COMMIT;	트랜잭션 내의 작업 확정 (해당 트랜잭션 종료, 완결)
ROLLBACK;	트랜잭션 내의 모든 명령어 취소 (해당 트랜잭션 내의 작업이 삭제) 마지막 커밋 직후의 상태로 회귀

 

커밋, 롤백은 트랜잭션 단위로 작업을 확정/취소할 경우에 사용하는 명령어입니다.

 

 

2. 트랜잭션의 종류

트랜잭션 종류		자동/수동 커밋 여부
1개 이상의 DML문	논리적 작업 단위를 형성하는 DML문의 모음. DML : 테이블에 새로운 레코드(행) 추가(INSERT), 레코드 갱신(UPDATE), 레코드 삭제(DELETE), 병합(MERGE)	SQL Developer 또는 SQL*plus가 정상적으로 종료 시 자동 커밋. 또는 Commit;을 통한 수동 커밋 ​ 시스템 Failure, 비정상 종료 시 커밋X
1개의 DDL문	DDL : 테이블 등 객체 생성(CREATE), 삭제(DROP), 변경(ALTER), 자르기(TRUNCATE)	실행 시 자동 커밋
1개의 DCL문	DCL : 권한 부여(GRANT), 권한 해제(REVOKE)	실행 시 자동 커밋

 

 

3. 트랜잭션 과정

​

1) 트랜잭션 시작

​

- 첫 번째 DML SQL 문장이 실행될 때

​

(* DML : INSERT, UPDATE, DELETE, MERGE)

​

​

2) 트랜잭션 종료

​

- 다음 중 한 가지의 경우가 발생한 경우

​

(1) COMMIT 또는 ROLLBACK 실행

(* 수동 커밋, 수동 롤백)

​

- COMMIT : 해당 트랜잭션 확정+종료

- ROLLBACK : 트랜잭션 시작 직전으로 되돌아가기

​

​

(2) DDL 또는 DCL문 실행

(* 자동 커밋)

​

- DDL : CREATE, ALTER, TRUNCATE, DROP,

- DCL : GRANT, REVOKE

​

(3) 사용자가 SQL Developer 또는 SQL Plus를 정상적으로 종료

(* 자동 커밋)

​

(4) 시스템 failure 또는 중단, 비정상 종료

(* 자동 롤백, 마지막 커밋 작업 직후로 회귀)