Home » SQL & PL/SQL » SQL & PL/SQL » xml generate from db on redaction column (Oracle 12 C 2)
xml generate from db on redaction column [message #686559] Wed, 12 October 2022 03:16 Go to next message
sss111ind
Messages: 628
Registered: April 2012
Location: India
Senior Member

Hi All,

I have applied dbms_redact on ename column. But i have to generate xml based on ename column as per following queries however its giving error. Please help me how to resolve.


SELECT XMLElement("emp", e.ename )  FROM emp e; 

Error code: ORA-28093
Description: operation on column string is not supported by data redaction

Thank you in advance.
Re: xml generate from db on redaction column [message #686560 is a reply to message #686559] Wed, 12 October 2022 04:16 Go to previous messageGo to next message
John Watson
Messages: 8793
Registered: January 2010
Location: Global Village
Senior Member
I get the same result as you, using release 19.3. I haven't tested on a later patchset or on 21.x, you can do that yourself. If it still doesn't work, then it may be a technology limitation, or perhaps they haven't got around to implementing it yet, or perhaps it is a bug.

In case anyone is interested, OP didn't provide a test case, but this is what I used:
grant dba to scott;

conn scott/tiger

exec dbms_redact.add_policy(-
object_schema   => 'scott',- 
object_name     => 'emp',- 
column_name     => 'ename',-
policy_name     => 'redact ename', -
function_type   => dbms_redact.full,-
expression =>'sys_context(''sys_session_roles'',''DBA'')= ''FALSE''');

select ename from emp;
SELECT XMLElement("emp", e.ename )  FROM emp e; 

set role none;

select ename from emp;
SELECT XMLElement("emp", e.ename )  FROM emp e; 
Re: xml generate from db on redaction column [message #686561 is a reply to message #686560] Wed, 12 October 2022 04:36 Go to previous message
sss111ind
Messages: 628
Registered: April 2012
Location: India
Senior Member

Thank you for reply john,

In oracle documentation 12c 2 ,it is written as follows, but it is creating problem for the reports where we have used JSON or XML.

You cannot use XML generation functions on columns that have Oracle Data Redaction policies defined on them.
Previous Topic: Need a trigger or similar thing
Next Topic: Fine Grain Access Policy
Goto Forum:
  


Current Time: Fri Jan 27 21:16:07 CST 2023