CREATE TABLE TAB_AUTHORIZATION_LOGON_USER
(
ID NUMBER(19) NOT NULL,
IP_ADDRESS VARCHAR2(200 BYTE),
TERMINAL VARCHAR2(200 BYTE),
NAME VARCHAR2(50 CHAR),
VALID NUMBER(1) NOT NULL,
CREATEDATE TIMESTAMP(6),
LASTDATE TIMESTAMP(6)
)
TABLESPACE BASE_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 20M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE TAB_AUDIT_LOGON_RECORD
(
ID NUMBER(19) NOT NULL,
LOGON_TIME DATE,
SESSION_ID NUMBER,
OS_USER VARCHAR2(200 BYTE),
IP_ADDRESS VARCHAR2(200 BYTE),
TERMINAL VARCHAR2(200 BYTE),
HOST_NAME VARCHAR2(200 BYTE),
USER_NAME VARCHAR2(30 BYTE)
)
TABLESPACE BASE_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 20M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE SEQUENCE SEQ_LOGON
START WITH 1
MAXVALUE 9999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
CREATE OR REPLACE PROCEDURE SYS.SP_KILL_SESSION (P_SESSION_ID IN NUMBER)
AS
SQL_STATEMENT VARCHAR2 (500);
BEGIN
SQL_STATEMENT := '0';
--定义游标
DECLARE
CURSOR MACHINE_AUDIT
IS
SELECT sid, serial#
FROM v$session
WHERE AUDSID = P_SESSION_ID;
--定义记录变量
MACHINE_RECORD MACHINE_AUDIT%ROWTYPE;
--开始使用游标取数据
BEGIN
OPEN MACHINE_AUDIT;
LOOP
FETCH MACHINE_AUDIT INTO MACHINE_RECORD;
--游标取不到数据则退出
EXIT WHEN MACHINE_AUDIT%NOTFOUND;
SQL_STATEMENT :=
'ALTER SYSTEM KILL SESSION '
|| ''''
|| MACHINE_RECORD.sid
|| ','
|| MACHINE_RECORD.serial#
|| '''';
EXECUTE IMMEDIATE SQL_STATEMENT;
END LOOP;
CLOSE MACHINE_AUDIT;
END;
END;
/
CREATE OR REPLACE TRIGGER TRG_LOGON_RESTRICT
AFTER LOGON
ON DATABASE
DECLARE
SQL_STATEMENT VARCHAR2 (500);
V_EXIST NUMBER (19);
TYPE LOGON_RECORD_TYPE --定义记录类型
IS
RECORD (
ID TAB_AUDIT_LOGON_RECORD.id%TYPE,
LOGON_TIME TAB_AUDIT_LOGON_RECORD.logon_time%TYPE,
SESSION_ID TAB_AUDIT_LOGON_RECORD.session_id%TYPE,
OS_USER TAB_AUDIT_LOGON_RECORD.os_user%TYPE,
IP_ADDRESS TAB_AUDIT_LOGON_RECORD.ip_address%TYPE,
TERMINAL TAB_AUDIT_LOGON_RECORD.terminal%TYPE,
HOST_NAME TAB_AUDIT_LOGON_RECORD.host_name%TYPE,
USER_NAME TAB_AUDIT_LOGON_RECORD.user_name%TYPE
);
LOGON_RECORD LOGON_RECORD_TYPE;
BEGIN
SQL_STATEMENT := '0';
V_EXIST := 0;
SELECT SEQ_LOGON.NEXTVAL id,
SYSDATE,
SYS_CONTEXT ('USERENV', 'SESSIONID'),
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT ('USERENV', 'TERMINAL'),
SYS_CONTEXT ('USERENV', 'HOST'),
ORA_LOGIN_USER
INTO LOGON_RECORD
FROM DUAL;
INSERT INTO TAB_AUDIT_LOGON_RECORD
VALUES LOGON_RECORD;
SELECT COUNT ( * )
INTO V_EXIST
FROM TAB_AUTHORIZATION_LOGON_USER
WHERE ip_address = LOGON_RECORD.ip_address
AND terminal = LOGON_RECORD.terminal
AND valid = 1;
IF V_EXIST IS NULL
THEN
V_EXIST := 0;
END IF;
IF V_EXIST > 0
THEN
DBMS_OUTPUT.put_line ('Authorized user, please be careful operation!');
ELSE
SP_KILL_SESSION (LOGON_RECORD.SESSION_ID);
raise_application_error (-20001,
'Restricted LOGON! Please contact DBA');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Login session does not exist');
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR (-20002,
'Restricted LOGON! Please contact DBA!');
END;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-758346/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25198367/viewspace-758346/