Skip to Content
Technical Articles

Duplicating ASE Table Placement and Permission in HANA

I am comfortable with ASE. For HANA it’s a little different right now, so coming up to speed in HANA often means that I am comparing what I know in ASE with what I don’t know in HANA. Take table placement and permission for example. Here is what I have discovered so far, and it can get complicated very quickly.
 
For starters we have terminology problems. There are HANA system databases versus tenant databases, HANA system-defined users versus user-defined users, HANA databases versus ASE dataservers, HANA schemas versus ASE databases and ASE database users, ASE database owners versus ASE database users, and roles, privileges, and user differences in both HANA and ASE. That’s just for starters.
 

Creating a table in HANA

 
I have the HANA Express Edition installed, with a SYSTEMDB database and an HXE tenant database. That’s a good place to start.
 
Using the HANA Cockpit, when you create a user-defined user called STOCKS (for example) in the HXE tenant database, a schema (sub-database) called STOCKS is automatically created in both the SYSTEMDB database and the HXE tenant database. These 2 STOCKS sub-databases are entirely separate. They have their own collection of tables, views, triggers, indexes, procs and so on.
 
From this I will say for comparison purposes that a database in HANA is the same as a dataserver in ASE. And a schema in HANA is the same as a database in ASE.
 
This is close enough. When you do a ps –ef from linux, you will see nameserver and indexserver for HANA and dataserver for ASE. From this, we can see that there are two HANA servers handling 2 “databases”, SYSTEMDB.STOCKS and HXE.STOCKS. For ASE, this would also be two separate ASE dataservers each handling its own STOCKS database, since you cannot have duplicate database names on one dataserver.
 
Strangely to me, a user in ASE is the same as a schema (sub-database) in HANA. I immediately see a problem. We have some ASE systems with upwards of 10,000 users attached to one database. Yes, we have business users which are not connection-pooled into a single database user. For HANA, I really don’t want 10,000 schemas (sub-databases), so the first thing is to NOT duplicate this ASE scenario in HANA. For HANA, we will ALWAYS go with a scenario where we create 10,000 business users in an application server and not the database server, leaving just a couple developer and administrator and runtime database users in HANA database. This already happens in many, but not all, of our ASE installations.
 
Good so far. We now do the table mechanics. In HANA, we have 4 login and table possibilities. Our schema is called STOCKS, tied to a user called STOCKS, created in the HXE tenant database. Our table will be called StockTicket.
 
1. Logging onto the SYSTEMDB with the SYSTEM user is possible, and we can create a table in the STOCKS schema or the SYSTEM schema:
 

hdbsql –u SYSTEM –p **** -d SYSTEMDB –i 90 –quiet –A –m
create table STOCKS.StockTicket (aaa int);
insert into STOCKS.StockTicket values (1);
create table SYSTEM.StockTicket (aaa int, bbb int);
insert into SYSTEM.StockTicket values (2,2);

 
2. Logging on to the SYSTEMDB with the STOCKS user is not possible because the user was created in HXE and not SYSTEMDB, even though a STOCKS schema was created in SYSTEMDB:
 

hdbsql –u STOCKS –p **** -d SYSTEMDB –i 90 –quiet –A –m
authentication failed SQLSTATE: 20000

 
3. Logging on to the HXE tenant database with the STOCKS user is possible, and we can create a table in the STOCKS schema:
 

hdbsql –u STOCKS –p **** -d HXE –i 90 –quiet –A –m
create table STOCKS.StockTicket (aaa int, bbb int, ccc int);
insert into STOCKS.StockTicket values (3,3,3);

 
4. Logging on to the tenant HXE database with the SYSTEM user is possible, and we can create a table in the SYSTEM schema:
 

hdbsql –u SYSTEM –p **** -d HXE –i 90 –quiet –A –m
create table SYSTEM.StockTicket (aaa int, bbb int, ccc int, ddd int);
insert into SYSTEM.StockTicket values (4,4,4,4);

 
We now have 4 entirely different copies of the StockTicket table depending on the database and schema:
 

hdbsql –u SYSTEM –p **** -d SYSTEMDB –i 90 –quiet –A –m
select * from STOCKS.StockTicket;
AAA
---
1
select * from SYSTEM.StockTicket;
AAA BBB
--- ---
2   2

 

hdbsql –u STOCKS –p **** -d HXE –i 90 –quiet –A –m
select * from StockTicket;
AAA BBB CCC
--- --- ---
3   3   3

 

hdbsql –u SYSTEM –p **** -d HXE –i 90 –quiet –A –m
select * from StockTicket;
AAA BBB CCC DDD
--- --- --- ---
4   4   4   4

 

A Specific ASE Table Scenario

 
In ASE, we can do the same thing using table owners and multiple dataservers. But actually, we will not. Our real-life scenario will follow the {dataserver.database.dbo.table} ASE model. This model will create one copy of the StockTicket table with one owner in one database on one dataserver.
 
We will have one unrestricted database administration user that creates the table, and two restricted users. One user is for the application and can select, insert, update, and delete. The other user is read-only and can only select data.
 
In ASE, we log on to the ASE dataserver as “sa” (equivalent to SYSTEM in HANA) and do this:
 

isql -Usa –P **** -S${DSQUERY} -I${INTERFACE} –Dmaster
create database STOCKS on data01="5G" log on log01="1G"
go

-- Add the database owner, application, and reporting logins
exec sp_addlogin STOCKDBO,****,STOCKS,null,'dbo login',0,0,0
exec sp_addlogin STOCKAPP,****,STOCKS,null,'application login',0,0,0
exec sp_addlogin STOCKREPORTING,****,STOCKS,null,'reporting login',0,0,0
go

-- Add users to the database
use STOCKS
go
checkpoint
go
exec sp_addgroup "MY_STOCKAPP_GROUP"
exec sp_addgroup "MY_STOCKREPORTING_GROUP"
go
exec sp_adduser STOCKAPP,STOCKAPP,MY_STOCKAPP_GROUP
exec sp_adduser STOCKREPORTING,STOCKREPORTING,MY_STOCKREPORTING_GROUP
go

-- Make STOCKDBO the owner of the database.
-- This automatically grants STOCKDBO all object permission.
exec sp_changedbowner STOCKDBO,true
go

 
Now log on to the ASE dataserver as the database owner, STOCKDBO, and create a table in the STOCKS database. This user and “sa” are the only users with permission to create a table:
 

isql -USTOCKDBO –P **** -S${DSQUERY} -I${INTERFACE} -DSTOCKS
create table StockTicket (aaa int)
go
grant select on StockTicket to MY_STOCKREPORTING_GROUP
grant select,insert,update,delete on StockTicket to MY_STOCKAPP_GROUP
go

 
Now log on to the ASE dataserver as an application user. Insert and select from the table:
 

isql -USTOCKAPP –P **** -S${DSQUERY} -I${INTERFACE} -DSTOCKS
insert StockTicket values (1)
go
select * from StockTicket
go
AAA
---
1

 
This is how you would do it in ASE. Given all the HANA possibilities, let’s see how close we can come in HANA.
 

Duplicating the ASE Table Scenario in HANA

 
Log in to the HXE tenant database as SYSTEM and create the STOCKS schema, a STOCKDBO unrestricted user, a STOCKAPP and STOCKREPORTING restricted user:
 

hdbsql –u SYSTEM –p **** -d HXE –i 90 –quiet –A –m
CREATE SCHEMA STOCKS;
CREATE USER STOCKDBO PASSWORD **** NO FORCE_FIRST_PASSWORD_CHANGE;
CREATE USER STOCKAPP PASSWORD **** NO FORCE_FIRST_PASSWORD_CHANGE;
CREATE USER STOCKREPORTING PASSWORD **** NO FORCE_FIRST_PASSWORD_CHANGE;

 
I see from the HANA Database Explorer that we have four schemas in the HXE tenant database, and nothing in SYSTEMDB. This is good, but different than I expected. Now create roles and assign them to the users. For now I will rely exclusively on HANA roles and omit HANA user groups:
 

hdbsql –u SYSTEM –p **** -d HXE –i 90 –quiet –A –m
CREATE ROLE MY_STOCKDBO_ROLE;
GRANT DATA ADMIN TO MY_STOCKDBO_ROLE;
GRANT ALL PRIVILEGES ON SCHEMA TO MY_STOCKDBO_ROLE;
GRANT CREATE ANY ON SCHEMA STOCKS TO MY_STOCKDBO_ROLE;
GRANT MY_STOCKDBO_ROLE to STOCKDBO;

CREATE ROLE MY_STOCKAPP_ROLE;
GRANT SELECT ON SCHEMA STOCKS TO MY_STOCKAPP_ROLE;
GRANT INSERT ON SCHEMA STOCKS TO MY_STOCKAPP_ROLE;
GRANT UPDATE ON SCHEMA STOCKS TO MY_STOCKAPP_ROLE;
GRANT DELETE ON SCHEMA STOCKS TO MY_STOCKAPP_ROLE;
GRANT MY_STOCKAPP_ROLE TO STOCKAPP;

CREATE ROLE MY_STOCKREPORTING_ROLE;
GRANT SELECT ON SCHEMA STOCKS TO MY_STOCKREPORTING_ROLE;
GRANT MY_STOCKREPORTING_ROLE TO STOCKREPORTING;

 
We can now create the table in the STOCKS schema:
 

hdbsql –u STOCKDBO –p **** -d HXE –i 90 –quiet –A –m
create table STOCKS.StockTicket (aaa int);
create public synonym StockTicket for STOCKS.StockTicket;

 
We create a public synonym for the table so that all users can access the table without the schema prefix STOCKS. This also says that there is no other schema that needs to share the table name StockTicket publicly. Now we finish up and select the data:
 

hdbsql –u STOCKAPP –p **** -d HXE –i 90 –quiet –A –m
insert into StockTicket values (1);
select * from StockTicket;
AAA
---
1

 
The HANA Database Explorer shows the HXE tenant database with 4 schemas (STOCKS, STOCKAPP, STOCKDBO, and STOCKREPORTING). I see one table StockTicket in the STOCKS schema. I also see one public synonym StockTicket for the HXE tenant database.
 
The other schemas are entirely empty. From what I read you cannot delete these schemas because the user will be deleted also. We do not want to do this.
 
In the SYSTEMDB database I see absolutely no STOCK* schemas at all. This tells me there is a difference between adding a user with the HANA Cockpit and sqlscript. The HANA Cockpit puts the schema everywhere whereas sqlscript puts it exactly where you want it.
 
OK great! It looks like we can duplicate ASE exactly if needed. This is a good jumping off point for me, to get some more things up and running in HANA.
 

2 Comments
You must be Logged on to comment or reply to a post.
  • Thanks for this extensive write-up.

    It makes a good read and clearly has seen a good amount of editing/proofing. That’s a double thumbs-up!

    There are a few things mentioned in it that are not quite correct, though. I don’t want to “red-pen” this really good post, but I feel you might benefit from the following pointers. Most of the topics are covered in the HANA documentation and/or the various HANA books that are available nowadays anyway.

    So, here we go:

    • the SystemDB is not handled by any indexerserver process.
      Every indexserver process is responsible for just one single tenant database.

      The process that takes care of the SystemDB is the nameserver process.

    • the separation of user accounts and databases in SAP HANA is a bit more … “consequential” than in other DBMS (e.g. ASE or MSSQL).
      Every tenant DB has its own set of users, completely separated from all other tenant DBs or the SystemDB. And each and every DB user has a schema assigned to it, that has the same name as the user.
    • the equivalent of creating a DB in ASE would be to create a new tenant DB and not reuse the existing tenant DB (HXE).
      Creating roles and users in the SystemDB will not make any of them available in any tenant DB.

    Once again, I don’t want to “red-pen” this and I commend you for a really good blog post and hope that these conceptional corrections make it easier for you to work with HANA.

    Cheers,

    Lars

    • Thanks very much Lars, I am always happy for a “red-pen”. There is much to learn in HANA and many thousands of pages of documentation and other things; your comments are very much appreciated.

      Mark