这两天一直在弄客户端工具如PLSQL等登录数据库的权限控制,满足了这样不能满足那样,搞得头晕。本来在系统防火墙层面应该也可以实现,但是还是要求在代码上来操作,开始用同事写的一个脚本来测试,虽然可以限制,但是不满足要求。后来改了改,折腾了近一天,终于基本可以满足要求。具体问题在测试中!虽然是暂时可以了,但这个代码我感觉有漏洞,稍微动下手脚应该PLSQL就能登录了!有兴趣的可以拿脚本去测试哈!
要求:客户端工具不能连接数据库,但是java程序可以正常连接!!
开始的时候考虑用SYS_CONTEXT函数获取客户端的计算机名称、IP地址、以及登录数据库的用户名或者java程序服务器的IP和一个特殊字段来进行限制,但后来发现,当plsql和java在一台计算机上面的时候plsql和java都不能正常连接,显然这不是想要的效果!
实在没办法 只能通过v$session的Program字段来进行区别,经过测试,基本可以满足了,但是个人感觉通过Program来判断存在漏洞。具体就不说了!如果你也和我想到一块的,大家可以测试测试 !
附:SYS_CONTEXT函数使用
select SYS_CONTEXT('USERENV', 'TERMINAL') terminal, SYS_CONTEXT('USERENV', 'LANGUAGE') language, SYS_CONTEXT('USERENV', 'SESSIONID') sessionid, SYS_CONTEXT('USERENV', 'INSTANCE') instance, SYS_CONTEXT('USERENV', 'ENTRYID') entryid, SYS_CONTEXT('USERENV', 'ISDBA') isdba, SYS_CONTEXT('USERENV', 'NLS_TERRITORY') nls_territory, SYS_CONTEXT('USERENV', 'NLS_CURRENCY') nls_currency, SYS_CONTEXT('USERENV', 'NLS_CALENDAR') nls_calendar, SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') nls_date_format, SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language, SYS_CONTEXT('USERENV', 'NLS_SORT') nls_sort, SYS_CONTEXT('USERENV', 'CURRENT_USER') current_user, SYS_CONTEXT('USERENV', 'CURRENT_USERID') current_userid, SYS_CONTEXT('USERENV', 'SESSION_USER') session_user, SYS_CONTEXT('USERENV', 'SESSION_USERID') session_userid, SYS_CONTEXT('USERENV', 'PROXY_USER') proxy_user, SYS_CONTEXT('USERENV', 'PROXY_USERID') proxy_userid, SYS_CONTEXT('USERENV', 'DB_DOMAIN') db_domain, SYS_CONTEXT('USERENV', 'DB_NAME') db_name, SYS_CONTEXT('USERENV', 'HOST') host, SYS_CONTEXT('USERENV', 'OS_USER') os_user, SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') external_name, SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip_address, SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') network_protocol, SYS_CONTEXT('USERENV', 'BG_JOB_ID') bg_job_id, SYS_CONTEXT('USERENV', 'FG_JOB_ID') fg_job_id, SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') authentication_type, SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA') authentication_data from dual
下面是触发器代码!
CREATE OR REPLACE TRIGGER ACCOUNT_LOGON AFTER LOGON ON DATABASE DECLARE vv_host varchar2(50); v_ip varchar2(50); v_user varchar2(50); c_user number; c_pass number; c_program varchar2(220); pragma AUTONOMOUS_TRANSACTION; BEGIN select rtrim(SYS_CONTEXT('USERENV', 'HOST'),chr(0)) into vv_host from dual; select SYS_CONTEXT('USERENV', 'IP_ADDRESS') into v_ip from dual; select SYS_CONTEXT('USERENV', 'SESSION_USER') into v_user from dual; select program into c_program from v$session where sid = (select max(sid) from v$mystat) ; select count(*) into c_user from allow_user where Upper(login_user_name) = UPPER(v_user); select count(*) into c_pass from allow_user where (Upper(login_user_name) = UPPER(v_user) and IP_ADDRESS = v_ip and UPPER(V_HOST) = UPPER(vv_host) and JUDGE = 1) or (v_ip like IP_ADDRESS and upper(pass)= 'YES' and upper(c_program) not like '%PLSQL%'); if v_ip is not null and c_user >= 1 then if c_pass >= 1 then insert into u_login (HOST, U_NAME, IP, TIME, CONTEXT,PROGRAM) values (SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'SESSION_USER'), sys_context('USERENV', 'IP_ADDRESS'), sysdate, 'normal', c_program); commit; else insert into u_login (HOST, U_NAME, IP, TIME, CONTEXT,PROGRAM) values (SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'SESSION_USER'), sys_context('USERENV', 'IP_ADDRESS'), sysdate, 'ORA-20444', c_program); commit; RAISE_APPLICATION_ERROR(-20444,'You are not allowed to connect to the database'); end if; else insert into u_login (HOST, U_NAME, IP, TIME, CONTEXT,PROGRAM) values (SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'SESSION_USER'), sys_context('USERENV', 'IP_ADDRESS'), sysdate, 'normal', c_program); commit; end if; END;
代码中的and upper(c_program) not like ‘%PLSQL%’ 感觉应该可以换成具体的程序名称,未测试过!
本机没有java环境,测试了plsql的情况,红色框为我的测试IP,如下:
当配置表如下时,plsql 正常登录:
当配置表如下时,错误提示出现:
更多功能测试!