Our Oracle version is 19c We have a need to split our data into two separate databases. Most recent data will go into DB_1. Older data will go into DB_2. We do have Webserver tier. One options is to have views as follows CREATE VIEW all_cust as SELECT * FROM customer@DB_1 where ( date within 1 months ) union SELECT * from customer@DB_2 where ( date > 1 months ) But this will require massive changes to application - because all accesses to CUSTOMER will need to be changed to ALL_CUST. Can the above setup be configured so that it is completely transparent to application ? Can VPD be a solution to this ?

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these records, for business reasons, we don't want in this table. I was thinking I could write a trigger that "aborts" any of these rows without throwing an error (so that the application doesn't fail in the front end, we want it to think it did its thing). I am intimately familiar with this application and know it won't cause any ugly side effects, we just can't modify it to behave the way we need it to. So, is this possible, and if not, what would be another way to go about it? I thought about having some sort of Job that routinely cleans the table for records that we don't want, but I would rather for these records to never be there to begin with so there's no degree of "dirtiness" at any one time in the table.

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.

> **Possible Duplicate:** > [Column level security](https://dba.stackexchange.com/questions/24292/column-level-security) I have a view which can be viewed by multiple users, and I would like to restrict some columns to specific users. e.g I have a view with fields A, B... F, and I have users P, Q, R, S. Here's what I am trying to implement: * when user P browses the view, he will see data in A, B, C and F, and columns D and E as NULL * when user Q browses the view, he will see data in all fields * when user R browses the view, he will see data in A, D, E and F, and columns B and C as NULL

Can row-level security in Oracle be controlled by a user's session credentials or user context? Here's an example to illustrate what I'm looking for: Our database contains a bunch of sensitive company information, including salaries. HR administrators should be able to see salaries in their search results, but facilities management staff shouldn't, even if they use the same search parameters. The catch is that our app only has a single database user, and all requests go through it, so the security can't be set up to simply check the database user's ID. We'd have to pass the info in as, say, a user context. We're considering moving to Oracle Access Manager/WebLogic, if it makes a difference.

I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but aren't allowed to see items like salary or ssn. Is it possible to filter specific columns for users?

If there is a customer table, and some extra-private/secure information like SSN is there, should I create another table to store such information and lock that table so only certain people can view it even though it's strictly one-to-one? or can columns be locked too from unauthorized viewing based on rights? Even if both types of security mechanism do exist, is one preferred over the another? I might have said something very vague but I've used SQL Server more often.

Lets assume that we have the following situation: We have an Oracle schema/user: USER_TEST (with the "create session" privilege). SYS as SYSDBA creates couple Tables on schema USER_TEST. Is it possible to forbid access for the user USER_TEST to tables on his schema?