A High Level Comparison Between Oracle and SQL Server – Part IV
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:
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.
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 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 database management tool is the Enterprise Manager or the Management Studio. Both are Windows client applications.
Primary database management tool is the Enterprise Manager Database Control or the Enterprise Manager Grid Control. Both interfaces are web based.
Does not require any special service or process to be running on the database server.
Requires DBCONSOLE process for the database control or Agent service for grid control to be running on the database server.
Can be installed on a workstation without installing the database server.
Does not require any client workstation component.