Database Security
One notable difference between SQL Server and
Oracle security is that access to a SQL Server database is a two-step process.
At the instance level, the server maintains a list of user accounts called logins.
Logins are accounts that have been granted permission to connect to the
instance. However, to gain access to a database hosted in the instance, a login
also needs to be mapped to a user account within the database. A SQL
Server login can be a local Windows or Active Directory account, a certificate
or even an asymmetric key. It can also be something totally independent of
Windows: the DBA can create a username and assign it a password from within SQL
Server. In the first case, the login mapped to an AD account is known as a trusted
account. In the latter case, the login is said to be non-trusted.
For Oracle, there is no concept of trusted
and non-trusted accounts. A normal user account in Oracle is created only once.
Unlike SQL Server, Oracle does not map operating system accounts to database
accounts.
When SQL Server is installed, a special
non-trusted login called sa (shortened for System Administrator) is
created. Sa is the built-in database administrator account and owns every
database created in the instance. It has absolute power over the server: sa can
create, alter or drop logins and databases, modify system configuration,
shutdown the instance or grant other users system administrator privileges.
Apart from the server level sa account, every database will also have a DBO
or database owner account. This account will map to the server level principal
that created the database. Database owner has full privilege within the
database itself, but may not have any permission outside it.
When an Oracle database is created, there may
be a dozen or so system level accounts created. By default, all these accounts
except four will be expired and locked. The four special user accounts are SYS,
SYSTEM, SYSMAN and DBSNMP. Of these, SYS and SYSTEM
accounts are equivalent to SQL Server’s sa. The user SYS owns the data
dictionary (the Oracle data dictionary is created in the SYS schema) and the
user SYSTEM has access to all the objects in the database.
Apart from the sa account, SQL Server also
has some built-in fixed server roles. These fixed roles are like Windows
groups: they can contain one or more logins. Each role has a set of privileges
attached to it. All members of a role will thus have the same level of
permissions across the server. The highest privileged sever role is sysadmin
which has the same rights as the sa account. For Oracle, such privilege is
granted to the DBA role. Also, a user who has been granted the SYSDBA
or SYSOPER privilege can perform administrative actions such as starting
up or shutting down the database.
As said before, a SQL Server login can be
either trusted (Windows account) or non-trusted. Accordingly, a SQL Server user
can be validated by two types of authentication modes: trusted or non-trusted.
When using trusted or Windows authentication, SQL Server will rely on the user
being validated by the Windows operating system. If the user has a valid account
in the Active Directory or the local Windows Server, SQL Server will not
require him to supply a username and password again. In non-trusted
authentication, SQL Server will require the user to supply a username and
password. It will then check that username/password against its own internally
maintained list of valid users.
In Oracle, a user can be authenticated using
any of the three methods:
·
Data Dictionary
·
Password File
·
Operating System (this is not the same as
SQL’s trusted connection method)
Of these, data dictionary mode is the most
common and the other two methods are used when the database is not available or
the instance is not running. When normal users try to connect to an Oracle
database that is already open, they are validated against the data dictionary.
Although it works fine for ordinary users, DBAs and system administrators who
need to create, open, start or shut down the database cannot be verified in
this mode. This is because an Oracle instance can exist without its database
being opened or even created. The DBA will be the only user who can create or
open a database from an idle instance. Since there will be no data dictionary
present for authentication, Oracle will need some form of external mechanism to
validate the user. This form of external authentication can be done through a password
file or the operating system hosting the Oracle instance.
The password file contains usernames and
passwords of accounts that have been granted the SYSDBA or SYSOPER privilege.
When a user connects to Oracle with one of these privileges, he can start up,
mount, open, close, dismount, shutdown or alter the database. If the user is
directly logged on to the machine hosting the Oracle instance, he can use
operating system authentication. With operating system validation, Oracle
checks to see if the user trying to connect is a member of the operating system
group that owns the Oracle software. In Windows servers, it is the local
Windows group ora_dba, for Unix systems, it is typically the dba
group. If the user is a member of the privileged group, he does not need
to provide a username and password.
To show how all this works, let’s consider
two examples.
In the code below, the SYS user is trying to
connect with the SYSDBA privilege using password file authentication:
CONNECT
sys/password@DBNAME AS SYSDBA;
In the connection command below, the user is
directly logged on to the machine hosting Oracle. Since he is already validated
by the host operating system and his account is a member of the operating system
group that owns the Oracle installation, the user does not need to provide a
username and password. Instead, he provides a “/” symbol.
CONNECT /
AS SYSDBA;
Database Server Installation
Installing Microsoft SQL Server has always
been a relatively straightforward process. Basically the DBA needs to know what
components and features need to be installed, whether to install a default or a
named instance, the collation settings and the authentication mode to be used and
where in the disk location the installer will copy the files to. Planning the
installation can take some time, but running the installer is as simple as
double clicking on setup.exe. Installing SQL Server on a cluster can be time
consuming and sometimes tricky, but the process and the user interface is quite
similar to a single node install. At the end of the installation, new registry
keys will be created and a standard directory structure will also be created
with program binary files and data files copied into them. Five system
databases (including resource) will be created and the database instance will
be started.
Although Oracle ships for different operating
systems including UNIX, Linux and Windows, its installer has the same look and
feel across all the platforms. The installer is a Java based program called the
Oracle Universal Installer or OUI. The OUI is used to install the
Oracle software; creating the database can be done either separately using
scripts or optionally from within the OUI. The program that is invoked when
creating the database in GUI mode is called the Database Configuration
Assistant or DBCA.
With SQL Server 2008, the typical
installation will create a directory structure like the following:
·
%System Root%Program FilesMicrosoft SQL
ServerMSSQL10.<Instance_Name>MSSQLBinn:
Binary executables for the database server
·
%System
Root%<Root_Folder>MSSQL10.<Instance_name>MSSQLDATA:
Default Data folder
·
%System
Root%<Root_Folder>MSSQL10.<Instance_name>MSSQLLOG:
Default location for error log files and maintenance plan outputs
·
%System
Root%<Root_Folder>MSSQL10.<Instance_name>MSSQLBackup:
Default location for database backup files
·
%System
Root%<Root_Folder>MSSQL10.<Instance_name>MSSQLFTDATA:
Default location for full text catalogues
·
%System Root%<Root_Folder>MSSQL10.<Instance_name>MSSQLREPLDATA:
Default location for replication snapshots
The <instance name> in the above list
will be “MSSQL” for default instances. For SQL Server 2005, the folder
structures remain almost the same, except instead of having either “MSSQL” or
the instance name in the path, the main folder will have a name like MSSQL.n
where n will be a number. So the first instance in the machine (default
or named) will have its data directory under %System
Root%<Root_Folder>MSSQL.1MSSQLDATA, the second instance installed
in the same machine will have its default data folder under MSSQL.2MSSQLDATA
and so on.
Oracle installation also conforms to a
standardised directory structure. Oracle calls this the Optimal Flexible
Architecture or OFA. The idea behind OFA is that when database files
are installed in a number of well defined, standardised set of directories, it
becomes much easier for DBAs to administer the database, locate critical files
or install multiple instances of Oracle in the same machine. With Windows
installation of Oracle, the directories are automatically created by the OUI
and DBCA. With non-Windows platforms like UNIX and Linux, the directories need
to be created by the DBA as a pre-installation step. This is necessary because
in most UNIX systems Oracle files are spread across multiple mount points.
One of the pre-requisites of installing
Oracle in a non-Windows platform is to set up some environment variables. Two
of the variables related to the installation directories are called ORACLE_BASE
and ORACLE_HOME. ORACLE_BASE is the root directory under which all
Oracle products will be installed. ORACLE_HOME is the location where the
current Oracle database software will be installed. ORACLE_HOME is typically a
directory under ORACLE_BASE.
OFA recommends that the installation has:
·
A separate directory for the application
binaries and executables
·
A separate directory for Oracle alert logs,
control files, initialisation parameter files and trace files
·
A separate directory for the actual data
files.
As an example, in UNIX systems, the Oracle
binaries may be located under a directory structure that has the following
form: /pm/h/u/product/v
Here, “p” is a string literal and “m”
is a number. Together, they depict a mount point in the UNIX file system. “h”
can be a name indicating the purpose of the directory. “u” will be the
owner of the directory (in UNIX, every directory has an owner: this is same in
concept as NTFS security). Underneath that, “product” is a string literal and
then “v” will be the version of Oracle. So you may find the Oracle binaries
placed under a directory path of u01/database/oracle/product/10.2.0. In
Windows, it may look like this: C:oracleproduct10.2.0
Alert files, archived logs or initialisation
parameter files can be placed under a directory structure which may look like:
/pm/h/admin/d/a
Here, pm is again the UNIX mount point
and h is again a name that suggests the directory’s contents. “admin” is
a string literal and “d” stands for the SID (Systems Identifier) of the
database. “a” can be any of the special directories like bdump, udump,
pfile, cdump etc. So for example in a Linux system, the Oracle
initialisation parameter file may be located under
/u02/database/admin/orcl/pfile directory. In Windows, this file may be located
under D:oracleproduct10.2.0adminorclpfile folder.
Finally, according to OFA, the format for
naming data file directory would be /pm/q/d where pm
is the mount point in UNIX based systems or Oracle root directory in Windows, q
is a descriptive name that suggests that the contents of the directory are data
files (e.g. “oradata” or “datafiles”) and d is the SID of the database.
A UNIX system may have the Oracle data files located under /u03/oradata/orcl
directory whereas a Windows server may have them under E:oracleORADATAorcl
folder.
As you may have guessed, installing Oracle
can be a more involved process than installing SQL Server, particularly when
Oracle needs to be rolled out in non-Windows platforms. Apart from creating the
directory structure, the pre-installation process also involves modifying
operating system parameter files, creating OS groups and users as well as
setting environment variables and running shell scripts as the root user.
Client Connectivity
Client connectivity is another area where
there are similarities and differences between the two platforms. SQL Server
connectivity can be achieved by creating ODBC connections and installing the
required OLE DB drivers on the client PC. The connectivity tools from SQL Server
installation media can be installed on client workstations to set up the
required OLE DB drivers. You can further fine tune connectivity by enabling
TCP/IP and/or NetBIOS, assigning port numbers at the server side and creating
aliases at the client end. When you create ODBC connections in the client PC,
you have to specify the server name, connection mode (Windows or mixed) and
optionally provide a username and password. For ADO, all these details will be
included in the connection string.
In Oracle, client connectivity is also
achieved by installing the Oracle client tools. For 64 bit systems like
SQL Server machines connecting to Oracle, you will additionally need to install
Oracle Data Access Component (ODAC). Once the client tools are installed,
you have to specify the target server name and its properties from the Oracle Net
Manager application. Information you provide there includes the Oracle
server’s physical name, its service name or SID, network protocol used and the
port number to connect to. This is just like configuring ODBC in Windows for
SQL Server. To accept client connections, Oracle also needs to have its listener
service running at the server end. And just like you can use aliases for SQL
Server instances, Oracle also has the option of creating a service name
for an instance and using it from the client end.
Unlike SQL Server though, the network
connectivity parameters for Oracle are kept in plain ASCII text files. The listener.ora
file at the server end shows the listener parameters Oracle is using. At the
client end, tnsnames.ora file shows the parameters chosen from the Net
Manager tool.
Database Management Tools
The tool that SQL Server DBAs always used for
managing database instances was known as Enterprise Manager. SQL Server
Enterprise Manager went through a major facelift from version 6.5 to 7.0.
Enterprise Manager in version 2000 did not see much of a difference from its
predecessor. However, version 2005 saw another major change of the user
interface along with the rest of the product and this time the tool came to be
known as the Management Studio. SQL Server Management Studio (or SSMS)
is based on Microsoft’s Visual Studio development environment’s standard
interface. It has not changed significantly in SQL 2008.
Enterprise Manager or Management Studio
allows a DBA to centrally administer SQL Server instances running across the
network. Using the tool, the DBA can “register” SQL Server instances, look at
the properties of those instances and change them, run jobs, execute codes
against databases or create user accounts. The tool can be installed on the
server and can be also installed as a standalone client application on the
DBA’s workstation.
For Oracle, DBAs have been using a web-based
user interface from version 10g. Incidentally, this tool is also known as the Enterprise
Manager. Since it is accessible via a web browser, no client tool requires
to be installed; all the DBA needs is a browser to access the web site exposed
by the server.
Enterprise Manager in Oracle 10g and 11g
comes in two flavours. Enterprise Manager Database Control is installed
by default on the server where Oracle is running. Database Control allows the
administration of a single instance of Oracle database. Enterprise Manager
Grid Control on the other hand requires a separate installation and some
extra set up procedures. With Grid Control, the DBA is able to manage multiple
databases and other applications such as the Oracle Application Server.
This is similar to having multiple SQL Server instances registered in the SSMS.
The look and feel of the Database Control and the Grid Control is more or less
similar.
Using the Enterprise Manager Database Control
or the Grid Control, DBAs can create, drop and modify Oracle tablespaces and
user accounts, schedule jobs, monitor performance or change system parameters.
Although client access is via a web browser, the tool requires a separate
process called DBCONSOLE to be running on the database server. Without
this process (which runs as a service in Windows servers), the web interface is
inaccessible. Grid control requires special “agent” services to be running on
the servers monitored by it.
The following table shows the comparison
between the management tools of the two database platforms.
|
SQL Server 2000/2005/2008 |
Oracle 10g / 11g |
|
Primary |
Primary |
|
Does |
Requires |
|
Can |
Does |