New Users get "Service exception: [258] insufficient privilege"

[gregorwolf]

[13975]{311806}[14/-1] 2024-08-30 15:32:59.840850 a Authorization    SQLFacade.cpp(02474) : Exit Authorization::AuthorizationState Authorization::SQL::SQLFacade::hasSystemPrivilege(ptime::Transaction&, Authorization::User&, const Authorization::PrivilegeId&, bool)(392usec)
[13975]{311806}[14/-1] 2024-08-30 15:32:59.840861 i Authorization    SQLFacade.cpp(02735) : User USERNAME is missing valid privilege CREATE TEMPORARY TABLE for SCHEMA USERNAME
[13975]{311806}[14/-1] 2024-08-30 15:32:59.840869 A Authorization    SQLFacade.cpp(01315) : Return does NOT have all OnlySchemaPrivileges
[13975]{311806}[14/-1] 2024-08-30 15:32:59.840870 A Authorization    SQLFacade.cpp(01315) : Exit Authorization::AuthorizationState Authorization::SQL::SQLFacade::hasAllOnlySchemaPrivileges(ptime::Transaction&, Authorization::User&, Authorization::SQL::SQLSchemaCheckEntryHandle, Authorization::ObjectId&, Authorization::SQL::SQLCheckList&, bool)(621usec)
[13975]{311806}[14/-1] 2024-08-30 15:32:59.840876 a Authorization    SQLFacade.cpp(02288) : Return not authorized
[13975]{311806}[14/-1] 2024-08-30 15:32:59.840877 a Authorization    SQLFacade.cpp(02288) : Exit virtual bool Authorization::SQL::SQLFacade::isAuthorized(ptime::Transaction&, Authorization::User&, Authorization::SQL::SQLCheckList&, bool)(659usec)
[13975]{311806}[14/-1] 2024-08-30 15:32:59.840878 i Authorization    query_check.cc(04824) : User USERNAME tried to execute 'create local temporary table #r1_7450AD52FB422F429933345FFDA65386( "0" INTEGER,"ID" INTEGER,"Location" NVARCHAR(100),"EventDate" TIMESTAMP,"StartTime" TIMESTAMP,"EndTime" TIMESTAMP,"MaxParticipants" INTEGER,"HomepageURL" NVARCHAR(256),"Type" NVARCHAR(1),"Description" NVARCHAR(100),"Visible" NVARCHAR(1),"HasPreEveningEvent" NVARCHAR(1),"HasPostEveningEvent" NVARCHAR(1),"1row" BIGINT)'
[13975]{311806}[8/-1] 2024-08-30 15:32:59.842115 d Authorization    InsufficientPrivilegeDetails.cpp(00270) : INSUFFICIENT_PRIVILEGE_ERROR_DETAILS: C9F5B98E20BBFF4DB7B1149F7D96705C written to table

Old Users get the CREATE ANY privilege assigned to their own schema:

Despite the informaiton from SAP Note 3492112 - A HANA User has CREATE ANY Privilege Over their Own Schema new users do not get this privilege:

[gregorwolf]

It seems that new users are created as Restricted Users and the documentation mentions:

They cannot create objects in the database as they are not authorized to create objects in their own database schema.

[gregorwolf]

As described in this documentation the user can be converted to a normal user by running:

ALTER USER <Username> GRANT CREATE ANY ON OWN SCHEMA;

[gregorwolf]

Find all restricted user:

SELECT USER_NAME
FROM USERS
WHERE IS_RESTRICTED = 'TRUE'
ORDER BY CREATE_TIME DESC;

[gregorwolf]

It seems in some of the HANA revisions (Currently installed 2.00.079.00.1720729199) changed the behaviour for Dynamic User Creation which is enabled as documented in SAML Identity Provider Details.

[gregorwolf]

Even after the conversion to a normal user the registration to an event fails with the error message "Service exception: [258] insufficient privilege". In the trace the following error is visible:

[26620]{316328}[16/350048607] 2024-09-02 06:38:56.434567 i TraceContext     TraceContext.cpp(01367) : UserName=USERNAME, ApplicationUserName=USERNAME, ApplicationName=com.sap.sapmentors.sitreg.odataparticipant, ApplicationSource=/com/sap/sapmentors/sitreg/odataparticipant/service.xsodata/$batch, ClientIp=31.19.185.198, StatementHash=a31a0deb98019baf8042cc5be7eacab7, StatementExecutionID=844437815095574
[26620]{316328}[16/350048607] 2024-09-02 06:38:56.434567 i Authorization    SQLFacade.cpp(02735) : User USERNAME is missing valid privilege SELECT for TABLE SYS.DUMMY
[26620]{316328}[16/350048607] 2024-09-02 06:38:56.434591 i Authorization    query_check.cc(04824) : User USERNAME tried to execute 'SELECT "com.sap.sapmentors.sitreg.data::participantId".NEXTVAL

[gregorwolf]

The issue User USERNAME is missing valid privilege SELECT for TABLE SYS.DUMMY was fixed with f3d4f0e0f9d6e62be96dfeb3e971ac9a5bb8e8bb

[gregorwolf]

Created SAP Case 869681/2024 with priority high on 31.08.2024 at 08:53.

[gregorwolf]

In the Trace File xsengine_vsaigc0401.od.sap.biz.30007.executed_statements.000.trc I can see:

327902;15;350120362;1408328366413758;eb0b80006c1b5227481ecb3899d65620;SYSTEM;;1725461390646424;10481;;0;;;;;0;;;0;6:SYSTEM;;0;;;;0;0;0;;;0;
#CREATE RESTRICTED USER USERNAME WITH IDENTITY 'USERNAME' BY SAML PROVIDER HTTPS___HANA_ONDEMAND_COM_A5A504E08

327902;15;350120362;1408328366413759;6e4a45f2d3c5fa224b3e4219d8fddc3f;SYSTEM;;1725461390786340;6712;;0;;;;;0;;;0;6:SYSTEM;;0;;;;0;0;0;;;0;
#GRANT "HCP_PUBLIC" TO USERNAME

So now is the question if the RESTRICTED can be removed by configuration or needs a change from SAP side.

[gregorwolf]

According to the response via the SAP Case there was a change in 2022 and the Column USER_CREATION_USER_TYPE was added to the SAML Provider configuration which can be read using the SAML_PROVIDERS System View. By running:

ALTER SAML PROVIDER HTTPS___HANA_ONDEMAND_COM_A5A504E08 ENABLE USER CREATION USER TYPE STANDARD;

as documented in ALTER SAML PROVIDER Statement (Access Control) I activated the previous behaviour and the registration is now possible.