Home » RDBMS Server » Server Administration » adding a column to a policy in VPD (11g R2,Redhat linux 6.3)
adding a column to a policy in VPD [message #610823] Mon, 24 March 2014 15:02 Go to next message
oradba123
Messages: 78
Registered: June 2009
Location: india
Member


Hi guys,


we have already configure VPD(virtaul private database) on set of tables, our requirement is to add one more column for secured named YMPDBA on CRPDTA.F06106 table which has already 3 column secured (YMPHRT,YMRTAM,YMGPA) ,
please assists me how do i do this?
do i need to drop policy and recreate it by adding new column with existing ?
for example:




CREATE TABLE CRPDTA.F06106
(
YMAN8 NUMBER,
YMEXR NCHAR(30),
YMPDBA NUMBER,
YMDISO NCHAR(1),
YMRTAM NUMBER,
YMGPA NUMBER,
YMPHRT NUMBER,
YMPPRT NUMBER)






CREATE OR REPLACE TRIGGER CRPDTA.F06106_before_upd


BEFORE UPDATE ON CRPDTA.F06106
FOR EACH ROW
WHEN (
new.YMGPA = 0
)
DECLARE


BEGIN


if dbms_session.is_role_enabled('SECURED_PAYROLL_ROLE') then


:new.YMPHRT := :old.YMPHRT;
:new.YMRTAM := :old.YMRTAM;
:new.YMGPA := :old.YMGPA;


end if;


END;
/




BEGIN
SYS.DBMS_RLS.ADD_POLICY (
object_schema => 'CRPDTA'
,object_name => 'F06106'
,policy_name => 'F06106_POLICY'
,function_schema => 'CRPDTA'
,policy_function => 'PAYROLL_FGAC.FUNC_FGAC'
,statement_types => 'SELECT'
,policy_type => dbms_rls.dynamic
,long_predicate => FALSE
,sec_relevant_cols => 'YMPHRT,YMRTAM,YMGPA'
,sec_relevant_cols_opt => dbms_rls.all_rows
,update_check => FALSE
,static_policy => FALSE
,enable => TRUE );
END;
/


Best Regards.
Re: adding a column to a policy in VPD [message #610913 is a reply to message #610823] Tue, 25 March 2014 17:23 Go to previous message
spacebar
Messages: 26
Registered: February 2007
Junior Member
If I understand correctly that you want to add column(s) to a table, then check out this example:
http://www.dba-oracle.com/t_alter_table_add_column_syntax_example.htm
Previous Topic: Should table compression decrease redo?
Next Topic: SQL Tuning Adviser error in alert log.
Goto Forum:
  


Current Time: Fri Nov 28 20:37:35 CST 2014

Total time taken to generate the page: 0.08560 seconds