210611_1(데이터베이스7, 사용자관리, )
사용자관리 (권한)
권한 : 계정생성, 계정삭제, 권한부여 grant, 권한회수 revoke
일반 계정 발급 : create user 유저명 identified by "암호";
권한 부여 : grant 접속, 생성권한, ,,, to 유저명;
계정 전환 : conn 유저명/암호
conn system/oracle --관리자로전환
create user user1 identified by "1234";
conn user1/1234
ERROR:
ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied
conn system/oracle
grant create session to user;
conn user1/1234
연결되었습니다.
create table test(idx number(3));
*
1행에 오류:
ORA-01031: 권한이 불충분합니다
conn system/oracle
grant create table to user1;
conn user1/1234
create table test(idx number(3));
*
1행에 오류:
ORA-01950: 테이블스페이스 'SYSTEM'에 대한 권한이 없습니다.
권한을 부여하는데 끝이 없다.
결론. 그래서 계정을 만들때 가장 많이 사용하는 방법이 롤(여러개 권한을 묶은 role)을 이용한다.
대표적 롤 : connect, resource
conn system/oracle;
grant connect, resource to user1;
권한 정리
1. 데이터베이스 관리자(DBA) 같는 시스템 권한
create user : 유저 생성
drop user : 유저 삭제
drop any table : 임의의 테이블을 삭제하는 권한
query rewrite : 질의를 재작성 할 수 있는 권한
backup any table : 임의의 테이블을 백업할 수 있는 권한
2.데이터베이스 관리자(DBA) 갖는 to 유저 권한
create session : 접속 권한
create table : 사용자 스키마에 테이블을 생성할 수 있는 권한
create view : 사용자 스키마에 뷰를 생성할 수 있는 권한
create sequence : 사용자 스키마에 시퀀스를 생성할 수 있는 권한
create procedure : 사용자 스키마에 프로시져를 생성할 수 있는 권한
프로시져 정의 :
3. 데이터베이스 관리자(DBA)가 상기권한을 적용하거나 회수하는 권한 -- DCL문
grant 권한 to 유저;
revoke 권한 from 유저;
with admin option
부여받은 권한을 다시 부여할수있는 권한
grant 권한 to 유저 with admin option ;
1. user1 에게서 접속권한을 회수하라, 롤도 회수
revoke create session from user1;
revoke connect, resource from user1;
user1으로 접속시도
conn user1/1234
1행에 오류:
ORA-01031: 권한이 불충분합니다
2. user1에게서 테이블 생성 권한을 회수하라
revoke create table from user1;
3. user2 를 생성하시오
create user user2 identified "1234";
4. user2 접속권한 부여하되 with admin option옵션을 추가하시오
grant create session to user2 with admin option;
5. user2가 user1 접속 권한을 부여하시오
conn user2/1234
grant create session to user1;
6. user1로 접속을 시도해보시오
conn user1/1234
7. user2 에게서 접속 권환 회수
revoke create session from user2;
1행에 오류:
ORA-01031: 권한이 불충분합니다
8. user1로 접속을 시도해보시오
--user1에게 권한 -- user1 이 user2에게 권한 -- user1의 권한을 뺏음 -- user2의 권한 확인
grant create session to user1 with admin option;
conn user1/1234
grant create session to user2;
conn system/oracle
revoke create session from user1;
conn user2/1234
확인
-- 일단 부여된 권한은 권한을 주었던 유저의 권환이 무효화되어도 유효하다.
객체 권한 부여 / 회수
객체 table, view, index,,,,,,,
객체권한 : select update ,,,,,,,,,
기본형 : grant 객체권한 on 객체 to 유저;
revoke 객체권한 on 객체 from 유저;
1.user1으로 접속
conn user1/1234
2.emp테이블에서 이름, 사번을 출력하라
select ename, empno from scott.emp;
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
3.scott으로 접속
conn scott/tiger
4.user1에게 emp테이블에게 select 권한을 부여한다
grant select on emp to user1;
5. user1 접속
conn user1/1234
6. emp테이블에서 이름,사번을 출력하라 스키마? 소유자.테이블명
select ename, empno from scott.emp;
객체권한 정보보기
desc user_tab_privs_made : 부여한 권한
desc user_tab_privs_recd : 부여받은 권한
desc user_tab_privs_recd;
이름 널? 유형
----------------------------------------- -------- ----------------------------
OWNER 소유자 NOT NULL VARCHAR2(30)
TABLE_NAME 객체 NOT NULL VARCHAR2(30)
GRANTOR 부여한자 NOT NULL VARCHAR2(30)
PRIVILEGE 권한 NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
column owner format a20
column TABLE_NAME format a20
column GRANTOR format a20
column PRIVILEGE format a20
--user1의 부여 받은 권한
select OWNER, TABLE_NAME,GRANTOR, PRIVILEGE from user_tab_privs_recd;
save recd;
@recd
OWNER TABLE_NAME GRANTOR PRIVILEGE
-------------------- -------------------- -------------------- --------------------
SCOTT EMP SCOTT SELECT
-- scott의 부여한 권한
SQL> conn scott/tiger
SQL> desc user_tab_privs_made
이름 널? 유형
----------------------------------------------------------------- -------- --------------------------------------------
GRANTEE 부여받은사람 NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR 부여한사람 NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
column GRANTEE format a20
column TABLE_NAME format a20
column GRANTOR format a20
column PRIVILEGE format a20
select GRANTEE, TABLE_NAME,GRANTOR, PRIVILEGE from user_tab_privs_made ;
save made;
@made
GRANTEE TABLE_NAME GRANTOR PRIVILEGE
-------------------- -------------------- -------------------- --------------------
USER1 EMP SCOTT SELECT
객체 권한 회수
1. user1 부터 emp에 대하여 select 권한을 회수하시오 - 준놈이 뺏아야한다
conn scott/tiger
revoke select on emp from user1;
다른이로부터 부여받은 객체권한을 부여하는 옵션 - with grant option
기본형 : grant 객체권한 on 객체 to 유저 with grant option
1. user3/1234 를 생성하라
conn system/oracle
create user user3 identified by "1234";
2. scott 은 user2에게 emp에 대한 select 권한을 부여하되 with grant option을 같이줘라
conn scott/tiger
grant select on emp to user2 with grant option;
3.user2는 emp에 대하여 select 권한을 user3에게 줘라
conn user2/1234
grant select on scott.emp to user3 with grant option; --스키마를 이용해서
4.user3 전환
conn user3/1234
5.emp에서 사번, 이름, 급여를 출력하라
select empno, ename, sal from user2.emp -- X
select empno, ename, sal from scott.emp -- O
6. user4/1234 생성
conn system/oracle
create user user4 identified by "1234";
grant create sessiont to usert4;
7. user3가 user4에게 emp에 대하여 select 권한을 부여하되 with grant option을 같이줘라.
conn user3/1234
grant select on emp to user4 with grant option;
데이터베이스 롤 - role
롤이란, 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러개의 권한을 묶어 놓은것을 롤이라한다.
conn system/oracle
desc dba_sys_privs ;
이름 널? 유형
----------------------------------------------------------------- -------- --------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
select * from dba_sys_privs; --400개 행
select count (distinct grantee) from dba_sys_privs; --29개
select * from dba_sys_privs where grantee in ('CONNECT','RESOURCE');.
GRANTEE PRIVILEGE ADMIN_
-------------------- -------------------- ------
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
CONNECT CREATE SESSION NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
RESOURCE -- 8개의 권한을 묶어 놓은것
그래서 계정생성시
conn system/oracle
create user 유저명 identified by "암호";
grant connect, resource to 유저 ; <=== 롤부여
DBA 롤 관리자롤 부여하기
---정보
select * from dba_sys_privs where grantee in ('DBA'); --160개의 권한이 포함
conn system/oracle
create user master identified by "master";
grant dba to master;
롤만들기
기본방법 :
1. create role 롤명;
2. grant 권한 to 롤명 ; -- 롤에 권한을 부여한다.
3. grant 객체권한 on 객체 to 롤명; -- 롤에 객체권한을 부여한다
4. grant 롤명 to 유저 ; -- 롤을유저에게 부여한다
5. revoke 롤명 from 유저명 -- 롤 회수
실습
1. 관리자 전환
conn system/oracle
2. mrole 생성
create role mrole;
3. scott 전환
conn scott/tiger
4. emp에 대하여 select 권한을 mrole에 부여
grant select on emp to mrole;
5. 관리자 전환
conn system/oracle
6. user5/1234 생성, 접속 부여
create user user5 identified by "1234";
grant create session to user5;
mrole을 user5에게 부여
grant mrole to user5;
7. user5 전환
conn user5/1234
8. scott.emp에서 이름 사번 급여를 출력해보자
select ename, empno, sal from scott.emp;
롤이 적용된 테이블 정보보기 및 회수
select * from role_tab_privs where table_name in ('테이블명');
conn system/oracle
select * from role_tab_privs where table_name in ('EMP');
ROLE OWNER TABLE_NAME
---------------------------------------------------- -------------------- --------------------
COLUMN_NAME PRIVILEGE GRANTA
---------------------------------------------------- -------------------- ------
MROLE SCOTT EMP
SELECT NO
실습2
1. 관리자 전환
conn system/oracle
2. def_role 생성
create role def_role;
3. 접속 , 테이블생성 권한롤을 def_role에 부여
grant create session, create table to def_role;
4. scott 전환
conn scott/tiger
5. emp에서 수정,삭제,조회 의 객체권한을 def_role에 부여
grant select,delete,update on emp to def_role;
6. 관리자 전환
conn system/oracle
7. usera1 usera2 usera3 를 생성하고 암호는 모두 1234
create user usera1 identified by "1234";
create user usera2 identified by "1234";
create user usera3 identified by "1234";
8. usera1 usera2 usera3 에게 def_role 부여
grant def_role to usera1,usera2,usera3;
9. usera1 usera2 usera3 로 각각 접속하여 emp에서 사번,이름, 입사일자를 출력해보시오.
conn usera1/1234
select empno, ename, hiredate from scott.emp;
conn usera2/1234
select empno, ename, hiredate from scott.emp;
conn usera3/1234
select empno, ename, hiredate from scott.emp;
동의어 (synonym)
synonym -- 축약 별칭
SCOTT.emp 스키마 구조 --> emp 처럼 축약 해서 사용하는것
소유구조
기본형
동의어 : create synonym 별칭 for 스키마.객체 -- 특정 대상에 부여할때
공개 동의어 : create public synonym 별칭 for 스키마.객체 -- 복수의 대상에게 부여할때 - 관리자만 생성 가능
참조) 스키마(Schema)
컴퓨터과학에서 데이터베이스 스키마는 데이터베이스에서 자료의 구조,
자료의 표 방법, 자료 간의 관계를 형식 언어로 정의한 구조이다.
데이터베이스 관리시스템(DBMS))이 주어진 설정에 따라 데이터베이스 스키마(database schema)를 생성하며,
데이터베이스 사용자가 자료를 저장, 조회, 삭제, 변경할 떄 DBMS는 자신이 생성한 데이터베이스 스키마를
참조하여 명령을 수행한다.
스키마는 3층 구조로 되어있다.
외부 스키마(External Schema) : 프로그래머나 사용자의 입장에서 데이터베이스의 모습으로
조직의 일부분을 정의한 것
개념 스키마(Conceptual Schema) : 모든 응용 시스템과 사용자들이 필요로하는 데이터를
통합한 조직 전체의 데이터베이스 구조를 논리적으로 정의한것.
내부 스키마(Internal Schema) : 정체 데이터베이스의 물리적 저장 형태를 기술하는것
실습
1. 관리자 전환
conn system/oracle
2. test_role 생성
create role test_role;
3. test_role에게 connect,resource 롤 부여
grant connect, resource,create synonym to test_role;
4. scott 전환
conn scott/tiger
5. emp의 select 권한을 test_role에 부여
grant select on emp to test_role;
6. dept의 select 권한을 test_role에 부여
grant select on dept to test_role;
7. 관리자 전환
conn system/oracle
8. userb1, userb2 유저 생성 암호 1234
create user userb1 identified by "1234";
create user userb2 identified by "1234";
9. userb1에게 test_role 부여
grant test_role to userb1,userb2
10. userb1 전환
conn userb1/1234
11. scott.dept를 dept로 scott.emp를 emp로 접근하는 동의어 생성
create synonym emp for scott.emp;
create synonym dept for scott.emp;
12. 동의어를 이용해서 select 해봄
select * from emp;
select * from dept;
---------------------------------------
동의어 생성했던 userb1에서는 12번이 실행되었지만
생성하지 않았던 b2에서는 ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 라고 출력됨.
공개동의어
누구나 사용하는 동의어 : 공개동의어는 관리자가 생성하고 제거할수 있다.
create public synonym 별칭 for 스키마.객체 -- 복수의 대상에게 부여할때 -관리자만 생성 가능
1.관리자 전환
conn system/oracle
2.scott의 dept에 대하여 select를 공개동의어 pub_dept를 만든다
create public synonym pub_dept for scott.dept;
3.scott전환 해서 usera3 에게 select 권한주기
conn scott/tiger
grant select on dept to usera3;
3.usera3 전환
conn usera3/1234
4.scott의 dept에서 select 해봄
select * from pub_dept;
--잘된다
동의어삭제
conn userb1/1234
drop synonym dept,emp;
conn system/oracle
drop synonym dept,emp;
-------------------------------생성한 계정이 삭제를 해야한다.