0

I'm working with Microsoft Reporting Services 2008. In our database, we have a small group of confidential students that need to be taken into account for several queries. If they are a confidential student, the database needs to essentially return nothing. Currently, we do something along the lines of:

select
  case 
    when CONFIDENTIALITY_IND = 'Y' or :EmployeeClass = 'XA'
      then 'CONFIDENTIAL'
    else db.table.name
  end as name
from
  db.student

This isn't a big deal for such a small query, but most of them return many fields (often 20+) and I'm not a fan of so many case statements for obvious reasons. Because of how Reporting Services work, I can't do a general replacement either, it must be done on each and every field.

Is there a more efficient method where I could replace every value in the row with 'Confidential' in a single case statement, or at least something more elegant?

Edit: To clarify, that there isn't just this one field in the select. I only wrote one for the example, but in production, some reports are looking at displaying a huge amount of columns. I'm trying to avoid making two comparisons on every column for reports that could return rather large sets of data.

  • 2
    Can you do a "union all" with that tool? Might be slightly less painful if you selected the "confidential" students in one select, and the normal ones in another, then union (& order if necessary) . – Mat Sep 19 '14 at 5:04
  • Is the actual data in Oracle database? I'm assuming so because of the oracle tag. In that case, could you provide the version of the database? This might be a good case to use Oracle's Data Redaction if it is available for you. – sjk Sep 19 '14 at 7:06
  • A more elegant way would be writing a stored procedure. – user1363989 Sep 19 '14 at 8:53
  • @user1363989: A stored procedure would work yes, but the problem is I have to run the check on nearly every field returned, and some reports a huge number of fields. In reality, each of these clauses will need to make 2 checks. I'm trying to avoid that many statements. – Jacobm001 Sep 19 '14 at 16:43
  • 1
    Are you allowed to define views? – ypercubeᵀᴹ Sep 19 '14 at 19:37
1

what you can do, is to write two stored procedures with the following logic:

  1. Build a stored procedure which does the case thing. This may be optional but the resulting sql would look better and easier to debug/develop.
  2. Define a stored procedure which has as input the tablename
  3. Read out the columns of the table - maybe here can be done some grouping/distinct which columns fall into the case CONFIDENTIALITY_IND
  4. Build up the sql statement dynamically by iterating over the result of 3. When the column is your special case column - apply the stored procedure of 1. Otherwise select it. How to build up dynamic sql look here: http://www.dba-oracle.com/t_oracle_execute_immediate.htm - execute immediate is your friend here.

Hope I understood your problem correct. This would be my way

1

In Oracle 11.2.0.3, Virtual Private Database allows you to hide rows or columns. For example:

create or replace function check_confidentiality( p_schema in varchar2, p_object in varchar2 ) return varchar2 as
begin  
  return 'not (CONFIDENTIALITY_IND = ''Y'' or EmployeeClass = ''XA'')';
end;
/

call dbms_rls.add_policy(
  object_schema => 'schema',
  object_name => 'students',
  policy_name => 'hide_confidential_students',
  function_schema => 'schema',
  policy_function => 'check_confidentiality',
  statement_types => 'select'
);
/

After adding this policy, Oracle injects the predicate returned by check_confidentiality to every query against the students table.

This is not the same as replacing every value with 'CONFIDENTIAL', but maybe it would be acceptable in your case. Conditional redaction is possible to implement in check_confidentiality function (for example by checking if the user is a member of a role that allows full access).

1

Another approach would be to create UNION ALL view:

select 'REDACTED' name, employeeclass, confidentiality_ind
from students
where (CONFIDENTIALITY_IND = 'Y' or EmployeeClass = 'XA') and not <privilege check>

union all

select name, employeeclass, confidentiality_ind
from students
where not (CONFIDENTIALITY_IND = 'Y' or EmployeeClass = 'XA') or <privilege check>;

Where privilege check could be a subquery or a call to Application Context (which would allow passing parameters to view)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.