User 관리하기

user Management

◆ Schema란?

- 특정 사용자가 생성한 object의 집합을 의미

- user가 생성될 때 관련 schema가 생성

- user와 schema를 혼용해서 사용하기도 함

Schema Object ( Table, Indexes, Views, Triggers, Constraints, Database links, Synonyms, Stored Procedures, Sequences )

* tablespace는 미리 생성 해놓아야 함!!!!!

 

◆ User 생성하기

1. 사용자를 생성하는 목적을 정리

2. 해당 사용자의 default tablespace를 결정하고 해당 테이블 스페이스를 먼저 생성

CREATE USER user_name IDENTIFIED BY password DEFAULT TABLESPACE taplespace_name TEMPORARY TABLESPACE temp_tablespace_name quota unlimited on tablespace_name quota 0m on system;

 

-- User 생성 실습

-- SMS 서비스용 user 생성

-- 계정명 : smsuser / passwd: smspwd

-- default tablespace : TS_SMS

-- temporary tablespace : temp_sms

-- quota : ts_sms : unlimited / system : 0m

--1. 테이블 스페이스 생성

CREATE TABLESPACE TS_SMS

datafile '/home/oracle/oradata/testdb/ts_sms.dbf' size 10M;

 

--2. temp 테이블 스페이스 생성

CREATE TEMPORARY TABLESPACE temp_sms

tempfile '/home/oracle/oradata/testdb/temp_sms.dbf' size 10M;

 

--3. user 생성

CREATE USER smsuser IDENTIFIED BY smspwd

DEFAULT TABLESPACE TS_SMS

TEMPORARY TABLESPACE temp_sms

quota unlimited on TS_SMS

quota 0m on system;

 

--선언된 link 확인

select * from dba_db_links;

 

--db link 생성

create public database LINK yang

CONNECT TO scott IDENTIFIED BY "imsi00"

using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)

(HOST=172.16.1.53)(PORT=1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL2)))';

-- scott계정에 모두 다 들어갈 수 있어서 public으로 선언 잘 안함

 

--DB Link 삭제

drop public database link yang;

 

--yoon 계정의 emp 조회

select * from emp@yang;

 

select * from dba_tables

where table_name='TEST';

 

select * from dba_synonyms

where synonym_name='TEST';

 

create or replace public synonym test for test@yang; -- synonym을 등록

 

select * from test; -- synonym 설정 후 하면 출력됨

 

select * from test@yang;

 

◆ Profile 관리하기

●Password 관련

1) FAILED_LOGIN_ATTEMPTS : LOGIN 시도를 실패할 경우 계정을 잠그는데 여기 설정된 횟수

2) PASSWORD_LOCK_TIME : 위에서 계정이 잠기면 며칠 동안 잠글 것 인지 기간을 정하는 파라미터 (일 단위)

-일수와 무관하게 DBA가 UNLOCK 할 수 있음

3) PASSWORD_LIFE_TIME : 동일한 암호를 며칠간 사용하게 할 것인지 설정하는 파라미터. (일 단위)

이 기간이 지나도 안 바꾸면 다음 로그인 할 때 강제로 바꾸게 프롬프트를 보여줌

4) PASSWORD_GRACE_TIME : 위 3번 항목에서 만료되어도 이 파라미터에 지정된 값 만큼 더 암호를 변경할 기간을 허용

5) PASSWORD_REUSE_TIME : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간 즉, 암호를 변경하라고 했는데 사용자가 동일한

암호를 다시 사용하려고 할 수 있기에 같은 암호를 다시 쓸 수 없게 만들어야 할 때 사용하는 파라미터

6) PASSWORD_REUSE_MAX : 동일한 암호를 위 5번 설정을 피해 재사용을 할 경우 최대 사용 가능한 횟수를 지정하는 파라미터

7) PASSWORD_VERIFY_FUNCTION : 암호를 보다 복잡하게 만들기 위해 특정 함수를 적용시켜 사용자의 암호를 점검 할 수 있음.

ORACLE에서 만들어 둔 기본 함수인 VERIFY_FUNCTION을 사용할 수 있고 사용자가 별도의

함수를 이용할 경우 어떤 조건을 점검하는 가

· 암호는 최소한 4글자 이상 되어야 한다

· 암호는 사용자 계정과 달라야 한다

· 암호는 하나의 특수문자나, 알파벳, 숫자가 포함되어야 한다

· 암호는 이전 암호와 3글자 이상 달라야 한다

-- password 관련 profile 생성하기

-- 다음의 profile을 만드시오. profile이름은 profile_sms

--smsuser에 profile_sms를 적용

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

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

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

--profile 생성

CREATE PROFILE profile_sms LIMIT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 5

PASSWORD_LIFE_TIME 15

PASSWORD_REUSE_TIME 15;

--user‘s profile 변경

ALTER USER SMSUSER PROFILE profile_sms;

 

◆ 리소스 관련 PROFILE

(1) CPU_PER_SESSION : 하나의 세션이 CPU를 연속적으로 사용할 수 없는 최대 시간을 설정합니다. 무한루프 같은 쿼리가 작동되면 혼자 서 CPU를 연속적으로 점유하기 때문에 이 설정을 사용해서 그런 일을 막고자 하는 것입니다. 1/100초 단위입니다.

(2) SESSIONS_PER_USERS : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속 할 수 있는지를 설정하는 파라미터입니다

(3) CONNECT_TIME : 하루 동안 DB SERVER에 접속을 허락하는 총 시간을 설정합니다. (단위는 분)

(4) IDLE_TIME : 연속적으로 휴면 시간이 여기 값을 넘으면 접속을 해제 합니다. 예를 들어 IDLE_TIME 5 이렇게 하면 5분 동안 활동이 없는 세션은 강제로 접속이 끊어지게 됩니다 (분 단위)

(5) LOGICAL_READS_PER_SESSION : 한 SESSION에서 사용 가능한 최대 BLOCK 수를 지정합니다.

(6) PRIVATE_SGA : MTS / shared server 일 경우 해당 session의 SGA 사용량을 byte 단위로 설정

(7) CPU_PER_CALL : 하나의 Call 당 cpu 점유할 수 있는 시간 1/100초 단위

(8) LOGICAL_READS_PER_CALL : 하나의 call당 읽을 수 있는 block의 개수 지정

 

-- Resource 관련 profile 생성

--profile_sms에 다음의 resource 관련 parameter 값을 수정

-- SYS> alter system set resource_limit=true;

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

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

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

 

ALTER PROFILE PROFILE_SMS LIMIT

CPU_PER_SESSION 1000

CONNECT_TIME 480

IDLE_TIME 10 ;

 

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; -- 리소스 내용이 적용 되려면 TRUE로 해야함

 

SELECT * FROM DBA_PROFILES

WHERE PROFILE='PROFILE_SMS';

 

◆ 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	Tablesapce 사용용량을 무제한으로 허용하는 권한 즉 quota 옵션 적용을 받지 않게 됨

-- 권한 부여

-- SYSTEM privilege 부여

GRANT CREATE SESSION, CREATE ANY TABLE TO SMSUSER;

-- OBJECT privilege 부여

GRANT SELECT ON SCOTT.EMP TO SMSUSER;

 

--권한확인

--01. 계정별 권한 조회(role 제외)

select * from dba_sys_privs

where grantee='SMSUSER';

 

select * from dba_tab_privs

where 1=1

and grantee='SMSUSER'

and TABLE_NAME='EMP';

 

--02. 계정별 role 조회

select * from dba_role_privs

where grantee='SMSUSER';

 

--03. 그 role에 어떤 권한이 포함되어있는지

select * from dba_sys_privs

where grantee='CONNECT';

 

--04. role에 부여된 SYS 권한 조회

select * from dba_users;

 

◆ ROLE

-- ROLE 생성 하기

create role trole;

create role srole;

 

-- ROLE에 권한 할당 하기

grant create session, create table to trole;

grant select on scott.dept to srole;

 

-- ROLE 할당하기

grant trole, srole to smsuser;

 

1. smsuser에게 부여된 system privilege를 직접 부여된 권한과 role을 통해 부여된 권한을 한번에 조회하도록 쿼리 작성

select grantee, privilege

from dba_sys_privs

where 1=1

and grantee='SMSUSER'

union all

select ROLE, PRIVILEGE

from role_sys_privs

where ROLE IN (select GRANTED_ROLE

from dba_role_privs

where grantee='SMSUSER');

 

2. smsuser에게 부여된 table privilege를 직접 부여된 권한과 role을 통해 부여된 권한을 한 번에 조회하도록

select GRANTEE as "dd", OWNER, TABLE_NAME, PRIVILEGE

from DBA_TAB_PRIVS

where 1=1

and grantee='SMSUSER'

union all

select ROLE, OWNER, TABLE_NAME, PRIVILEGE

from role_tab_privs

where ROLE IN (select GRANTED_ROLE

from dba_role_privs

where grantee='SMSUSER');

 

3. table에 부여된 권한 확인

select * from DBA_TAB_PRIVS

where 1=1

and TABLE_NAME='DEPT';

 

4. table에 부여된 권한 부여 DDL

select 'grant '||PRIVILEGE||' on ' ||OWNER||'.'||TABLE_NAME||' to '||GRANTEE||';'

from DBA_TAB_PRIVS

where 1=1

and TABLE_NAME='DEPT';

 

5. table에 부여된 권한 회수 DDL

select 'grant '||PRIVILEGE||' on ' ||OWNER||'.'||TABLE_NAME||' from '||GRANTEE||';'

from DBA_TAB_PRIVS

where 1=1

and TABLE_NAME='DEPT';