More and more customers decide to use MaxDB and with that, the number of customers asking for a comprehensive list of differences or a comparison increases as well.
In my mini-series “Mind the gap” I will try to shed some light on where the little or big differences between MaxDB and Oracle databases are and what to keep in mind when working with them.
Today I will start with something very basic nevertheless important:
logon mechanisms and user types.
So let’s get started – about database logon mechanisms in SAP systems
One quite common problem is lost connectivity to the database. There are not too many SAP Basis administrators around that never got across a situation where the workprocesses were not able to connect to the database anymore.
In such a situation it is good to know how the workproceses actually perform the connect to the database.
How it works with Oracle…
For Oracle systems the SAP workprocesses make use of an operation-system (OS) authentication called OPS$-mechanism.
One creates a user in the database and sets it up in a way with the flag “identified externally“.
Now whenever this OS-user tries to connect without specifying username and password (e.g. “sqlplus /”) the database asks back to the OS if this user is known.
If so, the access is granted.
Once the workprocesses have done this successfully, they have access to just one table called SAPUSER that contains the password for the SAP-schema user.
With that password the workprocesses then perform the final connect as the SAP-user against the database – this time with a username and a password.
That approach allows storing the logon data for the actual SAP-schema user inside the database and relies (obviously) on the authentication of the users by the OS.
Of course, one has to know for sure, which OS-users run the SAP workprocesses in this case.
Anyhow, to make this mechanism work correctly the sqlnet configuration has to be done correctly (sqlnet.ora, tnsnames.ora, ORACLE_SID environment etc.) and of course, the OS-based authentication has to work properly (which might be difficult in some Windows Active Directory setups).
… and with MaxDB
MaxDB uses stored logon data as well, but it does not store them in the database.
Instead it is stored them on OS-level for every user on every server separately and encrypted.
The facility that MaxDB uses for this is called XUSER.
Therefore, for the OS-users that run the workprocesses (<sid>adm on UNIX/LINUX and SAPSERVICE<SID> on MS Windows) it is necessary to setup and store the logon data.
A XUSER-entry consists of all information needed to connect to the database, that is username, password, database SID, hostname of the database and some other “special” settings.
Once the data has been setup correctly, the workprocesses (or any other process like e.g. sqlcli or dbmcli) can logon to the database just by specifying the KEY under which the logon data is stored.
For the SAP schema user the key is usually ‘SAP<SID>’, so if your SAP System Identifier is ‘SDB’ it would be possible to connect just via:
sqlcli -U SAPSDB
No need to specify the database or the host on which it runs, just the XUSER-key, that’s it.
*) ok, in the area of CCMS there we have something called “central authorization” where the logon data is stored within the database. However, this is just used by the CCMS.
Checking the Connectivity
Another important thing to know is how to check if the logon mechanism was configured correctly.
Since it is time consuming to try the “startsap”-option repeatedly, a shortcut is using R3trans.
It uses the same logon mechanisms as the workprocesses do, but has a much smaller startup overhead (just one process, no memory initialization etc.).
to try a connect to the database including the read access to some internal tables.
If it works – great, chances are that the workprocesses will also be able to perform the connect.
If not it will have generated a trace file named “trans.log” in the current directory containing detailed information about what went wrong with the logon.
Since R3trans is database-dependent is knows how to use the OPS$-mechanism for Oracle and how to use the XUSER-mechanism with MaxDB.
ATTENTION: mind the user context you are running R3trans in!
It might well be that you test it on e.g. Windows as the <sid>adm user and it works and still the workprocesses fail to connect as they are running in the SAPSERVICE<SID> context (VERY popular mistake!).
The second part of my blog is about a drier topic: user concepts.
Both database systems know different kinds of users that are required in different contexts.
Let’s start with the easy to understand SQL-user first.
This database user is used most.
It can logon to the database, create and drop database objects and query and update data.
Just what a database user is all about concerning working with the database itself. The workprocesses use this type of user to connect to the databases.
What these users usually are not able to do are actions like backup, recovery, shutdown and (of course not) startup the database.
Since SQL-users only exist within the database, it is necessary that the database is up and running to use them. Therefore, these users are just not there to start the database instance…
The second kind of user is the administrative (dba) user. This user type is focused on performing dba tasks like backup, recovery, shutdown and startup ❗ the database. It is the “magic database god” straight away as it can create the database instance.
This distinction between the both user types is present in both MaxDB and Oracle. Nevertheless, there are again important differences.
… in Oracle
With Oracle there is the SYSDBA user. This user (called SYS) can do anything – really anything in the database.
Permission checking just does not exist for this user. Beside any DBA actions it can also access any data in the database. It is really the “almighty” on Oracle instances.
This comes in very handy, as this user is also os-authenticated.
Just put the os-level user you are starting sqlplus with into the dba-group and all you have to type in is
sqlplus "/as sysdba"
to connect to the database instance.
With every light there is a shadow: this approach does not protect you from any mistakes. If you are about to mess things up – this user makes possible the worst. (Amazing how quick you can drop a multi-GB-tablespace… just one command…)
… and in MaxDB
MaxDB on the other side strictly separates SQL-users and DBAs (called Database Manager Operators in MaxDB-speech).
As such a DBM user (usually named CONTROL) you can also do anything you like with the database instance – except accessing the data in the database. (I here praises of SOX-consultants rising in the background…).
Nevertheless there are also exceptions to this separation – the DBM users are allowed to access the database catalog and some internal tables to get information about the database instance when it is up and running.
To enable this “double-life” the user-logon information are stored twice: in a special file (user profile container or upc-file) and in the database (table users).
In former versions of MaxDB it was possible to change the password of a user in just one of both location and thus create an inconsistency. Nowadays this is past – so a change of the password for e.g. CONTROL or SUPERDBA in one of the locations automatically updates the other.
My view to it
To me this strict distinction has been one of the major obstacles when I first got hands on MaxDB after developing on Oracle for quite some time.
However, I have to say, “I like that!”
Like most people, I tend to get lazy and sometimes thoughtless in the hurry of the day and then it is easy to issue just that catastrophic command that kills your database.
Concerning that Oracle pushes the “learn through pain“-approach where MaxDB just do not do anything until you do it right (sometimes it is even too quiet about what exactly was not right…)
When it comes to flexibility with the setup of logon mechanisms Oracle is clearly in the lead – so many options, so many parameters than can be set.
However, on the few thousand Oracle installations of SAP systems that I have seen in the last years most often just the standard setup was used. Sometimes not even the recommendations had been followed.
Viewed from that angle MaxDBs logon mechanism has everything required for a SAP installation and is much easier to configure.
The cheat sheet ————- 8< ———— cut here ———— 8< ————
To reward you for your endurance and reading this so far, here’s a short cheat sheet that provides the quick overview over the differences and the similarities between the both DBMS.
superuser called SYS
superuser called CONTROL
workprocesses use OPS$– and
workprocesses use the XUSER data to logon to the database
SYS can do anything, including accessing SAP-data
CONTROL can only access the database instance, but not the SAP-data
slight distinction between DBA and SQL-user (SYS is both)
strict distinction between DBA and SQL-User
notes to know:
notes to know:
———————- 8< ———— cut here ———— 8< ——————–
In the next issues of “Mind the gap” I am going to talk about topics like data storage, query optimizers, database software installations … let me know if you’re interested in a specific topic and “stay tuned!”