Creating, dropping, granting, revoking repository roles

[martin_chambers]

Hi,

which privileges do I need if I wish to create, drop, grant and revoke all kinds of repository roles in SAP HANA.

We're on SPS9.

Any help will be appreciated.

Cheers,

Martin

[former_member220937 · Accepted solution]

Hi

You can find a list of prerequisites of granting and revoking privileges and roles here

Prerequisites for Granting and Revoking Privileges and Roles - SAP HANA Administration Guide - SAP L...

BR

Dermot

[martin_chambers · Accepted solution]

Hi Dermot,

Thanks for responding.

The link you sent states that to grant or revoke a repository role, I would require the object privileges GRANT_ACTIVATED_ROLE and REVOKE_ACTIVATED_ROLE.  I do have both these object privileges.

In my second post in this thread, I describe how I couldn't delete a repository role I had previously created. I'm wondering which privilege I'm missing. Can you help me here?

Cheers,

Martin

[former_member183326 · Accepted solution]

Hello Martin,

What error are you seeing when you try to delete the repo role in question? Are you seeing a privileges issue here also? If so you can do the following:

Its always helpful to run a trace to see what exact privilege you are missing:

1) Please run the following statement in the HANA database to set the DB  trace: alter system alter configuration ('indexserver.ini','SYSTEM') SET   ('trace','authorization')='info' with reconfigure;

2) Reproduce the issue/execute the command again

3) When the execution finishes please turn off the trace as follows in the Hana studio: alter system alter configuration ('indexserver.ini','SYSTEM') unset   ('trace','authorization') with reconfigure;

If you look into the trace file that is generated after you turn off the trace you can see what privilege you are missing to delete the repo role in question.

For further information please see:

http://scn.sap.com/docs/DOC-68108

Regards,

Michael

[martin_chambers · Accepted solution]

Hi Michael,

Thanks!

I've tried your idea. Actually, I set up the trace configuration in system administration view (one of the tabs is called trace configuration). Unfortunately, all I could find in my authorization trace, was the log showing the set* command (see below) at 12:58, followed by the unset command at 13:03. What it didn't show was the my attempt to delete the repository role at 13:01.

All I get, is this message in the web IDE.

(Security) Deleting role 'MARCHAMB.Repository_Roles::Modeling' failed: Error in deleting an existing role: insufficient privilege: Cannot drop activated roles: line 1 col 11 (at pos 10)

Can it be that the trace doesn't work for the web IDE? Do I actually have to write some SQL to delete the repo role if I want to see something in the trace?

Any ideas?

Cheers,

Martin

*alter system alter configuration ('indexserver.ini','SYSTEM')
SET ('traceprofile_Martin', 'authorization') = 'info' with reconfigure

[former_member220937 · Accepted solution]

Hi

It appears you are running into the issue explained in note

2058299 - How-To: Delete design time roles in HANA Studio

BR

Dermot

[martin_chambers · Accepted solution]

Hi Dermot,

thanks for replying to my query.

No, I'm not running into the issue described in 2058299. At least, I don't think so.

I didn't try to drop the role using SQL. I did a mouse right click on the hdb role in the web IDE and selected delete.

Still, if I correctly understand note 2058299, I can't use SQL to make my authorization trace work, as you can't use SQL to drop a repository role.

What to do 😞

[martin_chambers · Accepted solution]

Hi Dermont,

it seems that I had not read note 2058299 correctly.

I have now deleted the role in the repository view (in my package), activated the parent directory and "Hey Presto!" the repository role was gone.

Thanks and all the best for 2016

Martin

[Former Member]

FYI - You can download the HANA documentation for hana sp 9 under Earlier Releases – SAP Help Portal Page this link has all hana docs (security, admin, install/upgrade etc)

[rindia]

Hi Martin,

To Grant and Revoke repository role, you should have Object privilege "Execute" on the procedures

GRANT_ACTIVATED_ROLE, REVOKE_ACTIVATED_ROLE.

Technically speaking, only the user _SYS_REPO needs the privileges being granted in a role, not the database user who creates the role. However, users creating roles in the SAP HANA Web-based Development Workbench must at least be able to select the privileges they want to grant to the role. For this, they need either the system privilege CATALOG READ or the actual privilege to be granted.

To drop repository role, you must delete it in the repository and activate the change. The activation process deletes the runtime version of the role.

Regards

Raj

[martin_chambers]

Hi Raj,

Thanks for your insight.

Unfortunately, I will not be able to test your idea concerning CATALOG_READ, as we only have a HANA sandbox system and our system admin is on holiday until the new year.

I tried deleting a repository role in the web IDE I had previously created and got this error message -

• (Security) Deleting role 'XYZ' failed: Error in deleting an existing role: insufficient privilege: Cannot drop activated roles: line 1 col 11 (at pos 10)

I would have thought, I could delete anything I had created. It seems not.

Any idea why?

Cheers,

Martin