Oracle(27일차)

* 권한(부여/회수)

- 시스템권한 : system관련권한, dba~, 연쇄회수 X

- 객체권한 : 객체관련권한, 객체소유자 

*권한부여

grant 권한, [권한, ...]

to user명

grant create session

to jj1;

*user생성

create user user명

identified by 비번

[with admin option]

- sys에서 

jj1 생성

create user jj1

identified by jj1;

jj2 생성-------------------------> jj3부여

create session 부여

[with admin option]

jj3 생성

jj3에게 create session권한 부여

jj2에서...

grant create session

to jj3

with admin option

* 권한조회

select ~ 

from user_sys_privs;

select *

from user_sys_privs;

* 권한 회수

revoke 권한, [권한, ...]

from user명

jj1으로부터 session권한을 회수...

revoke create session

from jj1

jj2로부터

create session 권한 회수

revoke create session

from jj1;

super user에서

super.dept 객체 --> jj3가 super.dept

select

insert

update

delete

select * 

from super.dept;

* 권한부여

grant 권한[, 권한, ...]

on 객체명

to user명

[with grant option]

grant select

on dept

to jj3

select *

from super.dept

jj3상태에서 

super.dept 테이블에 

임의 데이터 입력

insert into super.dept

values(60, '', '');

권한이 불충분...

super에서 jj3에 insert, update, delete 권한부여

grant insert, update, delete

on dept

to jj3;

insert into super.dept

values(60, '', '');

update super.dept

set dname='권한연습중'

where deptno=60;

delete super.dept

where deptno=60;

super.dept ---> jj1 super.dept ---> jj2 super.dept

select select

update

jj1 : create session

jj2 : create session 을 가질수 있게...

grant create session

to jj1, jj2;

conn super/man

grant select, update

on dept

to jj1

with grant option;

conn jj1/jj1

grant select

on super.dept

to jj2;

conn jj2/jj2

select *

from super.dept;

객체권한 조회

select *

from user_tab_privs;

객체권한 받은것

select *

from user_tab_privs_recd;

객체권한 준것

select *

from user_tab_privs_made;

권한회수

revoke 권한[, 권한, ...]

on 객체명

from user명;

super

jj3로부터 super.dept

select, insert, update, delete 회수

revoke select, insert, update, delete

on dept

from jj3;

super는 

jj1으로부터 dept객체에 대한 select, update 권한을 회수합니다.

revoke select, update

on dept

from jj1;

===============================================

* Role

: 권한집합

- DBA

- CONNECT

- RESOURCE

*role 생성

create role role명

- role에 권한부여

- role에 권한회수

-sys에서

role roletest

- create session, create table, drop table

grant create session, create table, drop any table

to roletest;

jj1 user에게 roletest 부여

grant roletest

to jj1;

create session권한을 roletest에서 회수

revoke create session

from roletest

jj1 user로부터 roletest 회수

revoke roletest

from jj1

* role 삭제

drop role role명

drop role roletest;

select *

from user_role_privs;

select * 

from role_sys_privs;