#20_140710_WSHOP_유저관리

# 유저관리

문제63. king 이라는 유저를 아래와 같이 생성하시오

create user king

IDENTIFIED BY tiger

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

ACCOUNT UNLOCK

PASSWORD EXPIRE;

GRANT CREATE SESSION TO king;

grant unlimited tablespace to king;

l DEFAULT TABLESPACE users - king 유저가 사용할 기본 테이블 스패이스

l TEMPORARY TABLESPACE temp - select 구문 정렬작업하기 위한 disk 공간

l PASSWORD EXPIRE; tieger 패스워드를 바로 소멸시키겠다.

king으로 접속할 때 새롭게 패스워드를 설정해야 한다.

문제65. DB에 유저가 누가누가 있고 그 유저들의 default tablespace 가 어딘지 확인하시오(sys에서)

select username, default_tablespace

from dba_users;

n 내 세션이 가지고 있는 시스템 권한이 무엇인지 조회

select * from session_privs;

sys는 DB를 올렸다 내렸다 할 수 있는 권한이 있지만 system 은 없다

l 유저의 인증방법

1. 관리자(DB를 올렸다 내렸다 할 수 있는 유저)

- OS 인증방법 : 원격에서 DB로 접속이 불가능

remote_login_passwordfile = none

- password file 인증방법 : 원격에서 접속가능

remote_login_passwordfile = exclusive

SQL> show parameter remote_login_passwordfile NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile            string      EXCLUSIVE

C:\Users\실습실>sqlplus sys/oracle@dbdb as sysdba SQL*Plus: Release 11.2.0.2.0 Production on 목 7월 10 14:42:45 2014 Copyright (c) 1982, 2010, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

  

문제66. 관리자 권한으로 원격에서 접속 못하게 파라미터를 설정하시오

SQL> alter system set remote_login_passwordfile=none scope=spfile;

2. 일반유저

- OS 인증방법 : guest 계정을 인증해줄 떄 사용하는 인증방법

관련된 파라미터 : os_authent_prefix

grant create session : 접속권한

SQL> show parameter os_authent_prefix NAME                           TYPE        VALUE ------------------------------------ ----------- ------------------ os_authent_prefix                  string      ops$ SQL> create user ops$oracle   2  identified externally; SQL> grant create session to ops$oracle; SQL> sqlplus /

- DB 인증방법 : 아래 처럼 data dictionary 에 패스워드가 있어서 이를 보고 인증해주는 방법

SELECT username, PASSWORD FROM dba_users;

- global 인증방법 : 오라클 협력업체에 다양한 DB 인증 툴을 이용해서 DB에 접속할 수 있도록 인증하는 방법

 3. LOCK

문제67. scott 계정을 잠그고 scott으로 접속을 시도하시오

alter user scott account lock;

alter user scott account unlock;

문제69. 패스워드를 복잡하게 설정해야지 변경될 수 있도록 강제하는 방법은 무엇인가?

답 : 아래의 스크립트를 수행해서 함수 생성하고 해당 함수를 프로파일에 적용하면 된다

@?/rdbms/admin/utlpwdmg.sql

- 물음표는 오라클 홈을 의미한다

- 오라클 홈(cd $ORCLE_HOME) : /u01/app/oracle/product/11.2.0/dbhome_1

- 스크립트 오픈 : ed ?/rdbms/admin/utlpwdmg.sql

l 이 스크립트를 수행하면 최소한 8글자 이상이고 문자가 추가되야 password를 변경할 수 있다

 l 패스워드 변경시에 주의사항

- 반드시 문자, 숫자, 특수문자를 섞어서 변경 해야한다.

문제70. scott 의 패스워드를 oracle 로 변경하시오

SQL> alter user scott identified by oracle; alter user scott identified by oracle * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20001: Password length less than 8

l 오라클의 권한 2가지

- 객체 권한 : 특정 테이블을 데이터를 엑세스하거나 조작하는 권한

- 시스템 권한 : 테이블을 생성하거나 변경하거나 하는 즉 오브젝트를 생성하거나 변경하는 권한

- with admin option : 시스템 권한을 남에게 줄 수 있는 권한

릴레이로 권한을 부여하면 중간에 권한이 뻇겨도 나머지는 살아있다

- with grant option : 객체 권한을 남에게 줄 수 있는 권한

릴레이로 권한을 부여한 상태에서 중간에 권한을 뻇으면 그 밑에 권한을 부여한 유저들도 함께 권한을 상실한다

l sys가 smith에게 다른 유저에게도 create user의 권한을 주게 할려면 with admin option을 붙여줘야 한다

- grant create user to smith with admin option;

l sys가 smith에게 권한을 주고 smith가 jack 에게 권한을 부여한 상태에서 smith의 권한을 revoke 명령어로 뻇어도 jack의 권한은 남아있다(with admin option일 경우만 가능)

  - revoke create user from smith; 

 ● 프로파일

l 프로파일(profile)로 관리할 수 있는 것

- 리소스 관리

- 패스워드 관리

l 리소스 관리

- 메모리에서 읽는 버퍼의 개수를 20개로 제한

ALTER PROFILE DEFAULT LIMIT

LOGICAL_READS_PER_CALL 20;   

- 리소스 사용을 제한하는 파라미터를 켬
    ALTER SYSTEM SET resource_limit=TRUE;

     - 확인 : select * from dba_profiles;

l     패스워드 관리

- 패스워드 3번이상 틀리면 1시간 동안 계정을 잠그겠다

- alter profile default limit

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 1;

SQL> select count(*)   2  from sales a, sales b, sales c, sales d; select count(*) * ERROR at line 1: ORA-02395: exceeded call limit on IO usage

- 리소스 사용을 제한하고 악성 SQL을 수행하자 위와 같은 오류가 발생한다

마지막문제. 4가지 프로시져를 하나의 패키지에 포함시켜서 암호화

l 테이블 스패이스 관련 패키지 – 프로시져

1.     테이블스패이스 생성

2.     테이블스패이스 정보확인(공간사용률)

3.     테이블스패이스 공간추가

4.     테이블스패이스 삭제

CREATE OR REPLACE PACKAGE pack_tablespace IS

 PROCEDURE spa_create(tname VARCHAR2,tsize number);

 PROCEDURE spa_used;

 PROCEDURE add_file(p_table VARCHAR2, p_file VARCHAR2, p_size number);

 PROCEDURE resize_file(p_file VARCHAR2, p_size number);

 PROCEDURE spa_rev(p_file varchar2);

END pack_tablespace;

/

CREATE OR REPLACE PACKAGE BODY pack_tablespace IS

 PROCEDURE spa_create (tname VARCHAR2,tsize number) IS

 BEGIN

  EXECUTE IMMEDIATE

   'CREATE TABLESPACE '||tname||' DATAFILE '||'''c:\'||tname||'.dbdf''' ||' SIZE '||tsize||'m';

 END spa_create;

 PROCEDURE spa_used IS

 CURSOR table_cursor IS

  SELECT u.tablespace_name space

        , sum(nvl(u.bytes,0)) / 1024000 large

        , (sum(nvl(u.bytes,0)) - sum(nvl(f.bytes,0))) / 1024000 used

        , (sum(nvl(f.bytes,0))) / 1024000 unused

        , trunc((sum(nvl(f.bytes,0)) / sum(nvl(u.bytes,0))) * 100,2) unused_percent

        , 100 - trunc((sum(nvl(f.bytes,0)) / sum(nvl(u.bytes,0))) * 100,2) used_percent

         FROM DBA_DATA_FILES u

         , DBA_FREE_SPACE f

         WHERE u.file_id = f.file_id(+)

         GROUP BY

         u.tablespace_name

         ORDER BY u.tablespace_name;

        

 BEGIN

 dbms_output.put_line('TABLESPACE NAME     USED(percent)');

 dbms_output.put_line('---------------------------------');

  FOR table_record IN table_cursor LOOP

   dbms_output.put_line(RPAD(table_record.space,20,'  ') || '   ' || table_record.used_percent || '%' );

  END LOOP;

 END spa_used;

 PROCEDURE add_file(p_table VARCHAR2, p_file VARCHAR2, p_size number) IS

 BEGIN

  execute IMMEDIATE

   'alter tablespace ' || p_table

   || ' add datafile '|| '''/u01/app/oracle/oradata/dbdb/' || p_file || ''' size ' || p_size || 'm';

 END add_file;

 PROCEDURE resize_file(p_file VARCHAR2, p_size number) IS

 BEGIN

  execute IMMEDIATE

   'ALTER DATABASE DATAFILE '||'''/u01/app/oracle/oradata/dbdb/' ||p_file||''' RESIZE '|| p_size||'m';

 END resize_file;

 PROCEDURE spa_rev(p_file varchar2) IS

 BEGIN

  EXECUTE IMMEDIATE

   'drop tablespace ' || p_file || ' including contents and datafiles';

 END spa_rev;

END pack_tablespace;

/