Utah Oracle Users Group

1
Utah Oracle Users Group
Oracle8i Virtual Private DatabasesTim
Gorman14 February, 2001

• Evergreen Database Technologies, Inc.
• www.EvDBT.Com

2
Agenda

• Brief discussion of the levels of security in the
  Oracle RDBMS
• The need for fine-grain or row-level security
• Oracle8i Virtual Private Database (VPD) feature
• how it is implemented
• where it is might be useful

3
Levels of security in the Oracle RDBMS

• Accounts
• password protection
• authentication by Oracle server or by client
• analogous to databases in other DBMSs like
  Sybase or MySQL
• Tables, views, and procedures
• GRANT and REVOKE
• static permissions
• roles
• session-based permission groupings
• can be passworded
• Rows
• ummmmwellhmmmgee

4
Row-level security???

• Prior to Oracle8i, row-level security is not
  something that was provided as part of the
  database engine
• the application is made responsible
• if need be, you could use database views in a
  manner similar to the data dictionary views
• USER_xxx, ALL_xxx, DBA_xxx views
• but in complex applications, the introduction of
  database views might severely complicate
  performance tuning
• until Oracle8.0 and the advent of INSTEAD OF
  triggers, DML could not be performed on complex
  views
• The need for row-level security was not clearly
  defined...

5
The need for row-level security

• Satisfying the auditors
• Generally, SQL statements and stored procedures
  provide row-level security within the
  application, and the client-side interface uses
  these to enforce application security
• application modules thus act as a proxy, allowing
  access according to the rules of the application
• but ad-hoc tools (i.e. SQLPlus, Discoverer) and
  low-level application programming interfaces or
  APIs (i.e. PROC, OCI, JDBC, SQLJ) bypass all of
  this
• as a result, from an auditors point of view, an
  application is not secure if a method of data
  access exists which does not enforce application
  security
• in short, it can be hacked!

6
The need for row-level security

• Satisfying nervous customers and end-users
• Your application might serve customers who are
  mortal enemies of one another
• if they become aware of the fact that they are
  co-habitating the same database, how can you
  satisfy them that their data will never, EVER be
  viewed (or modified) by the wrong party?
• Often, logical explanations about application
  security do not provide the necessary reassurance
• tales about hackers and disgruntled employees are
  very powerful
• for political reasons, it might become necessary
  to create separate databases on separate servers!
• Talk about a draconian solution!

7
The need for row-level security

• Avoiding the amoeba or hydra-headed application
• Case study Oracle Applications and multi-org
  functionality
• full suite of Oracle Applications (Financials,
  Manufacturing/ERP, etc) totals to dozens of
  accounts/schemas, thousands of tables and
  indexes, and might be integrated with 3rd-party
  packages (i.e. tax, catalogs) and customizations
• getting it all installed, configured, integrated,
  and customized in the first place and then
  maintaining it over time can be extremely complex
• imagine doing it 15-20 times, once for each
  subsidiary or department in your company
• After all, each subsidiary or department uses the
  same accounting structures, but they do not want
  (or are not allowed) to see each others data

8
The need for row-level security

• In summary, row-level (fine-grain) security might
  be needed for the sake of
• security
• manageability
• all of the above
• others???
• The feature of virtual private databases (VPDs)
  has been introduced with Oracle8i to supplement
  application security logic built into application
  code
• it will also form the core of Oracles Secure
  Server 2.0 product line, which is the
  (long-delayed) successor to the Oracle7-based
  Trusted Oracle product line

9
Virtual Private Databases

• How are they implemented?
• a brief illustration using the history of the
  multi-org functionality in Oracle Apps as a case
  study
• description of the mechanisms
• simple code examples
• some ideas for other uses of VPDs

10
Oracle Apps Multi-Org

• A full implementation of the Oracle Apps for a
  single organization within a company is very
  complex
• What about an implementation which includes
  multiple independent organizations (i.e.
  divisions, subsidiaries, etc) within the same
  company?
• Requirements
• Each organization is only allowed to access (and
  modify) its own data
• Corporate-level users want to view all data
  globally

11
Oracle Apps Multi-Org (contd)

• Solutions?
• Separate installation for each organization?
• satisfies the separation requirement
• fails to satisfy the global view requirement
• One big installation for all organizations, and
  each is on the honor system to only
  access/modify their own information?
• Satisfies the global view requirement (all too
  well!)
• Fails to satisfy the separation requirement
• One big installation for all organizations, and
  some form of row-level security is utilized

12
Oracle Apps Multi-Org (contd)

• Starting in the R10.6 timeframe, Oracle
  implemented multi-org functionality using
• database views for each table to be shared
  amongst multiple organizations
• each table was renamed lttable-namegt_ALL
• each view named after the original table
• each user logged on a OE_ltorg-namegt instead of
  just OE
• each view included a big DECODE statement which
  selected ORG_ID based on the current session
  username
• some implementers embedded PL/SQL functions to
  replace the big, hardcoded DECODE
• turned out to be a big mistake

13
Oracle Apps Multi-Org (contd)

• In R10.7 (and R11.0?), things got better and
  simpler
• views and tables named _ALL still remain
• but the big DECODE is removed
• instead, a stored procedure is executed when a
  user logs on through an Oracle Apps module and
  the ORG_ID is stored in the Oracle SGA using the
  supplied packaged procedure SET_CLIENT_INFO in
  the package DBMS_APPLICATION_INFO
• ORG_ID value is stored in VSESSION.CLIENT_INFO
• the WHERE clause of all multi-org views now
  contained the simple predicate
• AND ORG_ID USERENV(CLIENT_INFO)
• problem is, this can be hacked!

14
VPDs

• For VPDs, the idea of storing a security context
  labels in the SGA continues from R10.7
• context is just a label or string
• stored in the SGA, associated with the session
• can be retrieved any time during the session
• But, in order to meet even moderate security
  schemes, the simplistic scheme of
  storing/retrieving just data values is inadequate
• many common security schemes might utilize
  advanced programming techniques and complex logic
• might need to interface with operating-system
  security

15
VPDs

• for example, in addition to just ORG_ID value, an
  applications security scheme might have
  additional considerations such as
• time of day or day of week
• physical location of end-users connection
• data values in other tables in the database or
  conditions out in the operating-system
• an access control list, which includes inclusion
  and exclusion logic, possibly in a
  tree-structured or recursive n-dimensional format
• revalidation might be necessary after a period of
  time or number of operations (i.e. security token
  expiration)
• You probably cant do this with a stored string
  saved in the SGA -)

16
VPD mechanisms

• The Optimizer
• determines if a security policy is in effect for
  one (or more) table(s) being accessed by the SQL
  statement being parsed
• if so, a user-defined PL/SQL packaged function is
  executed
• function contains code which implements the
  user-defined row-level security policy
• returns a string which is appended to the
  WHERE-clause of the SQL statement being parsed
• the optimizer edits the SQL statement!
• then, the Optimizer finishes parsing the finished
  SQL statement after it has been edited, and then
  the statement is executed

17
VPDs simple case study

• Roadmap to setting up a virtual private database
• 1. Determine what your security scheme will be
• 2. Create a PL/SQL package to implement it
• package contains at least one procedure for
  setting security context (as defined in your
  scheme)
• package contains at least one function which
  takes two input strings (table OWNER and NAME)
  and returns a VARCHAR2 string
• 3. Use the new CREATE CONTEXT command to
  designate the PL/SQL package as the trusted
  enforcer of the scheme
• 4. Use the new DBMS_RLS.ADD_POLICY procedure to
  link a specific PL/SQL function in the trusted
  package to be executed upon a specific action on
  a specific table
• function returns string which becomes a
  WHERE-clause predicate in the original SQL
  statement

18
VPDs creating a PL/SQL package

• create or replace package vpdtest as
• procedure setlabel
• function getlabel(owner in varchar2, name in
  varchar2)
• returns varchar2
• end vpdtest
• /
• create or replace package body vpdtest as
• procedure setlabel
• is
• begin
• if to_number(to_char(SYSDATE,HH24)) between 9
  and 17
• then dbms_session.set_context(MYTEST,LABEL,
  X)
• else dbms_session.set_context(MYTEST,LABEL,
  )
• end setlabel

19
VPDs creating a PL/SQL package

• function getlabel(owner in varchar2, name in
  varchar2)
• returns varchar2
• is
• begin
• if sys_context(MYTEST,LABEL) ltgt X then
• return 1 2
• else
• return
• end if
• end getlabel
• end vpdtest
• /

20
VPDs CREATE CONTEXT

• Once the package is successfully defined, then
  associate it with a context to make it the
  privileged enforcement package
• CREATE CONTEXT MYTEST USING VPDTEST
• the procedure DBMS_SESSION.SET_CONTEXT will fail
  unless it is executed inside the trusted PL/SQL
  package
• protects against hackers

21
VPDs DBMS_RLS.ADD_POLICY

• ADD_POLICY procedure from the supplied package
  DBMS_RLS is used to associate actions against
  tables with specific PL/SQL functions inside the
  privileged package for a context
• DBMS_RLS.ADD_POLICY (
• object_schema IN VARCHAR2 NULL,
• object_name IN VARCHAR2,
• policy_name IN VARCHAR2,
• function_schema IN VARCHAR2 NULL,
• policy_function IN VARCHAR2,
• statement_types IN VARCHAR2 NULL,
• update_check IN BOOLEAN FALSE,
• enable IN BOOLEAN TRUE)

22
VPDs simple case study in action

• SQLgt connect scott/tiger
• SQLgt select count() from emp
• COUNT()
• --------
• 0
• SQLgt exec vpdtest.setlabel
• SQLgt select count() from emp
• COUNT()
• --------
• 14

23
Some ideas for ways to set the context

• Use an AFTER LOGON trigger to fire as someone
  connects to the database
• CREATE OR REPLACE TRIGGER SCOTT.VPDLOGON
• AFTER LOGON
• CALL SCOTT.VPDTEST.SETLABEL
• Authentication certificate
• if the SETLABEL procedure is not called, then no
  access is allowed (as in the example)
• forces users to call SETLABEL
• SETLABEL can authenticate the user using rules
  stored in the database
• More???

24
VPDs Performance issues

• Previous row-level security schemes involving
  database views could complicate SQL tuning issues
• VPDs, allowing complex logic yet simpler
  predicates, may avoid such complications
• Executing some user-defined code during the PARSE
  phase of SQL execution allows powerful
  functionality to execute only once, instead of
  each time a row is retrieved
• editing in ORG_ID IN (value-list) can be much
  faster than adding ORG_ID IN (sub-query) or
  ORG_ID join-column

25
VPDs the fine print

• Predicates cant be longer than 2000 characters
• I havent tested this to see if it is true -)
• Use the table-name in the returned predicate to
  clarify ambiguities, in the event that a SQL
  statement hits more than one table with a policy
• The Optimizer will resolve the table name to the
  appropriate table-alias before appending the
  predicate to the SQL statement
• there is no determinate order for applying the
  policies
• The trusted PL/SQL package cannot reference a
  table which it is protecting
• results in indefinite locks on library cache pin

26
A sneaky hack?

• The trusted PL/SQL package must not change
  database state using DML (i.e. INSERT, UPDATE,
  DELETE statements)
• documentation says that using PRAGMA
  RESTRICT_REFERENCES(func-name, WNDS) is required
• this isnt true (so far!)
• What does this imply?
• the mechanism of VPDs is almost like a database
  trigger firing, isnt it?
• imagine triggers on SELECT statements...
• ...the mind reels...

27
Data Warehousing

• Security in the decision-support environment is
  not often discussed
• How can you control data access in an environment
  where ease-of-use, accessibility, and even ad-hoc
  access from a variety of off-the-shelf utilities
  part of the design?
• VPDs provide an effective way to control and
  audit access in numerous creative ways.

28
Summary

• Even without such hacking (which might be closed
  off in future versions anyway), the mechanism of
  VPDs promises some very interesting and creative
  solutions to problems in security and application
  management
• QA?

29
Documentation

• Oracle8i (8.1.6) Application Developers Guide -
  Fundamentals
• chapter 11 on Establishing Security Policies
• section on Fine-grained Access Control
• downloadable and viewable at http//technet.oracle
  .com
• Oracle Magazine article by Mary Ann Davidson at
  http//www.oracle.com/oramag/oracle/99-Jul/49sec.h
  tml
• Online example of Virtual Private Database on
  my web-site at http//www.evdbt.com/library.htm