SAP Hana User System deaktivieren


User mit den Rollen “USER ADMIN” erstellen

SYSTEM deaktiveren
ALTER USER SYSTEM DEACTIVATE USER NOW

SYSTEM wieder aktivieren
ALTER USER SYSTEM ACTIVATE USER NOW


#################################################
sudo su –
su – hb1adm
hdbsql
\c -i 12 -n sles287.comp.ds:31215 -d HB1 -u SYSTEM -p <PASSWORT>
\c -i 12 -n sles287.comp.ds:31215 -d HB1 -u USER -p <PASSWORT>

hdbsql -n sles287.comp.ds:31215 -i 12 -d HB1 -u USER -p <PASSWORT> “SELECT * FROM “PUBLIC”.”EFFECTIVE_PRIVILEGES” where USER_NAME = ‘SYSTEM’;” | column -t | more

hdbsql -n sles287.comp.ds:31215 -i 12 -d HB1 -o test2.txt -u USER -p PASSWORD “SELECT * FROM “PUBLIC”.”EFFECTIVE_PRIVILEGES” where USER_NAME = ‘SYSTEM’;”


#Show Granted Role
SELECT * FROM “PUBLIC”.”EFFECTIVE_ROLES” where USER_NAME = ‘SYSTEM’;
SELECT * from GRANTED_ROLES where GRANTOR = ‘SYSTEM’ and GRANTEE_TYPE = ‘ROLE’;
SELECT * from GRANTED_ROLES where GRANTOR = ‘SYSTEM’ and GRANTEE_TYPE = ‘USER’;


#Show All Granted Privileges
SELECT * FROM “PUBLIC”.”EFFECTIVE_PRIVILEGES” where USER_NAME = ‘SYSTEM’;
SELECT * from GRANTED_PRIVILEGES where GRANTOR = ‘SYSTEM’ and GRANTEE_TYPE = ‘USER’;
SELECT * from GRANTED_PRIVILEGES where GRANTOR = ‘SYSTEM’ and GRANTEE_TYPE = ‘ROLE’;

#Show Granted object Privileges
SELECT DISTINCT PRIVILEGE, grantee, grantee_type, object_type,is_valid from
GRANTED_PRIVILEGES where grantee = ‘SYSTEM’ and object_type <> ‘SYSTEMPRIVILEGE’;


#2251556 – SAP HANA SYSTEM user can now be locked for too many failed logon attempts. SYSTEM User entsperren
ALTER USER SYSTEM RESET CONNECT ATTEMPTS


####################################################################
#Hana User Kopieren########################################################################
#https://blogs.sap.com/2014/10/13/copying-users-in-sap-hana/

PROCEDURE “<SCHEMA>”.”<package1>.<package2>::copy_user” ( IN source_user VARCHAR(256), new_user VARCHAR(256) )
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
AS
— SQL statement we’re going to execute when creating
v_statement VARCHAR(1024);
— variable used for validation queries
found INT := 0;
BEGIN
— get all repo roles granted to the user:
declare cursor c_cursor FOR select role_name, grantee, grantor from granted_roles where grantee=:source_user and role_name like ‘%::%’;
— prepare error handling in case of invalid arguments
DECLARE USERNOTEXIST CONDITION FOR SQL_ERROR_CODE 11001;
DECLARE USERALREADYEXIST CONDITION FOR SQL_ERROR_CODE 11002;
DECLARE WRONGROLETYPE CONDITION FOR SQL_ERROR_CODE 11003;
DECLARE PRIVSGRANTED CONDITION FOR SQL_ERROR_CODE 11004;
DECLARE EXIT HANDLER FOR USERNOTEXIST RESIGNAL;
DECLARE EXIT HANDLER FOR USERALREADYEXIST RESIGNAL;
DECLARE EXIT HANDLER FOR WRONGROLETYPE RESIGNAL;
DECLARE EXIT HANDLER FOR PRIVSGRANTED RESIGNAL;



— check input parameter source_user:
— does the user exist?
SELECT COUNT(*) INTO found FROM “USERS”
WHERE “USER_NAME” = :source_user;
IF :found = 0 THEN
SIGNAL USERNOTEXIST SET MESSAGE_TEXT =
‘Source user does not exist: ‘ || :source_user;
END IF;

— check input parameter new_user:
— does the user exist?
SELECT COUNT(*) INTO found FROM “USERS”
WHERE “USER_NAME” = :new_user;
IF :found > 0 THEN
SIGNAL USERALREADYEXIST SET MESSAGE_TEXT =
‘New user already exists: ‘ || :new_user;
END IF;

— check roles granted to source user. We can only copy repository roles (containing ::)
— the only allowed catalog role is PUBLIC
SELECT COUNT(*) INTO found FROM GRANTED_ROLES
where GRANTEE=:source_user and ROLE_NAME != ‘PUBLIC’ and ROLE_NAME NOT LIKE ‘%::%’;
IF :found > 0 THEN
SIGNAL WRONGROLETYPE SET MESSAGE_TEXT =
‘There are catalog roles (other than PUBLIC) granted to the source user ‘ || :source_user;
END IF;


— check that there are no privileges (not roles) granted directly to the user – except for
— privileges granted directly by SYS -> these would be privileges on the user’s
— own schema, or on schemas that the user has created.
— If the user has any privileges directly granted (grantor != SYS), we will not copy
SELECT COUNT(*) INTO found FROM GRANTED_PRIVILEGES
where GRANTEE=:source_user and GRANTOR != ‘SYS’;
IF :found > 0 THEN
SIGNAL PRIVSGRANTED SET MESSAGE_TEXT =
‘There are privileges granted directly to the source user ‘ || :source_user;
END IF;

— create the new user with dummy password BadPassword1
v_statement := ‘create user ‘ || :new_user || ‘ password BadPassword1’;
exec v_statement;

open c_cursor;
— and grant all the roles.
for ls_row as c_cursor DO
— assemble grant statement for role in current loop:
v_statement := ‘call grant_activated_role ( ”’|| ls_row.ROLE_NAME ||”’, ”’|| :new_user ||”’)’;
— and grant role:
exec v_statement;
END FOR;
END;
########################################################################
#How to create multiple users on SAP HANA using sql
#https://jonujoy.wordpress.com/2014/09/24/how-to-create-multiple-users-on-sap-hana-using-sql/

CREATE USER SAPSUPPORT1 PASSWORD ABC123 ;
ALTER USER SAPSUPPORT1 DISABLE PASSWORD LIFETIME; ( disabling the passwd change )
GRANT MONITORING,USER to SAPSUPPORT1 with ADMIN OPTION ; ( granting roles)
GRANT SAP_INTERNAL_HANA_SUPPORT TO SAPSUPPORT1 WITH ADMIN OPTION;
GRANT CATALOG READ TO SAPSUPPORT1 WITH ADMIN OPTION; ( granting system privilege)
CREATE USER SAPSUPPORT2 PASSWORD ABC123 ;
ALTER USER SAPSUPPORT2 DISABLE PASSWORD LIFETIME;
GRANT MONITORING,USER to SAPSUPPORT2 with ADMIN OPTION ;
GRANT CATALOG READ TO SAPSUPPORT2 WITH ADMIN OPTION;
CREATE USER SAPSUPPORT3 PASSWORD ABC123 ;
ALTER USER SAPSUPPORT3 DISABLE PASSWORD LIFETIME;
GRANT MONITORING,USER to SAPSUPPORT3 with ADMIN OPTION ;
GRANT CATALOG READ TO SAPSUPPORT3 WITH ADMIN OPTION;

hdbsql -n abc115 -i 00 -u SYSTEM -p password
\o outputfile.txt (incase you need an output to a file)
\i create_hana_users.sql (input file name )
\o (closing the output file)
\q ( quit)
########################################################################
#Vorgehnsweise

#User USER erstellen

sudo su –
su – hb2adm
hdbsql
\c -i 22 -n sles287.comp.ds:32215 -d HB2 -u SYSTEM -p Hamburg1

CREATE USER USER PASSWORD PASSWORD;
ALTER USER USER DISABLE PASSWORD LIFETIME;
GRANT MONITORING,USER to USER with ADMIN OPTION ;
#GRANT SAP_INTERNAL_HANA_SUPPORT TO USER WITH ADMIN OPTION; #Bringt im Hana Alert Manager: The SAP_INTERNAL_HANA_SUPPORT is currently granted to 1 user(s) Check if the corresponding users still need the role. If not, revoke the role from them.
GRANT CATALOG READ TO USER WITH ADMIN OPTION;
GRANT AUDIT ADMIN TO USER WITH ADMIN OPTION;
GRANT USER ADMIN TO USER WITH ADMIN OPTION;
GRANT AFLPM_CREATOR_ERASER_EXECUTE TO USER WITH ADMIN OPTION;
GRANT AFLPM_ONLINE_REGISTRATION_EXECUTE TO USER WITH ADMIN OPTION;
GRANT CONTENT_ADMIN TO USER WITH ADMIN OPTION;
GRANT DBA_COCKPIT TO USER WITH ADMIN OPTION;
# GRANT HMS_MONITOR TO USER WITH ADMIN OPTION;
GRANT MODELING TO USER WITH ADMIN OPTION;
# GRANT sap.hana.admin.cockpit.sysrep.roles::SysRepAdmin TO USER WITH ADMIN OPTION;
# GRANT sap.hana.admin.roles.::Monitoring TO USER WITH ADMIN OPTION;
GRANT TABLE_REDISTRIBUTION TO USER WITH ADMIN OPTION;
GRANT LICENSE ADMIN TO USER WITH ADMIN OPTION;
exit

#Login mit neuen User USER
hdbsql
\c -i 22 -n sles287.comp.ds:32215 -d HB2 -u USER -p PASSWORD
#SYSTEM deaktiveren
ALTER USER SYSTEM DEACTIVATE USER NOW;


########################################################################
#Backup Status ansehen . DESC vom letzten ausgehen.
#GREP nach “complete data backup” um Full Backups zu suchen
hdbsql -n sles287.comp.ds:31215 -i 12 -d HB1 -u USER -p PASSWORD “SELECT * FROM SYS.M_BACKUP_CATALOG ORDER BY UTC_START_TIME DESC”

Post Revisions:

Tags: