Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of an oracle virtual private database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The so-called virtual private database (VPD) refers to the configuration in the database so that different users can only view part of the data in a table. VPD is divided into two levels.

Row level: at this level, you can control that some users can only see certain rows of data. For example, for the sales data table sales, each salesperson can only retrieve his own sales data, not the sales data of other salespeople.

Column level: at this level, you can control that some users cannot retrieve the value of a column in a table. For example, the employees table under user HR contains a salary column. Because this column is sensitive, other users are not allowed to query the value of this column. When other users retrieve the column, they will find that its values are all null.

First, row-based VPD

Row-based VPD is also called Fine-Grained Access Control, or FGAC for short. FGAC is implemented by defining rules, which are called FGAC policies (policy). If FGAC is set on a table, when a user issues a query or DML statement against the table, Oracle automatically rewrites those SQL statements according to the defined FGAC policy. It is rewritten by automatically adding where conditions after the SQL statement.

For example, we have a table sales_list under OE users that holds all the sales records. Each salesperson can only check his own sales records. Therefore, we set the FGAC policy on the sales table to implement this business requirement. If a salesperson (assuming the user name he or she logs in to is S0020) issues the following query statement:

Select * from sales_list

When Oracle is executing the statement, if it is found that the FGAC policy exists on the sales_list table, the SQL statement will be rewritten according to the FGAC policy as follows:

Select * from sales_list where seller_id='S0020'

To the user, the process of adding where conditions is completely transparent, and the user does not know that Oracle has rewritten the SQL statement he issued, thus filtering the query results. Of course, if the salesperson issues a statement that reads:

Select * from sales_list where values > 1000

Then, when Oracle rewrites the SQL statement, it will be rewritten as follows:

Select * from sales_list where qty_sold > 1000 and seller_id='S0020'

There are many advantages to using FGAC policies to limit the way records are returned. For example, there is no need to rewrite the application, completely transparent to users, centralized settings, easy to manage, and so on.

When using FGAC, the concept of application context (Application Context) is involved, which can be used to simplify the implementation of FGAC. The application context is a database object that can be understood as a global environment variable for each session in the database. Once the user logs in to the database and creates the session, the application context is available throughout the life cycle of the session. Multiple properties can be defined in the application context and specific values can be set for these properties. On the other hand, the user cannot modify the value of the property directly, but can only modify the value of the property through the package. The application context is always owned by the user sys.

For example, for the previous example of the sales_list table. We can create an application context in which the user's ID number is placed as an attribute value when the user logs in. Then when the FGAC policy is defined, the user's ID number is taken out and returned to Oracle as a qualification phrase (that is, a where conditional statement) to implement FGAC.

In the Oracle database, an application context has been pre-established for each session: userenv. Once the session is established, the session can use this application context. Some properties are pre-defined in userenv, such as ip_address, session_user, db_name, and so on. We use the sys_context function when getting property values in the context of the application. This function contains two parameters, the first parameter represents the name of the application context, and the second parameter represents the name of the property to be displayed. As follows:

SQL > select sys_context ('userenv','ip_address') "IP", sys_context (' userenv','db_name') "DB" from dual; IP DB-- 152.68.32.60 ora10g

We can also create our own application context, as follows:

SQL > create or replace context sales_ctx using oe.sales_app_pkg

In this case, sales_ctx is the name of the application context, and sales_app_pkg is the package used to set properties in sales_ctx. When creating an application context, the package specified to set the properties in it does not have to exist in advance. However, when setting property values for the application context, the package must exist, otherwise an error is reported. If you want to delete the application context, use the following command:

SQL > drop context sales _ ctx

Once we have created the application context, we can set properties in it. When setting specific application context properties, you must use the package dbms_session.set_context provided by Oracle to set its properties. The format of its use is:

Dbms_session.set_context ('context_name',' attribute_name', 'attribute_value')

We can only use dbms_session.set_context in the package, not directly in SQL*Plus. As follows:

SQL > show user USER is "SYS" SQL > exec dbms_session.set_context ('sales_ctx','seller_id','S0020'); BEGIN dbms_session.set_context (' sales_ctx','seller_id','S0020'); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 90 ORA-06512: at line 1

Let's create the oe.sales_app_pkg package, as follows:

SQL > connect oe/oe SQL > create or replace package sales_app_pkg is 2 procedure set_sales_context; 3 end; 4 / SQL > create or replace package body sales_app_pkg is 2 procedure set_sales_context is 3 begin 4 dbms_session.set_context ('sales_ctx','seller_id',user); 5 end; 6 end; 7 / SQL > grant select on sales_list to public; SQL > grant update on sales_list to public; SQL > grant execute on sales_app_pkg to public

After giving all users permission to execute the oe.sales_app_pkg package, we can test that the application context is in effect.

SQL > connect hr/hr SQL > exec oe.sales_app_pkg.set_sales_context; SQL > select sys_context ('sales_ctx','seller_id') from dual SYS_CONTEXT ('SALES_CTX','SELLER_ID')-HR

As you can see, the application context is in effect. Next, we create a function for the FGAC rule.

SQL > create or replace package sales_app_pkg is 2 procedure set_sales_context; 3 function where_condition 4 (p_schema_name varchar2,p_tab_name varchar2) 5 return varchar2; 6 end; 7 / SQL > create or replace package body sales_app_pkg is 2 procedure set_sales_context is 3 v_user varchar2 (30); 4 begin 5 dbms_session.set_context ('sales_ctx','seller_id',user); 6 end 7 8 function where_condition 9 (p_schema_name varchar2,p_tab_name varchar2) return varchar2 is 10 v_seller_id varchar2: = upper (sys_context ('sales_ctx','seller_id')); 11 v_where_condition varchar2 (2000); 12 begin 13 if v_seller_id like' S% 'then 14 v_where_condition: =' seller_id ='| |''| v_seller_id | |'| 15 else 16 v_where_condition: = null; 17 end if; 18 return where conditions; 19 end; 20 end; 21 /

Here, we focus on the where_condition function, which returns a qualification for the FGAC rule. Such an FGAC rule function must have two incoming parameters, the first representing the schema name and the second representing the name of the table. Indicates that FGAC rules are added to which table under which schema. At the same time, a character value must be returned, which is automatically added by Oracle to the where condition section of the SQL statement. However, function names and parameter names can be specified as needed. You can see from the function body defined here that if the login user name starts with S, it will be restricted by the FGAC rule. Seller_id='Sxxxx' will be added to the where condition, and Sxxxx represents the login user name. Otherwise, if you log in as another user, you will not be restricted by FGAC rules.

After creating the function for the FGAC rule, we begin to define the FGAC rule.

SQL > connect / as sysdba SQL > begin 2 dbms_rls.add_policy (3 OBJECT_SCHEMA= > 'oe', 4 OBJECT_NAME= >' sales_list', 5 POLICY_NAME= > 'oe_sales_list_fgac', 6 FUNCTION_SCHEMA= >' oe', 7 POLICY_FUNCTION= > 'sales_app_pkg.where_condition', 8 STATEMENT_TYPES= >' select,update', 9 UPDATE_CHECK= > true, 10 ENABLE= > true); 11 end; 12 /

As shown above, we use the dbms_rls package to create FGAC rules. We create a rule for the sales_list table under user OE; this rule uses the sales_app_pkg.where_condition function under user OE to return the where condition; the SQL statement type of the rule is select and update; the update_check parameter indicates whether to determine whether the updated result meets the FGAC rule; when creating the rule, we also enable the rule (enable is set to true).

After creating the FGAC rule, we need to call the set_sales_context stored procedure in the sales_app_pkg package to set the value of the seller_id property in the user's application context when the user logs in to the application. In practical application, we can set it on the login interface when the user clicks the login button. To demonstrate the effect here, we create a login trigger to set it, as follows:

SQL > connect / as sysdba SQL > create or replace trigger set_seller_id_on_logon 2 after logon on DATABASE 3 begin 4 oe.sales_app_pkg.set_sales_context; 5 end; 6 /

Now we can start testing the effect of the FGAC rules.

SQL > connect oe/oe SQL > select seller_id,count (*) from sales_list group by seller_id; SELLER_ID COUNT (*)-- S0010 1067 S0030 968 S0020 1465

After logging in as user OE, you can see the number of rows of data for each of the three salespeople. Then log in as S0010:

SQL > connect s0010/s0010 SQL > select sys_context ('sales_ctx','seller_id') from dual; SYS_CONTEXT (' SALES_CTX','SELLER_ID')-- S0010 SQL > select seller_id,count (*) from oe.sales_list group by seller_id SELLER_ID COUNT (*)-S0010 1067

It is obvious that the FGAC rules we set are in effect. Let's continue to test the update operation:

SQL > select seller_id,qty_sold from oe.sales_list where id=300; SELLER_ID QTY_SOLD-- S00101 SQL > update oe.sales_list set seller_id='S0020' where id=300; update oe.sales_list set seller_id='S0020' where id=300 * ERROR at line 1: ORA-28115: policy with check option violation

Because we specified update_check as true when we created the FGAC rule, when the user S0010 logs in, update the sales_list table and update the seller_id from S0010 to S0020 Times error, because S0010 does not have the right to query and modify sales data that does not belong to him. If update_check is specified as false, such update statements are allowed to succeed.

The use of FGAC rules is very flexible, and the key lies in the writing of where_condition functions. If you want to delete the FGAC rule, execute the following code:

SQL > begin 2 dbms_rls.drop_policy (3 OBJECT_SCHEMA= > 'oe', 4 OBJECT_NAME= >' sales_list', 5 POLICY_NAME= > 'oe_sales_list_fgac'); 6 end; 7 /

2. Column-based VPD

For some sensitive columns, such as employees' salaries, we can mask these sensitive columns by creating column-based VPD, which can only be accessed by authorized users.

Column-based VPD, like the FGAC discussed earlier, is achieved by setting policies. When setting up a column-based VPD, we first need to create a function that the policy needs to use, as shown below.

SQL > connect hr/hr SQL > create or replace function hr_col_vpd 2 (p_owner in varchar2,p_obj in varchar2) 3 return varchar2 4 is 5 l_ret varchar2 (2000); 6 begin 7 if (p_owner = USER) then 8 l_ret: = NULL; 9 else 10 l_ret: = '1cm 2; 11 end if; 12 return latellorette; 13 end; 14 /

Here, we create a rule function. As with the FGAC rule, this function must have two incoming parameters, the first representing the schema name to which the table to be qualified belongs and the second the name of the table to be qualified. In this function, we define that if the login user is the owner of the table, you can view all columns; otherwise, if the login user is not the user to which the table belongs, you cannot view the specified column.

As for which columns are to be masked, you need to specify them when defining the policy, as follows:

SQL > begin 2 dbms_rls.add_policy (object_schema= > 'hr', 3 object_name= >' employees', 4 policy_name= > 'hr_emp_col_policy', 5 function_schema= >' hr', 6 policy_function= > 'hr_col_vpd', 7 statement_types= >' select', 8 sec_relevant_cols= > 'salary', 9 sec_relevant_cols_opt = > dbms_rls.all_rows 10); 11 end; 12 /

Creating a column-based VPD is the same as creating a FGAC policy, using the add_policy stored procedure in the dbms_rls package. Here, we define a policy called hr_emp_col_policy. The policy acts on the employees table under the user HR; the policy function adopted is the hr_col_vpd under the user HR.

Unlike the FGAC policy, we need to specify two other parameters: sec_relevant_cols represents the name of the column to be masked, and multiple columns can be specified, separated by commas; if sec_relevant_cols_opt is set to all_rows, the salary column is masked for all records in the employees table.

We log in as user HR and display the salary column.

SQL > connect hr/hr SQL > select employee_id,last_name,salary from hr.employees where rownum connect oe/oe SQL > select employee_id,last_name,salary from hr.employees where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.