Oracle Database Security

source: xkcd

Oracle default security features
User accounts
  • naming convention, password profiles, password policies
  • Unlike SQL Server (2005/2008), there is no clear separation between database user and database schema
Authentication methods
  • database level, OS level, network level
Privileges and roles
  • restrict data access
Application security
  • User session information using Application CONTEXT
  • Application Context: name-value pair that holds session info.
  • You can retrieve info about a user (i.e., username/terminal, username/deptid) and restrict database and application access based on this information.
Virtual Private Database: restrict database access on the row and column levels.
  • VPD policy: dynamically imbeds a WHERE clause into SQL statements
Audit database

Additional database security resources
  1. Oracle Advanced Security
    • TDE, Wallet Mgmt, Network Encryption, RADIUS, Kerberos, Secure Sockets Layer authentication
  2. Oracle Label Security
    • Secures database tables at the row level
  3. Oracle Database Vault
    • Provides fine-grained access control to sensitive data
  4. Oracle Audit Vault
    • Collect data from audit trail tables, OS audit files and redo logs.
  5. Oracle Enterprise User Security
  6. Oracle Total Recall
  7. Oracle Database Firewall
  8. Oracle Data Masking

Managing Oracle Security
Database Security involves Authentication, Authorization, Data Access, and Audit

(1) Authentication Methods
  • Database Authentication (Default)
    • Oracle implements authentication through a user ID and password.
  • Operating System authentication
    • Sometimes referred as OPS$ accounts.
    • Oracle looks for a user ID that matches your OS login ID and prepends an OPS$ to it.
    • Additional benefit: OS scripts can log into the database without hard coding user names and passwords.
  • Third Party Authentication
    • You can tie authentication to third-party providers like Kerberos or DCE (called network authentication) (Needs EE+Security Pack)
    • or provide it from the middle tier (called multitier authentication).

(2) Authorization
  • Giving access to certain objects, processes or resources.
  • Implemented using GRANTS on objects to ROLES or USERS.
  • Oracle implements authorization via users, roles and permissions

(3) Securing Data Access
  • The ability to access specific data values associated with an object (across records or columns)
  • STORED PROCEDURES are commonly used to secure data access.
  • Benefits of using stored procedures for all programmatic data access: (a) secure the data; (b) provide consistent application access to the data; (c) Hide the data structure.
  • Secure VIEWS can also be used to restrict access. (use WITH CHECK OPTION Constraint)
  • Controlling access at the record level:
    • Previous versions (before Oracle 10g): using secure views
    • More recently:
  • (a) Use Virtual Private Databases (VPDs)
  • (b) Use Fine-Grained Access Control (FGAC) - uses DBMS_RLS package (Row Level Security)
  • With FGAC (DBMS_RLS), you can transparently include a WHERE clause on any combinations of DML and SELECT when a user queries the database.

(a) Option: use Secure Views
userA owns EMPLOYEES table.
userB needs access to EMPLOYEES but cant see SALARY or MANAGER records.
userA may create a view and grant access to userB on that view.

-- User A:
 SQL> create or replace view emp_no_sal as 
      select employee_id, last_name, first_name, email, job_id, manager_id
      from iranmr.employees a
      where employee_id NOT IN (
           select nvl(manager_id, -1)
           from iranmr.employees b);

 SQL> grant select on emp_no_sal to userB;

(b) Option: Use Virtual Private Database (VPD)
  • With Virtual Private Databases (VPDs), Oracle allows column masking to hide columns.
  • When you select the row, Oracle will only display NULL for the secure columns.
  • If you're securing at the row level and column level, it's probably easier to just implement VPDs and not the secure views.

What's a Virtual Private Databases (VPDs)?
  • A VPD is just asking Oracle to put a where clause on DML against an object with a security policy on it.
  • A security policy is defined with DBMS_RLS package.
  • A security policy is normally defined in a CONTEXT (a piece of data that says how the where clause should be built).

(4) Audit
  • Older versions: audit implemented through triggers.
  • Trigger limitations: (a) triggers can easily be disabled or modified; (b) triggers do not fire on SELECT; (c) require extensive coding and testing.
  • Newer versions: Use Fine Grained Auditing (FGA). (DBMS_FGA package)

No comments:

Post a Comment