Oracle Database Design – Why Different Accounts See Different Data

database-designoracle

At my work, we have an Oracle database that I can connect to using different accounts.

The server name and port are identical between the accounts, but each account sees different data, and can see different table schemas. (I do mean different columns in different tables. i.e. if I add a column to a table in one account, I don't see that change reflected in the other account.)

Is there a name for this practice? I would like to read up on it, but I could not find anything in google.

Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

Best Answer

I see two possibilities, and they are not mutually exclusive.

First is that you are not really looking at the same table. If you connect as SCOTT and select from EMP without qualifying the table name, you are selecting from SCOTT.EMP. If you connect as FRED and do the same, you will be selecting from FRED.EMP. Two different schemas with the same table name. The two tables are totally unrelated and could very well have different structures in addition to certainly having different data.

The other is Virtual Private Database (VPD). Two users actually selecting from the same table, but only seeing the rows they are allowed to see, based on the VPD policies.

With SQLplus or SQL Developer, connect with one account and SELECT TABLE_NAME FROM USER_TABLES; Do the same with the other account. Be sure to select from USER_TABLES, not ALL_TABLES or DBA_tables. That will show you just the tables owned by the connected user.

Related Question