SAP HANA DB Security Made Easy

Let's go through each of these topics to provide a comprehensive understanding of how to manage and audit privileges, as well as troubleshoot issues in SAP HANA.

OBJECT PRIVILEGES

OVERVIEW

Object privileges are used to control access to tables, views, columns, etc. They are granted to users or roles and define the type of operations allowed (SELECT, INSERT, UPDATE, DELETE).

PACKAGE PRIVILEGES

OVERVIEW

Package privileges are a type of privilege that allows access to SQL objects within a schema's package. Unlike object privileges, which can be granted on individual objects, package privileges are granted on the entire package and apply to all SQL objects contained within it.

GRANTING PACKAGE PRIVILEGES - USING SQL

GRANT PACKAGE <privilege> ON SCHEMA.<schema_name>.<package_name> TO <user_or_role>;

GRANTING PACKAGE PRIVILEGES - WITH SAP HANA STUDIO AND SAP HANA WEB IDE

• Navigate to the "Security" perspective.
• Right-click on the schema containing the package.
• Select "Manage Schema Permissions".
• Define the package and the privileges to be granted.

GRANTING PACKAGE PRIVILEGES - WITH REPOSITORY ROLES

You can create a repository role and grant package privileges to it, then assign this role to users or other roles.

ANALYTIC PRIVILEGES

INFORMATION VIEWS - OVERVIEW

Information views (INFO Providers) are used in classic analytical processing for reporting and analysis. They can be accessed by users with the appropriate classical or SQL analytical privileges.

CLASSICAL ANALYTIC PRIVILEGE - PART 1

Classical analytical privileges allow access to INFO Providers without understanding the underlying schema.

CLASSICAL ANALYTIC PRIVILEGE - PART 2

• For read access:

  GRANT ANALYTIC TO <user> ON PROVIDER <provider_name>;
  

• For write access, the user also needs INSERT and UPDATE object privileges on the INFO Provider.

CLASSICAL ANALYTIC PRIVILEGE - PART 3

• Ensure that the INFO Provider is not private (ALTER PROVIDER <provider_name> SET SCHEMA PUBLIC;).

SQL ANALYTIC PRIVILEGE - PART 1

SQL analytical privileges are similar to classical but use SQL syntax to reference the underlying database objects.

SQL ANALYTIC PRIVILEGE - PART 2

• For read access:

  GRANT SQL ANALYTIC TO <user> ON SCHEMA.<schema_name>.<fact_table>;
  

• For write access, the user also needs INSERT and UPDATE object privileges on the fact table.

SQL ANALYTIC PRIVILEGE - PART 3

• Ensure that the SQL view or fact table is not private (GRANT SELECT ON SCHEMA.<schema_name>.<view_or_fact_table> TO <user>;).

DYNAMIC CLASSICAL ANALYTIC PRIVILEGE - PART 1

Dynamic classical analytical privileges allow users to access different views of the same underlying data model.

DYNAMIC CLASSICAL ANALYTIC PRIVILEGE - PART 2

• Use contexts and conditions to provide dynamic access based on user input or other criteria.

DYNAMIC SQL ANALYTIC PRIVILEGE - PART 1

Dynamic SQL analytical privileges work similarly to dynamic classical but with the ability to use SQL syntax for more flexibility.

DYNAMIC SQL ANALYTIC PRIVILEGE - PART 2

• Configure contexts, conditions, and InfoProvider attributes to control access dynamically.

GRANTING ANALYTIC PRIVILEGES

Use the GRANT statement as described above for both classical and SQL analytical privileges.

GRANTING ANALYTIC PRIVILEGES USING REPOSITORY ROLE

Create a repository role, grant analytical privileges to it, and assign this role to users or other roles.

AUDITING AND MONITORING

Use auditing settings in SAP HANA to monitor and log user activities, including privilege grants and revokes.

REVOCING PRIVILEGES

To revoke privileges, use the REVOKE statement:

REVOKE <privilege> FROM <user_or_role> ON SCHEMA.<schema_name>.<object>;

EFFECTIVE DATES

You can specify effective dates for when privileges should be active using the WITH GRANT OPTION clause.

SECURITY TIPS AND BEST PRACTICES

• Use roles to group privileges and assign them to users rather than granting privileges directly to users.
• Regularly review user privileges to ensure compliance with your organization's security policies.
• Use the principle of least privilege, granting only the necessary permissions required for a user's role.

AUDITING PRIVILEGES AND ACTIVITY

SAP HANA provides auditing capabilities that can be configured to track who is granted or revoked privileges and what operations are performed on database objects.

TROUBLESHOOTING

If you encounter issues with privileges, check the following:

• Ensure the user has the necessary schema permissions.
• Confirm the correct roles are assigned to the user.
• Verify that there are no conflicting grants or revokes.
• Check if the user is part of a group with overlapping privileges.

SAP HANA Cockpit and Transparent User Administration

For centralized user administration, you can use the SAP HANA Cockpit's User Administration section. It provides a transparent way to manage users, roles, and their associated privileges across different schemas and databases within a system.

By following these guidelines and understanding the different types of privileges available in SAP HANA, you can effectively manage access controls and secure your data and analytics environment.