Oracle 限制ip访问 通过触发器来实现

http://www.bunyamindemir.com/?p=115

create table
CHECK_USER_IP_LOGIN
(
username          varchar2(50),
ip                varchar2(15)
);

INSERT INTO CHECK_USER_IP_LOGIN VALUES ('bunyamin', '10.10.9.1');
INSERT INTO CHECK_USER_IP_LOGIN VALUES ('bunyamin', '10.10.9.2');

And i need a trigger for check my whitelist at the logon time.

create or replace trigger "user_ip_restrict"
AFTER LOGON ON DATABASE
DECLARE
ip_count INTEGER;
user_count INTEGER;
BEGIN
SELECT COUNT(username) INTO user_count FROM CHECK_USER_IP_LOGIN
WHERE username=lower(user);
SELECT COUNT(ip) INTO ip_count FROM CHECK_USER_IP_LOGIN
WHERE username=lower(user) AND ip=SYS_CONTEXT('USERENV','IP_ADDRESS');
IF ( (user_count > 0 ) AND (ip_count = 0) ) THEN
RAISE_APPLICATION_ERROR(-20001,'You are not allowed with this IP');
END IF;
END;

Enter user-name: bunyamin
Enter password:
ERROR:

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed with this IP
ORA-06512: at line 10

PS: It doesnt work for DBA users.

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>