Mapping SQL Server Features to Oracle Database

Database
administrators are often faced with the need to learn where features of his/her
system live or reside on a less familiar system. Steve Callan approaches this
need by mapping SQL Server features back into Oracle Database.

Database Security

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

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.

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.

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.

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.

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.

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.
Information about Oracle Data Access Components can be found at OTN in the Windows
technology area
. If you are using much older versions of either product,
Microsoft Data Access Components info can be found here.

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', 
                                   @srvproduct=N'Oracle', 
                                   @provider=N'OraOLEDB.Oracle', 
                                   @datasrc=N'WIN2003:1521/ORCL'
 
 
EXEC master.dbo.sp_addlinkedserver @server = N'ALSO_ORCL', 
                                   @srvproduct=N'Oracle', 
                                   @provider=N'MSDAORA', 
                                   @datasrc=N'ORCL'
 

Using a
simple queries such as…

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

…can
return different results (one will work; the other may not, with a complaint
about a numeric error conversion). Check
your client versions
of software (one is to be above 10.2.0.1, 10.2.0.2 and
higher seems to work well).

Triggers

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.

A couple of
good examples covering and using DDL triggers can be found here
and here.

Summary

In the next
part of this series, we’ll continue on with more of the mappings from SQL
Server to Oracle.

»


See All Articles by Columnist

Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles