Transparent Avoidance Using Virtual Private Database
Virtual Private Database
(VPD) is an advanced data conflict avoidance technique for
transparent data segmentation. After creating database's
table polices and implementing fine-grain access control and
application contexts, SQL statements are dynamically
modified to control rows (and columns) level access by
database users. Figure 5-27 shows how users on node-1 and on
node-2 access different subset of data, even when the
session is connected to the same database. Fine-grain access
control limits rows access by implementing a database
function that returns a predicate clause (WHERE) to
virtually limit row access by the database users.
Figure 5-27: Data Segmentation Using Virtual
Private Database
Our objective for using
Virtual Private Database is to enforce data access based on
ownership, so that a group of users within a database sees
only their own accounts.
The implementation is
transparent - defined using policies that are independent of
the applications. These
active policies are cached in the SGA shared pool allowing
in-memory access for faster
evaluation and processing of fine-grain access control
using SYS_CONTEXT function.
Begin by using DBMS_SERVICE
PL/SQL supplied package to create non-default service names
which will be used to associate a database user to
one-and-only-one group at any time. However, in the event of
a node failure, the user connects to the surviving node
using the same service name but referencing the surviving
node database service. Figure 5-27 shows the service name
s1e2_ma for users connecting to node-1 database and t1e2_ny
for users connecting to node-2 database.
The calls to
DBMS_SERVICE.CREATE_SERVICE create the service names s1e2_ma
and t1e2_ny, then start these service names for user
connections. The procedure
DBMS_SERVICE.CREATE_SERVICE requires the service name
and service network name, use the service name to manage the
service name.
SQL> BEGIN
2
DBMS_SERVICE.CREATE_SERVICE(service_name=>'s1e2_ma',
3
network_name=>'s1e2_am.precisetrace.com');
4
END;
5 /
PL/SQL procedure successfully
completed.
SQL> EXEC DBMS_SERVICE.START_SERVICE('s1e2_ma');
PL/SQL procedure successfully
completed.
SQL> BEGIN
2
DBMS_SERVICE.CREATE_SERVICE(service_name=>'t1e2_ny',
3
network_name=>'t1e2_ny.precisetrace.com');
4
END;
5 /
PL/SQL procedure successfully
completed.
SQL> EXEC DBMS_SERVICE.START_SERVICE('t1e2_ny');
PL/SQL procedure successfully
completed.
Use 'netca utility' to create
the database connect string for the new service names and
perform quick tests. By creating the two service names on
node-1 and node-2 instances, they are readily available in
the event of either node failure. The connect string for
s1e2_ma is configured (t1e2_ny is not shown), followed by a
successful test using
the connect string s1e2_ma
and t1e2_ny.
S1E2_MA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ggs_source)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = s1e2_ma.precisetrace.com)
)
)
SQL> CONN fawzi/oracle@s1e2_ma
Connected.
SQL> CONN deema/oracle@t1e2_ny
Connected.
|
|
 |
|
Oracle GoldenGate 12c
The above is an excerpt from the upcoming
12c book
Oracle GoldenGate 12c: A Hands-on Guide to Data
Replication & Integration using Oracle & SQL Server.
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
