Re: ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED

From: Sanae <aithssaine.sanae_at_gmail.com>
Date: Wed, 15 Jul 2015 06:44:05 -0700 (PDT)
Message-ID: <b4aa16f0-6d58-43f3-b53b-271071327258_at_googlegroups.com>

to clarify things I have this procedure

create or replace procedure open_table is begin                 

execute immediate 'create or replace view emp_view as select

							              decrypter(id,image)as id,
							 decrypter(nom,image)as nom,
							 decrypter(prenom,image)as prenom,
							 decrypter(salaire,image)as salaire
							

 from emp';
SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (

	name             => 'emp_rewrite',
	source_stmt      => 'SELECT id,nom,prenom,salaire FROM emp',
	destination_stmt => 'SELECT id,nom,prenom,salaire FROM emp_view',
	validate         => FALSE,
	rewrite_mode     => 'TEXT_MATCH');

execute immediate 'ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED';

 dbms_output.put_line('ouverture securisé de la table! ');  end;/         

when I call this procedure it works very well but i need to call it an trigger before logon on database so i create this trigger

CREATE or replace TRIGGER login_MISD
AFTER logon ON DATABASE
 begin

          if user='XXX' then
		 open_table;
           end if ;

 end;
  /

 the advanced query rewrite is created but not working until I execute this request ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; manually so i don't get any Oracle error
how to enforced my trigger to execute this query? thank's in advance Received on Wed Jul 15 2015 - 15:44:05 CEST