Reader small image

You're reading from  Advanced Oracle PL/SQL Developer's Guide (Second Edition) - Second Edition

Product typeBook
Published inFeb 2016
Reading LevelIntermediate
Publisher
ISBN-139781785284809
Edition2nd Edition
Languages
Right arrow
Author (1)
Saurabh K. Gupta
Saurabh K. Gupta
author image
Saurabh K. Gupta

Saurabh K. Gupta is a seasoned database technologist with extensive experience in designing high performance and highly available database applications. His technology focus has been centered around Oracle Database architecture, Oracle Cloud platform, Database In-Memory, Database Consolidation, Multitenant, Exadata, Big Data, and Hadoop. He has authored the first edition of this book. He is an active speaker at technical conferences from Oracle Technology Network, IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his twitter handle (or SAURABHKG) or through his technical blog www.sbhoracle. wordpress.com, with comments, suggestions, and feedback regarding this book.
Read more about Saurabh K. Gupta

Right arrow

Chapter 6. Virtual Private Database

Information security is one of the key challenges that organizations have to address to protect data privacy and its regulation. Leakage of sensitive information or unauthorized access to data might lead to hazardous consequences. With the stringent protocols of data distribution and steep rise of the internet usage, data security can pose a tedious question to the organizations. Information that is prone to invasions can be personal details, financial data, credit card information, business leads, or an intellectual property. A relational database can expose potential areas of injection in the form of network access, over privileged user accounts, non-database file access, non sanitized inputs, and proliferation of production data to unregulated environments. Knowing the fatal consequences of data breaches, the data security solutions must be efficient to bypass the attacks from the database layer, network, and application.

In this chapter, we will walk...

Oracle Database Security overview


Oracle offers a comprehensive suite of database security solutions that aim to secure the databases and applications by protecting sensitive information, detecting threats and attacks, and managing security policies along the lines of organizational compliance. The defense-in-depth approach of Oracle can be categorized under preventive, detective, and administrative controls that ensure all the vulnerable areas of an application are covered. A powerful security strategy can be developed and laid down by using the right mix of security features. The factors impacting the formulation of a security strategy can be threat anticipation, data sensitivity, data distribution and user classification, compliance, and internal regulations.

Each of the above categories includes various products and technical features. The following diagram bifurcates the security pillars into security measures:

Oracle Database security feature under three categories

The Oracle Database...

Fine-Grained Access Control


In an enterprise application, user isolation or role-based multitenancy ensures that data is accessed by the authorized users only. In a Software as a Service (SaaS) model application, tenant isolation is one of the prime demands. Application users are authorized to access only their world of data and not peek into other user's data.

Fine-grained access control (FGAC) enables the enforcement of security policies on the access of rows and columns based on the user's role and authorization. For example, an HR representative is authorized to view the details of employees that belong only to his vertical. Similarly, a relationship officer in a bank is authorized to access the account details of those customers that belong to his branch. The FGAC feature provides you with the mechanism to expose the authorized piece of data only to the approved user.

How FGAC works

Fine-grained access control enables the creation of security policies that can be associated with a table...

Virtual Private Database


The working principle of the Virtual Private Database technology is that users should have isolated and distinguished data access. Virtual Private Database (VPD) is a feature that is built on fine-grained access control and uses application contexts to define and add the predicates to the SQL queries. Similar to FGAC, Virtual Private Database lays down the security framework through the security policies in the Oracle Database and associates them directly to tables, views, or synonyms. The security policies act as a safety net on the objects, and by no means they can be bypassed.

How does Virtual Private Database work?

Whenever a user issues an SQL query against a Virtual Private Database protected table, Oracle invokes the policy and evaluates the policy function. The policy function is a PL/SQL function that returns a predicate or a WHERE clause. The query is then rewritten using dynamic views to add the predicate clause. The modified query is executed against the...

Virtual Private Database metadata


Oracle captures the static and dynamic details of the VPD policies in dictionary views. You can query the following dictionary views to find the metadata about the Virtual Private Database security policies:

Oracle Database 12c Security enhancements


Oracle Database 12c introduced a number of features and enhancements to further strengthen the in-depth security collateral. In this section, you will understand some of the key security enhancements and features in Oracle Database 12c. The following is a summary of the new security features:

  • Real Application Security(RAS): Oracle Database 12c introduces a data authorization solution to provide end-to-end security in a multitier application architecture. You can now declare and enforce the application-level security policies in the database kernel. The RAS security model understands the application-level security policy constructs, such as application users, privileges, and roles within the database, and enforces the security policies in the context of the application. As well as data access, RAS can help the applications to secure the access control operations of an application user.

  • Oracle Data Redaction: The Data Redaction feature masks the sensitive...

Summary


In this chapter, we covered the fundamentals of database security solutions from Oracle. After an overview of the Oracle Database Security solution, we dived into the fine-grained access control and Virtual Private Database. The Virtual Private Database enforces row-level security through the policies and restricts the data access for unauthorized users. Depending on a user's identity and role, the application can set up multitenancy and ensure user isolation as well.

Oracle Database 12c made considerable enhancements to its security offering. The summary of these enhancements was included, while the Data Redaction feature was covered in detail along with demonstrations. In the next chapter, we will dive into another key area that has continuously gained more weight with time: handling of large objects in the Oracle Database. We will be focusing the majority of our discussion around SecureFiles and its optimizations.

Practice exercise


  • Identify the correct statements about the working of Fine Grained Access Control.

    1. A table can have only one security policy.

    2. Different policies can be used to protect the SELECT, INSERT, UPDATE, and DELETE statements on a table, but not one.

    3. The policy function returns the predicate information as WHERE <Column> = <Value>.

    4. Once associated, the FGAC policy cannot be revoked from the table.

  • A security policy can be associated to a group of objects by the DBA. State whether this is true or false.

    1. True

    2. False

  • Choose the correct statement about DBMS_RLS.

    1. DBMS_RLS is used only for row-level security policies.

    2. The package is owned by SYS.

    3. It can create / drop / refresh policies and create/drop policy groups.

    4. Using DBMS_RLS to set the policy degrades the application performance.

  • Identify the correct statements about the context of an application.

    1. A user who holds the CREATE CONTEXT privilege can create a context.

    2. It is owned by the SYS user.

    3. A user can check the context metadata in...

lock icon
The rest of the chapter is locked
You have been reading a chapter from
Advanced Oracle PL/SQL Developer's Guide (Second Edition) - Second Edition
Published in: Feb 2016Publisher: ISBN-13: 9781785284809
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
undefined
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $15.99/month. Cancel anytime

Author (1)

author image
Saurabh K. Gupta

Saurabh K. Gupta is a seasoned database technologist with extensive experience in designing high performance and highly available database applications. His technology focus has been centered around Oracle Database architecture, Oracle Cloud platform, Database In-Memory, Database Consolidation, Multitenant, Exadata, Big Data, and Hadoop. He has authored the first edition of this book. He is an active speaker at technical conferences from Oracle Technology Network, IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his twitter handle (or SAURABHKG) or through his technical blog www.sbhoracle. wordpress.com, with comments, suggestions, and feedback regarding this book.
Read more about Saurabh K. Gupta

Dictionary view

Comments

[ALL | USER | DBA]_POLICIES

The view captures the security policy on objects accessible to a user, owned by a user, or within a database.

[ALL | USER | DBA]_POLICY_ATTRIBUTES

The view captures the application context namespaces, their attributes, and their association with the Virtual Private Database policy.

[ALL | USER | DBA]_POLICY_CONTEXTS

The view captures information about the driving contexts for the objects.

[ALL | USER | DBA]_POLICY_GROUPS

The view captures information about the policy groups on the objects.

[ALL | USER | DBA]_SEC_RELEVANT_COLS

The view captures the specifications of the column-level Virtual Private Database policy on the objects.

V$VPD_POLICY

The dynamic view captures information about...