How to Grant Access To All Managed HANA Data Lake Tables For HDLADMIN
In March of 2021, a massive update was released for the HANA Cloud, data lake service (also known as HANA data lake, or HDL for short). The release included new support for raw file storage in HDL and the ability to connect directly to HDL, and this functionality was made available to existing HDL instances as well as any newly created ones. In this blog I will talk about the new direct connection and how existing HANA data lake IQ (HDLIQ for short) users can take advantage of this feature in their existing HDL instances.
Prior to this release, HDLIQ was only available in a ‘managed’ mode, where the HANA Cloud, HANA database took care of all management of the data lake – schemas, user access permissions, interfaces, etc…
The ability to connect directly to a HANA data lake IQ instance means that you can now provision an HDL instance without having to have a HANA database instance to manage it. This opens the door for our on-premise SAP IQ customers who are looking at a move to the cloud to more easily migrate to HANA Cloud, since the direct connection capability uses and supports the same set of client interfaces as SAP IQ.
Besides providing a nice migration path, direct connections also open up other possibilities for HANA Cloud usage, like improving performance for certain operations, and supporting non-HANA database based use-cases for HDLIQ.
A side effect of this direct-connection support is that if you choose to connect directly to the HANA data lake IQ, you must manage users and access permissions separately. To enable this, direct connections to the data lake are initially made via a new user, called HDLADMIN, which has a purpose and access similar to DBADMIN in the HANA Cloud, HANA database.
If you are creating a new instance, this is fine, but what if you already have a HANA data lake instance, with tables and data stored in HDLIQ? How do you enable direct connections?
Well, to enable direct connections for an existing HANA data lake IQ instance, you need to get the HDLADMIN password. This means you need to log a support ticket to have the password for HDLADMIN reset, since it will have been set to a random value during the upgrade process.
Note that enabling direct connections to the data lake does not prevent you from continuing to use the data lake through the managed HANA database connection
Once you have the HDLADMIN password, you can make direct connections… BUT… you cannot access any of the tables that you previously created through the HANA managed connection to the data lake. This is both good and bad. This is done as a security measure, to isolate operation of the HANA managed HDLIQ from the direct access HDLIQ.
However, the reality is, if you already have a bunch of tables and data in your HANA data lake IQ instance, it is highly likely that you would want to have access to that data, regardless of whether your connection was direct or managed by HANA.
Fortunately, you can grant access permissions to any objects you have created in the HANA data lake IQ instance to any HDLIQ user using the standard SQL GRANT statement. For example, if I wanted to grant read permission for a HDLIQ table that was created through the managed HANA connection to the HDLADMIN user, I could execute the following from my HANA db connection:
CALL SYSRDL#CG.REMOTE_EXECUTE('GRANT SELECT ON "myHDLIQtable" TO HDLADMIN;' );
But what if you have many tables? Unfortunately, there is no GRANT statement that will give access to all tables in the managed HDLIQ schema. But there is a fairly easy way to do this quickly, by using SQL to generate the GRANT statements you want. The following SQL, executed from the HANA db instance, will create a view in HDLIQ that, when executed, returns a result set consisting of a grant statement for every table in the managed schema.
CREATE OR REPLACE VIEW grantpriv AS
select ''CALL SYSRDL#CG.REMOTE_EXECUTE(''''GRANT SELECT ON "'' + table_name + ''" TO HDLADMIN;'''' ); ''
from SYSTABLE st, SYSUSERS su
where su.name = ''SYSRDL#CG'' and st.creator = su.uid and table_type = ''BASE''
CREATE VIRTUAL TABLE generate_grant AT "SYSRDL#CG_SOURCE"."NULL"."SYSRDL#CG"."grantpriv";
select * from "generate_grant";
Your result set should look something like this:
Now all you need to do is copy the result set and paste it into the SQL execution window and execute it. Once completed, the HDLADMIN user will have read access to all tables in your managed HDLIQ schema. The nice thing about this is that you can also easily modify the list of permissions ( eg. to add write permissions for tables) or limit the set of tables by simply updating the view definition.
This trick of using SQL to generate a batch of SQL statements to run against a database has come in handy many times. If it is not in your toolbox yet, it is definitely worth adding.
Thanks for reading!