How to create sap hana users using SQL Sql Script Method
About Sap Hana user
There are few user categories in sap hana. It is possible to create any user type using sap hana studio, sap hana cockpit or SQL scripts.
Although sap hana tools are very convenient to perform user creation and privileges assignments, it is not the best way to proceed within a company.
A company needs order and regulation at all levels. Company standard and security will not be respected by everyone when such tools are permited to create users and to dispatch privileges.
In order to enforce standard and security, creating hana users via SQL and procedures are to be enforced.
How to create sap hana users from SQL scripts?
Create Standard hana user SQL templates exist on the internet. The create sap hana user syntax is the same in every case. [ CREATE USER < AUSER > PASSWORD < APASSWORD > ]
Other options such as [ NO FORCE_FIRST_PASSWORD_CHANGE or FORCE_FIRST_PASSWORD_CHANGE ] are available to enforce security. See also : create user syntax available options
Create hana standard User
-- Accoupt user in relation to a person, password update to be done on the first login CREATE USER NEW_STANDARD_USER PASSWORD ToBeChangedOnly01 FORCE_FIRST_PASSWORD_CHANGE;
Create hana technical User
-- Accoupt user in relation to a task requirement, predefined password is kept unchanged CREATE USER NEW_TECHNICAL_USER PASSWORD BatchOnly01 NO FORCE_FIRST_PASSWORD_CHANGE;
Sap hana roles will not be created as often as users but it is important to mention that role should also be created via SQL.
A role is a simple entity which will hold other roles and privileges. It is very good idea to create roles in relation to group of privileges.
A user can then be assigned to a pecific role in relation to its function and work requirements.
CREATE ROLE MY_APPLI_ADMIN_ROLE;
CREATE ROLE MY_APPLI_WRITE_DATA_ROLE;
CREATE ROLE MY_APPLI_READ_DATA_ROLE;
Which privileges to assign to a sap hana user?
A sap hana privilege PUBLIC is assigned to any user by Default.
Otherwise very specific privileges will be given to user via Roles. Those roles might already exist, if not it will have to be created.
privileges for a user
CREATE USER TRAINING_USER_PAULA PASSWORD TobeChanged01 FORCE_FIRST_PASSWORD_CHANGE;
CREATE ROLE MY_APPLICATION_TRAINING_ROLE;
GRANT CREATE SCHEMA TO MY_APPLICATION_TRAINING_ROLE;
GRANT DEVELOPMENT TO MY_APPLICATION_TRAINING_ROLE;
GRNAT EXPORT TO MY_APPLICATION_TRAINING_ROLE;
GRANT IMPORT TO MY_APPLICATION_TRAINING_ROLE;
GRANT MONITORING TO MY_APPLICATION_TRAINING_ROLE;
GRANT BACKUP OPERATOR TO MY_APPLICATION_TRAINING_ROLE;
GRANT CATALOG READ TO MY_APPLICATION_TRAINING_ROLE;
GRANT MY_APPLICATION_TRAINING_ROLE TO TRAINING_USER_PAULA;
Here are different users that you might want in your sap hana system :
How to create sap hana technical users step by step?