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 indexserver for HANA and dataserver for ASE. From this, we can see that there is one HANA indexserver handling 2 “databases”, SYSTEMDB.STOCKS and HXE.STOCKS. For ASE, this would 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.