External SAP HANA PSE File and Certificate Management

External SAP HANA PSE File and Certificate Management

Certificates can also be stored and managed within files located in the file system of the SAP HANA instance. 

By default, they’re stored in the following path:

/hana/shared/<SID>/HDB<instance number>/<host name>/sec

you’ll find several files with a .pse extension. For example:

sapsrv.pse

saplogon.pse

sapslcs.pse

sapsys.pse

sap_system_pki_instance.pse

sap_system_pki_internal.pse

sapsrv_internal.*.pse

Each file has an intended purpose,

1.authentication 

2.TLS secure communication.

There are two main ways to configure PSE files:

1. Command-line using SAPGENPSE tool

2. Web-based SAP Web Dispatcher Administration GUI

*using SAP web dispatcher tool, we can manage .pse certificates effectively.

To access the Web Dispatcher Admin GUI, the user must have the role:

sap.hana.xs.wdisp.admin::WebDispatcherAdmin

Access URL example:

http://<SAP_HANA_XS_HOST>:80(<instance #>)/sap/hana/xs/wdisp/admin/

Example:

http://w4-dh-hana19e-corp.root.internal.com:8000/sap/hana/xs/wdisp/admin

Authentication is required with SAP HANA internal user credentials.

the landing page will default to the SAP web dispatcher monitor.on the leftside,MENU -->PSE management-->just under the SSL and trust configuration.

The interface has a Manage PSE dropdown where you select the PSE file to manage.

With a PSE selected, the following actions are available:

Interface Option | Description

Recreate PSE

When selected, the active PSE file will be reset to a default state. All certificates will also be removed from the trust store.

Delete PSE

To delete a PSE from the file system, select the PSE file in the Manage PSE dropdown menu, then select this option.

Create New PSE

Used to create a new PSE. When clicked, a new window will appear, allowing you to define the PSE encryption algorithm, key length, distinguished name, and file name.

Export Own Certificate

When selected, a new window will appear containing the certificate of the selected PSE file.

Create CA Request

When selected, a new window will appear containing the certificate request text. Copy this text to a certificate authority to generate a new certificate response.

Import CA Response

When selected, a new window will appear containing a text entry block in which the certificate response text can be pasted or entered.

Import Certificate

When selected, a new window will appear containing a text entry block in which a trusted certificate’s text can be pasted or entered.

SAP HANA Security -cretificate management inside DB

Certificates in SAP HANA

SSL = Secure sockets layer (SSL) is communication protocol.

The goal of SSL protocols within SAP HANA is to secure the communication channel between a client and the SAP HANA Platform.

Client communications occurs via JDBC, ODBC or HTTP with in the SAP HANA platform.

Encryption: If we’re using SAP HANA studio to execute queries and return datasets, then enabling SSL in our system. Connection properties will ensure that the data sets 

are transmitted in an encrypted format. Then decrypted by the SAP HANA studio.

In SAP HANA DB certificates can be stored either in the

1.database itself

or

2.with in file system

************************************************************

1/4. Database certificate management:

* SAP HANA allows X.509 certifications to be stored with in DB itself.

* Certificate information can be directly imported in to the DB using SQL console and specific SQL commands.

Once imported, they are assigned to a certificate collection, also called an internal personal security environment (PSE).

In-database certificate management is accomplished using SQL commands.

There are 4 categories of commands to discuss:

1. Adding certificates to the system

2. Managing the certificate collection

3. Managing certificates in PSE

4. Defining the purpose of the certificate

---

1) Add a certificate to the In-Database Store:

→ To add a certificate to the in-database certificate store, use the

CREATE CERTIFICATE FROM SQL command.

→ To execute this statement, grantees will need the

certificate admin system privilege.

---

CREATE CERTIFICATE FROM  

-----BEGIN CERTIFICATE-----  

ERSJK  

-----END CERTIFICATE-----

COMMENT 'E-Corp Certificate CA Client Communications';

Drop a certificate that hasn't already been added to a Certificate collection

→ Use the DROP CERTIFICATE SQL Statement.

→ For example, execute the following SQL statement to drop a certificate with certificate ID of 123456:

DROP CERTIFICATE 123456;

To determine the certificate ID of a previously imported certificate,

SELECT * FROM SYS.CERTIFICATES;

→ Next, we need to assign the certificate to a Certificate collection

 2.create/delete a certificate collection or PSE.

Certificates must be associated with a PSE, so we need to create one before we can assign the purpose of the certificate.

To create a PSE, execute the CREATE PSE SQL command. To execute this SQL command, the grantee must have the TRUST ADMIN system privilege.

---

The following statements provide the general syntax and an example:

CREATE PSE <PSE_NAME>;  

CREATE PSE "BI-SSO-SAM-CERT";

→ To view a list of certificates by PSE, we can query the PSE_CERTIFICATES system view. For example, to view all the certificate collections or PSEs within an SAP HANA system, use the following SQL query:

SELECT * FROM PSE_CERTIFICATES;

→ To view a list of PSEs defined in the system, execute the following SQL statement:

SELECT * FROM PSES;

→ To delete a PSE,

DROP PSE <PSE_NAME>;  

DROP PSE "BI-SSO-SAM-CERT";

Next, we need to assign a certificate to a PSE.

3) Manage certificates within the PSE:

We can use SQL commands to add and remove certificates from a PSE.

To add a certificate to the PSE, use the ALTER PSE SQL command.

To alter the PSE, you must be the owner of the PSE. Also, the grantee must have the ALTER object privilege on the certificate collection or PSE.

The following SQL will grant access to a PSE:

GRANT ALTER ON PSE "BI-SSO-SAM-CERT" TO SECURITY_ADMIN;

To add an existing certificate to the PSE:

ALTER PSE <PSE> ADD CERTIFICATE <certificate-ID>;

Replace <PSE> with the name of your PSE

<certificate-ID> = ID no.

To remove a certificate from a PSE, use the following SQL command:

ALTER PSE <PSE> DROP CERTIFICATE <certificate-ID>;

---

4) Define the purpose of the PSE:

The final step in in-database certificate management is to define the purpose of the PSE and its certificates.

2 types

→ In both cases, grantee must have access to the PSE

Authentication

USER ADMIN – system privilege

TLS (Transport Security Layer)

SSL ADMIN – system privilege

Supported PSE purposes for in-database certificate management:

Purpose Use

SAML → If the PSE is used for SAML SSO authentication.

SAPLOGON → If the PSE is used for SAP assertion ticket authentication.

X509 → If the PSE is used for X.509 certificate-based authentication.

SSL/TLS → If the PSE is used to secure communication using JDBC, ODBC, or SAP HANA-specific clients.

DATABASE REPLICATION → If the PSE is used to secure the network data packets communicated during system replication.

JWT → If the PSE is used for JSON Web Token authentication.

---

If a grantee doesn't have access to the PSE or isn't the owner of the PSE, the REFERENCES object privilege must be assigned to the grantee.

For example, the following SQL command will grant REFERENCES to the CERT_ADMINS role:

GRANT REFERENCES ON PSE "BI-SSO-SAM-CERT" TO CERT_ADMINS;

→ Role name

To define the purpose of the PSE, execute the SET PSE SQL command:

SET PSE <PSE Name> PURPOSE <Purpose>;

SET PSE "BI-SSO-SAM-CERT" PURPOSE SAML;

→ In the first example, replace the <PSE Name> variable with the name of the PSE. Replace the <Purpose> variable with the name of the purpose listed in the above list or table.

→ To remove the purpose, use the UNSET PSE SQL command:

UNSET PSE "BI-SSO-SAM-CERT" PURPOSE SAML;

→ here BI-SSO-SAM-CERT is Certificate

---

The internal certificate store doesn’t include all possible certificates.

Let’s look at how we can also use files within the OS to store certificates.

GRC add ons List #2

* NOTA Fiscal Electronica - for

 

environment

health and safety

use add on - SLL-NFE

can customise standard GRC configuration.

changes from dev to qad to prd. can push using TMS

satillite system:

add -on list

GRC intro#1

Here is the extracted text from the image:

GRC

Access control consists of 4 main parts.

1. Access risk analysis(ARA) - If there are any risks in SOD. This will find all the risks.

2. Access request management(ARM) - Provides access to the request access.

3. Business Role Management(BRM) - Provides roles based on risk analysis.

4. Emergency access management(EAM) - Provides emergency access to the user, to fix issue. in emergency situations.

SOD Analytical reports

* Users

* user groups

* Roles

* profile

similarly, we can check reports for,

* critical action

* critical permissions

* critical roles

* critical profile

* simulation to check risks, can be check in both adding and removing roles.

Work flow of ARA:

Identify and select risks to manage

Build and maintain rules

Detect authorization risk

Remediate and mitigate

Test and report

prevent

Work flow of Access Request Management:

HR event -> Request generated -> Manager approval -> Risk analysis -> Automated provisioning.

Emp hire or retired -> 100% automated -> via mail -> one click simulation -> 100% automated.

BRM -> Business role management:

Role definition and maintenance in a single location.

If we have many landscapes like ECC, BW etc., if you are using GRC then you can maintain all role definition in one single place.

During the role creation itself GRC will check SOD risks and mitigations.

EAM - Emergency Access Management:

Problem:

Support one user need additional authorization, then business user will ask basis team to get it. once after completing the activity. Basis team again need to revoke the access and audit logs need to maintain. For audit purposes.

Solution: firefighter. will give additional authorization in the controlled manager. saves time. as firefighter already configured.

2 types of firefighter: 1. role based and 2. user based.

Elevated access can be provided in to controlled environment. logs will generate.

1. Access risk analysis(ARA).

2. Access request management(ARM).

3. Business Role Management(BRM).

4. Emergency access management(EAM).

all 4 are integrated to each other.

Enqueue wp

SAP NetWeaver AS ABAP has an independent lock mechanism that's used to synchronize database access and at the same time ensure that two transactions can change the same data in the database in parallel.

As administrator, you therefore have no power over when a lock entry is created or deleted. Nevertheless, it’s extremely important that you understand the SAP lock mechanism because you may have to delete orphaned lock entries. The lock mechanism works closely with the update mechanism.

There are different types of locks:

Write locks (E): This is also known as exclusive lock mode as the lock data can be edited by only one user. Any other requests from work processes to set another write lock or read lock are rejected. A cumulate lock can be applied on the lock data by the same lock owner again.

Read locks (S): This is also known as shared lock mode as several users can have read access to the locked data at the same time. Additional read lock requests are entertained even if they are from different users. However, a write lock is rejected.

Enhanced write locks (X): This is also known as exclusive non-cumulative lock mode. An enhanced write lock can be requested only once even if it is by the same transaction.

Difference between write locks (E) and Enhanced write lock (X) is: write locks can be set and released by the same transaction several times but X type locks can also be set once even by the same transaction.

Optimistic locks (O): These locks are set up when the users display the data in change mode. Several optimistic locks can be setup on the same data. Optimistic locks are read locks (S), and later converted to write lock (E) when the user wants to save the data. If an optimistic lock on a data is changed to write lock (E), all other optimistic locks on that data will be deleted.

Locks that are set by an application program are released by the program itself or they are released by the update program once the database has been changed.

Transaction code SM12 can be used to monitor SAP locks.

:analysis

sm13 is for update wp monitoring.

in which we can retry the updates.