Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 24, 2010

Mapping SQL Server Features to Oracle Database

By Steve Callan

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date