oracle服务端用户管理,oracle 客户端非授权登录限制管理(2)

怼烎@ 2023-01-18 06:39 143阅读 0赞

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)

SQL\_STATEMENT   VARCHAR2 (500);

BEGIN

SQL\_STATEMENT := '0';

\--定义游标

DECLARE

CURSOR MACHINE\_AUDIT

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;

CREATE OR REPLACE TRIGGER TRG\_LOGON\_RESTRICT

AFTER LOGON

ON DATABASE

DECLARE

SQL\_STATEMENT   VARCHAR2 (500);

V\_EXIST         NUMBER (19);

TYPE LOGON\_RECORD\_TYPE                                       --定义记录类型

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;

oracle服务端用户管理,oracle 客户端非授权登录限制管理(2)

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle11g客户端连接到服务端

相关 Oracle用户、授权、角色管理

相关 oracle服务端用户管理,oracle 客户端非授权登录限制管理(2)

相关 Oracle 数据库web客户端管理工具软件

相关 oracle客户端与服务端的连接

相关 ORACLE客户端乱码--修改Oracle客户端字符集

相关 ORACLE客户端乱码--修改Oracle客户端字符集

相关 oracle rac限制客户端访问

相关 Oracle用户、授权、角色管理

相关 Oracle安装（客户端）