#眉標=Oracle、Hacker、SQL Injection
#副標=Oracle資料庫優化實務經驗談(2)
#大標=常見的資料庫安全漏洞與防範
#作者=文／圖 賞金獵人


===<反灰>=============
Insert into sys.product_user_profile (product,userid,attribute,char_value) values('SQL*Plus','JACKSON','CREATE','DISABLED');
==<end>==============



===<反灰>=============
insert into sys.product_user_profile(product,userid,attribute,char_value) values('SQL*Plus','JACKSON','CREATE','DISABLED');
insert into sys.product_user_profile(product,userid,attribute,char_value) values('SQL*Plus','JACKSON','SELECT','DISABLED');
insert into sys.product_user_profile(product,userid,attribute,char_value) values('SQL*Plus','JACKSON','INSERT','DISABLED');
insert into sys.product_user_profile(product,userid,attribute,char_value) values('SQL*Plus','JACKSON','UPDATE','DISABLED');
insert into sys.product_user_profile(product,userid,attribute,char_value) values('SQL*Plus','JACKSON','DELETE','DISABLED');
insert into sys.product_user_profile(product,userid,attribute,char_value) values('SQL*Plus','JACKSON','ALERT','DISABLED');
==<end>==============



===<反灰>=============
Alter profile "DEFAULT" limit 
Sessions_per_user 3
Password_life_time 60
Password_lock_time 1
Failed_login_attempts 3
==<end>==============





===<反灰>=============
create table logon_off_table
(
  user_id		varchar2(30),
  session_id	number(8),
  host		varchar2(30),
  ipaddress	varchar2(30),
  program		varchar2(48),
logon_date	date,
logout_date	date
)
==<end>==============




===<反灰>=============
--這段Trigger會在用戶登入時，進行記錄
create or replace trigger logon_trigger after logon on database
BEGIN
insert into logon_off_table values(
　user,
  sys_context('USERENV','SESSIONID'),
  sys_context('USERENV','HOST'),
  ora_client_ip_address,
  (select program from v$session where sys_context('USERENV','SESSIONID') = audsid),
  sysdate,
  null
);
END;

--這段會在用戶登出時進行Trigger
create or replace trigger logoff_trigger before logoff on database
BEGIN
Update logon_off_table set logout_date = sysdate
where sys_context('USERENV','SESSIONID') = session_id;
END;
==<end>==============






===<反灰>=============
--建立存放Login failure的 table
CREATE TABLE connection_failure
(
try_date   date,
try_host   varchar(20),
try_ip varchar(20),
err        varchar(256)
)

--攔截有關於Login failure的錯誤
CREATE OR REPLACE TRIGGER logon_fail AFTER SERVERERROR ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO connection_failure
    VALUES
    (SYSDATE,  sys_context('USERENV','HOST'), sys_context('USERENV','IP_ADDRESS'),'ORA-1017');
  END IF;
END logon_fail;
==<end>==============





===<反灰>=============
--限定用戶登入時間與IP
CREATE OR REPLACE TRIGGER LoginIP_check AFTER
LOGON ON DATABASE
BEGIN
  IF USER IN ('MARY','JACK') AND sys_context('USERENV','ip_address') LIKE '192.168.1.%' THEN
   RAISE_APPLICATION_ERROR(-20002, USER || ' , you can not login from ' || sys_context('USERENV','ip_address') );
  END IF;
  IF USER IN ('MARY','JACK','TOM') AND to_char(sysdate, 'D') in ('7','1') THEN
   RAISE_APPLICATION_ERROR(-20003, USER || ' , you can not access to Oracle Database on ' || to_char(sysdate, 'Day') );
  END IF; 
END;
==<end>==============