HANA Schema & package authorization issue?

[Former Member]

Hi All,

For example i am having 2 users say user1 & user2 in SAP HANA DB.

User 1 has created a schema called schema1

User 1 has created a package called package1

Now user2 should have the privilege to the user1 schema & package.

Can anyone of you please tell us how can we assign the authorizations in such a way that user2 can view the schema's & packages created by the user1.

Which HANA roles do i need to add for this scenario.

[Former Member · Accepted solution]

Good morning, Sekhar,

Consider the recommendations in Richard Bremer's excellent How To... Define Standard Roles for Administrators and Developers in SAP HANA Database.

First schema1 should be created via an hdbschema file so as to simplify security. Once activated the schema belongs to _SYS_REPO and the creation, granting and migration of access roles is simplified.

The access for your two users should be divorced as much as possible from the existence of these two users. Richard's design for design time roles provides the template for that.

You will need a number of schema roles depending upon how granular you wish to get with authorizations. I have four levels that build (extend) upon the lower level access role:

• <schema>_select - SELECT
• <schema>_execute - EXECUTE (extends select)
• <schema>_power - everything but TRIGGER and DEBUG (extends execute)
• <schema>_admin - all schema powers (extends power)

This allows you to grant only at the access level required.

For packages you can utilize a similar method:

• <package>_read - REPO
• <package>_native - REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS and REPO.MAINTAIN_NATIVE_PACKAGES (extends read)
• <package>_imported - REPO.EDIT_IMPORTED_OBJECTS, REPO.ACTIVATE_IMPORTED_OBJECTS and REPO.MAINTAIN_IMPORTED_PACKAGES (extends read)

Ideally User 3 (the Security admin) creates these roles and grants them to User 1 and User 2. And maybe User 4 (the DBA admin) created the schema depending upon your separation of duties model.

Good luck,

Robert Hanno

[Former Member · Accepted solution]

Thanks for the information.

Let's assume that User2 should have full authorization to User1 schema.

In that case can you please give me the " exact hana command " to provide the access for user2.

I am not able to understand which specific command do i need to enter .

[rindia · Accepted solution]

Hi Sekhar,

You can issue the GRANT statement on the schema as mentioned in this depending upon what you want to grant.

GRANT syntax

Regards

Raj

[former_member212236]

Hi Sekhar,

The packages mentioned by Robert would help to allow access to other users.

REPO.READ - Read access to the selected package and design-time objects (both native and imported)

REPO.EDIT_NATIVE_OBJECTS - Authorization to modify design-time objects in packages originating in the system the user is working in.

On top of it in case if you would like to, restrict the user to be able to create or delete only sub packages under this package1 then remove the "rootpackage" in package privilege and include the package name on which the user is working.

Failing to maintain this will give the user a privilege on Root package in turn will allow the user1 to create and delete any package in the Content folder.

Regards,

Vinoth V

[Former Member]

All,

My issue is resolved. thanks for both of you.