Oracle-LimitConnection

Oracle限制登录触发器实现

类似使用 trigger 限制登陆后，非 sysdba 方式再也无法在 DB 服务器本地使用 sqlplus 登陆了，因为 DB 服务器本地使用 sqlplus 登陆不需要监听器，而触发器这套规则需要监听器，所以有了触发器后 DB 服务器本地使用 sqlplus 登陆会提示 IP 为 NONE

先建立一个表，存放IP

CREATE   TABLE DBA_CONTROL_RULE
(
  RULE_ID        NUMBER(8)     NOT NULL PRIMARY KEY,
  IP_ADDRESS     VARCHAR2(15 BYTE)
)

建立一个表，存放登陆记录

CREATE TABLE DBA_ACCESS_LOG
(
  SESSION_ID     NUMBER(10),
  IP_ADDRESS     VARCHAR2(15 BYTE),
  DB_USER_NAME   VARCHAR2(20 BYTE),
  OS_USER        VARCHAR2(50 BYTE),
  MACHINE_NAME   VARCHAR2(50 BYTE),
  TERMINAL_NAME  VARCHAR2(50 BYTE),
  PROGRAM_NAME   VARCHAR2(50 BYTE),
  ACTION_DATE    DATE
)

建立限制登陆的触发器

CREATE OR REPLACE TRIGGER SYS.DBA_LOGON_CHECK
AFTER LOGON ON DATABASE
declare
    v_session_id    number;
    v_ip_address   	varchar2(30);
    v_db_user_name  varchar2(20);
    v_os_user      	varchar2(20);
    v_machine_name  varchar2(50);
    v_terminal_name varchar2(50);
    v_program_name  varchar2(50);
    v_permit        varchar2(1);
    v_rule_id       number;
    v_check         varchar2(1) := 'Y';
begin
    begin
        select 
            nvl(sid,0),nvl(sys_context('USERENV','IP_ADDRESS'),'NONE'),
            nvl(username,'NONE'),nvl(osuser,'NONE'),nvl(trim(machine),'NONE'),
            nvl(terminal,'NONE'),nvl(program,'NONE')
            into v_session_id,v_ip_address,v_db_user_name,v_os_user,
            v_machine_name,v_terminal_name,v_program_name
        from 
            v$session
        where 
            audsid=sys_context('USERENV','SESSIONID') 
            and instr(nvl(program,'NONE'),'(J0')=0;
        exception when others then
            v_check := 'N';
    end;
    v_permit := 'D';
    if v_session_id>0 and v_check='Y' then
        begin
            --Permit Access
            select 
                rule_id into v_rule_id 
            from 
                DBA_CONTROL_RULE 
            where 
                nvl(ip_address,v_ip_address)=v_ip_address 
                and rownum=1;
            v_permit := 'P';
            exception when no_data_found then
                null;
        end;
        insert into dba_access_log 
            (session_id,ip_address,db_user_name,os_user,machine_name,
            terminal_name,program_name,action_date)
            values(v_session_id,v_ip_address,v_db_user_name,v_os_user,v_machine_name,
            v_terminal_name,v_program_name,sysdate);
        if v_permit = 'D' then
            raise_application_error(-20001,'You(IP:'||v_ip_address||') have no priviledge to access the DB!please contact with DBA' );
        end if;
    end if;
end;