A problem with a function
Oracle Version: 10g Operating System: Microsoft windows 2003 Hello, I need some help, I would appriciate if anyone could help me out ( I don't have Hebrew in my computer)... on one hand, I have a table that contains a list of table names that are sensitive in my org. In addition, not all the columns in the table are sensitive, some tables have only one or tow sensitive columns on the other hand, I have another table that contains all the SQL Statements that passed through all my DB's in the org + some other details like username, machine name etc. the sql statement is one columns - varchar2(4000). ALL the SQL TEXT is printed there. I need to create a report of the SQL Statements and their details that have any reference to the sensitive entities. I'm kind of new to PL/SQL or programming in DB in general. Can you please assist me in designing the write function and script. Additional details: Sensitive_tables table: table_name varchar2 (65) -- the names of the sensitive tables All_fields number (1) -- 0=not all the fields are sensitive, 1=all the fields are sensitive the conditions where the function should return true: 1. It's not a select/SELECT statement. 2. There is a relation to a sensitive table that all its columns are sensitive 3. There is a relation to a sensitive table and to a specific sensitive column. below is the function. There are 2 cursors - the first loop will check if the SQL Statement touches a sensitive column + a sensitive table. The second one checks if the the SQL Statement a table that all its columns are sensitive. CREATE OR REPLACE function sens_entity_match (p_statement varchar2) return VARCHAR2 IS v_match VARCHAR2(5); sql_stmt VARCHAR2(4000); TYPE tbls_wth_all_flds IS REF CURSOR; tbls_all_fields tbls_wth_all_flds; table_name_y varchar2(65); TYPE tbls_wthout_all_flds IS REF CURSOR; tbls_some_fields tbls_wthout_all_flds; table_name_n varchar2(65); BEGIN sql_stmt:=upper(p_statement); OPEN tbls_all_fields FOR select table_name from sens_tables where ALL_FIELDS='1'; OPEN tbls_some_fields FOR select table_name from sens_tables where ALL_FIELDS='0'; FETCH tbls_all_fields INTO table_name_y; FETCH tbls_some_fields INTO table_name_n; WHILE tbls_some_fields%FOUND LOOP BEGIN IF upper (sql_stmt) like 'SELECT%' THEN BEGIN RETURN ('FALSE'); v_match:='FALSE'; END; ELSIF ( ((sql_stmt LIKE '%ID_NUMBER%' ) OR (sql_stmt LIKE '%id_number%' )) AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%ADDRESS%') OR (sql_stmt LIKE '%address%')) AND (sql_stmt LIKE table_name_n) ) OR ( (((sql_stmt LIKE '%EMAIL%') OR (sql_stmt LIKE '%E_MAIL%')) OR (sql_stmt LIKE '%email%') OR (sql_stmt LIKE '%e_mail%') ) AND (sql_stmt LIKE table_name_n) ) OR (( (sql_stmt LIKE '%BANK%') or (sql_stmt LIKE '%bank%')) AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%PYM%') OR (sql_stmt LIKE '%PYM%')) AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%CREDIT_CARD%') OR (sql_stmt LIKE '%credit_card%')) AND (sql_stmt LIKE table_name_n) ) OR ( ( (sql_stmt LIKE '%USER%') OR (sql_stmt LIKE '%user%') )AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%CHURN%') OR (sql_stmt LIKE '%churn%') ) AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%LEGAL%') OR (sql_stmt LIKE '%legal%') ) AND (sql_stmt LIKE table_name_n) ) THEN BEGIN v_match:='TRUE'; RETURN ('TRUE'); END; ELSE v_match:='FALSE'; END IF; FETCH tbls_some_fields INTO table_name_n; END; END LOOP; WHILE tbls_all_fields%FOUND LOOP BEGIN IF upper (sql_stmt) like 'SELECT%' THEN BEGIN v_match:='FALSE'; RETURN ('FALSE'); END; ELSIF (sql_stmt LIKE table_name_y) THEN BEGIN v_match:='TRUE'; RETURN ('TRUE'); END; ELSE BEGIN v_match:='FALSE'; RETURN ('FALSE'); END; END IF; FETCH tbls_all_fields INTO table_name_y; END; END LOOP; RETURN (v_match); END; / Eexampls that should should be true: select sens_entity_match ('update address_data set "EMAIL" = :24 where rowid=
ld_rowid') from dual; {address_data is a sensitive_table that exists in "sensitive_tables" table and email is a sensitive column} select sens_entity_match ('isnert into legal (ssss) select bbbb from input where rowid = 666;') from dual; {legal is a sensitive_table that exists in "sensitive_tables" table and all its columns are sensitive} An example that shouldn't should be true: select sens_entity_match ('select bank from bank_
Oracle Version: 10g Operating System: Microsoft windows 2003 Hello, I need some help, I would appriciate if anyone could help me out ( I don't have Hebrew in my computer)... on one hand, I have a table that contains a list of table names that are sensitive in my org. In addition, not all the columns in the table are sensitive, some tables have only one or tow sensitive columns on the other hand, I have another table that contains all the SQL Statements that passed through all my DB's in the org + some other details like username, machine name etc. the sql statement is one columns - varchar2(4000). ALL the SQL TEXT is printed there. I need to create a report of the SQL Statements and their details that have any reference to the sensitive entities. I'm kind of new to PL/SQL or programming in DB in general. Can you please assist me in designing the write function and script. Additional details: Sensitive_tables table: table_name varchar2 (65) -- the names of the sensitive tables All_fields number (1) -- 0=not all the fields are sensitive, 1=all the fields are sensitive the conditions where the function should return true: 1. It's not a select/SELECT statement. 2. There is a relation to a sensitive table that all its columns are sensitive 3. There is a relation to a sensitive table and to a specific sensitive column. below is the function. There are 2 cursors - the first loop will check if the SQL Statement touches a sensitive column + a sensitive table. The second one checks if the the SQL Statement a table that all its columns are sensitive. CREATE OR REPLACE function sens_entity_match (p_statement varchar2) return VARCHAR2 IS v_match VARCHAR2(5); sql_stmt VARCHAR2(4000); TYPE tbls_wth_all_flds IS REF CURSOR; tbls_all_fields tbls_wth_all_flds; table_name_y varchar2(65); TYPE tbls_wthout_all_flds IS REF CURSOR; tbls_some_fields tbls_wthout_all_flds; table_name_n varchar2(65); BEGIN sql_stmt:=upper(p_statement); OPEN tbls_all_fields FOR select table_name from sens_tables where ALL_FIELDS='1'; OPEN tbls_some_fields FOR select table_name from sens_tables where ALL_FIELDS='0'; FETCH tbls_all_fields INTO table_name_y; FETCH tbls_some_fields INTO table_name_n; WHILE tbls_some_fields%FOUND LOOP BEGIN IF upper (sql_stmt) like 'SELECT%' THEN BEGIN RETURN ('FALSE'); v_match:='FALSE'; END; ELSIF ( ((sql_stmt LIKE '%ID_NUMBER%' ) OR (sql_stmt LIKE '%id_number%' )) AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%ADDRESS%') OR (sql_stmt LIKE '%address%')) AND (sql_stmt LIKE table_name_n) ) OR ( (((sql_stmt LIKE '%EMAIL%') OR (sql_stmt LIKE '%E_MAIL%')) OR (sql_stmt LIKE '%email%') OR (sql_stmt LIKE '%e_mail%') ) AND (sql_stmt LIKE table_name_n) ) OR (( (sql_stmt LIKE '%BANK%') or (sql_stmt LIKE '%bank%')) AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%PYM%') OR (sql_stmt LIKE '%PYM%')) AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%CREDIT_CARD%') OR (sql_stmt LIKE '%credit_card%')) AND (sql_stmt LIKE table_name_n) ) OR ( ( (sql_stmt LIKE '%USER%') OR (sql_stmt LIKE '%user%') )AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%CHURN%') OR (sql_stmt LIKE '%churn%') ) AND (sql_stmt LIKE table_name_n) ) OR ( ((sql_stmt LIKE '%LEGAL%') OR (sql_stmt LIKE '%legal%') ) AND (sql_stmt LIKE table_name_n) ) THEN BEGIN v_match:='TRUE'; RETURN ('TRUE'); END; ELSE v_match:='FALSE'; END IF; FETCH tbls_some_fields INTO table_name_n; END; END LOOP; WHILE tbls_all_fields%FOUND LOOP BEGIN IF upper (sql_stmt) like 'SELECT%' THEN BEGIN v_match:='FALSE'; RETURN ('FALSE'); END; ELSIF (sql_stmt LIKE table_name_y) THEN BEGIN v_match:='TRUE'; RETURN ('TRUE'); END; ELSE BEGIN v_match:='FALSE'; RETURN ('FALSE'); END; END IF; FETCH tbls_all_fields INTO table_name_y; END; END LOOP; RETURN (v_match); END; / Eexampls that should should be true: select sens_entity_match ('update address_data set "EMAIL" = :24 where rowid=
ld_rowid') from dual; {address_data is a sensitive_table that exists in "sensitive_tables" table and email is a sensitive column} select sens_entity_match ('isnert into legal (ssss) select bbbb from input where rowid = 666;') from dual; {legal is a sensitive_table that exists in "sensitive_tables" table and all its columns are sensitive} An example that shouldn't should be true: select sens_entity_match ('select bank from bank_