[Oracle] DML, DCL

DML

INSERT

• 기본적으로 MySQL과 동일한 문법으로 새로운 데이터를 입력할 수 있다.
• 다만 명령어 하나당 하나의 행의 값만 추가할 수 있다.

 

• 다음과 같이 mysql 문법처럼 사용하면 에러가 발생한다.

INSERT INTO DEPARTMENTS VALUES (280, 'itwillbs', 100, 2700), (290, 'itwillbs2', 100, 2700);

SQL Error [933] [42000]: ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다

Error position: line: 2 pos: 61

DCL(Data Control Language / 데이터제어어)

• 데이터베이스의 권한을 관리하는 문법
• 계정 소유 스키마(schema) : 사용자가 생성한 오브젝트들의 구조나 관리 명세를 볼 수 있다.
  다른 계정의 스키마는 권한이 없다면 볼 수 없다.
• 직접 생성한 테이블은 테이블을 생성한 사용자가 소유권을 가진다.

 

사용자 생성

• 명령어는 관리자 계정인 sys로 진행한다.

 

SQL> conn sys/oracle as sysdba

Connected.

 

SQL> CREATE USER demo    ← 계정명   2  IDENTIFIED BY demo; ← 패스워드

 

User created.

 

• 세션 생성 권한의 부족으로 접속을 할 수 없다.

SQL> conn demo/demo

ERROR:

ORA-01045: user DEMO lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

 

• 동일한 계정으로 접속하더라도 접속 클라이언트에 따라 세션이 구분되어 진행 중인 작업내역은 공유되지 않는다.
• 세션을 생성하지 못한다면 데이터베이스에 접속 할 수 없다.

 

• 접속 실패 시 로그아웃이 되므로 다시 로그인한다.

SQL> conn sys/oracle as sysdba

Connected.

 

시스템 권한

• 데이터베이스 시스템을 조작할 수 있는 권한
• DBA가 소유하고 있다.

 

시스템 권한 부여(GRANT)

• 권한을 위임할 수 있는 권한을 가진 계정에서는 다른 계정에게 권한을 부여해 줄 수 있다.

 

• GRANT절 : 부여할 권한의 목록을 작성한다.
• TO절 : 권한을 부여 받을 사용자를 작성한다.

SQL> GRANT create session, create table, create sequence, create view   2  TO demo;

 

Grant succeeded.

 

• 접속 잘 됨.

SQL> conn demo/demo

Connected.

SQL> CREATE TABLE test   2  ( no NUMBER);

 

Table created.

SQL> DESC test

Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 NO                                                 NUMBER

시스템 권한 회수(REVOKE)

• 더 이상 필요없어진 권한이 있는 경우 회수하는 것이 좋다.
• 권한의 회수는 권한을 관리할 수 있는 계정에서 수행할 수 있다.

SQL> conn sys/oracle as sysdba

Connected.

 

• REVOKE절 : 회수할 권한의 목록을 작성한다.
• FROM절 : 권한을 회수 당할 계정을 작성한다.

SQL> REVOKE create table   2  FROM demo;

 

Revoke succeeded.

 

• 다시 DEMO 계정으로 접속 후 테이블을 생성해 본다.

SQL> conn demo/demo

Connected.

 

• 관련 권한이 없기 때문에 작업은 실패한다.

SQL> CREATE TABLE test2   2  (no NUMBER);

CREATE TABLE test2

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

오브젝트 권한

• 오브젝트를 다룰 수 권한

SQL> conn demo/demo

Connected.

 

• 오브젝트 명 앞에 hr과 같은 스키마명을 생략하면 접속 중인 계정 소유의 동명의 스키마를 가리키는게 된다.

SQL> SELECT employee_id, last_name, salary   2  FROM hr.employees;

FROM hr.employees

        *

ERROR at line 2:

ORA-00942: table or view does not exist

 

• 위의 경우 문법적으로는 문제가 없으나 hr.employees 테이블에 대한 권한의 부족으로 데이터 조회를 할 수 없다.

 

오브젝트 권한 부여(GRANT)

• 필요한 오브젝트에 대한 사용에도 권한이 필요하다.
• 권한은 해당 오브젝트의 소유자가 관리할 수 있다.

 

• hr.employees 테이블에 대한 작업이므로 hr 계정이 소유자로 작업을 진행할 수 있다.
• GRANT절 : 부여할 권한들의 목록
• ON절 : 스키마 범위 내에서 권한을 관리할 오브젝트명을 작성한다.
• TO절 : 권한을 부여받을 사용자들을 작성한다.

SQL> conn hr/hr

Connected.

SQL> GRANT select   2  ON employees   3  TO demo;

 

Grant succeeded.

 

• 권한을 부여받아 정상적으로 값을 출력할 수 있게 되었음.

SQL> conn demo/demo

Connected.

 

SQL> SELECT COUNT(*)   2  FROM hr.employees;

 

  COUNT(*)

----------

       107

 

오브젝트 권한 회수(REVOKE)

• 권한의 회수 역시 권한의 소유자가 수행할 수 있다.

 

• REVOKE절 : 회수할 권한의 목록
  ON절 : 권한의 대상 오브젝트
  FROM : 권한을 회수할 계정

 

• 소유자인 hr 계정으로 접속

SQL> conn hr/hr

Connected.

 

• employees 테이블에 대한 select 권한을 demo에게서 회수한다.

SQL> REVOKE select   2  ON employees   3  FROM demo;

 

Revoke succeeded.

 

SQL> conn demo/demo

Connected.

 

• demo계정은 더 이상 hr.employees 테이블에서 select 할 수 없게 된다.

SQL> SELECT * FROM hr.employees;

SELECT * FROM hr.employees

                 *

ERROR at line 1:

ORA-00942: table or view does not exist