Hi Experts,
In our project we use dynamic database views, which are not created by file but by users who execute stored procedures for creating views.
When a user creates a simple database view in schema XXX using the following syntax in SQL Console:
create view "XXX"."test_view" as select * from "XXX"."test_table";
only the user who created the view is authorized to see the view. No other user has authorization to make this select:
select * from "XXX"."test_view";.
The following error occurs when any other user apart from user who created the view tries to make this select:
Could not execute 'select * from "XXX"."test_view"'
SAP DBTech JDBC: [258]: insufficient privilege: Not authorized: line 1 col 22 (at pos 21)
The user who tries to make select on view has authorization on schema XXX and is able to see all other objects on schema XXX apart from the views which are created by any other user.
The following error is in trace file:
UserId(580149) is not authorized to grant SELECT on ObjectId(2,0,oid=580132).
So this means that error is on the side of user who creates the view. We found that we need to give SELECT privileges WITH GRANTABLE TO OTHERS OPTION to the users who create views on schema XXX, in order to enable other users to see those views.
Schema XXX is created by file, so the owner of this schema is _SYS_REPO. No user is authorized to give those privileges on schema XXX apart from the owner of the schema - _SYS_REPO. It is not possible to change the owner of the schema and it is not possible to login with _SYS_REPO user. Instead of that, there is a procedure which is used for giving privileges on behalf of user _SYS_REPO - GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT, but this procedure doesn’t have input parameter for grantable to others option:
Call _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT('select','XXX','MY_USER');
By calling this procedure, it is possible to give SELECT privilege to user MY_USER to schema XXX, but not with GRANTABLE TO OTHERS OPTION.
The conclusion is that because schema is created by file and the owner is _SYS_REPO, it is not possible to give object privilege on schema with GRANTABLE TO OTHERS OPTION.
Is there any other way to give object privilege on schema with GRANTABLE TO OTHERS OPTION, or to enable other users to see the views which are created by users other than _SYS_REPO?
Thank you in advance for reply!