DCL) 권한 부여 및 회수 (GRANT, REVOKE 구문)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

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

- 권한을 부여/회수하는 명령어

grant

revoke

- TCL (Transcation Control Language) 포함

commit

rollback

 

DCL 문법

grant 권한/롤 to 사용자/롤/public [with admin option]

※ 권한(previliage) - create session, create table, select on table, ....

※ 롤(role : 권한묶음) - connect, resource, dba, ....

※ public - 모든 사람이 쓸 수 있게끔!

※ with admin option - 부여 받은 권한/롤을 다시 다른 사용자에게 부여할 수 있는 권한 

 

 

chany 사용자 생성

반드시 관리자 계정으로 실행해야 합니다.

-- (관리자로 실행 시작)
-- 일반 사용자 (12c부터) c##chany, C##CHANY처럼 생성해야 하지만, 번거로우므로 아래 코드 실행
alter session set "_oracle_script" = true;

create user chany
identified by 비번
default tablespace users;

chany 계정이 생성 되었으므로, 접속을 해보겠습니다.

create session 권한을 부여해주지 않았기 때문에

"상태: 실패 -테스트 실패: ORA-01045: 사용자 CHANY는 CREATE SESSION 권한을 가지고있지 않음; 로그온이 거절되었습니다" 이와 같은 메세지가 출력 되는 것을 확인 할 수 있습니다.

 

connect session 권한 부여

-- 권한 부여 (관리자계정)
-- connect 롤 안에 create session 권한 포함!
grant connect to chany;

테스트 결과 성공이라는 메세지가 출력 되었고, 접속이 정상적으로 이루어졌습니다.

 

chany 계정으로 접속해보겠습니다.

접속이 잘 되는 것을 확인할 수 있습니다.

 

테이블 생성 시도

-- chnay 계정
create table abcd(
    id varchar2(20)
);  -- ORA-01031: insufficient privileges

chany 계정으로 테이블 생성을 하려고 하니 "ORA-01031: insufficient privileges" 권한이 부족하다는 오류 메세지가 뜹니다.

객체를 생성하는 권한을 주지 않았기 때문이죠.

 

객체 생성 권한 부여

-- 권한 부여(관리자 계정)
-- resource 롤 안에 create table 권한 포함!
grant resource to chany;

 

-- chany 계정
create table abcd(
    id varchar2(20)
);	-- ORA-01031: insufficient privileges

하지만 또 "ORA-01031: insufficient privileges" 오류 메세지가 뜨며 실패합니다.

table 생성은 가능하지만, 실제 테이블이 만들어질 공간에 대한 권한을 부여받지 못했기 때문입니다.

 

객체 용량 권한 부여

-- 관리자 계정
alter user chany quota unlimited on users;

 

-- chany 계정
create table abcd(
    id varchar2(20)
);

드디어 테이블 abcd가 생성 되었습니다.

(만약 quota 부여 후에도 테이블 생성이 안된다면 계정 재접속 시도 필요!)

 

부여된 권한/롤 조회

-- 부여된 권한/롤 조회
select * from dba_sys_privs where grantee = 'CHANY';    -- 권한 조회

권한을 낱개로 준 것이 아니기 때문에 dba_sys_privs 권한 조회에서는 아무것도 조회되지 않습니다.

 

select * from dba_role_privs where grantee = 'CHANY';   -- 롤 조회

위에서 connect과 resource롤을 통해 권한 부여를 해줬기 때문에 롤 조회에서는 connect, resource가 조회됩니다.

 

connect 롤 안에 권한 조회

select * from dba_sys_privs where grantee = 'CONNECT';  -- connect 롤 안에 권한 조회

 

resource 롤 안에 권한 조회

select * from dba_sys_privs where grantee = 'RESOURCE'; -- resource 롤 안에 권한 조회

connect 롤과 resource 롤 안에 어떤 권한이 있는 지 조회할 수 있습니다.

 

---

테이블 조회/추가/수정/삭제 권한 부여 및 회수

 

테이블 생성

-- coffee 테이블 생성
-- 관리자 계정이 아닌 kh계정으로 접속
create table coffee(
    name varchar2(50),
    price number,
    company varchar2(50),
    constraint pk_coffee_name primary key(name)
);

insert into coffee values('맥심', 3000, '동서식품');
insert into coffee values('카누', 5000, '동서식품');
insert into coffee values('네스카페', 4000, '네슬레');

select * from coffee;
commit;

 

chany 계정에서 kh로 생성한 coffee 테이블 조회 시도

-- kh.coffee 테이블 조회
select * from kh.coffee;    -- ORA-00942: table or view does not exist

"ORA-00942: table or view does not exist" 오류 메세지가 뜨면서 조회 불가!

당연히 조회가 불가할 것 입니다. coffee 테이블은 타사용자가 권한을 부여받지 않은 이상 kh+관리자계정에서만 볼 수 있습니다.

따라서 chany계정에서 보려고 하니, 접근을 할 수가 없는 것이죠.

 

coffee 테이블의 소유주 kh가 테이블 조회 권한을 chany에게 부여

-- kh 계정 접속
-- coffee 테이블의 소유주 kh가 테이블 조회 권한을 chany에게 부여
grant select on kh.coffee to chany;
-- grant all on kh.coffee to chany; -- 모든 권한 부여

-- chany 계정
-- 권한 부여 후 kh.coffee 테이블 조회
select * from kh.coffee;

테이블 조회를 kh로부터 부여받은 chany 계정은 kh.coffee 테이블을 조회할 수 있습니다.

 

chany 계정에서 테이블 행 추가 시도

-- chany 계정
-- kh.coffee 테이블 행 추가
insert into kh.coffee values('프렌치카페', 4500, '남양유업');    
-- SQL 오류: ORA-01031: insufficient privileges

이번에는 chany 계정에서 kh.coffee 테이블에 행을 추가하려고 하니 "SQL 오류: ORA-01031: insufficient privileges" 똑같은 오류 메세지가 출력 되었습니다.

kh는 조회 권한만 주었는데, 추가까지 하려고 하니 실패하는 것이죠.

 

데이터 추가, 수정, 삭제 권한 chany에게 부여

-- kh 계정
-- 데이터 추가, 수정, 삭제 권한 chany에게 부여
grant insert, update, delete on kh.coffee to chany;

-- 권한 부여 후 테이블 행 추가
insert into kh.coffee values('프렌치카페', 4500, '남양유업');
commit;	-- commit 하지 않으면 kh에게서 chany가 작업한 내용 확인 불가!!
select * from kh.coffee;

데이터 추가가 성공적으로 되는 것을 확인할 수 있습니다.

또한 commit; 작업을 하지 않으면 메모리 상에서만 관리하고 있기 때문에 kh는 chany가 작업하는 내용이 확인 되지 않습니다.

(커밋 필수!!)

 

-- chany 계정
-- 삭제
delete from kh.coffee where name = '프렌치카페';

-- 수정
update kh.coffee set price = 5000 where name = '맥심';
commit;

select * from kh.coffee;

 

데이터 추가, 수정, 삭제 권한 회수

-- kh 계정
-- 데이터 추가, 수정, 삭제 권한 회수
revoke insert, update, delete on kh.coffee from chany;

 

권한 회수 후 데이터 추가 시도

-- chany 계정
-- 권한 회수 후 데이터 추가 시도
insert into kh.coffee values('조지아', 4500, '조지아');
-- SQL 오류: ORA-01031: insufficient privileges

마찬가지로 권한이 없다는 "SQL 오류: ORA-01031: insufficient privileges" 메세지가 뜨면서 실패하게 됩니다.

 

데이터 조회 권한 회수

-- kh 계정
-- 테이블 조회 권한 회수
revoke select on kh.coffee from chany;
-- revoke all on kh.coffee from chany;로 한 번에 회수 가능

 

권한 회수 후 테이블 조회 시도

-- chany 계정
-- 권한 회수 후 데이터 조회 시도
select * from kh.coffee;    -- ORA-00942: table or view does not exist

마찬가지로 "ORA-00942: table or view does not exist" 오류 메세지가 뜨며 실패하게 됩니다.