Structured Storage approaches

(Extracts from a good discussion posted by James Hamilton in his blog. You can check the original article and comments here.)

Although a couple of years old, James Hamilton provided a good requisite-based breakdown of data storage systems. These are some of his points:

• The world of structured storage extends far beyond relational (Oracle, DB2, SQL Server, MySQL, NoSQL, etc) systems.
• Many applications do not need the rich programming model of relational systems and some are better seviced by lighter-weight, easier-to-administers, and easier-to-scale solutions.

• Structured storage approaches can be classified based on customer major requirements.
• These are Feature-first, scale-first, simple structured storage and purpose-optimized stores.

(1) Feature-First

• Traditional Relational database management systems (RDBMS) are the structured storage system of choice here.
• Driven by requirements for Enterprise financial systems, human resource systems, customer relationship management systems (FIN, HR, CRMs)
• Examples here include Oracle, MySQL, SQL Server, PostgreSQL, Sybase, DB2.
• Cloud solutions here include:
• Amazon RDS (Relational Database Service) is a cloud-based solution that basically makes availble from the cloud the functionality of Oracle or MySQL databases.
• Microsoft SQL Azure is in the same line.
• Oracle Public Cloud, just launched at the 2011 Oracle OpenWorld

(2) Scale-First

• This is the domain of very high scale website (i.e. facebook, Gmail, Amazon, Yahoo, etc)
• Scaling capabilities are more important than more features and none could run on a single rdbms.
• The problem here is that the full relational database model (including joins, aggregations, use of stored procedures) is difficult to scale (especially in distributed contexts).
• Distributing data across tens to thousands of rdbms instances and still maintain support for the distributed data as if it were under a single rdbms engine is difficult.
As an alternative, very high scale may be supported with the use of key-value store solutions. These include HBase, Amazon SimpleDB (cloud-based), Project Valdemort, Cassandra, Hypertable, etc

(3) Simple Structure storage

• Applications that have a structure storage requirement but do not need features, cost and complexity of RDBMSs neither have very high scalability requirements.
• Some implementations include:
• Facebook: email inbox search (Cassandra)
• Amazon: retail shopping card (Dynamo)
• Berkeey DB

(4) Purpose-Optimized stores

• Mike Stonebraker argued that the existing commercial RDBMS offerings do not meet the needs of many important market segments
• Some special purpose real-time, stream processing solutions (StreamBase, Vertica, VoltDB) have beat the RDBMS benchmart by +30x...

Readings:
Mike Stonebraker, One Size fits all

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
	Encryption: DBMS_CRYPTO and DBMS_SQLHASH
	Audit database

Additional database security resources Oracle Advanced Security TDE, Wallet Mgmt, Network Encryption, RADIUS, Kerberos, Secure Sockets Layer authentication Oracle Label Security Secures database tables at the row level Oracle Database Vault Provides fine-grained access control to sensitive data Oracle Audit Vault Collect data from audit trail tables, OS audit files and redo logs. Oracle Enterprise User Security Oracle Total Recall Oracle Database Firewall 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.

Securing DATA ACCESS
(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)

Oracle Advanced Security

Oracle Advanced Security (OAS)

• Introduced in Oracle8i.
• Combines (a) strong authentication with (b) encryption of data in storage and while being transferred to and from the database.
• Includes Transparent Data Encryption (TDE), Wallet Management, Network Encryption, RADUIS, Kerberos, Secure Socket Layer (SSL) Authentication, etc.
• Helps customers address regulatory compliance requirements, including
• - Sarbanes-Oxley (?)
• - Payment Card Industry Data Security Standard (PCI-DSS),
• - Health Insurance Portability and Accountability Act (HIPAA), and numerous
• - Breach notification laws.

OAS provides (transparent) data encryption and strong authentication services
- Protect sensitive data on the network, on storage media and within the database from unauthorized disclosure.
- Also protects against theft, loss, and improper decommissioning of storage media and database backups

OAS Components

Transparent data Encryption (TDE)

• Encrypts data before it is written to storage and
• automatically decrypts data when reading it from storage without any changes to existing applications (no need for triggers, views, etc..)
• Access controls that are enforced by the Oracle database still remain in effect. These include object grants, roles, virtual private database and Oracle Database Vault.
• Two supported modes: TABLESPACE ENCRYPTION (11g only) and COLUMN ENCRYPTION (Introduced on 10g r2)
• Tablespace Encryption: good for encrypting entire app tables
• Column Encryption: good for individual data elements (credit cards, SSNs, etc).
• Frequently accessed data blocks are cached in memory in the same manner as traditional nonencrypted data blocks

KEY Management

• Two-tier key management architecture: MASTER encryption key + one or more DATA encryption keys.
• TDE MASTER Encryption key (MEK): used to encrypt and protect the DATA encryption keys.
• TDE MEK: can be stored in the Oracle Wallet.

Network encryption

• Provides standards-based network encryption
• Connections can be rejected from clients that have encryption turned off
• No changes to existing applications are required

Strong Authentication

• Kerberos, PKI or RADIUS
• SSL-based authentication can make use of Smart Cards.

Encrypted database backups

• RMAN backups encrypted data.
• RMAN can call TDE during the backup process to encrypt the entire database (including SYSTEM and SYSAUX).
• RMAN can COMPRESS and use TDE to ENCRYPT => compact and secure backups.