Too many privileges for _SYS_REPO

[WR01]

According to SAP Note 2101316 – “SAP HANA: Privileges of the ABAP database user” the Software Provisioning Manager (SWPM) seems to require additional privileges above and beyond the default. For example:

GRANT ALTER ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT CREATE ANY ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT DEBUG ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT DELETE ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT DROP ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT EXECUTE ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT INDEX ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT INSERT ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT REFERENCES ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT SELECT ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT TRIGGER ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

GRANT UPDATE ON SCHEMA SAP<SID> TO _SYS_REPO WITH GRANT OPTION;

I could imagine that this may be required when additional software is provisioned. For example, if you want to activate a table into your own private schema from the repository, you must manually grant _SYS_REPO CREATE ANY to your private schema (private schemas are created as run-time objects as part of user provisioning). Another example is an SDA/SDI remote source connection as these too cannot be created through the repository. This is normal behaviour.

The ABAP schema (let’s say for this example SAPDTM) and all its content also only exist as a run-time objects. In HEC, _SYS_REPO only has SELECT privileges on this schema. In on-premise we see that it has additional privileges allowing modification of ABAP schema content.

Why is this important if you cannot login as _SYS_REPO? Because when you design a repository security role, you can include any privilege that _SYS_REPO possesses in that security role. So for example, in our data modeller role for the formal namespace, we include the object privilege “catalog schema "SAPDTM": SELECT;” to enable data modellers to SELECT data from ABAP tables. This role can be activated because _SYS_REPO has the SELECT privilege granted on SAPDTM. In the HEC example, you could not for example change this role to read “catalog schema"SAPDTM": SELECT, DROP;” because _SYS_REPO does not possess this privilege. However, in the on-premise example, this statement could be activated, thereby inadvertently granting all users with this role the ability to drop objects from the ABAP schema.

Thanks for your help J

[lbreddemann]

If I understand your question correctly, you want to know, why the SAP note and the SWPM assign this seemingly excess of privileges to the activation user _SYS_REPO.

Having not made that design decision I can offer this guess: to enable all possible extension development around the NetWeaver core applications. E.g. one might want to be able to create additional application logic in stored procedures that requires creating or changing objects in the application schema - one example for that could be to develop a procedure to partition tables or to move data from hot to warm store.

Can one create code that could potentially harm the SAP<SID> schema this way? Yes, but that's likewise possible via ABAP coding.

[WR01]

Hi Lars,

thanks for the quick reply. It does somewhat answer my question. Totally accept that you have not designed the tool. But once SWMP has made whatever changes were necessary should it then not revert _SYS_REPO back to the original set of privileges?

As a security conscious customer I could imagine that user authorisations in ABAP/JAVA are set to the minimum required, one could then argue the same should be true for the DB?

[lbreddemann]

The mentioned note defines the required privileges for the ABAP user (and the required access to the ABAP schema) and SWPM implements those. Seen the other way around, one could argue, that if the privileges were any different (i.e. reduced to a minimum), then that would be wrong and SWPM implements the correct state. (I prefer this approach as it reduces the chance to run into hard to analyse problems later on and surely fits the majority of users).

What's the worst that could happen with such privileges? Somebody could create models/procedures on top of the SAP<SID> schema. But without additional privileges, this code cannot be executed against any of the actual data.

In fact, to be able to create these artefacts, the user needs to have the necessary modelling privileges.

Of course, this can get mixed up in MCOD setups where multiple SAP<SID> schemas are installed in the same DB, which would be another good reason to look into using HANA MDC (multitenant database containers).

Other than that, I don't really see a problem here. NetWeaver security on DB level always had been bound to the privileges accessing the schema and these privileges had been "all or nothing" (think of the administrative DB users who can change table/index structures, export/import data etc.). The approach for _SYS_REPO is no different here as it only enables the usage of general HANA features, without actually granting access to any specific user.
So, I don't agree with the idea that all ABAP users can suddenly do damage on the DB - to be able to do that, these users must have the appropriate roles on ABAP level (e.g. being able to run SE38/SA37 and the like) which normal users must never have.

Again, all this is my POV.