Symptom
If you add a user other than HDLADMIN in DataLake and execute sp_iqdbspace, an error will occur.
Could not execute 'sp_iqdbspace'
Error: (dberror) [-204]: You do not have 'SET ANY CUSTOMER PUBLIC OPTION' system privilege or authority to set the option 'auto_commit'
Environment
- SAP HANA Cloud, Data Lake SQL Reference for Data Lake Relational Engine
Cause
- This is a specification for Data Lake Relational Engine(SAP IQ).
System procedures are not authorized to be executed by anyone other than an administrator user.
Resolution
-
For a user to run sp_iqdbspace, some privileges are required.
ex: Replace it with a username that exists in your environment.To have public user SQLTester execute sp_iqdbspace(), grant the following privileges.
Run this SQL with HDLADMIN.GRANT MANAGE ANY DBSPACE to SQLTester;
GRANT EXECUTE ON sp_iqdbspace to SQLTester;
GRANT MONITOR to SQLTester;
GRANT SELECT ON SYSDBSPACE to SQLTester;
GRANT SELECT ON SYSDBFILE to SQLTester;
GRANT SELECT ON SYSIQDBFILE to SQLTester;
GRANT SELECT ON SYSIQDBSPACE to SQLTester;
GRANT SELECT ON SYSIQLOGICALSERVER to SQLTester;
Keywords
"Data Lake", "IQ", "SAP IQ", sp_iqdbspace, grant, "SET ANY CUSTOMER PUBLIC OPTION" , KBA , HAN-CLS-HC-HDL , HANA Cloud HANA Data Lake , BC-SYB-IQ , Sybase IQ , How To