1、创建允许登录用户表
CREATE TABLE "CHF"."LOG$LOGIN_OS"
(
"OS_USER" VARCHAR2(60 BYTE)
)
2、创建触发器实现限制用户登录
create or replace
TRIGGER TR_LOGIN_RECORD_TEST
AFTER logon ON DATABASE
DECLARE
mtSession v$session%ROWTYPE;
CURSOR cSession(iiQuerySid IN NUMBER) IS
SELECT * FROM v$session where USERNAME is not null
and nvl(osuser,'x') <> 'SYSTEM' and type <> 'BACKGROUND' and audsid = iiQuerySid;
USER_NUM NUMBER(5);
V_SQL VARCHAR2(100);
BEGIN
OPEN cSession(userenv('SESSIONID'));
FETCH cSession INTO mtSession;
IF cSession%FOUND THEN
select count(*) into USER_NUM FROM CHF.LOG$LOGIN_OS WHERE OS_USER=mtSession.Osuser;
IF USER_NUM!=0 THEN
V_SQL:=' alter system kill session '||''''||mtSession.Sid||','||mtSession.Serial#||'''';
EXECUTE IMMEDIATE V_SQL;
END IF;
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
-- dbms_output.put_line('登记登录信息错误:'||SQLERRM);
RAISE;
END;
注:使用sysdba帐号创建触发器,因为在oracle中user不能kill掉自己的session,如果是用sysdba那么就可以kill掉其他的任何非自身的session