Perhaps I'm missing something. But I'm struggling to understand why you want to use invoker's rights with this method.
If you create your procedures with invoker's rights, they need select privileges on the base tables:
grant create session, create table, unlimited tablespace to
datau identified by datau;
grant create session, create procedure to
codeu identified by codeu;
grant create session, create procedure to
appu identified by appu;
create table datau.t (
t_id int not null primary key,
email_address varchar2(320) not null
);
insert into datau.t values (1, 'chris@chris.com');
grant select on datau.t to codeu;
create or replace function codeu.def_rights ( id int )
return datau.t.email_address%type
authid definer as
email datau.t.email_address%type;
begin
select email_address into email
from datau.t where t_id = id;
return email;
end def_rights;
/
create or replace function codeu.inv_rights ( id int )
return datau.t.email_address%type
authid current_user as
email datau.t.email_address%type;
begin
select email_address into email
from datau.t where t_id = id;
return email;
end inv_rights;
/
grant execute on codeu.inv_rights to appu;
grant execute on codeu.def_rights to appu;
conn appu/appu
select codeu.inv_rights(1) from dual ;
ORA-00942: table or view does not exist
select codeu.def_rights(1) from dual ;
CODEU.DEF_RIGHTS(1)
chris@chris.com Which kind of defeats the point of creating this hard shell!
If you can select from the tables, what's to stop people bypassing your code and querying them directly?
So why exactly do you want to use invoker's rights?
In any case, you're right: if you're using the SESSION_USER sys context to enable the redaction policy, then your code will see the redacted data. Not the original:
BEGIN
DBMS_REDACT.add_policy(
object_schema => 'datau',
object_name => 't',
column_name => 'email_address',
policy_name => 'redact_email_addr',
function_type => dbms_redact.regexp,
regexp_pattern => dbms_redact.re_pattern_email_address,
regexp_replace_string => dbms_redact.RE_REDACT_EMAIL_ENTIRE,
regexp_position => dbms_redact.re_beginning,
regexp_occurrence => dbms_redact.re_all,
expression => q'|SYS_CONTEXT('USERENV','SESSION_USER') not in ('CODEU', 'DATAU')|'
);
END;
/
create or replace function codeu.def_rights ( id int )
return datau.t.email_address%type
authid definer as
email datau.t.email_address%type;
begin
select email_address into email
from datau.t where t_id = id;
if email = 'chris@chris.com' then
dbms_output.put_line('Hi Chris!');
else
dbms_output.put_line('Who are you?!');
end if;
return email;
end def_rights;
/
conn codeu/codeu
select def_rights(1) from dual ;
DEF_RIGHTS(1)
chris@chris.com
Hi Chris!
conn appu/appu
select codeu.def_rights(1) from dual ;
CODEU.DEF_RIGHTS(1)
xxxx@xxxxx.com
Who are you?!You could look into pushing as much of your processing into SQL as possible. But only the join and where clauses. Data you manipulate in the select is still redacted:
create or replace function codeu.def_rights ( id int )
return datau.t.email_address%type
authid definer as
email datau.t.email_address%type;
output varchar2(20);
begin
select email_address ,
case email_address
when 'chris@chris.com' then 'Hi Chris!'
else 'Who are you?!'
end
into email, output
from datau.t where t_id = id;
dbms_output.put_line( output );
return email;
end def_rights;
/
conn appu/appu
select codeu.def_rights(1) from dual ;
CODEU.DEF_RIGHTS(1)
xxxx@xxxxx.com
Who are you?!To overcome this you could change your redaction policies to work on other userenv variaables, such as the action. Then set this as needed in your code:
create or replace function codeu.def_rights ( id int )
return datau.t.email_address%type
authid definer as
plain_email datau.t.email_address%type;
redacted_email datau.t.email_address%type;
begin
dbms_application_info.set_action('clear');
select email_address into plain_email
from datau.t where t_id = id;
dbms_application_info.set_action('redact');
select email_address into redacted_email
from datau.t where t_id = id;
if plain_email = 'chris@chris.com' then
dbms_output.put_line('Hi Chris!');
else
dbms_output.put_line('Who are you?!');
end if;
return redacted_email;
end def_rights;
/
begin
dbms_redact.alter_policy(
'datau', 't', 'redact_email_addr',
action => dbms_redact.MODIFY_EXPRESSION,
column_name => 'email_address',
expression => q'|SYS_CONTEXT('USERENV','ACTION') <> 'clear'|');
end;
/
conn appu/appu
select codeu.def_rights(1) from dual ;
CODEU.DEF_RIGHTS(1)
xxxx@xxxxx.com
Hi Chris!But this can get really messy. Keeping track of which of the variables are redacted is tricky. And potentially doubles the number of your SQL queries.
It's better to separate your data processing and fetching code completely. Though I understand this brings its own complications...