Wednesday, 12 March 2014

Mapping SQL Server Features to Oracle Database

Database Security

This category includes three child items: Logins, Server Roles, and Credentials.

Mapping SQL Server Features to Oracle Database

Logins in SQL Server can be one of two types with respect to authentication: managed by Windows (typically within Active Directory, but also local to the server) and managed by SQL Server (mixed mode). The examples shown in the screenshot above show both types. As a general rule, login names with one word or without slashes are indicative of being MSSQL accounts. The “sa” account (analogous to SYS and SYSTEM) is an example of this type of account.

Administrators get a free account; one of the several built-in groups in Windows is the Administrators group. Other built-in and local, but Windows authenticated, accounts include the NT AUTHORITY accounts. The NT AUTHORITY\SYSTEM account is granted a login along with the built-in Administrator account, and a common practice is to remove both of these accounts and rely upon AD authentication. If you look at the properties (right-click the account, then go to the Server Roles page), you can see how the SYSTEM account has been granted the sysadmin fixed server role. In Oracle terms, this account has SYS/SYSTEM level privileges).

A common question in Oracle, with respect to licensing, has to do with how many users have access to the database. Letting Oracle manage user security makes the DBA_USERS data dictionary view a fairly valid source of what the count is. With practically all SQL Server logins (from human users) being managed via Active Directory, the Logins folder is not going to be quite as useful, and if a user doesn’t appear under Logins, he won’t appear in a table either. This problem is compounded when DOMAIN\Authenticated Users is created as a login. Any and all domain authenticated users can logon with this login account.

When you logon in Oracle, you have whatever roles, grants, and privileges your account/schema has been authorized. It is quite possible you cannot see objects in another schema. The SQL Server counterpart to this uses a building and office analogy. Your basic logon gets you into the building. To get into a specific office, you need another key. So, within a database, security is (further) managed by granting whatever roles to a user.

Mapping SQL Server Features to Oracle Database

Speaking of roles, SQL Server comes with eight pre-defined server roles (shown in the Server Roles folder). These roles are actually fixed server roles, and their server-level purpose, function or permission is shown below.

Role name Function
bulkadmin Administer bulk operations (run bulk insert)
dbcreator Alter any database (create, and alter/restore their own)
diskadmin Alter resources (manage disk files)
processadmin Alter any connection, alter server state (terminate processes) 
securityadmin Alter any login (grant, deny, revoke server/database permissions, reset passwords)
serveradmin Alter any endpoint, alter resources, alter server state, alter settings, shutdown, view server state (change server-wide configuration options, shutdown)
setupadmin Alter any linked server (add/remove linked servers)
sysadmin Control server (do anything)

The fixed roles are server level, and a user who has one of these roles can grant that role to other logins (Oracle comparison - a role granted with admin included). Several “sp_whatever” queries are available for server roles (as with many other features, such as sp_helpdb), but they may not be very informative. The results from sp_helpsrvrole are shown below.

Mapping SQL Server Features to Oracle Database

As to which logins have which server roles assigned, the sp_help results (sp_helpsrvrolemember) are much more informative. You can see the value of running this periodically as an audit policy (using the principle of least permissive access). To illustrate, I created a new sample user and assigned a fixed role.

Mapping SQL Server Features to Oracle Database

The last folder item under Security is Credentials, and the name is suggestive of what Oracle can use. Database Control, as an example, allows you to enter credential information. When the database needs to go out to the operating system, a login with the appropriate credential may do so. The credential needs a Windows username and password, and the SQL Server login is granted the credential. You’ll see this more often than not in .NET applications (where the database can make calls out to procedures or assemblies).

Server Objects

This category contains some very useful functionality or features, some of which will be more near and dear to the Oracle DBA.

Mapping SQL Server Features to Oracle Database

It goes without saying that backups are critical to all database systems. A Backup Device simplifies some scripting (or entry in a GUI) with respect to where a backup will be created. Instead of scripting out a path, you can create a location and basically alias it within MSSQL. The Oracle analogy (for the most part) is a combination of the archived redo log location(s) and the Flash Recovery Area. As you may recall, the files contained in the FRA include the control file (and an auto backed up version), RMAN files, flashback logs, data file copies, and archived redo logs. Oracle has a lot more going on in the FRA than SQL Server has in a Backup Device aliased location, but you can at least see the similarity.

Endpoints are gateways into MSSQL and are used without an active (human) user session (e.g., one database talking to another database via a process). What are some ways connections into a database can be made? Think of Internet connections in particular, but also connections over a network in general. Database Mirroring is a prime example of this. Mirroring is much like using a standby database in Oracle. The connection between the partners (which database is the primary, and which one is the mirror) is facilitated via endpoints on each server. Multiple databases can be mirrored, and they will all use the same endpoint. Included in the setup of an endpoint are port information and the authentication method. An advantage SQL Server has here is your ability to use the same AD account at both ends (or even a third “end” if using a witness server) for the authentication. If using a local account, you’ll run into server certificate issues.

SQL Server provides several catalog views of endpoint-related information, which can be found here. These will include information about other types of endpoints (Service Broker, SOAP, and TSQL).

A linked server in SQL Server is completely analogous to a database link in Oracle. The list of providers is fairly extensive.

Mapping SQL Server Features to Oracle Database

Two of the providers are directly related to Oracle: MSDAORA and OraOLEDB.Oracle. From Oracle’s perspective, connecting to SQL Server may involve ODAC or MDAC.

Setting up a linked server can be frustrating, and the truth of the matter is that there is more than one way to create a linked server to Oracle. The two create statements (sp_addlinkedserver stored procedure) accomplish the same thing.

EXEC master.dbo.sp_addlinkedserver @server = N'TO_ORCL',
EXEC master.dbo.sp_addlinkedserver @server = N'ALSO_ORCL',

Using a simple queries such as...

Select * from TO_ORCL..SCOTT.EMP
Select * from ALSO_ORCL..SCOTT.EMP


Finally, the last folder item covered in this part of the series is Triggers. How are these triggers different from the triggers found under Programmability in a Database? Well, the first is that the names are different. Within a database, the formal label is Database Triggers. In this area, the name is simply Triggers. SQL Server supports server-level triggers, which are more along the lines of DDL triggers as opposed to DML triggers. DDL triggers can be created at the database level and also at the server level. If you wanted to monitor who created a database, then a server-level DDL trigger would apply, and the trigger would be found under Server Objects > Triggers. In Oracle, all triggers are found under the respective owner. In investigating what happens with data and objects within SQL Server, you have (at least) two places to inspect: database-level and server-level triggers.