The new experience of ORACLE's virtual Private database

Oracle
The new experience of ORACLE's virtual Private database

===============================================

Welcome everyone to exchange with me: small white enhydra_boy@tom.com

Welcome to reprint, please keep this statement, thank you!

================================================

Before beginning the introduction of virtual Private database for Oracle, I would like to say a few words about the concept of Row-rule control (row record-level access controls).

Row record level access control problem and its significance?

The enterprise's application system is inseparable from the database system, the database system's permission control is a very important link, the large-scale database system (ORACLE, DB2, SYBASE, MS SQL Server) all provides the perfect user management mechanism, thus may control the database object (table, view, function , stored procedures, packages, and so on. However, this is often object-level.

As business needs continue to be raised, there are requirements for line records control:

1 data query and report output data need to be able to effectively isolate, such as in a simple sales data statistics application, regional managers and sales staff to query the data is different.

2 ASP (Application Service Provider) system appears, in the system structure, there are many enterprise user data will be stored in the same database species, but the system needs to be able to effectively isolate.

In order to meet this demand, in the business data table needs to add some fields to control, the application development will often develop a lot of code to implement the line record control. However, as the business changes, we find it increasingly expensive to develop and maintain this management requirement.

We need to look for a new solution that makes the architecture of an application system simple, scalable, and inexpensive to manage and maintain.

A new feature of oracle8i virtual Private Database

ORACLE 8i provides a new feature to implement row-level rules, called Virtual Private Database. Make full use of the virtual Private database technology provided by 8i, you can realize that the data of a database schema is accessed by multiple database users at the same time, but it can isolate the data content well. Obviously, this is not the original object-level control concept.

The following is the author's experience, written down for your reference. Let me begin by using a simple example to illustrate the new feature of Oracle 8i (which requires Oracle 8i Enterprise Edition).

Environment: Windows Server + ORACLE 8.1.7 (Enterprise Edition)

Under Scott's user, there is a Customers table that records customer information and assigns an Oracle database login account to each client in the future, and customers can log in and inquire about their order. So, what we need to do is to be able to match each login account with the client code. This means that to implement a mapping relationship, of course, you can do this by building your own relational mapping table, writing code, but Oracle 8I makes it all very simple (my next introduction will revolve around Oracle 8i This technology is how our work is simpler and easier to control, remember, This is the author's purpose of writing this article.

--Create a SECUSR account for permissions control

Connect system/manager@oracle;

Create user secusr identified by SECUSR;

Grant CONNECT,RESOURCE,DBA to SECUSR;

--giving Secusr the right to inquire customers

Connect scott/tiger@oracle;

Grant SELECT on "Customers" to secusr;

--Connect to SECUSR user

Connect secusr/secusr@oracle;

--Create context

The Create context Customer_context USING secusr. Customer_security_context;

--Create Package Customer_security_context

Create or Replace Package

Secusr.customer_security_context is

Procedure Set_customerid;

End



Create or Replace package body

Secusr. Customer_security_context is

Procedure Set_customerid is

Begin

IF sys_context (' USERENV ', ' session_user ') = ' SCOTT ' THEN

Dbms_session. Set_context (' Customer_context ', ' CustomerID ', ' ALFKI ');

End IF;

End

End



--Authorize

Grant execute on SECUSR. Customer_security_context to public;

ORACLE 8i provides the concept of context (connection contexts), similar to the session in ASP, where you can set up multiple global variables for the current connection and record the information until the connection is released. The above code, is to use SCOTT account login connection, a client code mapping (SCOTT->ALFKI), and, at any time can sys_context to query.

The specific code is as follows:

Sql> Connect scott/tiger@oracle;

is connected.

Sql> Execute secusr. Customer_security_context.set_customerid;

The PL/SQL process has completed successfully.

Sql> Select Sys_context (' Customer_context ', ' CUSTOMERID ') from DUAL;

Sys_context (' Customer_context ', ' CUSTOMERID ')

--------------------------------------------------------------------------------

ALFKI

However, I do not feel enough, to be able to do each connection when the establishment of the automatic completion of this mapping, it is gratifying that ORACLE 8i provides a system-level triggers, let me easily achieve.

--scott User Login Trigger

Connect system/manager@oracle

CREATE OR REPLACE TRIGGER scott.tg_set_usr_context

After LOGON on DATABASE

BEGIN

Secusr.customer_security_context.set_customerid;

End;

--Cut off the connection and land again.

Oracle8i Enterprise Edition Release 8.1.7.0.0-production

With the partitioning option

Jserver Release 8.1.7.0.0-production



Sql> CONNECT scott/tiger@oracle

is connected.

Sql> Select Sys_context (' Customer_context ', ' CUSTOMERID ') from DUAL;

Sys_context (' Customer_context ', ' CUSTOMERID ')

--------------------------------------------------------------------------------

ALFKI

Sql>

Well, that's easy enough.

How do I use virtual Private database to achieve data Control access requirements on SQL DML?

Virtual Private Database technology can set the filtering policy for DML operations on a table record. Oracle8i provides the concept of policy and is equipped with a system package to complete the setup. Next, let me introduce:

Connect secusr/secusr@oracle

--do a function to return the corresponding filter condition

Create or Replace package secusr.customer_security is

function customer_sec

return VARCHAR2;

End



Create or replace package body secusr.customer_security

Is

function customer_sec (D1 varchar2,d2 varchar2)

return VARCHAR2

Is

Begin

IF sys_context (' USERENV ', ' Session_user ') in (' SYS ', ' SYSTEM ', ' secusr ') THEN

return NULL;

ELSE

Return ' customerid= ' | | Sys_context (' Customer_context ', ' CUSTOMERID ') | | '''';

End IF;

End

End



--Set up the partition filter of table data

EXECUTE Dbms_rls. Add_policy (' SCOTT ', ' Customers ', ' customers_sec_policy ',

' Secusr ',

' Customer_security.customer_sec ',

' Select,update,delete ');

The Customer_sec function of the customer_security package is implemented, according to the contents of CustomerID in the previous context, to return a filtering strategy, the function of the parameter form is fixed. By dbms_rls the add_policy process of the package, the policy condition is bound to the table, and every select,update,delete will automatically apply this strategy.

Okay, now let's test it out.

--Log in with Scott and see only his own information.

Sql> Connect scott/tiger@oracle;

is connected.

Sql> Select customerid,city from "Customers";



CUSTOMERID City

---------- ------------------------------

ALFKI Berlin

--With System login, you can see all the

Sql> Connect system/manager@oracle;

is connected.

Sql> Select customerid,city from "SCOTT". " Customers ";

CUSTOMERID City

---------- ------------------------------

ALFKI Berlin

Anatr México D.F.

ANTON México D.F.

Arout London

Bergs Lule? D.F.

Blaus Mannheim

Blonp Strasbourg

Bolid Madrid

Bonap Marseille

Bottm Tsawassen

Bsbev London

Very good, completely satisfied with the requirements, now can be assured that Scott account to the customer (ALFKI), customers can inquire about their own orders, of course, can only be their own.

Sql> SELECT A.orderid,a.customerid,a.orderdate,

2 sum (c.unitprice*c.quantity* (1-c.discount)) as Totalmoney

3 from "Orders" A, "Customers" B, "Order Details" C

4 where A.customerid=b.customerid and A.orderid=c.orderid

5 Group by A.orderid,a.customerid,a.orderdate;



ORDERID CUSTOMERID ORDERDATE Totalmoney

---------- ---------- ---------- ----------------------------------------

10643 ALFKI 2 May-August-97 814.5

10692 ALFKI March-October-97 878

10702 ALFKI 1 March-October-97 330

10835 ALFKI 1 May-January-98 845.8

10952 ALFKI 1 June-March-98 471.2

11011 ALFKI September-April-98 933.5

6 rows have been selected.

Below, let me summarize the advantages of virtual Private database:

• Provides a workable, reliable, fully transparent user-controlled protocol

With your own set of user control permissions, Virtual Private database is transparent to users, your application does not have to consider this, your application needs to be more focused on the implementation of your business process, rather than let data isolation control, as a process of your business process. You can develop functional applications completely, then add data isolation control, which is based on the backend database system, which is completely compatible with existing applications and does not cause existing programs to run

L control more closely, not only for business systems

As a result of the background database technology, this control, all applications (in addition to business systems, database management tools, etc.) have played a controlling role. In contrast, the use of their own rights control applications, only in the application of the business system is effective, while the other (sqlplus,sqlplus worksheet, etc.) is completely ineffective. And most systems use a database account login to the system, in this case, the account for the business database generally operating permissions are very high, and this account once leaked, the attackers use sqlplus tools can be used to steal data.

L This technology is more flexible and easier to manage than the methods implemented by the application itself, while the cost of development is minimal.

The use of this logging control technology, do not need to implement through the application, greatly simplifies the application system complexity, but also make your system structure is very clear, and the possibility of error is lower. Of course, your own control system can also do Oracle 8i function, but will greatly increase the cost of development.

L for ASP (Application Service Provider) system, satisfies the requirement that the user data is stored together, but can be independently accessed separately. The author believes that this technology is particularly useful for building users of ASP applications, making full use of the new technologies provided by Oracle to make your system more secure and reliable.

In short, Virtual Private database is a powerful line-level rule control technology, is a powerful feature provided to us by oracle8i, fully applied, can greatly reduce the cost of software development.

The author believes that virtual Private database technology is a very useful technology, while in SQL Server 2000 and Sybase ASE 12.5, no similar technology is seen.