Or13Privileges

/********

# 파일명 : Or13Privileges.sql

# 사용자권한

# 설명 : 새로운 사용자를 생성하고 시스템권한을 부여하는 방법을 학습

********/

/*

[사용자 생성 및 권한설정]

- 해당 작업은 DBA권한이 있는 최고관리자(sys, system)로 접속후 해야한다.

[1]사용자 생성 및 암호 설정

사용법 : 

    create user 아이디 Identified by 비밀번호;

*/

create user fest_user1 Identified by 1234; --사용자계정 생성 성공

create user test_user1 Identified by 1234; --사용자계정 생성 성공

conn test_user1/1234;

conn hr/1234;

SQL > conn test_user1/1234;/*

    새로 생성한 사용자로 접속을 시도 : 접속 거부됨.

    -사용자는 생성이 되었으나 접속권한이 없기 때문에 접속이 거부된다.

    -사용자는 생성 직후에는 어떠한 권한도 가지지 못한다.

    -DB에 접속하려면 create session 권한을 부여받아야 한다.

*/

/*

[2] 사용자에 권한 혹은 역할부여

사용법 : 

    grant 시스템권한1[,시스템권한2....] | [역할1 [, 역할....]

        To 사용자1[,사용자2...] | [역할1 [,역할2....]

        [with grant option] <- 부여받은 시스템 권한을 다른 사용자에게

        부여할 수 있는 권한

- 사용자는 데이터베이스에 엑세스하기 위한 시스템권한(System Privilege)

이 필요하고, 데이터베이스 객체의 내용을 조작하기 위해 객체권한(Object Privilege)

이 필요하다.

- 시스템 권한은 종류가 200개 이상이고, 객체권한은 28개이다 (10g기준)

- 시스템 권한은 주로 DBA가 부여한다. (SYS /SYSTEM)

- DBA는 상급의 시스템 권한을 부여한다.

*/

--cmd창에서 실습

/*

* whith grant option을 사용하여 권한을 부여받은 사용자는 다른 사용자에게

자신의 권한을 부여할 수 있게 된다.

*/

--CMD창에서 실습

/*

2-1] 시스템 권한 부여

-시스템 권한 확인하기

SQL> 

select * from system_privilege_map;

-alter any 계열 : alter any table, alter any proceddure, alter any index,

alter user 등 (자체 스키마 뿐만 인라 모든 스키마에서 권한 있음)

-create any 계열 : create any table, create any procedure, create any index 등

(자체 스키마 뿐만 아니라 모든 스키마에서 권한있음)

_create 계열 : create session, create table, create view, create user 등

(자체 스키마에서만 권한있음)

create session : 접속할 수 있는 권한(conn 아이디/ 패스워듬)

create table : 테이블 생성 권한

create view : 뷰 생성 권한

create user : 사용자 생성권한

2-2] 객체 권한 부여

- 객체 권한 확인하기

SQL>

select * from table_privilege_map;

-객체 권한 시스템 권한을 주면 (create table) 자동으로 객체권한(insert/select/

delete/update)등이 부여된다.

사용예] grant create table, create session, create view to 사용자아이디 :

-> 사용자에게 접속, 테이블생성, 뷰 생성 권한을 부여함

[3] 암호변경 및 할당량 부여

-암호변경

alter user 사용자아이디 identified by 새암호;

-할당량 부여

create table 권한을 부여한 후 할당량을 부여받을 수 있다.

alter user 사용자아이디 quota 할당량 on user;

사용예] alter user test_user1 quota 5M on users;

[4] 권한이 없는 다른 사용자에게 특정 테이블 조회권한 부여하기

사용법 :

    grant select on 다른사용자아이디, 테이블 to 사용자아이디;

    

[5] ROLE(를)을 통해 여러가지 권한 동시에 주기

1) 롤의 기념개념과 사용법

: 여러 사용자가 다양한 권한을 효과적으로 관리할 수 있도록 관련된 권한끼리

묶어놓은것

롤의 종류

-DBA : 시스템 관리에 필요한 모든 권한을 부여함(전체권한)

-CONNECT : DB접속이 가능하도록 가장 기본적인 시스템권환 8가지를 그룹화함

(접속권한)

-RESOURCE : 사용자가 객체(테이블, 뷰, 인덱스)를 생성할 수 있도록 하기 위해

시스템 권한을 그룹화함(객체생성권한)

* 최초 사용자를 생성하면 기본적으로 Connect롤과 Resource롤을 부여한다.

*/

--CMD

/*

2) 롤 생성 : 사용자가 원하는 권한을 묶어 롤을 생성할 수 있다.

사용법 :

    create role 롤명칭;

3) 롤에 권한 부여

    사용법 :

        grant 권한1, 권한2,.....권한N to 롤명칭;

        

4) 새로운 사용자에게 롤 권한 부여

    사용법 :

        grant 롤명칭 to 사용자1, 사용자2.....;

        

5) 롤 제거

    사용법 :

        drop role 롤명칭;

        

-롤 확인하기 :

select * from role_sys_privs where role like '%롤명칭%';

[6] 권한제거

    사용법 

    revoke 권한 및 역할 from 사용자아이디:

    * 단, with grant option으로 부여된 권한은 취소되지 않는다.\

    

    

--CMD

[7] 사용자삭제

    사용법 :

        drop user 사용자아이디 cascade;

        

* cascade 를 명시하면 유저네임과 관련된 모든 데이터베이스 슼니마가

데이터사전으로부터 삭제되고 모든 스키마 객체도 물리적으로 삭제된다.

* 데이터사전이란 ? : 사용자가 데이터베ㅐ이스 지원을 효율적으로 관리하기 위한

다양한 정보를 저장하는 시스템 테이블의 집합이다. 사용자가 테이블을 생성/변경

작업등을 할 때 자동적으로 업데이트 된다.

* 사용자목록보기

SQL> desc dba_users;

SQL> select username from dba_users;

*사용자가 가진 시스템관련 보기

SQL> desc dab_sys_privs;

SQL> select privilege from dba_sys_privs where grantee=upper('test_user1');

*사용자계정 잠금풀면서 동시에 암호변경(hr. scott계정에서 사용한적 있음)

SQL> alter table 사용자아이디 account unlock identified by 비번;

SQL> alter table 사용자아이디 acount unlock; <- 계정잠금해제

SQL> alter table 사용자아이디 identified by 비번; <- 암호부여

*/