사용자 관리

계정은

1.관리자 계정 - sys , system

2.일반 계정 - scott , HR 등등..

으로 나뉜다.

일반계정을 추가 생성하는 경우는 공식으로 정해진 것은 없지만 보통 새로운 업무가 생성될 때.

User 와 Schema

우리가 Oracle Server 에 접속하기 위해 conn scott/tiger 할 때 scott 이 user 이다.

즉, User는 오라클 서버에 접속하기 위해 사용하는 것.

Schema란 특정 사용자(User) 가 만들어놓은 모든 Object 의 집합이다.

ex) 오라클 설치 시 연습용으로 제공되는 HR schema 를 예로들면 Oracle Server 안에 HR 계정으로 로그인해서 만들어 놓은 모든 것을 다 모으면 그것을 HR schema 라고 하는 것이다. table, index, view, constraint, trigger, dblink, synonym, sequence 등을 통틀어서 HR schema.

새로운 사용자 생성 순서

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

2. 생성할 사용자가 사용하는 temporary tablespace를 결정하고 먼저 생성.

3. 사용자 생성.

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

실습해보자.

실습1. 사용자 생성

실습2. Password 관련 profile 생성

실습3. Resorce 관련 profile 만들기

실습1. 사용자 생성

신규 서비스로 웹하드 서비스를 런칭했다고 가정.

DBA는 Oracle DB 서버에 해당 서비스를 할 수 있도록 작업.

TS_WEBHARD라는 Tablespace 생성하고 웹하드 관련 테이블은 모두 이곳에 저장하기로.

TS_WEB_IDX Tablespace 에 웹하드 관련 인덱스 저장하기로.

(1) webuser 의 default tablespace 생성하기

create tablespace ts_webhard datafile '/app/oracle/oradata/testdb/ts_web01.dbf' size 100M ;

create tablespace ts_web_idx datafile '/app/oracle/oradata/testdb/ts_web_idx01.dbf' size 10M ;

확인

select tablespace_name , bytes/1024/1024 MB , file_name from dba_data_files ;

(2) Temporary tablespace 생성하기

create temporary tablespace temp_web tempfile '/app/oracle/oradata/testdb/temp_web01.dbf' size 10M ;

(3) 사용자 생성

create user webuser identified by webpwd -> 아이디 패스워드

default tablespace ts_webhard -> 기본 테이블스페이스

temporary tablespace temp_web -> 임시 테이블스페이스

quota unlimited on ts_webhard -> 할당량 지정 여기선 무제한

quota 0m on system ; -> 시스템 테이블스페이스 사용 못하게

(4) 권한 설정

grant resorce , connect to webuser ;

접속확인

conn webuser/webpwd

사용자 정보 확인하기

default tablespace 와 temporary tablespace 정보 확인하기

set line 200

col default_tablespace for a10

col temporary_tablespace for a10

select username , default_tablespace "Default TS" , temporary_tablespace "Temp TS" from dba_users where username='WEBUSER' ;

profile 관리하기

사용자 계정의 행동에 제약사항을 두기 위해 사용하는 경우가 대부분.

ex) 10분 이상 활동 없으면 접속 강제종료. 10일마다 암호 다른것으로 바꾸게 강제 적용.

※ 관련 파라미터

Failed_login_attempts : 로그인 시도 실패 후 계정 잠그기. ex) Failed_login_attempts 5

5번 실패 후 6번째부터는 로그인 못하게.

Password_lock_time : attempts 에서 계정이 잠기면 며칠 동안 잠글것인지. 단위:일. 

DBA가 unlock 해서 사용 가능.

Password_life_time : 동일한 암호를 며칠간 사용하게 할 것인지. 단위:일.

Password_grace_time : life_time 에서 만료되어도 이 파라미터에 지정된 값 만큼

더 암호를 변경할 기간을 허용.

Password_reuse_time : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간.

Password_reuse_max : 동일한 암호를 재사용 할 경우 최대 사용 가능 횟수.

Password_verify_function : 암호를 복잡하게 만들기 위해 특정 함수를 적용. 점검.

오라클에서 만들어 둔 기본 함수를 사용할 수도 있고 사용자가 직접 만들기도 가능.

이 함수를 사용할 경우 아래 사항 확인.

-암호는 최소 4글자 이상

-암호는 사용자 계정과 달라야함

-하나의 특수문자나, 알파벳, 숫자가 포함

-이전 암호와 3글자 이상 달라야 함

실습2. Password 관련 profile 생성

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

조건2. 계정 암호는 10일에 한 번씩 변경하게

조건3. 동일한 암호는 10일동안 사용 못하게

create profile sample_prof limit

failed_login_attempts 5

password_lock_time 5

password_life_time 10

password_reuse_time 10;

Resorce profile 관련 파라미터

이 profile 사용 위해서는 resorce_limit=true 설정이 되어있어야 함

startup 시 사용되는 파라미터 파일에 위 문장을 적어놓거나

alter system set resorce_limit=true; 실행.(9i 이상 버전)

CPU_PER_SESSION : SESSION 이런 1명의 접속을 의미. 이 파라미터는 하나의 세션이 CPU를 연속적으로 사용할 수 있는 최대 시간을 설정. 무한루프 같은 쿼리가 작동되면 혼자서 CPU를 연속적으로 점유하기 때문에 그런일을 막고자. 1/100초 단위.

SESSIONS_PER_USER : 하나의 사용자 꼐정으로 몇 명의 사용자가 동시에 접속할 수 있는지.

CONNECT_TIME : 하루동안 DB Server에 접속할 수 있는 총 시간 설정.

IDLE_TIME : 연속적으로 휴면 시간이 이 값을 넘으면 접속 해제하고 commit 안된 작업은 모두 rollback한다. 사용자가 서버에 접속하면 서버 프로세스가 생성되고 PGA가 할당되기 때문에.

LOGICAL_READS_PER_SESSION : 한 세션에서 사용 가능한 최대 블락 수.

PRIVATE_SGA : MTS / shared server 일 경우 해당 세션의 SGA 사용량을 bytes 단위로 설정.

CPU_PER_CALL : 하나의 call 당 cpu를 점유할 수 있는 시간. 1/100초 단위.

LOGICAL_READS_PER_CALL : 하나의 call 당 읽을 수 있는 블락 개수 지정.

실습3. Resorce 관련 profile 만들기

alter system set resource_limit=true;

※ Resource profile 사용 위해서는 resorce_limit=true 설정이 되어있어야 함

조건1. 1명당 연속적으로 CPU를 사용할 수 있는 시간을 10초로 제한

조건2. 하루 중 8시간만 DB에 접속 가능하게

조건3. 10분동안 사용하지 않으면 강제로 접속 해제

사용자에게 profile 할당하기

위에서 만든 profile을 webuser 에게 설정

현재 모든 사용자가 적용 받고 있는 profile 확인

set pagesize 50

select username "사용자명" , profile "적용 프로파일" from dba_users where username='WEBUSER';

해당 profile 에 어떤 내용이 있는지 확인하기 (sample_prof 내용 조회)

set line 200

col profile for a13

col resource_name for a30

col resource for a10

col limit for a10

select * from dba_profiles where profile='SAMPLE_PROF' ;

re_sample_prof 내용 조회하기

col profile for a15

select * from dba_profiles where profile='RE_SAMPLE_PROF' ;

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

alter user webuser profile sample_prof;

alter user webuser profile re_sample_prof;

select username,profile from dba_users where username='WEBUSER';

※ 여러 개의 프로파일을 적용시킬 순 없다. 처음부터 원하는 파라미터를 전부 넣고 한꺼번에 만들고 그것을 적용시켜야 함

사용 안하는 profile 삭제하기

현재 사용자에게 할당되어있는 profile은 기본적으로 삭제가 안된다.

그러나 cascade 옵션으로 삭제하면 삭제되고 그 프로파일을 사용하던 사용자는 기본값 프로파일을 사용하게 된다. default profile.

drop profile re_sample_prof cascade;

select username, profile from dba_users where username='WEBUSER';

privilege (권한) 관리

profile -> 하지 못하게 하는 것이라면

privilege -> 할 수 있게 하는 것

권한은 크게 system 관련 : system 전체에 영향을 주는 권한

object 관련 : 특정 object에 관련된 권한 으로 나뉜다.

일반 OS -> 계정,암호

Oracle -> 계정,암호,권한

(1) SYSTEM 관련 주요 권한

(2) SYSOPER / SYSDBA 권한

(3) SYSTEM 관련 권한 할당/해제

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

(5) Object 관련 권한

(6) Object 권한 할당/해제

Role 관리

Role 은 권한 꾸러미이다. 여러 권한을 한번에 넘겨줄 수 있다.

(1) Role 생성하기

(2) Role에 create session , create table 권한 할당하기

(3) scott 사용자에게 trole 할당하기

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

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