3 Root Causes And Solutions
3.7 Security-Related Issues
This section looks at issues to do with security like licensing, authorization, and authentication.
3.7.1 System Locked Due to Missing, Expired, or Invalid License
New installations of SAP HANA are equipped with a temporary license that expires after 90 days. To keep the system functional after this period, you have to install a permanent license.
Improper licensing may lead to a lockdown of your SAP HANA system. In this case, the only allowed action is to install a valid license.
The system goes into lockdown in the following situations:
● Your first temporary license of 90 days has expired.
● Your permanent license has expired and you do not renew it within 28 days.
● An old backup was used for recovery and the license key in the backup has expired in the meantime.
● The installed license key is an enforced license key and current memory consumption exceeds the amount specified in the license key. Note that such licenses are only used in some scenarios.
● You have deleted all license keys installed in your database.
For more information, see Managing SAP HANA Licenses in the SAP HANA Administration Guide.
Related Information
SAP HANA Administration Guide
3.7.2 License Problem Identification and Analysis
The first signs of problems related to licensing will be visible by Alert 31 or Alert 44 being issued.
To check your current license using SAP HANA studio, right click on a system in the Systems view, choose Properties and then License.
Alternatively, you can retrieve the same information using SQL:
select * from m_license;
The M_LICENSE system view provides you with the following information:
● License data:
○ SID
○ Hardware key
○ Installation number
○ System number
○ Product limit (licensed amount of memory)
○ Validity start date
○ Expiration date
○ Last successful check date
● License status (permanent, valid, or enforced)
Note that in case of system lockdown, only SID and hardware key are displayed. Information on previously installed licenses is available.
Note
To be able to query license information, you must have the system privilege LICENSE ADMIN.
Related Information
SAP HANA SQL and System Views Reference
3.7.3 Resolution of License Issues
If your license becomes invalid, you need to install a new license.
You can install a new license either in the SAP HANA studio or using SQL.
Note
To install a license key, you need the LICENSE ADMIN system privilege.
You install a license key with the following SQL statement:
SET SYSTEM LICENSE '<license file content goes here, line breaks matter>';
Note
Line breaks are essential for interpretation of the license key text, hence they must not be removed. If you use the command line tool SAP HANA HDBSQL to install the license, make sure to enable multi-line statement support (command line option -m or \mu ON when within SAP HANA HDBSQL).
The command will fail if the license key has a different installation number or system number than the current ones in the database instance. If you have successfully installed a license but your system is still locked down, check the following:
● The current system time is within the validity period of the license.
● Your installed license key is correct, in particular, the M_LICENSE view displays only one row with a valid license for the product SAP HANA.
● The SAP Notes in the Related Links section.
For more detailed information about how to install a license key, see Install a Permanent License in the SAP HANA Administration Guide.
Related Information
SAP Note 1704499 - System Measurement for License Audit
SAP Note 1634687 - License request and installation for SAP HANA database SAP Note 1699111 - License key update in SAP HANA prior to Rev.26
SAP HANA Administration Guide
3.7.4 Troubleshooting Authorization Problems
SAP HANA implements its authorization concept based on the entities user, privilege, and role.
General Analysis
The system view EFFECTIVE_PRIVILEGES is useful for checking the privileges of a specific user. It includes information about all privileges granted to a specific user (both directly and indirectly through roles), as well as how the privileges were obtained (GRANTOR and GRANTOR_TYPE column).
Figure 17: Output of Effective Privileges
For more information about using this view and other system views related to authorization, see System Views for Verifying Users' Authorization.
For more information about the authorization concept in SAP HANA, see the SAP HANA Security Guide.
Related Information
SAP HANA Security Guide SAP HANA Administration Guide
SAP HANA SQL and System Views Reference
3.7.4.1 Troubleshoot the Error "Insufficient Privilege: Not Authorized"
If the error Insufficient privilege: Not authorized occurs during statement execution, you need to find out which privileges the user is missing and then grant them to the user.
Prerequisites
You have the system privilege TRACE ADMIN.
Procedure
1. On the Trace Configuration tab of the Administration editor, set the database trace level for the component authorization of the indexserver service to INFO.
Note
The component is not visible by default. To see it, choose Choose All Components.
2. Execute the statement that triggered the error.
3. Set the database trace level for the component authorization of indexserver service back to DEFAULT.
4. On the Diagnosis Files tab of the Administration editor, examine the indexserver trace to find out about the failed authorization check.
Usually, you will find something like: UserId(<nnnn>) is not authorized to do SQL_ACT_abc on ObjectId(m,n,oid=<oid>) followed by a structure showing which privileges are checked on which schemas and objects. In this structure, you will find the name which belongs to <oid>. In many cases, the name belonging to UserId is given below that structure. Use that information to grant the missing privilege.
If the user cannot access a view due to a missing analytic privilege, the trace will also list all relevant analytic privileges that have not been granted to the user.
Related Information
SAP HANA Administration Guide
3.7.4.2 Troubleshoot the Display of Unrestricted or
Incorrect Results for a View Secured with Analytic Privileges
If a user has unrestricted access to a view or sees results that he should not, even though he has been granted an analytic privilege, you need to determine which privileges have been granted to the user and whether or not they are correct.
Prerequisites
To troubleshoot this issue, you require the following system privileges:
● CATALOG READ
● TRACE ADMIN
Procedure
● Check which analytic privileges have been granted to the user using the system view EFFECTIVE_PRIVILEGES.
Execute the following SQL statement:
SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = '<user>' AND OBJECT_TYPE
= 'ANALYTICALPRIVILEGE';
In particular, verify that the user does not have the analytic privilege _SYS_BI_CP_ALL. This analytic privilege potentially allows a user to access all the data in all activated views, regardless of any other analytic privileges that apply. Usually, the user will have this analytic privilege through a role, for example, MODELING.
Caution
The MODELING role is very privileged and should not be granted to users, particularly in production systems. The MODELING role should only be used as a template.
● Identify wrong filters specified in the analytic privileges granted to the user.
Information about filter conditions generated from the relevant analytic privileges can be traced in the indexserver trace file. This can help you to identify wrong filters specified in the analytic privileges granted to the user.
On the Trace Configuration tab of the Administration editor, set the database trace level for the component analyticprivilegehandler of the indexserver service to DEBUG.
Related Information
SAP HANA Administration Guide
SAP HANA SQL and System Views Reference SAP HANA Security Guide
3.7.4.3 Troubleshoot the Error "Insufficient privilege: Not authorized" Although User Has Analytic Privileges
Even if a user has the correct analytic privileges for a view, he still may receive the error Insufficient privilege: Not authorized if there is an issue with privileges at another level.
Prerequisites
To troubleshoot this issue, you require the following system privileges:
● CATALOG READ
● TRACE ADMIN
Procedure
● Verify that the _SYS_REPO user has all required privileges (for example, SELECT) with GRANT OPTION on the base tables of the view.
You can do this by selecting from the EFFECTIVE_PRIVILEGES system view:
SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = '_SYS_REPO';
● Verify that the analytic privileges required for any underlying views have been granted to the user.
If the view is a top-level view (calculation view) with underlying views, the granted analytic privilege grants access only to this top-level view. Analytic privileges are required for all underlying views. Note that analytic privileges have to contain at least a view attribute with or without filter condition in order to grant access to the view.
You can verify a user's privilges by selecting from the EFFECTIVE_PRIVILEGES system view:
SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = '<user>' AND OBJECT_TYPE
= 'ANALYTICALPRIVILEGE';
● If the analytic privilege uses a database procedure to define dynamic value filters at runtime, check for errors in the execution of the underlying procedure.
To find out the actual error during procedure execution for analytical privileges, check the
indexserver_alert_<host>.trc trace file (accessible on the Diagnosis Files tab of the Administration editor).
3.7.4.4 Troubleshoot the Error "Invalidated View" During SELECT Statement Execution
A user may receive the error Invalidated view when executing a SELECT statement against a view that was activated from the repository. In addition, thee data preview for an activated view does not show any data.
Prerequisites
To troubleshoot this issue, you require the following system privileges CATALOG READ.
Procedure
● Verify that the _SYS_REPO user has all required privileges (for example, SELECT) on all base objects (for example, tables) of the view.
You can do this by selecting from the EFFECTIVE_PRIVILEGES system view:
SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = '_SYS_REPO';
3.7.5 Troubleshooting Problems with User Name/Password Authentication
Common problems with regards to authentication are related to incorrect or expired passwords.
User administrators can change users' password in the User editor of the SAP HANA studio.
Figure 18: User Editor
For more information about managing users in the User editor, see Security Administration in the SAP HANA Administration Guide.
Related Information
SAP HANA Administration Guide
3.7.5.1 Resetting the Password for the SYSTEM User
In case the password for the SYSTEM user is not available anymore, it can be reset.
As a prerequisite, <sid>adm access to the (primary) SAP HANA host is required. Follow the procedure as described in detail in the SAP HANA Administration Guide.
Related Information
SAP HANA Administration Guide
3.7.5.2 Troubleshoot the Error "User is locked"
A user receives the error User is locked after too many failed log on attempts.
Prerequisites
You have system privilege USER ADMIN.
Context
An example of this error might look like:
Error "user is locked; try again later: lock time is 1440 minutes; user is locked until 2014-05-28 21:42:24.12214212" (the time is given in UTC).
Most likely, the user logged on too many times with the wrong password. The default maximum number of failed logon attempts is 6. This is defined by the password policy parameter
maximum_invalid_connect_attempts.
For more information about this and other password policy parameters, see Password Policy Configuration Options in the SAP HANA Security Guide.
Procedure
Reset the invalid connect attempts with the following SQL statement:
ALTER USER <user> RESET CONNECT ATTEMPTS;
The user can now log on again.
Related Information
SAP HANA Security Guide