Day12-Day13

Authorization Dependency Viewer • The authorization dependency Authorization Error Status viewer is a graphical tool that NOT AUTHORIZED ( 258) depicts the object dependency Authorization or invalid object errors structure of stored procedure and occur if the object owner does not calculation view together with SQL have all the required privileges on all authorization status of the object underlying objects on which the owner along with dependency object depends (for example, tables, path. views, and procedures) • Privilege Required: CATALOG INVALIDATED VIEW (391) Views which are not activated in ADMIN / DATA ADMIN INVALIDATED PROCEDURE HANA ( 430) Procedures contain invalidate views.

Security Mode: DEFINER / INVOKER We can define procedure with option: Definer/ Invoker mode • INVOKER MODE: Specifies that the procedure is executed with the privileges of the user who invoked the procedure. • DEFINER MODE: Specifies that the procedure is executed with the privileges of the user who defined the procedure. SAP NOTE: 2229171 - Other users cannot run a procedure with DEFINER option.

Line Connectivity in Authorization Dependency

Resolving an Invalidated Procedure Error

Copying Users Privilege Action Status in HANA DB SAP HANA Database role ( Monitoring, Only Public role get copied if it is a • We can create a user by copying Modeling, etc.) standard user and no other role the existing user in HANA DB. Catalog Roles NO Privilege Required  USER ADMIN Repository Roles YES EXECUTE on the System Privilege NO Object Privilege NO GRANT_ACTIVATED_ROLE (SYS_REPO) Analytical Privilege NO Here is the status of privileges which Application / Package Privilege NO are granted to user while copying.

USER ACTIONS DESCRIPTION Changing Users • Authentication methods supported for the user • Password for user name/password authentication • External ID for Kerberos authentication • Identity provider and external user ID for SAML authentication • User's public key certificate for X.509 certificate authentication • Session client • Granted roles and privileges USER ACTIONS Deleting Users We can delete the user in HANA DB and if we choose cascade then all objects owned by the user are deleted, and privileges Deactivating users granted to others by the user are revoked. Furthermore, all Reactivating users objects in the user's schema are deleted even if they are owned Additional by a different user. All privileges on these objects are also parameters revoked. We can specify following additional properties for a user using the ALTER USER or CREATE USER statements: • Locale • Time zone • E-mail address Example ALTER USER USER1 SET PARAMETER LOCALE = 'en_US.UTF-8', TIME ZONE = 'Europe/Berlin', EMAIL ADDRESS = '[email protected]'

SYSTEM PRIVILEGE







Repository System Privileges

Auditing Activity in SAP Hana Systems • The auditing feature of the SAP HANA database allows you to monitor and record selected actions performed in your system. • System Privilege : AUDIT ADMIN • We need to enable the audit policy in security console to create the audit policies in HANA System. • Audit logs can be stored in below area: Syslog  Logging system of the linux operating system Database Table  Internal database table ( AUDIT_LOG), only SELECT privilege apply on the same. System Privilege : AUDIT OPERATOR CSV Text File  This is not secure as this files are written in the location of traces files and any user with DATA ADMIN, CATALOG READ, TRACE ADMIN OR INFILE ADMIN.

AUDIT POLICIES • Only those actions are recorded which are taken inside the database engine therefore system recovery & system restart will not be audited • Action Status • Audit configuration for System DB – nameserver.ini while for tenant DB – global.ini • With privilege : AUDIT OPERATOR, we can truncate the logs as well delete the policies.

Actions for SAP HANA AUDITING User’s Creating or Authentication of authorization deleting database users changes objects System Audit Accessing and configuration configuration altering sensitive changes changes information Logon monitoring SYSTEM User Technical users’ events actions Activities of exceptional users

HIGHLIGHTS (_SYS_AUDIT)

Auditing in SAP • Role Required: HANA COCKPIT sap.hana.security.cockpit.roles::MaintainAuditPolicy

ENCRYPTION • We can perform the encryption in two ways in HANA database:  Reinstall the system ( Which required downtime)  Impose the encryption immediately • System Privilege : RESOURCE ADMIN • If we enable encryption in a running system, due to the shadow memory nature of SAP HANA persistence, outdated version of pages are still unencrypted on disk. • We can disable the encryption as well on the running system. • We can create the encryption key for encrypting the data on HANA database and can view all in M_PERSISTENCE_ENCRYPTION_KEYS

Availability & Scalability SAP HANA is fully designed for high High availability is achieved by Fault recovery is the process of Disaster recovery is the process of availability. It supports recovery eliminating single points of failure (fault recovering and resuming operations after recovering operations after an outage measures ranging from faults and tolerance), and providing the ability to due to a prolonged data center or site software errors, to disasters that rapidly resume operations after a system an outage due to a fault. outage with minimal business loss (fault failure. decommission an entire data center. High availability is the name given to a set of resilience). techniques, engineering practices and design principles that support the goal of business continuity. We can scale SAP HANA either by Scale up - This means increasing the size Scale out - This means combining increasing RAM for a single server, or of one physical machine by increasing multiple independent computers into adding hosts to the system in order to deal with larger workloads. This allows the amount of RAM available for one system. The main reason for you to go beyond the limits of a single processing. distributing a system across multiple hosts (that is, scaling out) is to overcome physical server. the hardware limitations of a single physical server.

Hana Authorization Trace Privilege – TRACE ADMIN • Go to HANA system Administration, Trace Configuration • Click on User Specific Trace • New Configuration • CONTEXT NAME – Description for this user defined trace • Select Indexserver, select ‘Show All Authorization’ select authorization • It can be set to ‘ INFO’

Display Information about an \"Insufficient Privilege\" Error • Call SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS('<GUID>', ?)

Reference • KBA #2220157 – Database error 258 at EXE insufficient SAP NOTES • KBA #1735586 – Unable to grant privileges for SYS_REPO.-object s via SAP HANA Studio authorization management. • KBA #1966219 – HANA technical database user _SYS_REPO cann ot be activated. • KBA #1897236 – HANA: Error&quot ; insufficient privilege: Not authorized &quot; in SM21 • KBA #2092748 – Failure to activate HANA roles in Design Time. • KBA #2126689 – Insufficient privilege. Not authorized • KBA #2250445 – SAP DBTech JDBC 485 – Invalid definition of structured privilege: Invalid filter condition

System Views for Verifying Users' Authorization Privilege Required – CATALOG READ





How to Find User Historical Query Statements in HANA Database Select statement_string, last_execution_timestamp from SYS.M_SQL_PLAN_CACHE where user_name = 'USERNAME' order by last_execution_timestamp desc;

SAP HANA Repository Workspaces • The HANA Repository is storage for development objects and is built into SAP HANA. • HANA Repository supports Version control, Transport and sharing of objects between multiple developers. • A workspace is a local directory that you map to all part of package hierarchy in the SAP HANA repository. • SAP HANA copies the content of the package hierarchy to your workspace.

• A delivery unit ( DU) is a group of transportable packages that contain objects used for content delivery. • Privilege Required: sap.hana.xs.lm.roles: Administrator Delivery Unit • The delivery unit (DU) is the vehicle that SAP HANA application lifecycle management uses to ship software components from SAP to a customer. • The DU also a container which is use to transport the application content in our system landscape.

Maintain Repository Packages • All content delivered as part of the application; we develop for SAP HANA is stored in packages in the SAP HANA repository. • Repository package hierarchy can include sub packages For example establishing the part child relationship. • Child package can belong to different DU as compared to parent child, but SAP suggest to have one DU for every project. • We need to sure that all things delivered by SAP should be SAP package as SAP can update or overwrite anything in that package. • Package can be structural or nonstructural, some packages contain content, other packages contain other sub packages: For example: sap.test.hana.db::myobject • To check the change history of object : SYS_REPO.REPOSITORY_OBJECTS

Native Repository Packages Native Repository Package  is created in the current SAP HANA system and expected to be edited in the current system

Imported Repository Packages An imported repository package is created in remote SAP HANA system and imported into the current system. It is not recommended to work on imported packages. Imported packages should be modified in exceptional cases.

Repository • SAP HANA Application Services provide or allow the Package Types following package types: 1. Structural – Package only contains sub-packages: it cannot contain repository objects. 2. Non Structural – Package contains both repository objects and sub packages. Following packages are delivered by default in repository: • SAP  Transportable package reserved for content delivered by SAP. Partners and customers must not use the sap package as it can overwrite the customer/partner packages in it. • System-local  Non-transportable, structural packages (and sub packages), content in this package is not transportable. • System-local.generated  Non transportable, structural packages for generated content. • System-local.private  Non transportable, structural package reserved for objects that belong to individual users, for example system-local.private.<user_name>

Delete a • In SAP HANA development, repository Repository packages are used to manage various Package elements of your application development project. Sometimes we need to delete the packages which contain the repository objects from other developers. • System Privilege required: REPO.WORK_IN_FOREIGN_WORKSPACE • While deleting the package, we need to confirm for the active & inactive objects as well. Select*from\"_SYS_REPO\".\"INACTIVE_OBJECT“ Select * from \"_SYS_REPO\".\"ACTIVE_OBJECT\"

STORED SQL STATEMENT DESCRIPTION PROCEDURE CREATE PROCEDURE <proc_name> SQL statement for creating procedure • A stored procedure is a prepared SQL code that we REPLACE PROCEDURE <proc_name> “REPLACE” statement worked as ALTER for existing procedure OR if can save, so that code can procedure not exist then it worked as CREATE be reused again & again. PARAMETER – IN / OUT / INOUT IN – INPUT, OUT – OUTPUT, INOUT - BOTH Datatype in SQL INTEGER / VARCHAR / BIGINT / REAL / DECIMAL LANGUAGE <lang> SQLSCRIPT / R / GRAPH SQL SECURITY < MODE> <mode> : : = DEFINER / INVOKER READS SQL DATA Specifies that the procedure is read only mode DEFAULT SCHEMA <default_schema> <default_schema_name> ::= <identifier> <proc_Block> BEGIN ……………………………………………….. END;

SPECIFICATION CONTENT CATALOG Stored Transportable YES NO Procedures in Version YES NO Content vs Management Catalog STORAGE _SYS_REPO _SYS_BIC OWNERSHIP _SYS_REPO DATABASE USER Object Type DESIGN TIME RUN TIME Debugged YES NO Dependency Technical User Database User

CONTENT PROCEDURE REVOKE/GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT(<privilege>, procedure_name>,< <<username>) • While creating the repository procedure and if we choose storage schema - _SYS_REPO and any user have EXECUTE WITH GRANT OPTION on _SYS_REPO then he/she can assign that procedure to any user & grantor will be his/her name. • IF we select the storage schema – ANY DATABASE USER SCHEMA and he/she has the EXECUTE WITH GRANT OPTON ON _SYS_REPO then he/she can assign the procedure to any database user but GRANTOR will be _SYS_REPO.

CATALOG PROCEDURE GRANT / REVOKE EXECUTE ON <PROCEDURE_NAME> TO <USER_NAME>

1994104- Enable write access to stored procedure in HANA repository • By default, repository bases stored procedures can only be created with read-only access mode. • Note, repository-based stored procedures are those created in packages under HANA's Content folder. Therefore to make them write mode, we need to change the parameter: sqlscript_mode – Unsecure in inidexserver.ini

DML ( CREATE/ALTER/INSERT/UPDATE/DELETE) Reference Link: https://answers.sap.com/questions/11616216/create-users-with-procedure.html

Granting and Revoking Privileges & Roles Procedure work on repository objects & objects owned by _SYS_REPO



Modeling • Attributes views: Attribute is a non measurable and Views descriptive data such as Vendor ID, Vendor Name, City etc. - It allows us to join the relationship between multiple tables • Analytical Views: In SAP Hana Analytical view, dimension table are joined with the fact table that contain transactional data. Dimensional table contains descriptive data while fact table contains both descriptive and measurable data (Amount, tax etc) • Calculation Views: One of the exceptional feature of HANA is the ability to do calculation views in the database, which offer level of programing flexibility that goes beyond the aggregations found in the analytical views and bringing data from multiple analytic and attribute views.

Analytical • Analytic privileges grant different users access to Privileges different portions of data in the same view based on their business role. • Within the definition of an analytic privilege, the conditions that control which data users see is either contained in an XML document or defined using SQL. • Analytic privileges are used in the SAP HANA database to provide such fine-grained control at row level of which data individual users can see within the same view. • Sales data for all regions are contained within one analytic view. However, regional sales managers should only see the data for their region. In this case, an analytic privilege could be modeled so that they can all query the view, but only the data that each user is authorized to see is returned.

Design Time (XML- Versus SQL-Based Analytic Privileges)  file suffix .hdbanaly ticprivilege

Structure of SQL-Based • An analytic privilege consists of a set of restrictions Analytic Privileges against which user access to a particular attribute Privilege: CREATE view, analytic view, calculation view, or SQL view is STRUCTURED PRIVILEGE verified. In an SQL-based analytic privilege, these restrictions are specified as filter conditions that are STRUCTURE PRIVILEGE fully SQL based. ADMIN (DROP) • CREATE STRUCTURED PRIVILEGE <privilege_name> FOR <action> ON <view_name> <filter_condition> • The FOR clause is used restrict the type of access (only the SELECT action is supported) • The <filter condition> parameter is used to restrict the data visible to individual users. The following methods of specifying filter conditions are possible: Fixed filter (WHERE) clause Dynamically generated filter (CONDITION PROVIDER) clause

Securing Views Using SQL-Based Analytic Privileges SAMPLE VIEW ANALYTICAL PRIVILEGE GIVING ACCESS TO VIEW GRANTING CATALOG ANALYTICAL PRIVILEGE TO USER / ROLE

TROUBLESHOOTING • Using the EFFECTIVE_STRUCTURED_PRIVILEGES system view SELECT*from\"PUBLIC\".\"EFFECTIVE_STRUCTURED_PRIVILEG ES\" where ROOT_SCHEMA_NAME = '<schema>' AND ROOT_OBJECT_NAME = '<OBJECT>' AND USER_NAME = '<USER>’ • STRUCTURE PRIVILEGE CHECK – This is for catalog SQL analytical check • REGISTERVIEWFORAPCHECK – This is for catalog XML analytical check  Value = 0 ( NO XML Analytical check) Both check cannot be done on same view simultaneously to the user / role.

Catalog Roles • It is possible to create roles as pure run time and Repository objects that follow classic SQL principles or Roles as design time objects in the repository of Compared the SAP HANA database. • In general, repository roles are recommended as they offer more flexibility over catalog roles. • Repository roles are transported between systems which is important for application development as it means that developer can model roles as part of their application security concept. • Roles created in design time objects are associated with technical user: _SYS_REPO while catalog associate with database user.

FEATURES CATALOG ROLES REPOSITORY ROLES Transportability Version Management Roles cannot be transported between Roles can be transported between Relationship to creating systems. They are creating runtime with systems. database user privilege : ROLE ADMIN Grant & Revoke process No version management possible The repository provides the basis for versioning. As repository objects, roles are stored in specific repository tables inside the database.  Roles are owned by the database user who The technical user _SYS_REPO is the creates them. To grant privileges to a role, a owner of roles, not the database user user requires all the privileges being granted who creates them. Therefore, roles are to the role. If any of these privileges are not directly associated with the revoked from the granting user, they are creating user. To create a role, a automatically revoked from the role. If the database user needs only the privileges creating user is dropped, any roles created in required to work in the repository. the user's own schema are also dropped Roles created in runtime are granted directly Roles are granted and revoked using by the database user using the SQL GRANT built-in procedures. Any administrator and REVOKE statements. Roles can only be with the EXECUTE privilege on these revoked by the grantor. If the granting user is can grant and revoke roles. Role dropped (not necessarily the role creator), all creation is decoupled from the grant roles that he or she granted are revoked. and revoke process.

PRE –DEIVERED CATALOG ROLES GRANT <ROLE_NAME> WITH /WITHOUT GRANT OPTION TO <USERNAME> REVOE <ROLE_NAME> from <USERNAME> CATALOG ROLES DESCRIPTION PUBLIC • Minimum privilege for standard user to work with database • Contain privileges for filtered read only access to the system views • Only objects for which users have right will be visible MONITORING • Contain privileges for full read only access to all metadata • Current system status and monitoring views • Data of the statistics server CONTENT_ADMIN • Contain the same privilege as Modeling role but person assigned with this roles can assign to other user as well • Contain repository privileges for working with imported objects MODELING • Contains all privileges required for using the information modeler in the SAP HANA SAP_INTERNAL_HANA_SUPPORT Studio. • Contain the database authorization for a modeler to create all kinds of view and analytical privilege • Contain analytical privilege : _SYS_BI_CP_ALL • Contain read only privileges to all metadata for example – system status, monitoring views, data of the statistics server • It cannot be assigned to SYSTEM USER and only can be assigned to single user at a time • It cannot be granted to any another role

VERSION CONTROL CONCEPT • Version control enables multiple people to simultaneously work on a single project. Each person edits his or her own copy of the files and chooses when to share those changes with the rest of the team. Thus, temporary or partial edits by one person do not interfere with another person's work. Version control also enables one person you to use multiple computers to work on a project, so it is valuable even if you are working by yourself. • Version control integrates work done simultaneously by different team members. In most cases, edits to different files or even the same file can be combined without losing any work. In rare cases, when two people make conflicting edits to the same line of a file, then the version control system requests human assistance in deciding what to do. • Version control gives access to historical versions of your project. This is insurance against computer crashes or data lossage. If you make a mistake, you can roll back to a previous version. You can reproduce and understand a bug report on a past version of your software. You can also undo specific edits without losing all the work that was done in the meanwhile. For any part of a file, you can determine when, why, and by whom it was ever edited.