Mapping SQL Server Features to Oracle Database
March 24, 2010
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.
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 doesnt appear under Logins, he wont 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.
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. Youll see this more often than not in .NET applications (where the database can make calls out to procedures or assemblies).
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, youll 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 Oracles 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.
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).
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.
In the next part of this series, well continue on with more of the mappings from SQL Server to Oracle.