Day12-Day13

Day12

HANA SECURITY

SAP HANA vs • SAP HANA is an in-memory database technology S/4 HANA vs which acts as the core technology for a lot of Native HANA other SAP or non-SAP applications. • SAP S/4 HANA is a new generation ERP solution which runs on SAP HANA database architecture. • SAP Native HANA is a system where you do the modelling by bringing data from SAP/Non-SAP sources to HANA DB using SLT/BODS(ETL tool) or other development on the top HANA DB itself and perform reporting on top of HANA DB. For Example : HANA Studio is a native HANA application.

WHAT IS HANA • SAP HANA is termed as High-performance analytic appliance. • SAP HANA is an application that uses in memory database technology that allows processing of massive amount of real time data. • The in-memory computing engine allows HANA to process data stored in RAM as opposed to reading it from disk. • First released in 2010 • HANA cloud platform in 2012 • S/4 system in 2015

HANA IS MUCH • HANA is just not developing next generation database MORE THAN A but also radically simplifying the existing infrastructure, DATABASE combining transaction processing and analytics. • Integration of third party tools and gathering data from multiple sources. • Providing access to any device in a cost effective way. • HANA address the challenges as a database, platform and a technology. • HANA is in- memory columnar approach dramatically increases speed because processor do not to spin their wheels looking for actual row. • HANA is a powerful platform that easily integrate with any technology. • Other databases also perform in- memory computing but still they are databases only, can’t like HANA as platform. • Other database run as row calculation due to complex structure and migration in HANA is 100% successful.

Row and Column • Relational databases typically uses row Storage in SAP based data storage but SAP HANA used both HANA row based and column based data storage. • The column oriented database system perform better than traditional row – oriented database systems. Property Row Store Column Store Reason Memory Usage Higher Lower Compression Transactions Faster Slower Update in multiple column Analytics Slower Faster Inherent indexing

SAP HANA VERSION • HANA VERSION: 1.00.122.04.1478575636 • 1.00 = HANA 1.0 Version • 122 = SPS12 Support Stack Revision 2 • 04 = Maintenance Revision 04 • 1478575636 = Build HANA 2.0 = Multitenant database HANA 1.0 = Single container database

HANA is OLTAP • HANA combines OLAP & OLTP operations into single systems that is read & write in single systems. • OLAP – Online Analytical Processing • OLTP – Online Transactional Processing • HANA remove the need to maintain separate systems for OLTP and OLAP operations as it required in previous systems.

TECHNOLOGY • TREX Search Engine ( in-memory column IN HANA DB oriented search engine) • P*TIME ( in-memory online transaction processing platform) • MaxDB ( in-memory live cache engine)

SAP HANA CORE ARCHITECTURE

SYSTEM ARCHITECTURE • SAP HANA database management component is known as index server. Which contain actual data stores and the engine for processing data. • Index server processes incoming SQL or MDX statements in the context of authentication sessions and transactions. • It has its own scripting language called SQL script. • SAP Hana support framework for the optimized functions library which integrated with index server. PAL ( Predictive Analytical Libraries ) • Persistence layer responsible for durability and atomicity for transaction. • Preprocessor server for analyzing the text data and text search • Name Server contain the landscape information of HANA.

SAP HANA ENGINE • Join Engine: The engine is for processing the joins ( all type of joins / attribute views) • OLAP Engine: The engine is used to process analytical view. • Calculation Engine: The engine used to process complex calculation that cannot be processed by the join and OLAP.

SECURITY FUNCTION OVERVIEW • Authentication  Able to login to the system. • Authorization  Able to see and what they need to fulfill their tasks. • Audit Logging  Record of critical user actions in the system.

SAP HANA STUDIO It enables technical users to manage the SAP HANA database, to create and manage user authorizations, and to create new or modify existing models of data in the SAP HANA database. It is a client tool, which can be used to access local or remote SAP HANA databases.

Administrator 1. Systems Console 2. System Monitor Perspective 3. Administrator 4. SQL Console 5. Backup 6. Security

Adding HANA DB • Host name  Server name on which HANA DB has mounted. • Instance Number  Instance on which HANA DB mounted. • Mode  Single Container is single database in HANA DB Multiple Container  Tenant database is a isolated database. System database is a central administrator database.

SAP HANA TENANT DATABASES • A tenant database system contains one system database and can contain multiple tenant databases. • A system always has exactly one system database, used for central administration and any number of tenant databases ( included zero). • SAP HANA system is identified by a single system id ( SID). Databases are identified by a SID and a database name. • All databases share the same installation. • All tenant databases are isolated databases but mounted on common instance.

Day13

DATABASE Different Type of Users in Hana DB: USERS 1. Standard Users 2. Restricted Users 3. Technical Database Users Both Standard & Restricted Users are corresponding to real people while Technical database user are pre- delivered by SAP which can not be login and designed for specific tasks. Exceptional Pre-Delivered Users: 1. SYSTEM USER 2. SCHEMA USER These users can be login through studio and have wider access.

STANDARD USER • Standard users correspond to users created with the “CREATE USER” statement. • They have access to read data in system view • They are assigned with default role: “PUBLIC” which is responsible of view access in Hana DB. • When we create any standard users, it is created with its own schema in the HANA DB with same name as user id.

Restricted Users • Users which are created with statement “CREATE RESTRICTED USER”. • Users has no “PUBLIC” role assigned. • When restricted user deployed into the system, there is no schema created for the same. • User cannot login through the Hana Studio as ODBC/JDBC connection are disable for the user. • User is strictly used to perform some specific tasks in Hana DB and can login through Http/s connection.

Convert • GRANTING the Public role ( ALTER USER <user_name> Restricted User GRANT / REVOKE ROLE PUBLIC to Standard User • GRANTING the Schema access ( ALTER USER <user_name> GRANT / REVOKE CREATE ANY ON OWN SCHEMA • Enabling JDBC/ODBC access ( ALTER USER <user_name> ENABLE /DISABLE CLIENT CONNECT System view: IS_CLIENT_CONNECT_ENABLED = TRUE • Restricted user connecting via JDBC /ODBC connection required below roles: RESTRICTED_USER_ODBC_ACCESS RESTRICTED_USER_JDBC_ACCESS

Technical Database • Users are pre-delivered by SAP and users they are either owner of standard processed or created for application specific purposes. • They are not corresponding to any real people. • We cannot use this users as login purpose as they are pre-delivered users. • They are available as Standard only but cannot be activated. • Example _SYS_REPO, SYS etc.

ACTION SQL COMMAND Standard User CREATE USER <username> PASSWORD <password> Restricted User CREATE RESTRICTED USER <username> PASSWORD <password> Change Password ALTER USER <username> PASSWORD <password> SQL No Password ALTER USER <username> Password <password> NO Commands Change FORCE_FIRST_PASSWORD_CHANGE Deactivate User ALTER USER <username> DEACTIVATE USER NOW Activate User ALTER USER <username> ACTIVATE USER NOW SQL QUERIES 1. SELECT * from SYS.USERS DELETE USER 2. SELECT * from SYS.INVALID_CONNECT_ATTEMPTS DROP USER <username> CASCADE { user is dropped together with all of the object belong to user }

Standard Users Vs Technical Database Users • When standard user dropped from the Hana DB then all privileges / roles created by this user will dropped automatically from the system as well as if this user has granted any privilege to other users is also got dropped that is we never deleted the users in Hana DB due to their dependency. • Technical users are pre-defined users and they never dropped from the database so whatever linked with them it always remain in database.

SCHEMA • A schema is like a container which contains all the different elements or objects of a relational database. • The elements of the system reside in the catalog node of SAP Hana Information modeler. • Within the catalog node, the relational SAP HANA database is divided into sub-databases known as Schema. • Schema name is same as the name of the standard user id created in HANA database. • System privilege required to create schema is “CREATE SCHEMA” • System defined schemas such as _SYS_BIC, _SYS_REPO, _SYS_BI, _SYS_STATISTICS, _SYS_XS etc.

ACTION SQL COMMAND Schema Creation CREATE SCHEMA <schema> SCHEMA – SQL Owner Assignment to CREATE SCHEMA <schema> OWNED BY Commands Schema <username> Delete Schema DROP SCHEMA <schema> GRANT ACCESS ON GRANT SELECT<privilege> ON SCHEMA SCHEMA <schema> TO USER <username> GRANT ACCESS ON GRANT SELECT ON SCHEMA <schema> TO SCHEMA WITH GRANTOR USER <username> WITH GRANT OPTION List of Schema’s in HANA SELECT * FROM \"SYS\".\"SCHEMAS\" DB

SAP HANA Multitenant Database Architecture • SAP HANA Multitenant Database structure is basically a concept that allows multiple but isolated databases in one SAP HANA. • SAP HANA multiple container system have one system database for central system administration and additional multitenant database containers called tenant databases. • System database provides centralized administration activities and can be used to create, drop, start, stop tenant databases and perform backup/recovery, system replication activities for all tenant databases at once. • As there are different server in HANA DB, every isolate database used all combination of server except name server which is used by only system DB, which contain information about tenant DB’s. • In Single container, name server contain information about schema’s, tables but in multiple container it only contain the information of Tenant DB’s and each tenant has catalog folder where this information stored. • Tenant databases are self-contained and has its own database users, db catalog, repository, backups and logs.

Cross-Database • Every tenant database is self contained with its own isolated set Authorization in of database users and isolated database catalog but to support Tenant Databases cross application reporting, cross database SELECT queries are possible. • A user in one database can run a query that references objects in another database, that user is known as remote identity. This is the user who execute the query in the remote database and therefore user whose authorization is checked. • Remote identities can only be used if in system database, they enable cross database access of the system and which tenant database can communicate with whom. • REMOTE USERS ( SYS ) Select * from SYS.REMOTE_USERS • Only SELECT privileges are possible in cross database authorization while other privileges get ignored. • Privilege required : “USER ADMIN”

Cross-Database • There are two tenant databases DB1 & DB2, where DB2 Authorization in user want to build a cross application reporting in which Tenant Databases he/she want to read the data of table1 in schema1 in DB1. 1. Administrator of DB1 create user in DB1 with remote identity in DB2. CREATE USER<user1> WITH REMOTE IDENTITY <user2> AT DATABASE <DB2> 2. Administrator of DB1 grant user1, SELECT privilege on Schema1 in DB1. GRANT SELECT ON SCHEMA1 TO <user1> Now USER2 in DB2 can SELECT * from DB1.Schema1.Table1 • Remote connection is unidirectional. • A user can be the remote identity for only one user in another databases. • ALTER USER <username> ADD REMOTE IDENTITY <remote_user_name> AT DATABASE < database_name>

Enable and • Read only queries between tenant databases are Configure Cross- supported but not enable by default. We need to Database Access enable this feature in the system database. • Privilege Required: INFILE ADMIN • Enable the cross database access by executing the following statement in the system database. ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') set ('cross_database_access', 'enabled')='true' WITH RECONFIGURE; ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') set ('cross_database_access’,'targets_for_<source_db_name>')='<target_d ;b1>[,<target_db2>...]' WITH RECONFIGURE • Suppose there are two database DB1 & DB2 and if we want to access the data of DB1 from DB2 then target_for_DB2 = DB1 • Global.ini file is a configuration file which contain in administration console and click in cross_database_access

Troubleshooting

System and Statistics Views in Tenant Database Systems • Every database has its own SYS and _SYS_STATISTICS schemas that contain information about that database only.  • Additional views are accessible in the system database: the M_DATABASES (SYS) view and the views in the SYS_DATABASES schema. • M_DATABASES  It provides overview of all tenant databases in the system. Privilege : Database Admin • SYS_DATABASES  It contain aggregate information of view from SYS & _SYS_STATISTICS schema of all all tenant databases in the system. • Privilege: Database Admin & Catalog Read

Prevent Changes • System configuration (*.ini) files have a database to System layer to facilitate the configuration of system Properties in properties for individual tenant databases. Tenant Databases • Select the configuration file multidb.ini and the section readonly_parameters. • Privilege: INFILE ADMIN Layer Result System Configuration not possible in any tenant database. Database Configuration not possible in the specified tenant \\ database(s)

Server Architecture of Tenant Databases • The system database does not have the same functionality as a tenant database. • The system database is not a database with full SQL support. • The system database cannot be distributed across multiple hosts, in other words, scale-out is not possible. • If you need a full-featured SAP HANA database, you always have to create at least one tenant database. • The system database does not support Application Function Libraries (AFL) and SAP liveCache applications. • Cross-database access between the system database and a tenant database is not possible. The system database can show monitoring data from tenant databases (views in the schema SYS_DATABASES) but can never show actual content from tenant databases. • The system database cannot be copied or moved to another host.

Exporting / • We can export the SAP Hana systems from Importing List Hana Studio as an XML file and then import of SAP Hana it into other instance of SAP Hana studio or Systems use it as system archive to which other user can link. • Go to File – Export • Select SAP HANA SYSTEM – Landscape • Choose next Similarly way we can import the landscape as well.

License • SAP Hana system support two kinds of License keys: Management Temporary License Keys and permanent License Keys. • Temporary License key is valid for 90 days. • Permanent Licenses are of two types: unenforced and enforced. • Unenforced License has no impact on memory consumption • Enforced License has impact on memory consumption. • System lockdown if License is not installed. • Privilege : License Admin • In system view , right click on the system and choose properties. • SQL Command: SET SYSTEM LICENSE < License file content> • License key file entry Unenforced - SWPRODUCTNAME=SAP-HANA Enforced  SWPRODUCTNAME=SAP-HANA-ENF

SAP HANA Before a user can connect to the SAP HANA database , the LOGON system performs several checks as part of the logon process. CHECKS 1.The system authenticates the user using the configured mechanism. For example, if user name/password authentication is being enforced, the provided user name and password are verified. 2.The system verifies that the user’s account is within its validity period. 3.The system verifies that the user’s account is active or not. If all of the above checks are successful, the user is logged on to SAP HANA.

• The confirmation that the user is entitled to perform the requested operation is called authorization. Privilege type Description USER SYSTEM PRIVILEGE System privileges are SQL privileges that control general system activities and are mainly for AUTHORIZATION administrative purposes. OBJECT PRIVILEGE Object privileges are SQL privileges that are used to allow access to and modification of database objects, such as tables and views. Schema privileges are SQL object privileges that are used to allow access to and modification of schemas and the objects that they contain. ANALYTICAL PRIVILEGE Analytic privileges are used to allow read access to data in SAP HANA information models (that is analytic views, attribute views, and calculation views) depending on certain values or combinations of values. PACKAGE PRIVILEGE Package privileges are used to allow access to and the ability to work in packages in the repository of the SAP HANA database. Developers of applications using SAP HANA Extended Application Services (SAP HANA XS) APPLICATION PRIVILEGE can create application privileges to authorize user and client access to their application. PRIVILEGES ON USERS Privileges on users are SQL privileges that users can grant on their user. ATTACH DEBUGGER is the only privilege that can be granted on a user.

Configuring the Password Policy • Password for the user name / password authentication of database users are subject to certain rules. or password policy. • Privileges Required : INFILE ADMIN • The password policy is defined by parameter in the password_policy section of indexserver.ini system properties file. • Password policy configuration options:  Minimum password length  Required character types  User must change password on first logon  Name of previous password excluded  Number of allowed failed logon attempts  Minimum / Maximum password Lifetime A user administrator can exclude users from this password check with the following SQL statement: ALTER USER <user_name> DISABLE PASSWORD LIFETIME.

DATA PROVISIONING • SAP HANA supports the integration of data from many data sources to enrich your application and deliver in depth analysis, including federated queries, data replication. • SAP HANA smart data access allows us to access remote data as if the data was stored in local table in SAP HANA, without copying the data into SAP HANA. • In SAP HANA, you use linked database or create virtual tables, which point to remote tables in different data sources and then write SQL queries in SAP HANA, using this virtual tables. • Remote Source Credentials type Technical user  A valid user and password in remote database. This valid user is used by anyone using the remote source. Secondary credentials  A unique access credential on the remote source assigned to specific user. • Privilege Required: Data Admin & Create Remote Source

SAP HANA Smart Data Access ( Security) • SAP HANA smart data access makes it possible to Privilege Type Privilege connect remote data sources and to present the SYSTEM data contained in these data sources as if from PRIVILEGE CREATE REMOTE local SAP HANA tables. SQL Object SOURCE Privilege Create Virtual table, • In SAP HANA, virtual tables are created to Drop represent the tables in remote data source. Using these virtual tables, joins can be executed between tables in SAP HANA and tables in the remote data source. • Authorization to access data in the remote data source is determined by the privilege of the database user as standard. • SELECT * FROM ownership where object_name = ‘<Name of the remote source>’

Privileges for System Administration SYSTEM MONITORING SYSTEM ADMINISTRATION

Privileges for System Administration CATALOG OBJECT ADMINISTRATION SYSTEM ADMINISTRATION

Privileges for System Administration TROUBLESHOOT Backup and Recovery

Technical Database Users A number of predefined users are required for installing, upgrading, and operating the SAP HANA database. TECHNICAL USER DESCRIPTION PASSWORD/ LOGON SYSTEM • Most powerful database user which irrevocable system • Password of the SYSTEM users is set privileges, such as ability to create other database at time of installation. users, access system tables and so on. • System administrator right can reset • It has privilege to handle tenant databases. the password of SYSTEM user. • SYSTEM user does not automatically have access to • User having privilege: DATABASE objects created in the SAP HANA repository. ADMIN in SYSTEM DB, can reset the • SYSTEM user required to migrate SAP systems and in password of SYSTEM user in tenant duration of upgrade, installation & migration. database. SYS • It is the owner of database objects such as system • It is not possible to login through this tables ( USERS, REMOTE_USERS etc.) and monitoring user views. ( OWNER of Repository ( CONTENT FOLDER)) • REPOSITORY_REST(SYS) XSSQLCC_AUTO_USER • In runtime configuration of an SAP HANA XS • Password based logon not possible _<generated_ID> application, a technical user is automatically generated as it automatically disabled. for an SQL configuration (SQLCC) if no user specified. • The user created on activation of the SQLCC and is automatically granted the role specified in the configuration.

TECHNICAL USER DESCRIPTION PASSWORD/ LOGON _SYS_AFL _SYS_EPM • It is a technical user that owns all objects for Application Function • Not Applicable _SYS_REPO Libraries. _SYS_STATISTICS • It is a technical user used by SAP Performance Management ( SAP • Not applicable _SYS_TASK EPM) application. _SYS_BIC • It is a technical user used by the SAP HANA repository. • Not applicable _SYS_BI • The repository consist of packages that contain design time version of various objects such as views, procedure, analytical privilege and roles. • It is the owner of all objects in the repository. • Any new object created in the repository, has to be provided to _SYS_REPO to get validated. • It is a technical user used by the internal monitoring mechanism • Not applicable of the SAP HANA database about status, performance. • Not applicable • Not applicable • It is a user in SAP HANA smart data integration. This user own all task framework objects. • Not applicable • Whenever we activate HANA models, it is going to store in _SYS_BIC schema. • This user schema stored column view of activated objects ( attribute views / procedure/calc) • This will hold the tables for created Variables, Time Data (Fiscal, Gregorian), Schema Mapping and Content Mapping tables.

System Views for Verifying Users Authorization • Granted Privileges SELECT * FROM \"PUBLIC\".\"GRANTED_PRIVILEGES\" where GRANTEE = '<USER>’ • Granted Roles SELECT * FROM \"PUBLIC\".\"GRANTED_ROLES\" where GRANTEE = '<USER/ROLE_NAME>’ • Effective_Privileges SELECT * FROM \"PUBLIC\".\"EFFECTIVE_PRIVILEGES\" where USER_NAME = '<USER>’ • Effective_Roles SELECT * FROM \"PUBLIC\".\"EFFECTIVE_ROLES\" where USER_NAME = '<USER>' • Effective_Structured_Privileges SELECT * from \"PUBLIC\".\"EFFECTIVE_STRUCTURED_PRIVILEGES\" where ROOT_SCHEMA_NAME = '<schema>' AND ROOT_OBJECT_NAME = '<OBJECT>' AND USER_NAME = '<USER>’ • Accessible Views SELECT * from \"PUBLIC\".\"ACCESSIBLE_VIEWS\" where USER_NAME = '<USER>’ Here, Public worked as synonyms of SYS To execute the above queries, we need to privilege: CATALOG READ

USERS & AUTHORIZATION REPORTING