HANA backup script hint… a little bit more security, please!

+++ UPDATE 23.11.2011 +++

After the release of revision 20 (SPS 03) of the HANA database, some more changes concerning command syntax and system object naming had been identified.
The commands in this blog post have been adapted to reflect this.

+++ UPDATE 23.11.2011 +++

+++ UPDATE 24.10.2011 ++++
I’ve been informed that with the current HANA revisions, starting with rev.16 which is available for download in the SAP Service Marketplace since 10th of September, a altered syntax for taking backups is used.
So, to make them easier to follow in the future, I changed the examples in this blog accordingly.
+++ UPDATE 24.10.2011 ++++

When you check the SAP In-Memory Database Backup and Recovery Guide on https://help.sap.com/hana you’ll find on page 13 (4.3.2 Using SQL Commands) a first reference to a command line sql client:
hdbsql.

The example that follows explains how to create a very simple SQL script that can be fed into the hdbsql tool in batch mode.
That way a super simple schedulable backup command is created, e.g. for scheduling via CRON or any other scheduler tool.

This is what we find in the PDF (syntax updated – see update note above):

-----------8<------snip--------8<----------
[...]
Create a file backup.sql with the following content:
connect -n <server name> -i <instance number> -u <user> -p <password>
//
BACKUP DATA USING FILE ('Monday')
//
Store this file with read rights for the crontab user.
Execute the backup by calling the file backup.sql:
/usr/sap/<SID>/HDB<instance number>/exe/hdbsql -I backup.sql
[...]
-----------8<------snip--------8<----------

Of course this is what most customers and partners do and use in their first HANA implementation.
And in the cases I’ve seen, for -u <user> the SYSTEM user was used.
This basically leads to a situation where there is a plain text sql script file that contains the full logon data for the SYSTEM user of your HANA box.

NOT GOOD! NOT AT ALL!

But are there alternatives? Sure there are!

Step 1 (and keep it repeating like a mantra) – Never use user SYSTEM for plain operation tasks like backups.

Instead create a backup operater user that is not allowed to do anything else then performing backups.
Let’s check how to do this step by step:

Logon to the HANA box as SYSTEM. You can do all the steps as well in HANA Studio, but as I assume most of you already are familiar with the user management there, so I present the command line version here (I chose a reddish color for the SYSTEM account sessions, just to make it a bit easier to recognize them – definitively NOT for decorative purposes…):

C:\\Program Files\\SAP\\hdbclient>hdbsql -U S
Welcome to the SAP HANA Database interactive terminal.

Type:  \\h for help with commands
       \\q to quit

hdbsql HAN=> select user() from dummy
CURRENT_USER
"SYSTEM"

1 row selected (0 usec)

So, I’m logged on as SYSTEM user. Great! Let’s go and create the user for backups!
(+++ 24.10.2011 – changed the logon from -U b to -U S more clarity +++)

hdbsql=> create user backop password 123
* 412: invalid password layout: minimal password length is [8] SQLSTATE: HY000

Ok, I always forget – we have a password rule build into HANA and minimal password lenght is 8 characters.
Let’s try again!

hdbsql HAN=> create user backop password 12345678
* 412: invalid password layout: password has to meet the rule ['A1a'] SQLSTATE: HY000

Grrr… I forgot again… this is really something I don’t like about password rules… but anyway: one more try!

hdbsql HAN=> create user backop password Aa12345678
0 rows affected (0 usec)

Success! Yes! Time for the first coffee break! (Ok, just kiddin’ we’re nearly done…)
Let’s connect with this user and try to take a backup!

hdbsql=> \\connect -n vml3012 -i 00 -u backop -p Aa12345678
Connected to HAN@vml3012:30015
hdbsql HAN=>
hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
* 414: user is forced to change password: alter password needed for current user SQLSTATE: HY000

This is also something I already had a few support messages for.
The very first time you logon with a specific user to HANA you are forced to change your password.
Unfortunately there usually is no dialogue for this (it’s present only in HANA Studio but which end-user has access to this?), so one either has to have access to a SQL command line (like we happen to have right now) or somebody must have taken care of this, by e.g. implementing a little macro in Excel for this.
Anyhow, there should be a better general solution, but for now just move on and change the password:

hdbsql HAN=> alter user backop password Back1234
0 rows affected (0 usec)  

Worked like a charme. Next step: take the backup!

hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
* 258: insufficient privilege: Not authorized SQLSTATE: HY000

Dang! We didn’t provide any of the required system privileges yet.

In a separate command line window I logon again as SYSTEM and run the following:

hdbsql -U S

Welcome to the SAP HANA Database interactive terminal.

Type:  \\h for help with commands
       \\q to quit
hdbsql HAN=> grant BACKUP ADMIN to backop
0 rows affected (1 usec)

That was easy cheesy, wan’t it?
Back to our backop-session and try once again:

hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
0 rows affected (12 usec)

HOOORAY!

We finally did it! Now we’ve got a safe user that can logon to the HANA DB but is only able to create backups.
Very nice.
Still we would have to put the logon data into the backup script file. But we have a second step to go…

Step 2 – Create and use a user store entry

The MaxDB veterans amongst the readers of this blog will already have recognized it:
HDBSQL is a ported version of SQLCLI the command line SQL client for SAPs very own MaxDB.
SQLCLI had a way to store user credentials and logon data called XUSER (I even wrote about it way back…).
And guess what: this had been ported as well.
It’s called hdbuserstore now but works pretty much the same way.

ATTENTION: hdbuserstore is part of the HANA client package ONLY. I wasn’t able to find it on a vanilla installation of HANA, so if you want to use it on the server (e.g. for the backup use case I present here) you’ve to install the HANA client on the server manually.

For this demonstration I simply used the HANA client on my laptop. To start, let’s review the command line options for this tool:

C:\\Program Files\\SAP\\hdbclient>hdbuserstore.exe -h

Usage: hdbuserprofileadm [options] command [arguments]

Options:
-u <USER>       perform operation for other operating system user
-v              verbose mode, print operations done
-h              this help message
Commands:
HELP
Print help message.
SET <KEY> <ENV> <USERNAME> <PASSWORD>
Set a profile entry.
<KEY>       entry key
<ENV>       connection environment (host and port)
<USERNAME>  user name
<PASSWORD>  password
DELETE <KEY>
Delete entry with key <KEY>.
LIST [<KEY> ...]
List entries of store. The passwords are not shown.

In short the programm allows us to create, display and delete sets of logon data.
These sets have a

• name or <KEY>,
• the ‘connection environment’ <ENV> which means HANA hostname and port (in my case this is vml3012:30015),
• of course a <USERNAME>
• and the <PASSWORD>.

Let’s go straight ahead and create an entry:

hdbuserstore set b vml3012:30015 backop Aa12345678

As you see, I’ve used

• b as <KEY>,
• vml3012:3001 as <ENV>,
• backop as <USERNAME>
• and Aa12345678 as <PASSWORD>.

I’d wish the hdbuserstore command would provide more feedback in case everything went OK, something along “OK, profile b successfully created!” or similar, but today it’s more along “no news are good news“…

Anyhow, we can review our newly created logon profile:

hdbuserstore list
LIST command for all entries is not yet implemented.

I guess we have to provide exactly what we want to see:

hdbuserstore list b
KEY b
ENV : vml3012:30015
USER: backop

Et voilá, there is our entry.

One thing to keep in mind is that these entries are acessible only by the operating system user that created it.
So in order to use this for the CRONJOB backup, you’d have to logon to the user cron executed the backup script with and create the logon profile there!

Now we put it all together and change the backup script to

BACKUP DATA USING FILE ('Monday')
//

With this file we can now call the backup just like this:

hdbsql -U b -I backup.sql

Again, we don’t get any output here if no errors occur.
This can become a bit annoying, especially when problems occur and debugging of the script should be done.
For that you can add the -f (show the SQL command) parameter and/or the -t (printout debugging information) parameter.
The output then looks like this:

hdbsql -U b -I backup.sql -f
BACKUP DATA USING FILE ('Monday')

or like this:

hdbsql -U b -I backup.sql -f -t
Username  : ""
DB name   : ""
Instance  : "(null)"
server    : ""
xuserkey  : "b"
nullvalue : "?"
fieldsep  : "(null)"
cmdsep    : "//"
CMDTrace  : "TRUE"
autocommit: "TRUE"
noHeader  : "FALSE"
useDBS    : "TRUE"
batch     : "FALSE"
rollbOnErr: "FALSE"
SQL mode  : "(null)"
Command   : "NULL"
BACKUP DATA USING FILE ('Monday')

That’s it once again!

As always I hope you liked the hands-on style of my blog and that you share your experiences with HANA.