Oracle Tips by
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.
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.
are created via data definition language (DDL) commands. Roles,
also known as groups, are also created via DDL. A user may have
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.
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.
and EnterpriseDB both implement additional "security by obscurity" by
allowing a configurable listener port.
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
management manages how and when a client connects to the database
server. Client management is where EnterpriseDB deviates the
most dramatically from 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.
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.
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.
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.
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.
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.
supported by both Oracle and EnterpriseDB include (but is not limited
Procedures and Packages
Sequences (with Oracle Style syntax)
JDBC and .Net client connections
Automatic Database Recovery
Cursors, Record Types and Table Types
Connect By Hierarchical Syntax
Oracle Style Data Dictionary
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.