인증되지 않은 TOOLS 의 ACCESS 제한하기

PURPOSE
이 자료는 Database 에 인증되지 않은 tools 의 접근을 막는 방법에 대한 설명이다.

Explanation

SQL*Plus 나 기타 다른 tools 의 접속을 제한할 필요가 발생할 수 있다.

단지 지정한 tools 만 사용하는 user 만 access 허용을 원한다면
예를 들어, forms 나 Reports 또는 다른 tools 만 사용하는 환경에서 SQL*Plus 나
다른 software 의 tools 을 사용하는 end user 의 접속을 제한하고, 단지 인증된 tools 만
접속이 되도록 하려고 한다.

그럴 경우에 PRODUCT_USER_PROFILE 테이블을 사용하여 SQL*PLUS 을 실행하는 session에
제한을 넣을 수 있다. 어떻게 수행되는지는 <Note:2181.1>를 참조해 보면 된다.

그러나 이 제한은 단지 SQL*PLUS 에서만 작동하고 다른 tools 에서는 동작을 하지 않을 수도 있다. Oracle8i 에서는 logon trigger를 이용하면 이 기능을 사용할 수 있다.

Example
인증되지 않은 access 를 검사하는 테이블을 생성한다.

SQL> conn sys/manager

Connected.

SQL> DROP TABLE LOGONAUDITTABLE CASCADE CONSTRAINTS;

SQL> CREATE TABLE LOGONAUDITTABLE (

EVENT VARCHAR2 (10),

TIMESTAMP DATE,

SCHEMA VARCHAR2 (30),

OSUSERID VARCHAR2 (30),

MACHINENAME VARCHAR2 (64),

SID NUMBER,

SERIAL# NUMBER,

PROGRAM VARCHAR2 (100));

Table created.

scott schema 에 아래의 trigger를 생성한다.

SQL> CREATE OR REPLACE TRIGGER logonauditing

AFTER LOGON ON scott.SCHEMA

DECLARE

machinename VARCHAR2(64);

osuserid VARCHAR2(30);

sid NUMBER;

serial# NUMBER;

program VARCHAR2(100);

CURSOR c1 IS

SELECT osuser, machine , sid , serial# , program

FROM v$session

WHERE audsid = USERENV( 'sessionid' );

BEGIN

DELETE LOGONAUDITTABLE;

COMMIT;

OPEN c1;

FETCH c1 INTO osuserid, machinename, sid , serial# , program ;

INSERT INTO LOGONAUDITTABLE VALUES ( 'LOGON', SYSDATE,

USER, osuserid, machinename , sid , serial#, program);

CLOSE c1;

COMMIT;

dbms_job.isubmit(12345,'sys.killsession;',SYSDATE);

END;

Trigger created.

log on trigger로부터는 같은 session 을 kill 시킬 수는 없다. 이 SESSION은 DBMS_JOB PACKAGE를
사용하여 매 10 초마다 해당하는 SESSION 을 찾아서 KILL 시키는 SCHEDULING 을 걸어서
사용할 수 있다.

sys 나 system schema로 아래의 procedure를 생성한다.

SQL> conn sys/manager

Connected.

SQL> CREATE OR REPLACE PROCEDURE KILLSESSION AS

sid NUMBER;

serial# NUMBER;

timestamp DATE;

CURSOR c1 IS SELECT sid , serial# , timestamp FROM scott.LOGONAUDITTABLE WHERE

INSTR(UPPER(program),UPPER('C:\Documents and Settings\All Users\시작 메뉴\프')) > 0;

/* 위의 부분에서 session 의 program column 값에서 원하는 application 의 value를 지정한다. */

BEGIN

FOR i1 IN c1 LOOP

dbms_output.put_line('alter system kill session ' ||''''||i1.sid||','||i1.serial#||'''');

execute IMMEDIATE 'alter system kill session ' ||''''||i1.sid||','||i1.serial#||'''';

DELETE scott.LOGONAUDITTABLE WHERE sid = i1.sid AND serial#=i1.serial# AND timestamp = i1.timestamp;

END LOOP;

COMMIT;

END;

Procedure created.

위의 procedure를 수행하기 위해서는 init.ora 파일에 아래의 parameters를 적용해 주어야 한다.

job_queue_process = 1

job_queue_interval = 10

위와 같이 setting 을 하면 아래의 PL/SQL block 을 사용하여 인증되지 않은 sessions 들을
매 10 초 마다 KILLSESSION procedure 를 fire시킬 수 있다.

SQL> connect sys/manager

begin

    dbms_job.isubmit(2345,'KILLSESSION;',sysdate,'sysdate+1/(24*60*6)');

end;

위의 명령을 실행하면 Client sqlplus 에서 아래의 메시지가 떨어진다.

SQL> select * from logonaudittable;

EVENT TIMESTAMP SCHEMA OSUSERID
------------------ ------------------------------

MACHINENAME SID SERIAL#
---------
PROGRAM

LOGON 03-FEB-03 SCOTT Administrator

BKCHEON 11 43911

C:\Documents and Settings\Administrator\시작 메

LOGON 03-FEB-03 SCOTT Administrator

BKCHEON 11 43923

C:\Documents and Settings\All Users\시작 메뉴\프

LOGON 03-FEB-03 SCOTT Administrator

BKCHEON 15 55572

C:\Documents and Settings\All Users\시작 메뉴\프

SQL> select * from tab;

select * from tab

*

ERROR at line 1:

ORA-00028: your session has been killed

Reference Documents

NOTE:105438.1

Note:70679.1 PL/SQL Example: How to Audit Logon Events with Triggers

원본 위치 <http://kr.forums.oracle.com/forums/thread.jspa?threadID=473589&tstart=150>