Granting WITH GRANT OPTION on a catalog object that is owned by the _SYS_REPO

[MichaelHealy779]

Hi,

I wish to grant WITH GRANT OPTION on a schema to a user but this schema is owned by the _SYS_REPO (someone change the ownership to _SYS_REPO from SYSTEM). Is there any other way of granting this access further or is the only solution to create a procedure and execute a GRANT statement inside the procedure so its executed by the _SYS_REPO?

Thanks,

Michael

[Cocquerel · Accepted solution]

You can run the grant statement in a procedure that executes with the privilege of _SYS_REPO user.
For this, create an .hdbprocedure with SQL SECURITY set to DEFINER (meaning _SYS_REPO) and put your grant statement in it.
Then, you can call this procedure with your user.

[MichaelHealy779 · Accepted solution]

Thank you Michael. I see Florian gave this recommendation also:

How to Give Access WITH GRANT OPTION if object is owned by _SYS_REPO | SAP Community

I have tried this but I am seeing issue with the statement:

This is the procedure:

PROCEDURE "_SYS_REPO"."<>::GRANT_ADMIN_OPTION_ON_SYS_REPO" ( )

LANGUAGE SQLSCRIPT

SQL SECURITY DEFINER

--DEFAULT SCHEMA <default_schema_name>

READS SQL DATA AS

BEGIN

declare stmt nvarchar(1000);

declare mycond condition for sql_error_code 10001;

if is_sql_injection_safe(user) <> 1 then

signal mycond set message_text = 'User parameter not SQL injection safe';

end if;

stmt = 'GRANT SELECT ON "SCHEMA_NAME" TO ' || user || ' WITH GRANT OPTION';

exec stmt;

END

[Cocquerel · Accepted solution]

try a procedure this this

PROCEDURE "_SYS_BIC"."MyProcedure" ( in SQLStatement nvarchar(1000) default '' ) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY DEFINER 
AS
BEGIN
EXECUTE IMMEDIATE SQLStatement;
END;

[MichaelHealy779 · Accepted solution]

Thank you Michael, that worked a charm.

Thank you again for your help 🙂