EnterpriseDB: Security
Oracle Tips by Burleson

The security model of EnterpriseDB (discussed in Chapter 3), is very much like Oracle's. Security is based on a user or group receiving specific permissions to database actions and objects.

Security is grantable at a very fine-grained level. Security in both EnterpriseDB and Oracle is available at the column level for tables and at the connection and resource levels.

Users are created via data definition language (DDL) commands. Roles, also known as groups, are also created via DDL. A user may have many roles.

Permissions such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc. are grantable to most database objects, and both directly to users or to roles, in both Oracle and EnterpriseDB. Individual grants are revocable while leaving other permissions in place.

Security in EnterpriseDB can be further controlled via the security configuration files found in the OS file system. Security to a cluster is defined via the pg_hba.conf file (discussed in detail in Chapter 2), which identifies allowed hosts, users, ip addresses, connectable databases and password encryption methods.

Oracle and EnterpriseDB both implement additional "security by obscurity" by allowing a configurable listener port.

In comparison, security between Oracle and EnterpriseDB is more alike than not. EnterpriseDB does allow an additional layer of host based security but it can be configured to function exactly like an Oracle installation.

Client Management

Client management manages how and when a client connects to the database server. Client management is where EnterpriseDB deviates the most dramatically from Oracle.

Oracle implements a networking client, Transparent Network Substrate (TNS) that allows almost any network protocol to connect to an Oracle database. The configuration of a TNS client can be a complex affair but provides many beneficial features, not the least of which are guaranteed encryption and connection tracing.

Later releases of Oracle have simplified connections through a client by offering Instant Client and Easy Connect. Instant Client is a drag and drop installation of an Oracle client for JDBC, .Net, ODBC and other client connections. Oracle 10g offers Easy Connect which is most like an EnterpriseDB connections string in that it lists the host, port and database name for the connection.

With EnterpriseDB, the connection process is normally a simple IP based connection configured within a client application. The EnterpriseDB method of connecting (as well as PostgreSQL), is both simpler and more complex. It is simpler for the administrator in that clients configure themselves but it is more complex for the client in that each client needs to configure itself.

For a managed client installation, the Oracle TNS client is a nice feature that ensures all clients are connecting in the same way to the same databases. Configuring that client requires learning a somewhat obtuse configuration schema. For a simpler, open client installation Oracle Easy Connect and the EnterpriseDB method of connecting offer the same pros and cons.

Application Design

From a global database application design perspective, EnterpriseDB is highly compatible with Oracle. The key thing to remember is that EnterpriseDB provides mostly the same overall functionality as Oracle but it may do it in a different way.

Syntactically, many features, such as creating a table or an index, in either database will be the same. The difference will come in the type of table or index and for non-ANSI standard features such as partitioning. For example, EnterpriseDB does not support Oracle style Index Organized or External tables. EnterpriseDB and Oracle both support partitioned tables but getting there (see Chapter 3) requires different methods.

There should be no problem with foreign key support or triggers within EnterpriseDB. A fully ANSI standards compliant database will easily be ported between EnterpriseDB and Oracle. Most applications are not pure implementations of the ANSI standard.

Features supported by both Oracle and EnterpriseDB include (but is not limited to):

* Primary Keys

* Unique Keys

* Foreign Keys

* B-Tree Indexes

* Partitioned Tables

* Views

* Updateable Views

* Deadlock Detection

* Stored Procedures and Packages

* Triggers

* Sequences (with Oracle Style syntax)