사용자 관리

사용자 - 관리자 : sys , system      DB처리, 장애처리 용도로 사용

    - 사용자 : scott 등..           작업을 수행하는 용도로 사용

새로운 사용자를 생성하는 순서

1) 생성할 사용자의 default tablespace 를 결정하고 해당 tablespace 를 생성

2) 생성할 사용자의 temporary tablespace 를 결정하고 해당 tablespace 를 생성

3) 사용자를 생성

4) 적절한 프로파일과 권한 , 롤 등을 생성한 후 할당

실습 1. 사용자 생성하기

웹하드 서비스를 위한 계정 생성

default tablespace 로 ts_webhard 라는 tablespace를 생성  ( 웹하드 관련 테이블은 모두 이곳에 저장 )

ts_web_idx tablespace 에 웹하드 관련 인덱스 저장

계정 명을 webuser , 암호는 webpwd

권한은 일반적인 사용자 권한 전부 부여

정렬 작업등을 위한 temporary tablespace 는 temp_web 으로 결정

실습시작  

(1) webuser 의 default tablespace 생성

sqlplus / as sysdba

SYS> create tablespace ts_webhard

   2    datafile '/app/oracle/oradata/testdb/ts_webhard01.dbf' size 100M ;       

SYS> create tablespace ts_web_idx

   2    datafile '/app/oracle/oradata/testdb/ts_web_idx' size 10M ;

SYS> select tablespace_name , bytes /1024/1024 MB , file_name

   2    from dab_data_files ;                   <--생성 확인

(2) temporary tablespace 생성

SYS> create temporary tablespace temp_web

   2    tempfile '/app/oracle/oradata/testdb/temp_web01.dbf' size 10M ;

(3) 사용자 생성

SYS> create user webuser

   2    identified by webpwd                           <-- 암호 지정부분

   3    default tablespace ts_webhard              <-- default tablespace 를 지정하는 부분

   4    temporary tablespace temp_web           <-- temporary tablespace 를 지정하는 부분

   5    quota unlimited on ts_webhard              <-- 할당량을 지정하는데 무제한으로 설정

   6    quota 0m on system ;                          <-- system tablespace 를 사용못하게 설정

(4) 권한 설정

SYS> grant resource , connect to webuser ;

SYS> conn webuser/webpwd       <-- 접속 되는지 확인

사용자 정보 확인하기

SYS> select username , default_tablespace "Default TS" , temporary_tablespace "Temp  TS"

   2    from dba_users

   3    where username='WEBUSER" ;

Profile 관리하기

• Failed_login_attempts : 설정된 횟수만큼 login 을 시도한 후 실패 시 계정 잠금
• Password_lock_time : 위 1번에서 계정이 잠기면 며칠 동안 잠글 것인지 기간을 정하는 파라미터
• Password_life_time : 동일한 암호를 며칠간 사용할 것인지 설정하는 파라미터
• Password_grace_time : 위 3번항목에서 만료되어도 이 파라미터에서 지정된 값 만큼 암호를 변경할 기간을 허용
• Password_reuse_time : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간, 같은 암호 사용 못하게 할때 쓰는 파라미터
• Password_reuse_max : 동일한 암호를 위 5번 설정을 피해 재사용할 경우 최대 사용 가능한 횟수를 지정하는 파라미터
• Password_verify_function : 암호를 복잡하게 만들기 위해 특정함수를 적용시켜 사용자가 입력한 암호가  암호로 적합한지 점검

- verify_function 함수 사용시 주의사항

• 암호는 최소 4글자 이상
• 암호는 사용자 계정과 달라야함
• 암호는 하나의 특수문자나 , 알파벳, 숫자가 포함되어야 함
• 암호는 이전 암호와 3글자 이상 달라야 함

실습 2. Password 관련 profile 생성

- 조건 1 : 로그인 시도 5회 실패시 계정을 5일동안 사용 못하게

- 조건 2 : 계정의 암호는 10일에 한 번씩 변경하게 할 것

- 조건 3 : 동일한 암호는 10일 동안 사용 못하게 할 것

SYS> create profile sample_prof limit

   2    failed_login_attempts 5              <-- 로그인 실패 제한 5회

   3    password_lock_time 5                <-- 로그인 실패시 5일동안 사용 제한

   4    password_life_time 10                <-- 계정 암호 10일에 한번씩 변경

   5    password_reuse_time 10 ;           <-- 동일 암호 10일동안 사용 못함

Resource profile 관련 파라미터

• CPU_PER_SESSION : SESSION 이란 1명의 접속을 의미, 하나의 SESSION 이 CPU를 연속적으로 사용할 수 있는 최대시간을 설정
• SESSIONS_PER_USER : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속할 수 있는지 설정하는 파라미터
• CONNECT_TIME : 하루동안 DB Server 에 접속할 수 있는 총 시간
• IDLE_TIME : 연속적으로 휴면시간이 이 값을 넘으면 접속을 해제, 접속 해제시 commit 안된 작업은 전부 rollback 됨
• LOGICAL_READS_PER_SESSION : 한 session 에서 사용 가능한 최대 block수 지정
• PRIVATE_SGA : MTS / shared server 일 경우 해당 session 의 SGA 사용량을 bytes 단위로 설정
• CPU_PER_CALL : 하나의 call 당 cpu 를 점유할 수 있는 시간 1/100초 단위
• LOGICAL_READS_PER_CALL : 하나의 call당 읽을 수 있는 block 의 개수 지정

실습 3. Resource 관련 profile 만들기

- 조건 1 : 1명당 연속적으로 cpu를 사용할 수 있는 시간 10초 제한

- 조건 2 : 하루 중 8시간만 db에 접속 가능하게 할 것

- 조건 3 : 10분 동안 사용하지 않으면 강제로 접속 끊음

SYS> create profile re_sample_prof limit

   2    cpu_per_session 1000          <-- 1명당 사용할수 있는시간 10초 제한        1/100초 단위임

   3    connect_time 480                <-- 60*8  8시간만 db에 접속 가능하게함

   4    idle_time 10 ;                      <-- 10분 사용제한

사용자에게 profile 할당하기

1. 현재 사용자가 적용받고 있는 profile 확인하기

SYS> select username, profile

   2    from dba_users

   3    where username= "WEBUSER" ;

USERNAME            PROFILE

----------           ---------

WERBUSER            DEFAULT           <--현재 아무profile 도 없음

2. 해당 profile 에 어떤 내용이 있는지 확인

- 위에서 만든 sample_prof 내용 조회 

SYS> select * from dba_profiles

   2    where profile="SAMPLE_PROF" ;

여러개 쭉 나오고 아래와 같은 옵션을 설정한 것도 나타남

         failed_login_attempts 5             

         password_lock_time 5               

         password_life_time 10                

         password_reuse_time 10 

3. 사용자에게 profile 적용시키고 확인하기

SYS> alter user webuser profile sample_prof ;

SYS> select username, profile

  2    from dba_users

  3    where username="WEBUSER" ;

USERNAME            PROFILE

---------            --------------

WEBUSER               SAMPLE_PROF

단 profile 을 적용시킬때 여러개의 profile 은 적용안되니 원하는 파라미터를 profile에 전부넣고 한번에 만들어야함

4. 사용안하는 profile 삭제하기

SYS> drop profile sample_prof ;

-> error 남          이유는 현재 사용자에게 할당된 profile 은 삭제가 안됨, 그러나 cascade 옵션으로 삭제하면 삭제가능

SYS> drop profile sample_prof cascade ;

-> 이렇게 삭제하고 나면 사용자는 default profile 을 사용하게 됨

privilege ( 권한 ) 관리하기

profile 은 사용자가 어떤 것들을 하지 못하게 제한하는 것이 목적 

privilege 는 사용자에게 어떤 것들을 할 수 있게 허락해주는 것이 목적

- system 관련 privilege : system 전체에 영향을 주는 권한들을 모아놓은 것

            - object 관련 privilege : 특정 object 에 관련된 privilege

SYSTEM 관련 주요 privilege
대분류	privilege	설명
Index	Create any index	소유자에 상관없이 모든 테이블에 인덱스를 생성할 수 있는 권한
	Drop any index	소유자에 상관없이 모든 인덱스를 삭제할 수 있는 권한
	Alter any index	소유자에 상관없이 모든 인덱스를 수정할 수 있는 권한
table	create table	자신의 테이블을 생성할 수 있는 권한
	create any table	소유자에 상관없이 다른 user 이름으로 테이블을 생성할 수 있는 권한
	alter any table	소유자에 상관없이 모든 테이블의 구조를 수정할 수 있는 권한
	drop any table	소유자에 상관없이 모든 사용자의 테이블을 삭제할 수 있는 권한
	update any table	소유자에 상관없이 모든 사용자의 테이블을 업데이트할 수 있는 권한
	delete any table	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삭제 할 수 있는 권한
	insert any table	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삽입 할 수 있는 권한
session	create session	서버에 접속할 수 있는 권한
	alter session	접속 상태에서 환경값을 변경할 수 있는 권한
	restricted session	restricted 모드로 open 된 db에 접속할 수 있는 권한
tablespace	create tablespace	tablespace를 만들 수 있는 권한
	alter tablespace	tablespace를 수정할 수 있는 권한
	drop tablespace	tablespace를 삭제할 수 있는 권한
	unlimited tablespace	tablespace 사용 용량을 무제한으로 허용하는 권한, 즉 quota 옵션적용을 받지않음

SYSOPER /SYSDBA privilege
privilege	할 수 있는 일
SYSOPER	startup / shutdown
	alter database mount / open
	alter database backup control file to ....
	recover database
	alter database archivelog
	restricted session
SYSDBA	SYSOPER privilege with admin option
	create database
	alter tablespace ... begin backup / end backup
	recover database until

SYSTEM 관련 권한 할당하기 / 해제하기

 - scott 사용자에게 create table , create session 권한을 할당

SYS> grant create table, create session to scott ;

 - scott 사용자에게 create table 권한을 해제

SYS> revoke create table from scott ;

사용자가 가지고 있는 권한 조회

 - scott 사용자가 가지고 있는 모든 권한 조회

SYS> select * from dba_sys_privs

  2    where grantee = "SCOTT" ;

Object 관련 Privilege 

주로 DML 과 연관이 많음. 즉 object 를 select , insert , update , delete 등을 할 수 있는 권한

object 권한 할당하기 / 해제하기   ( 실습 전에 webuser 계정으로 webtest 테이블을 생성하고 해야함 )

 - scott 사용자에게 webuser 가 만든 webtest 테이블을 select 할 수 있도록 허용

SYS> grant select on webuser.webtest to scott ;

- scott 사용자에게 webuser 가 만든 webtest 테이블을 update 할 수 있도록 허용, 그리고 scott 사용자가 이 권한을 다른 사람에게 줄 수 있는 권한도 부여

SYS> grant update on webuser.webtest to scott with grant option ;

- scott 사용자가 가진 webuser의 webtest 테이블을 select 하는 권한을 해제

SYS> revoke select on webuser.webtest from scott ;

Role 관리하기

role 속에 여러가지 권한을 넣어두고 사용자에게 role 을 부여하면 여러가지 권한을 갖게됨

(1) role 생성하기

SYS> create role trole ;

(2) role 에 create session , create table 권한 할당

SYS> grant create session , create table to role ;

(3) 사용자에게 trole 할당

SYS> grant trole to scott ;

(4) 어떤 사용자가 어떤 role 을 사용하는지 확인

SYS> select * from dba_role_privs where grantee='SCOTT' ;                   <-- SCOTT 계정에 어떤 role 이 있는지 확인

(5) 어떤 role 에 어떤 권한이 있는지 확인

SYS> select * from dba_sys_privs where grantee = 'CONNECT' ;              <--CONNECT 에 어떤 권한이 있는지 확인