ASKER
ASKER
ASKER
ASKER
ASKER
CREATE OR REPLACE FUNCTION schema name.function name(obj_schema VARCHAR2,
obj_name VARCHAR2) RETURN VARCHAR2 IS
d_predicate VARCHAR2(20000);
cnt_weekend INTEGER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF dbms_mview.i_am_a_refresh THEN
RETURN NULL;
END IF;
d_predicate := '1=1'; -- standard predicate
select count(1) into cnt_weekend
from dual
where MOD(TO_CHAR(sysdate, 'J'), 7) + 1 IN (6, 7);
if cnt_weekend = 1 then
d_predicate := '1=0';
end if;
RETURN d_predicate;
EXCEPTION
WHEN OTHERS THEN
RETURN '1=0'; -- when an exception occurs, prevent to see any data
END function name;
/
ASKER
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
TRUSTED BY
http://www.oracle.com/technetwork/database/options/advanced-security/index-099011.html
From what I can understand, it provides the much needed data at rest protection, when people can try to bypass the database and, based on the use I did, based on roles, protect the use through the normal database usage as well.
This will prevent to make changes related to fields encryption.
Now, about how long should you have information stored, this needs to keep up with your company policies for data retention. Usually, this would go around keep at a maximum of 7 years of information for the most cases.
Now, if you want to prevent users to see that not related to them, then you need to think a row level security, which could be implemented via security profiles. So for example, a manager can see his data and of everyone under him, but an Individual Contributor can only see his own information. On the data warehouse I work with, we use that + a security predicate function to filter each and every query at database level, without changing OBIEE or SAP BO models.