[Oracle] DBA : 권한(privileges)과 규칙(Roles) 관리, 유저(Users) 관리

1.권한

1.1. 권한 설명

#권한이란?

특정한 sql문을 수행할 수 있는 권리

#권한 2종류는?

시스템권한 - database에 영향을 줄 수 있는 권한. 함부로 주는게 아님.

객체권한 - 객체를 사용할 수 있는 권한. 특정테이블을 조회하고자 할 때.

#권한을 주는 사람은? 

시스템권한 - sys, dba

객체권한 - 객체소유자

#권한부여방법은?

role을 통해서 권한을 줄수도있고, dba가 직접부여 할 수도 있음.

1.2. 권한 조회 쿼리문

#user session 후 권한 조회 쿼리문 6가지는?

시스템 권한

select * from session_privs; --> 1)새션한 사용자 시스템 권한 확인 : 3)과 4)를 유니온 한 값임.

select * from session_roles; --> 2)새션한 사용자의 모든 롤 확인

select * from user_sys_privs; --> 3)dba -> user 에게 직접 부여한 시스템 권한 확인

select * from role_sys_privs; --> 4)role -> user 받은 시스템 권한 확인

객체 권한

select * from role_tab_privs; --> 5)role -> user 가 받은 롤 안에 객체 권한 확인

select * from user_tab_privs; --> 6)객체 소유자(tab owner)로부터 직접 받은 객체 권한과,  내가 다른 사람한테 부여한 객체 권한을 확인하는 방법

#user session 후 자신의 정보를 확인하는 대표적인 방법 가지는?

select * from user_users;

select * from user_tables;

select * from user_ts_quotas;

select * from user_recyclebin; --DROPSCN : COMMIT번호, BASE_OBJECT : OBJECT번호

select * from user_constraints where table_name = 'EMP_TEST';

select * from user_cons_columns where table_name = 'EMP_TEST';

select * from user_indexes where table_name = 'CUST'; 

select * from user_ind_columns where table_name = 'CUST'; 

#sys session 후 가능한 권한 부여 여부를 조회하는 쿼리문은?

select * from dba_sys_privs; --2)어떤 유저에게 어떤 권한을 줬는지 확인 가능. 뒤에 where grantee = '유저명'; 하면 빠르게 찾을 수 있음.

select * from dba_tab_privs; --3)객체권한을 어떤유저가 어떤유저에게 줬는지 확인하는 방법, 감사때 필요한 방법

select * from dba_roles; --4)데이터 베이스에 생성된 롤에 대한 정보를 확인하는 방법

select * from dba_role_privs; --5)롤을 어떤 유저한테 부여했는지 확인하는 방법

#sys session 후 user정보나 db정보를 확인하는 대표적인 방법 가지는?

^^select * from dba_users; --1)데이터베이스에 생성된 유저 정보를 확인하는 방법

^^select * from dba_data_files;--6)sys계정에서 물리/논리적인 저장공간을 확인하는 방법. 

^^select * from dba_temp_files; --7)임시저장소(temporary) 디스크용량 확인방법

^^select * from dba_ts_quotas; --8)quotas 확인방법

show parameter recyclebin;

show parameter undo;

#심화# select * from dba_data_files; 했을 시 나오는 정보

데이터 저장시 데이터는 하드디스크에 물리적인 공간으로 저장되며 표시는 논리적으로 된다.

물리적인 공간(c:\oracle\ ... \xe\users_dbf)

논리적인 공간(users, temp, undo,system)

1)system : 권한 등이 저장됨.

2)undo : 이전값이 저장됨. 변경작업할 때 사용됨.

undotbs1에 저장하는 이유

(1)rollback(DML 작업 되돌림)

(2)읽기 일관성(이전값으로 보게함)

(3)flashback 기능 때문에(drop된 테이블 다시 recyclebin 하기)

3)temp : 임시 저장소. 예를들어 100MByte를 정렬(sorting)해야하는데, 메모리는 10MByte 밖에 없음. 

메모리양이 부족하기 때문에 디스크에 잠시 옮기는데, 이때 디스크-메모리 왔다갔다 해서 정렬 작업은 느린거임.

4)users : 우리가 테이블을 저장할 수 있는 공간임. -> 즉 내가 사용할 수 있는곳은 users밖에 없음. 

2. user 생성

2.1. user 생성

#sys session 후 확인할 사항들

select * from dba_users; -- 유저네임이 이미 사용되고 있는지 확인

select * from dba_data_files; -- 어떤 tablespace있는지 확인

select * from dba_temp_files; --임시저장소 용량확인

#유저생성

create user olap

identified by oracle

default tablespace users    -- 아무땅에다 집을 지으면 안되니까. tablespace는 책장과 같고, 업무특성으로 나눠 

temporary tablespace temp   -- 임시저장소는 temp

quota 10m on users;     -- 땅을 할당함. users라는 tablespace에서 10Mbyte 정도 사용할 수 있게 함.

select * from dba_users;

#quota값 변경

alter user wondae

quota 0 on users; --회수

alter user wondae

quota unlimited on users; --무제한, 검색시 max_bytes가 -1로 표기됨.

#유저권한부여 --DCL(Data Control Language) : GRANT, REVOKE

session권한 부여/회수

grant create session to olap;

revoke create table from olap;

create권한 부여/회수

grant create table to olap;

revoke create table from olap;

select * from dba_sys_privs where grantee = 'OLAP'; --반드시 대문자

#심화# 유저가 테이블에 필요한 권한 2가지는?

1)create table 시스템 권한

select * from dba_user_privs where grantee = '유저명'; (<-> select * from user_sys_privs;)

2)tablespace의 quota사용 권한

select * from dba_users; (<->select * from user_users;) --tablespace가 users에 있는지 확인

select * from dba_ts_quotas; (<->select * from user_ts_quotas;) --할당한 저장용량 확인

#심화# ora-01536 space quota exceeded for tablespace 'users' 란 애러가 떴을 땐 어떻게 하는가?

유저에게 할당된 quota 값이 부족하거나, 0인 상태이므로 dba에게 quota값을 할당해달라고 요청한다.

#심화# 만약 dba가 sys아이디의 비밀번호를 잃어버리면?

os인증방식을 하면 됨.

로컬 os관리자로 로그인(즉, 서버컴퓨터의 슈퍼계정으로 로그인) -> cmd창 -> sqlplus / as sysdba (아이디비번입력안해도됨)

-> alter user sys identitied by 내가원하는비번;

3. user 삭제

3.1 유저 삭제

#유저 삭제

drop user olap;

#심화# 유저가 접속중이라 drop이 안될 때

sys session 후

select * from v$session; -- 여기서 내가 kill하고자 하는 sid와 serial의 숫자를 아래와 같이 입력함.

alter system kill session '52,59' immediate;

#심화# 유저가 테이블이 있어서 drop이 안될 때

drop user olap cascade; --cascade 하면 안에 있는 모든 테이블 삭제가 선행 실행된 후, 유저 삭제가 실행됨. 주의할 점으로, cascade하여 테이블을 삭제할 경우 복구가 불가능하다. 

4. 객체(테이블) 권한 부여

#hr유저의 테이블의 DML 권한 주기/회수

grant select on hr.employees to wondae; --hr테이블에서 실행하므로 사실상 hr.은 안붙여도 되지만 붙이는 습관을 들이자.

revoke select on hr.employees from wondae;

grant select, update, delete, insert on hr.employees to wondae; --merge는 안됨.

select * from user_tab_privs;