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

-- * 오라클 : 사용자(User) 권한(Role)

-- * 데이터 제어어 DCL (Data Control Language)

권한의 역할과 종류

1) 권한은 사용자가 특정 테이블을 접근할 수 있도록 하거나 해당 테이블에 SQL(SELECT / INSERT / UPDATE / DELETE) 문을 사용할 수 있도록 제한을 두는 것을 말한다

2) 데이터베이스 보안을 위한 권한은 시스템 권한(System Privileges)과 객체 권한(Object Privileges)으로 나누어 진다

3) 시스템 권한은 사용자이 생성과 제거 DB 접근 및 각종 객체를 생성할 수 있는 권한 등 주로 DBA에 의해 부여되면 그 권한의 수가 80가지가 넘기에 대표적인 시스템 권한만 정리했다

시스템 권한

CREATE USER : 새롭게 사용자를생성하는 권한

DROP USER : 사용자를 삭제하는 권한

DROP ANY TABLE : 임의의 테이블을 삭제할 수 있는 권한

QUERY REWRITE : 함수 기반 인덱스를 생성하는 권한

BACKUP ANY TABLE : 임의의 테이블을 백업하 수 있는 권한

-- [1] thomas 계정 생성

create user thomas identified by tiger; --thomas 계정생성  tiger 비밀번호 설정

cmd창 확인 접속 error 발생

계정을 생성 했다고 접속이 되는 것이 아니다

-- [2] 데이터베이스 접속 권한 부여

grant create session to thomas; 

cmd창 확인 접속 확인 성공

cmd창에서 실행 필요 -- 복사/붙여넣기로 실행 시 error 발생

SQL>sqlplus thomas/tiger

SQL>create table emp01(

           empno   number(2),

           ename   varchar2(10),

           job        varchar2(10),

           deptno   number(2)

       );

테이블 생성 권한이 없다는 문구의 에러가 발생한다 (아래그림 잘못된 것임)

-- [ 3] 테이블 생성 권한 부여

grant create table to thomas;

cmd창에서 실행 필요 -- 복사/붙여넣기로 실행 시 error 발생

SQL>create table emp01(

           empno   number(2),

           ename   varchar2(10),

           job        varchar2(10),

           deptno   number(2)

       );

-- [4] 테이블 스페이스 부여 받아야 한다

-- 테이블 스페이스(table space)는 디스크 공간을 소비하는 테이블과 뷰 그리고 

-- 그 밖의 다른 객체

-- 오라클 xe 버전의 경우 메모리 영역은 system으로 할당한다

-- 오라클 full 버전의 경우 메모리 영역은 users으로 할당한다

alter user thomas quota 2m on system;

thomas에게 메모리 공간도 할당이 되었다 

cmd창에서 실행 필요 -- 복사/붙여넣기로 실행 시 성공

SQL>create table emp01(

           empno   number(2),

           ename   varchar2(10),

           job        varchar2(10),

           deptno   number(2)

       );

SQL> select * from tab;

SQL> select * from emp01;

-- * 계정 생성 및 테이블 생성까지의 권한 부여 정리

SQL> sqlplus system/admin1234 (시스템 계정 접속)

[1] thomas 계정 생성 : create user thomas identified by tiger;

[2] 데이터베이스 접속 권한 부여 : grant create session to thomas; 

[3] 테이블 생성 권한 부여 : grant create table to thomas;

[4] 테이블 스페이스 부여 받아야 한다 : alter user thomas quota 2m on system;

-- [5] with admin option

사용자에게 시스템 권한을 with admin option과 함께 부여하면 그 사용자는 데이터베이스 관리자가 아닌데도 불구하고 부여 받은 시스템 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받게 된다

-- tester1 계정 생성 및 권한 부여

create user tester1 identified by tiger;

grant create session to tester1; 

grant create table to tester1;

alter user tester1 quota 2m on system;

SQL> sqlplus tester1/tiger;

SQL> grant create session to thomas;  -- error  (tester1은 권한 부여 속성을 가지고 있지 않다)

-- tester3 계정 생성 및 권한 부여

create user tester3 identified by tiger;

grant create session to tester3 with admin option; 

grant create table to tester3;

alter user tester3 quota 2m on system;

cmd 프롬프트에서 실행

SQL> sqlplus tester3/tiger

SQL> grant create session to thomas; -- 성공

-- [6] .테이블객체에 대한 select 권한 부여(scott/emp  --> thomas) 

SQL> sqlplus scott/tiger

SQL> conn scott/tiger

SQL> show user

SQL> grant select on emp to thomas;

SQL> conn thomas/tiger

SQL> select * from emp; --error

-- [7] 스키마(SCHEMA) : 객체를 소유한 사용자명을 의미

SQL> select * from scott.emp;

-- [8] 사용자에게 부여된 권한 조회

-- user_tab_privs_made : 현재 사용자가 다른 사용자에게 부여한 권한 정보를 알려준다

-- user_tab_privs_recd : 자신에게 부여된 사용자 권한을 알고 싶을 때

--thomas 계정 실행

SQL> select * from user_tab_privs_made; --error

SQL> select * from user_tab_privs_recd; --부여 받은 권한을 확인

--scott 계정 실행

SQL> select * from user_tab_privs_made;  --부여 해준 권한을 확인

-- [9] 계정의 비밀번호 변경

alter user thomas identified by thomas; -- 비밀번호 tiger 에서 thomas 로 변경

-- [10] 객체 권한 제거

SQL> sqlplus scott/tiger

SQL> revoke select on emp from thomas;

-- [11] with grant option -- 계정이 가지고 있는 옵션을 함께 부여 받는것이다

SQL> sqlplus scott/tiger

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

SQL> sqlplus tester1/tiger

SQL> select * from scott.emp;

-- [12] 사용자 계정 제거

drop user tester3;