Home » SQL & PL/SQL » SQL & PL/SQL » Problem on Fine Grained Auditing (Oracle 10.1)
Problem on Fine Grained Auditing [message #361936] Fri, 28 November 2008 11:16
dba_raf
Messages: 13
Registered: December 2005
Junior Member
Hi,
I've this table in schema AFM_SECURE:

CREATE TABLE AFM_USERS
(
USER_NAME VARCHAR2(64 BYTE) DEFAULT NULL NOT NULL,
GROUP_1 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_10 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_2 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_3 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_4 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_5 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_6 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_7 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_8 VARCHAR2(64 BYTE) DEFAULT NULL,
GROUP_9 VARCHAR2(64 BYTE) DEFAULT NULL,
USER_LEVEL INTEGER DEFAULT 0,
USER_PWD VARCHAR2(64 BYTE) DEFAULT NULL,
EM_ID VARCHAR2(80 BYTE) DEFAULT NULL,
COMMENTS VARCHAR2(200 BYTE) DEFAULT NULL,
BL_ID_LIST VARCHAR2(255 BYTE) DEFAULT NULL,
CLR_SCHEME VARCHAR2(16 BYTE) DEFAULT 'SLATE',
EMAIL VARCHAR2(50 BYTE) DEFAULT NULL,
HOME_PAGE VARCHAR2(255 BYTE) DEFAULT NULL,
LOCALE VARCHAR2(16 BYTE) DEFAULT 'DEFAULT',
PREFERENCES VARCHAR2(1000 BYTE) DEFAULT NULL,
ROLE_NAME VARCHAR2(64 BYTE) DEFAULT NULL,
SITE_ID_LIST VARCHAR2(255 BYTE) DEFAULT NULL,
VPA_OPTION1 VARCHAR2(500 BYTE) DEFAULT NULL,
VPA_OPTION2 VARCHAR2(500 BYTE) DEFAULT NULL,
VPA_OPTION3 VARCHAR2(500 BYTE) DEFAULT NULL,
VPA_OPTION4 VARCHAR2(500 BYTE) DEFAULT NULL
);

I've this table in schema AFM:

CREATE TABLE AFM_MODS
(
AFM_MODULE_01 VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_02 VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_03 VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_DE VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_FR VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_JP VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_NL VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_ZH VARCHAR2(48 BYTE) DEFAULT NULL,
COMMENTS VARCHAR2(256 BYTE) DEFAULT NULL,
VISIBLE INTEGER DEFAULT 1 NOT NULL,
SECURITY_GROUP VARCHAR2(64 BYTE) DEFAULT NULL,
HELP_TOPIC VARCHAR2(64 BYTE) DEFAULT NULL,
HELP_FILE VARCHAR2(64 BYTE) DEFAULT NULL,
DISPLAY_ORDER INTEGER DEFAULT 0 NOT NULL,
BUTTON VARCHAR2(12 BYTE) DEFAULT NULL,
AFM_MODULE_NO VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_KO VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_IT VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_ES VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE_CH VARCHAR2(48 BYTE) DEFAULT NULL,
AFM_MODULE VARCHAR2(32 BYTE) DEFAULT NULL NOT NULL
);

I've add these POLICY (user SYS):

begin
dbms_fga.add_policy (
object_schema=>'AFM_SECURE',
object_name=>'AFM_USERS',
policy_name=>'AFM_LOGIN'
);
end;

begin
dbms_fga.add_policy (
object_schema=>'AFM',
object_name=>'AFM_MODS',
policy_name=>'AFM_MODS'
);
end;

I've my application that connect Oracle via ODBC
my application uses internal passwords that are not Oracle server passwords but stored in AFM_SECURE.AFM_USERS.USER_PWD.
When I connect correctly sys.fga_log$ table stored:
DB_UID=AFM_SECURE
OBJ$SCHEMA=AFM_SECURE
OBJ$NAME=AFM_USERS
POLICY_NAME=AFM_LOGIN
LSQLTEXT=SELECT user_name, user_pwd, user_level, group_1, group_2, group_3, group_4, group_5, group_6, group_7, group_8, group_9, group_10, role_name, bl_id_list, site_id_list, vpa_option1, vpa_option2, vpa_option3, vpa_option4 FROM afm_users WHERE user_name=:1
LSQLBIND= #1(3):AFM

DB_UID=AFM
OBJ$SCHEMA=AFM
OBJ$NAME=AFM_MODS
POLICY_NAME=AFM_MODS
LSQLTEXT=SELECT afm_module,button,afm_module_it FROM afm_mods WHERE ( security_group is null or security_group LIKE '%' ) ORDER BY display_order
LSQLBIND=
When I connect not correctly, the application try connect with user AFM_SECURE
sys.fga_log$ table stored:
DB_UID=AFM_SECURE
OBJ$SCHEMA=AFM_SECURE
OBJ$NAME=AFM_USERS
POLICY_NAME=AFM_LOGIN
LSQLTEXT=SELECT
ROWID, BL_ID_LIST, CLR_SCHEME, EMAIL,
GROUP_1, GROUP_10, GROUP_2,
GROUP_3, GROUP_4, GROUP_5,
GROUP_6, GROUP_7, GROUP_8,
GROUP_9, HOME_PAGE, LOCALE,
PREFERENCES, ROLE_NAME, VPA_OPTION4,
VPA_OPTION3, VPA_OPTION2, VPA_OPTION1,
USER_PWD, SITE_ID_LIST, USER_LEVEL,
USER_NAME, BAD_LOGIN
FROM AFM_SECURE.AFM_USERS Tbl
LSQLBIND=

I'd like to save every connection attempt in the table AFM_USERS and the third failed attempt to change the password into AFM_SECURE.AFM_USERS.USER_PWD

Now I thought to create a new column into table AFM_USERS: "BAD_LOGIN" NUMBER incremental with these conditions:

if I connect not correctly I increase BAD_LOGIN of 1 (+1)
if I connect correctly I decrease BAD_LOGIN of 1 (-1)

If I stored every bad login into AFM_SECURE.AFM_USERS.BAD_LOGIN when the counter is 4 change password.

How can I do this from Oracle?
Thanks in advance!

Previous Topic: 2 employes in the register table, it is possible?
Next Topic: Time is not saved with date.
Goto Forum:
  


Current Time: Sat Dec 10 09:09:22 CST 2016

Total time taken to generate the page: 0.06549 seconds