Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Contact:
Franz
Security,
Access control on tables, views, Synonyms or Rows - Part
3
Fine-Grained Access Control
Fine-Grained access control works by means of Functions
that are used to implement your security policies, which will be associated
with tables, views, or synonyms. In
Fine-Grained access control the Oracle Database Server automatically enforces
your security policies, regardless of how the data in the tables is
accessed. It will work the same whether
your data is accessed through an application, or through ad-hoc queries from
something like SQL*Plus.
Fine-Grained access control includes the following
capabilities, which can be enabled.
-> You can limit access at the row level by using
different policies for SELECT, UPDATE, INSERT, and DELETE.
-> You can use
security policies only on the tables or data that contain the sensitive
information.
-> You can allow access to a table, but if the salary
column is referenced for example, then you can invoke the security policy.
-> You can restrict
access with a combination of row-level and column-level restrictions. A VPD policy applied to the table can have
this effect.
-> You have some policies called static policies that
are always applied to a table. Other
policies called dynamic policies, can be applied, according to the specific
situation.
-> You can use multiple policies for each table if
required. You can build on top of base
polices in packaged applications.
-> Policy groups enable different applications to use
different sets of policies. A policy
group will belong to an application.
-> You can control the use of INDEX, which can be
distinguished in row level security policies.
-> The driving context is the application context. When the fine-grained access control engine
looks up the driving context, during the execution of a specific application;
The policy group is determined according to the policy group, and all the
associated policies for that application, and that belong to that specific
policy group are enforced.
Use the PL/SQL package DBMS_RLS to administer your security
policies. With DBMS_RLS, you can add,
drop, enable, disable, and refresh the policies , or policy groups that you
create.
There are a number of areas in fine-grained access control:
-> Dynamic Predicates
-> Application context
->
Dynamic Contexts
->
Policy Groups (set of policies that belongs to an application)
->
INDEX in row level security policies
-> Application Context
or Driving Context: application context
indicates the policy group in effect. Fine-Grained
access control engine looks up the driving context to determine the policy
group in effect.
Dynamic Predicates
Dynamic predicates are associated with a security policy
which is acquired at statement parse time.
Dynamic predicates are aimed at table’s, view’s, and synonym’s. Dynamic predicates are defined through a
PL/SQL function which is implemented through a function, during a DML statement on the base table.
The Function or Package that implements the security policy
returns a predicate or a WHERE condition.
This predicate controls the access of the statement in the application
by re-writing it. Rewritten queries are
fully shareable and optimized.
For example:
EXEC DBMS_RLS.ADD_POLICY(‘fred’,’salary’,’sal_policy’,’fred’,’sal_security’,’select’);
Now whenever the fred.salary table is selected, the Oracle
Database Server calls the sal_security function which returns a predicate, in
this case “P1”. The predicate is based
on the requirements for the sal_policy .
The security policy may also have generated the predicates from the
session environment variables, or the application context.
Now the statement is assembled using a transient view:
SELECT * FROM fred.salary where P1;
P1 is then expanded to form.
SELECT * FROM fred.salary where salary < 1000;
The Oracle Database Server does not implement fin-grained
access control during MERGE statements.
Therefore you must use equivalent INSERT and UPDATE statements instead
of the MERGE statement. This will avoid
error messages and ensure correct access control operation.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment