32일차: Oracle – 사용자관리/롤/동의어/PL.SQL기초 2021.07.28

목차

1.     사용자 관리

2.     데이터베이스 롤

3.     동의어

4.     PL/SQL 기초

 

 

1. 사용자 관리 <17장>      

 

1) 데이터베이스 보안을 위한 권한

- 데이터베이스는 정보 유출, 불법적인 접근 등을 방지하기 위해서 적절한 보안이 이루어져야 한다.

- 데이터베이스 관리자가 사용자마다 서로 다른 권한과 롤을 부여함으로써 보안을 설정할 수 있다.

 

2) 권한의 역할과 종류

- 권한은 사용자(계정)가 특정 테이블을 접근할 수 있도록 하거나, 해당 테이블에 SQL(select/insert/update/delete)문을 사용할 수 있도록 제한을 두는 것을 의미한다.

- 데이터베이스 보안을 위한 권한은 시스템 권한(System Privileges)과 객체 권한(Object Privileges)으로 나뉜다.

 

① 시스템 권한                         → ★2021_07_28_system 참고

- 데이터베이스 관리자(DBA)가 가지고 있는 권한

- 사용자(계정)의 생성과 제거, DB 접근 및 각종 객체를 생성할 수 있는 권한으로 80개가 넘는다.

 

• 사용자(계정) 생성

- 사용자를 생성하려면 권한이 필요하다.

- 사용자를 생성하기 위해서는 시스템 권한을 가지고 있어야 한다.

- 시스템 권한을 가진 데이터베이스 관리자인 DBA는 SYS, SYSTEM이다.

- 사용자 계정을 발급받기 위해 시스템 권한을 가진 SYSTEM으로 접속해야 한다.

- 사용자는 사용자의 이름과 암호를 지정하여 생성한다.

- 형식: create user 사용자명 identified by 암호;

- ex) create user user01 identified by tiger;

     select * from dba_users; → 생성된 계정 목록 검색

 

• GRANT 명령어로 권한 부여

- 사용자에게 시스템 권한을 부여

- 형식: grant 권한 이름 to 사용자명;

 

- 명령 프롬프트로 위에서 생성한 user01 계정에 접속 불가(권한이 없음)

(→ 데이터베이스에 접속할 수 있는 권한인 CREATE SESSION 부여 후 접근 가능)

sqlplus user01/tiger ERROR: ORA-01045: user USER01 lacks CREATE SESSION privilege; logon denied

 

- user01 계정에게 데이터베이스 접속 권한 부여 : create session

  grant create session to user01;

  (→ user01 계정으로 데이터베이스에 접속 가능)

- grant create session, create table to user01;   → 여러 개의 권한 부여도 가능

 

• WITH ADMIN OPTION

- grant 명령으로 권한을 부여받을 때 ‘with admin option’을 붙여서 권한이 부여되면, 권한을 부여받은 일반 계정은 자기가 부여받은 권한을 제3의 계정에게 재부여할 수 있다.

 

- ex)   

      1. 새로운 계정 생성 : user02 / tiger

      create user user02 identified by tiger;

 

      2. 데이터베이스 접속 권한 부여 : create session

      grant create session to user02 with admin option;

 

      3. 제3의 계정 생성 : user03 / tiger

      create user user03 identified by tiger;

 

      4. user02 계정으로 접속 후 user03 계정에게 create session 권한 부여하기

      (명령 프롬프트에서 실행하기)

      SQL> conn user02/tiger

      SQL> grant create session to user03;    → 권한 부여 성공

 

      5. user03 계정은 user02 계정으로부터 create session 권한을 부여받았기 때문에 데이터베이스 접속 가능

      SQL> conn user03/tiger

      SQL> show user

     

     * 권한이 없는 user01 계정이 user03에 권한 부여하는 경우 오류 발생 (insufficient privileges)

 

② 객체 권한                     → ★2021_07_28_01 참고

- 특정 객체(테이블, 뷰, 시퀀스 등)에 DML문(SELECT, INSERT, DELETE)을 사용할 수 있는 할 수 있는 권한

- 객체 권한 부여 형식: grant 권한 이름 on 컬럼명 to 사용자명;

 

 

• 객체 권한 부여

- ex)

      1. 새로 생성된 user01 계정에게 scott 계정 소유의 emp 테이블 객체에 대한 select 객체 권한을 부여하기

      SQL> conn scott/tiger

      SQL> grant select on emp to user01;   (→ grant 권한이름 on 컬럼명 to 사용자명;)

 

      2. user01 계정으로 접속 후 EMP 테이블 객체에 대해서 select 해보기

      SQL> conn user01/tiger

      SQL> select * from emp;                   → 오류 발생 (동의어 설정을 하면 가능)

      SQL> select * from scott.emp;            → 검색 가능

 

    * sqldeveloper에서 emp 테이블의 권한 부여 목록 확인 가능

 

              

• 객체 권한 취소

- ex) revoke select on emp from user01;

 

• WITH GRANT OPTION

- user02 계정에게 scott 계정 소유의 emp 테이블 객체에 대해서 select 객체 권한을 부여할 때, ‘with grant option’을 붙여서 부여하면, user02 계정은 자기가 부여받은 권한을 제3의 계정(ex. user01)에게 재부여할 수 있다.

- ex)

      1. user02 계정에게 scott 계정 소유의 EMP 테이블 객체에 대한 select 객체 권한을 부여하기

      SQL> conn scott/tiger

      SQL> grant select on emp to user02 with grant option;

 

      2. user02 계정으로 접속 후 user01 계정에게 자기가 부여받은 객체 권한을 재부여하기

      SQL> conn user02/tiger

      SQL> select * from scott.emp;

 

      SQL> grant select on scott.emp to user01;

 

      3. user01 계정으로 접속 후 검색

      SQL> conn user01/tiger

      SQL> select * from scott.emp;   → 검색 가능

 

2. 데이터베이스 롤 <18장>               → ★2021_07_28_system / 2021_07_28_01 참고

 

- 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러 개의 권한을 묶어 놓은 것

- 데이터베이스의 접속 권한(CREATE SESSION), 테이블 생성 권한(CREATE TABLE), 테이블 수정(UPDATE), 삭제(DELETE), 조회(SELECT) 등과 같은 권한은 사용자에게 기본적으로 필요한 권한들인데, 사용자를 생성할 때마다 일일이 부여하는 대신 롤을 부여하는 것이 효율적이다.

- 롤은 ‘사전 정의된 롤’과 ‘사용자가 정의한 롤’로 구분된다.

 

1) 사전 정의된 롤의 종류

- 오라클에서 기본적으로 제공되는 사전 정의된 롤에는 connect 롤, resource 롤, dba 롤이 있는데, 이중에서 주로 connect 롤과 resource 롤을 사용한다.

 

① CONNECT 롤

- CREATE SESSION 등 데이터베이스에 접속과 관련된 8가지 권한을 묶어 놓은 롤

 

② RESOURCE 롤

- 사용자가 객체(테이블, 뷰, 인덱스)를 생성할 수 있도록 하기 위해 시스템 권한을 묶어 놓은 롤

 

③ DBA 롤

- 사용자들이 소유한 데이터베이스객체를 관리하고 사용자들을 작성하고 변경하고 제거할 수 있도록 하는 모든 권한을 가진 롤

 

- ex)

      1. 새로운 계정 생성 : user04 / tiger

      create user user04 identified by tiger;

 

      2. 생성된 계정 목록 확인

      select * from dba_users;

 

       3. user04 계정에게 롤(role) 부여 : connect, resource 2가지 롤 부여

      grant connect, resource to user04;

 

      4. user04 계정으로 접속 후 테이블 생성

      SQL> conn user04/tiger

      SQL> create table member(id varchar2(20), passwd varchar2(20));

 

2) 사용자 정의 롤 생성

- 사용자 정의 롤: 사용자가 정의해서 사용하는 롤

- 시스템 권한, 객체 권한 모두 가능

- 형식: create role 롤이름 grant 권한이름 to 롤이름;

 

① 사용자 정의 롤 : 롤에 시스템 권한 부여          → ★2021_07_28_system 참고

- ex)

      1. 롤 생성

      create role mrole;

 

      2. 생성된 롤에 시스템 권한 추가

      grant create session, create table, create view to mrole;

 

      3. mrole을 적용하기 위한 계정 생성 : user05 / tiger

      create user user05 identified by tiger;

 

      4. user05 계정에 mrole 부여

      grant mrole to user05;

 

② 사용자 정의 롤 : 롤에 객체 권한 부여  → ★2021_07_28_system / 2021_07_28_01 참고

- ex)

      1. 롤 생성 (SYSTEM)

      SQL> conn system/oracle

      create role mrole02;           → 롤 생성 성공

 

      2. 생성된 롤에 객체 권한 추가 (SCOTT)

      SQL> conn scott/tiger

      SQL> grant select on emp to mrole02;    → 권한 부여 성공

 

      3. user05 계정에 mrole02 부여 (SYSTEM)

      SQL> system/oracle

      grant mrole02 to user05;

 

      4. user05 계정으로 접속 후 emp 테이블 검색 (USER05)

      SQL> conn user05/tiger

      SQL> select * from scott.emp;

 

3) 롤 회수하기                                    → ★2021_07_28_system 참고

- 형식: revoke 롤이름 from 사용자명;

- ex) revoke mrole from user05;

 

4) 롤 삭제하기                                    → ★2021_07_28_system 참고

- 형식: drop role 롤이름;

- ex) drop role mrole;

 

5) 디폴트 롤                                       → ★2021_07_28_system / 2021_07_28_01 참고

- 디폴트 롤 = 시스템 권한 + 객체 권한

- 디폴트 롤을 생성하여 여러 사용자에게 롤 부여하기

- 시스템 권한 + 객체 권한이 부여된 롤을 사용자에 대해 권한 부여할 수 있다.

- ex)

      1. 디폴트 롤 생성 (SYSTEM)

      SQL> conn system/oracle

      create role def_role;

 

      2. 생성된 롤(def_role)에 시스템 권한 추가 (SYSTEM)

      SQL> conn system/oracle

      grant create session, create table to def_role;

 

      3. 생성된 롤(def_role)에 객체 권한 추가 (SCOTT)

      SQL> conn scott/tiger

      SQL> grant select on emp to def_role;

      SQL> grant update on emp to def_role;

      SQL> grant delete on emp to def_role;

 

      4. def_role 적용하기 위한 일반 계정 생성(SYSTEM) : user06 / tiger

      SQL> conn system/oracle

      create user usera1 identified by tiger;

      create user usera2 identified by tiger;

      create user usera3 identified by tiger;

 

      5. def_role을 생성된 계정(usera1, usera2, usera3)에 부여 (SYSTEM)

      SQL> conn system/oracle

      grant def_role to usera1;

      grant def_role to usera2;

      grant def_role to usera3;

 

       6. usera1 계정으로 접속 후 검색 (USERA1)

      SQL> conn usera1/tiger

      SQL> select * from scott.emp;

 

3. 동의어 <19장>                              → ★2021_07_28_system 참고

 

1) 동의어의 개념

- 다른 사용자가 소유한 객체에 접근하기 위해서는 소유자의 이름을 객체 앞에 지정해야 한다.

- 객체를 조회할 때마다 일일이 객체의 소유자를 지정하는 것이 번거롭기 때문에, 동의어를 정의해서 간단한 이름으로 접근하는 것이 효율적이다.

- 동의어는 개별 사용자를 대상으로 하는 비공개 동의어와 전체 사용자를 대상으로 한 공개 동의어가 있다.

 

2) 동의어의 종류

① 비공개 동의어

- 객체에 대한 접근 권한을 부여받은 사용자가 정의한 동의어로, 해당 사용자만 사용할 수 있다.

- 형식: create synonym 동의어명 for 객체소유자명.객체명;

 

② 공개 동의어

- 권한을 주는 사용자가 정의한 동의어로 누구나 사용할 수 있다. 공개 동의어는 DBA 권한을 가진 사용자만 생성할 수 있다. synonym 앞에 public을 붙여서 정의한다.

- 형식: create public synonym 동의어명 for 객체소유자명.객체명;

 

• 공개 동의어 예

sys.dual  →  dual

sys.tab   →  tab

sys.seq   →  seq

 

select 10+20 from sys.dual;

select 10+20 from dual;       → 공개 동의어

 

select * from sys.tab;

select * from tab;              → 공개 동의어

 

select * from sys.seq;

select * from seq;             → 공개 동의어

 

3) 동의어 생성

① 비공개 동의어 생성 예제

1. system 계정으로 접속 후 테이블 생성

SQL> conn system/oracle

create table systbl(ename varchar2(20));

 

2. 생성된 테이블에 데이터 추가

SQL>conn system/oracle

insert into systbl values('김석진');

insert into systbl values('박지민');

 

3. scott 계정에게 systbl 테이블에 대한 select 객체 권한을 부여 (SYSTEM)

SQL>conn system/oracle

grant select on systbl to scott;

 

4. scott 계정으로 접속 후 검색 (SCOTT)

SQL> conn scott/tiger

SQL> select * from systbl;                   → 오류 발생

SQL> select * from system.systbl;         → 검색 가능 (객체소유자.객체명)

 

5. scott 계정에게 동의어를 생성할 수 있는 시스템 권한을 부여하기 (SYSTEM)

SQL> conn system/oracle

grant create synonym to scott;

 

6. scott 계정으로 접속 후 비공개 동의어 생성 (SCOTT) : system.systbl  →  systbl

생성된 비공개 동의어는 scott 계정만 사용 가능

SQL> conn scott/tiger

SQL> create synonym systbl for system.systbl;

 

7. 동의어 목록 (SCOTT)

SQL> conn scott/tiger

SQL> select * from user_synonyms;     → scott 계정 소유의 비공개 동의어 검색

 

8. 동의어를 이용해서 검색 (SCOTT)

SQL> conn scott/tiger

SQL> select * from system.systbl;       → 검색 가능

SQL> select * from systbl;                → 검색 가능

 

9. 동의어 삭제

형식: drop synonym 동의어명;

SQL> conn scott/tiger

SQL> drop synonym systbl;

 

② 공개 동의어 생성 예제

- DBA 계정(SYS, SYSTEM)으로 접속해서 공개 동의어를 생성할 수 있다.

- 공개 동의어를 만들 때는 public을 붙여서 생성할 수 있다.

 

1. 공개 동의어 생성

SQL> system/oracle

create public synonym pubdept for scott.dept;

 

2. 공개 동의어 목록

select * from dba_synonyms;

 

3. 공개 동의어 삭제

SQL> conn system/oracle

drop public synonym pubdept;

 

4. PL/SQL 기초 <20장>                                → ★2021_07_28_02 참고

 

1) PL/SQL 개념

- PL/SQL은 Oracle's Procedural Language extension to SQL의 약자로, SQL을 확장한 절차적 언어

 

2) PL/SQL 구조

- 오라클의 환경 변수 serveroutput은 오라클에서 제공해주는 프로시저를 사용하여 출력해 주는 내용을 화면에

  보여주도록 설정하는 환경 변수로, 디폴트값이 OFF이기에 ON으로 변경해야 한다.

- PL/SQL 블록내에서는 한 문장이 종료할 때마다 세미콜론(;)을 사용한다.

- END뒤에 ;을 사용하여 하나의 블록이 끝났다는 것을 명시한다.

- DELCLARE나 BEGIN이라는 키워드로 PL/SQL블럭이 시작하는 것을 알 수 있다.

- 단일행 주석은 --이고 여러행 주석 /* */로 처리한다.

- PL/SQL은 다음과 같은 블록(BLOCK) 구조의 언어로서 크게 3 부분으로 나눌 수 있습니다.

- 선언부 / 실행부 / 예외 처리부로 구성

 

 

① 선언부(DECLARE SECTION)

- PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분

- DECLARE로 시작함

 

② 실행부(EXECUTABLE SECTION)

- 절차적 형식으로 SQL문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할 수 있는 부분

- BEGIN으로 시작함 ( BEGIN ~ END; / )

 

③ 예외 처리(EXCEPTION SECTION)

- PL/SQL문이 실행되는 중에 에러가 발생할 수 있는데 이를 예외 사항이라고 하고, 이러한 예외 사항이 발생했을 때 이를 해결하기 위한 문장을 기술할 수 있는 부분

- EXCEPTION으로 시작함

 

3) 간단한 PL/SQL문 실행

① 콘솔창(명령 프롬프트)에서 PL/SQL 구문 실행

- scott 계정으로 접속 후 아래 내용 입력

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL> set serveroutput on SQL> begin   2  dbms_output.put_line('Hello World!');   3  end;   4  / Hello World! PL/SQL procedure successfully completed.

② SQL Developer에서 PL/SQL 구문 실행

- scott 계정으로 접속 후 아래 내용 입력

set SERVEROUTPUT on begin DBMS_OUTPUT.PUT_LINE('Hello World!'); end;

4) 변수 선언

• 변수 종류

① 스칼라(scalar) 변수

- SQL에서 사용하던 자료형과 유사하게 선언하는 것

- 숫자를 저장하려면 NUMBER, 문자를 저장하려면 VARCHAR2를 사용해서 선언한다.

- ex) vempno number(4);

       vename varchar2(10);

 

② 레퍼런스(reference) 변수

- %TYPE속성을 사용해서 이전에 선언된 다른 변수 또는 데이터베이스 컬럼에 맞추어 선언되는 변수

- ex) vempno emp.empno%type;

       vename emp.ename%type;

 

5) 변수가 있는 PL/SQL문 실행

① 스칼라 변수 사용

- SCOTT 사원의 사번과 이름 검색하기

 

set serveroutput on                    → 환경변수 설정

declare                                    → 선언부 시작

    vempno number(4);                → 변수 선언 : 스칼라 변수

    vename varchar2(10);

begin                                      → 실행부 시작

    vempno := 7788;                   → 변수명은 대소문자를 구분하지 않음

    vename := 'SCOTT';

    DBMS_OUTPUT.PUT_LINE('사번 / 이름');

    DBMS_OUTPUT.PUT_LINE(VEMPNO ||' / '|| VENAME);

end;                                       → 실행부 끝

 

② 레퍼런스 변수 사용

- SCOTT 사원의 사원 번호와 이름 검색하기

 

set SERVEROUTPUT on

declare

    vempno emp.empno%type;     → 변수 선언 : 레퍼런스 변수

    vename emp.ename%type;

begin

    select empno, ename into vempno, vename from emp where ename='SCOTT';

    DBMS_OUTPUT.put_line('사번 / 이름');

    DBMS_OUTPUT.put_line(vempno || ' / ' || vename);

end;