SAP HANA repository role exclude/deny select with wildcard

[former_member635152]

HI,

I have a question on repository roles in HANA. I want to have a role that has access to a schema except some specific objects in that schema that are being identified with a wildcard.

The example below gives SELECT permissions on schema SCHEMADB but the second line should exclude/deny SELECT on all objects matching pattern "/TAB/EXAMPLE%" within that schema.

Is that possible?

role example::ROLE
{
catalog schema "SCHEMADB": SELECT;
catalog sql object "SCHEMADB"."/TAB/EXAMPLE%":	SELECT;
}

Thanks a lot!

[pfefferf · Accepted solution]

For HANA repository roles (which you are using like it seems) there exists no exclude option.

For HDI based roles with the "pattern_mode" such things could be done. You can find the details in the help here.

[former_member635152 · Accepted solution]

Thanks, I beieve the same applies to wildcards?

[pfefferf · Accepted solution]

rocksde Correct. Not supported for repository roles. Supported for HDI based roles when specific configuration is done ->

Wildcard object identifiers can only be used, if the configuration parameter feature_enablement_string is set to WildcardPrivilege in the global feature configuration section of the global.ini file.

[Cocquerel]

For repository role, the workaround would be to create a runtime role (example MY_RUNTIME_ROLE) , add it as sub-role to your repository role and grant objects in the runtime role by script.

DO BEGIN
DECLARE CURSOR c_objects FOR SELECT
 "SCHEMA_NAME",
 "OBJECT_NAME"
FROM "SYS"."OBJECTS" 
WHERE "SCHEMA_NAME" = 'SCHEMADB' 
AND "OBJECT_TYPE" IN ('TABLE','VIEW','FUNCTION') 
AND "OBJECT_NAME" NOT LIKE '/TAB/EXAMPLE%' 
AND "OBJECT_NAME" NOT LIKE '%/dp/%' 
AND "OBJECT_NAME" NOT LIKE '%/hier/%' 
AND "OBJECT_NAME" NOT LIKE '%/proc/%';

DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

FOR object AS c_objects
DO
EXEC 'GRANT SELECT ON "'|| object.SCHEMA_NAME || '"."'|| object.OBJECT_NAME || '" TO MY_RUNTIME_ROLE';
END FOR;
END;