Re: ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED

From: Mark D Powell <markp28665_at_gmail.com>
Date: Wed, 15 Jul 2015 10:55:26 -0700 (PDT)
Message-ID: <c5be88b1-6bd4-409d-acc4-cc251544ed32_at_googlegroups.com>

On Wednesday, July 15, 2015 at 9:44:08 AM UTC-4, Sanae wrote:
> 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

Sanae, without having read your code closely I just wanted to ask if you are using a regular user connection, that is non-sysdba, to test? Received on Wed Jul 15 2015 - 19:55:26 CEST