UPDATE: Since ASE 16.0 SP03, the aseuserstore binary is now available. This has the ability to construct a local, encrypted credential store for each Linux user, and can be easily used (just like hdbuserstore for HANA) to automate password-less database calls via isql with the “-k” parameter.
Like most companies, you will have the need to automate specific database tasks directly in the database.
As an example, frequent database backups.
In this post I go through some possible options, but discuss in-depth the use of the sybxctrl binary to facilitate password-less access to execute database tasks.
What are the DB Job Automation Options?
Within SAP ASE databases, if you have an attached ABAP stack, then DB13 can be used to schedule database level jobs, with the caveat that the ABAP stack must be running for jobs to run.
Another alternative, is to use the SAP ASE JobScheduler to automate tasks.
The JobScheduler comes with the SAP ASE system and runs as a separate process.
Using the JobScheduler you can configure jobs/tasks to be executed even if there is no ABAP stack.
However, it means that any configuration must be repeated across all databases and you will lack any central job scheduling control. Also, if the JobScheduler has a problem connecting to the database, then you will not get any backups (especially transaction log backups).
As a good catch-all and an alternative to the above options, there is another way.
Use an enterprise job scheduler (e.g. SAP Business Process Automation) combined with a centrally accessible server-side script to call the database routine/utility.
One central schedule and one script. Simple.
The added advantage being that the central job scheduler will also be responsible for controlling your SAP business processes. This allows an operator to tightly control and monitor the backup window around the critical business processes and also allows tighter integration of backups with cloud level operations (VM snoozing…).
There’s generally one problem with the use of a custom script, and that is where to put the password for the SAP ASE database login.
You can hardcode it into the script, and restrict permissions as much as possible, but this means you will have just one password (unless you put ALL the passwords for all the databases the script will be executed against). It’s also a big “NO” from the auditors and cyber security teams, since there are a undoubtedly some exploits available that will allow access to the script file in some form or another.
Introducing sybxctrl
A possible solution to the problem of where to put the password, is to use a little known binary of the SAP Kernel for ASE databases, called sybxctrl.
Within your usual DIR_CT_RUN location, there will exist somewhere, a binary called sybctrl and its twin, sybxctrl.
They are the same binary code but separate files.
What’s the difference? Well, it’s purely about Linux permissions.
After you install an ABAP Kernel (on Linux), you’re supposed to run the saproot.sh script, which prepares certain SAP binaries, adjusts ownerships and configures executable permissions.
With regards to sybctrl, it is owned by the Linux root user, and it also has the SUID permission set (numerical: 4750, character: swxr-x—).
When this binary is executed by any member of the sapsys Linux group (<sid>adm or syb<sid> users are members of this group), then it will switch executable context to that of the root Linux user. In essence, it executes almost as if you were the root user executing it.
This is a useful Linux feature, to be able to execute binaries in this way; however, it can also leave a security hole.
To provide a level of protection to the O/S during the standard Linux binary execution process, when an SUID enabled binary is executed the Linux environment puts a restriction around the contents of the user’s environment by arbitrarily setting variables like PATH and LD_LIBRARY_PATH.
This means that execution of sybctrl will limit the ability for loading of additional shared libraries in non-standard directory locations, which may be required for certain functions.
Why does sybctrl need root? This is fairly easy to explain, it’s because you normally log onto a Linux SAP system as the <sid>adm Linux user then start all components including the SAP ASE database. Except the SAP ASE database runs as Linux user syb<sid>. So, to be generic, the binary sybctrl is used and executed from <sid>adm via the root user (probably so that shared memory can be freed), before it switches to syb<sid> to start or stop the database.
So how does sybxctrl differ to its more powerful sybctrl? Simple, it doesn’t have the SUID permission set on it.
It doesn’t need to run via root for what it does, but it does need access to the SYBASE_OCS and other shared library locations. So, to ensure that those environment variables are maintained throughout execution, this separate copy of sybctrl was required.
How can we use sybxctrl?
As the <sid>adm Linux user, you can execute sybxctrl with no parameters to return the usage information (the parameters that it does accept).
Of interest to us for our password-less database script, are the parameters:
load_script
unload_script
exec_script
Here’s how we can use sybxctrl to embed a script into the database, then execute it without needing a password.
Before we can use the sybxctrl binary, you need to first ensure:
– You have Kernel 7.22_EXT pl500 and above (or 7.49+).
– You run the saproot.sh to correctly set up permissions on the SAP Kernel binaries.
– You run a “stopdb” as <sid>adm. This will actually copy across the sybxctrl from sybctrl.
– You run sybxctrl to load in a script.
During the execution of sybxctrl with the “load_script” parameter, a new database table called SYBSISQL is created in the DBO schema (not the usual SAPSR3[DB] schema!) of the <SID> ASE database. This table is where the script that you load into the DB, is stored.
Let’s now create a test script as Linux user <sid>adm:
echo "select user_name()\ngo" > /tmp/myscript.sql
All this script will do is print out the current database username.
Now we load the script into the database using the sybxctrl binary and the “load_script” parameter as follows:
sybxctrl load_script /tmp/myscript.sql -exe $SYBASE/$SYBASE_OCS/bin/isql -auth sapsa
You are prompted for the sapsa database user password.
Enter the password and the load should succeed.
What just happened?
You have just populated that new database table (<SID>.DBO.SYBSISQL) with the text of the script that you created, plus the details of how to run it (which binary and the full path, plus parameters).
We have told sybxctrl that we want to use the isql binary (SAP ASE command line SQL utility), and that we want it to execute as the sapsa database user (the “auth” parameter).
Now we’ve loaded the script, we can unload it and check it as follows:
rm /tmp/myscript.sql
sybxctrl unload_script myscript.sql /tmp/
NOTE: You remove the path from the name of the script.
You are prompted again for the sapsa database user password.
Enter the password and the unload should succeed.
We can now see the script contents:
Finally, we can now try and execute the script.
The execution happens as though it was a batch operation, so we need to provide an output file for any output from the script.
Execute the script as follows:
sybxctrl exec_script myscript.sql /tmp/myscript.out -auth sapsa
Did you notice, you didn’t need to enter the password for sapsa!
Check the output file that we specified:
To remove the script from the database:
sybxctrl delete_script myscript.sql
Enter the sapsa password and the deletion should succeed.
We can now embed a pre-configured script into the SAP ASE database, which can be executed at the O/S level as any Linux user with permissions to run sybxctrl, without requiring a password and it will execute as the sapsa database user.
There are many possibilities for this setup when you understand the full parameter list of both sybxctrl and isql.
As an example, you could pipe in parameters to the embedded script…
Couple the script with some standard code and configure the ASE database to use ASE backup configurations and you could have an automated backup routine that is password-less.
Are there any problems in using sybxctrl?
Yes, a few.
- sybctrl will be patched as part of the kernel patching, but synxctrl is not. So you must ensure that you either run a “stopdb” at some point to pickup the new script, or manually copy it into place after Kernel binary patching is completed.
- isql will be patched as part of the ASE patching. This changes the checksum value of the binary file which is recorded during the “load_script” in the new SYBSISQL table.
Therefore, after ASE binary patching, you should unload and re-load the script to re-create the table entry. - Any embedded scripts will be carried along with any SAP system copies if you use the database backup/restore method. But not if you use the R3load tools.
- If you use SAP Replication Server (SRS), this table and it’s contents will be replicated across to the secondary database (as the table is in the <SID> database).
- Bear this in mind for the contents of the backup script, because they may need to be generic if you want to run it on secondary or tertiary databases in an SRS setup.
Happy scripting.
