Assigning SELECT privilege to a schema via admin ID

[Former Member]

Hi All,

We are trying to assign users SELECT privileges to an existing schema. We are using an admin account to do so, but unfortunately the admin account does not have the privileges to do so. We tried this with the SYSTEM ID as well, but same result. Not enough privileges.

Could not execute 'GRANT SELECT ON SCHEMA "SAPBPC" TO _SYS_REPO WITH GRANT OPTION'

SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

The schema in question is not that of a normal user, but that of a system-user. This system-user (SAPBPC in this case) is the user-id used by the BPC application to communicate with the HANA database.

One solution would be to log in as the SAPBPC user-id, and then grant privileges. But we do not wish for individuals to have to know the password for this user and are looking for alternate approaches for certain administrator users to be able to grant SELECT privileges to any existing or newly formed SCHEMA.

Is there a way to do so without having to discover and use the password for the SAPBPC userid?

thanks,

Nitin

[Bojan-lv-85]

Hi Nitin,

there are few things which come to my mind:

1. how about granting the desired privilege to your "admin" user with the "grantable to others" option?

Nitin goel wrote:
[...] are looking for alternate approaches for certain administrator users to be able to grant Sjavascript:;ELECT privileges to any existing or newly formed SCHEMA [...]
javascript:;

2. on any newly created schema you would need to have your schema owner first granting the privileges to "certain administrators".

3. you could consider creating copies of the schema owners and revoking the "undesired" power of the copied user

BR, Bojan

[Former Member]

Hi Bojan,

For (1) & (2), essentially every time a new schema is created, we would have to add a step to provide of grantable access to that schema to the admin user IDs. So that they later in turn have the ability to grant that to other users as needed.

The issue with that is that it is a significant one time effort to get caught up on all existing schemas and requires knowing the passwords for each of the users to do so.

I wonder if an alternate approach exists?

For (3), I don't believe copying a user automatically copies all of its permissions. You would have to explicitly grant those permissions, which brings us back to the original problem.

thanks,

Nitin