⛏️/Mysql | Oracle

[Oracle] DML, DCL

defyuil 2023. 12. 28. 14:29

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