SET TRANSACTION Syntax

SET TRANSACTION <isolation_level> | <transaction_access_mode>

Syntax Elements

isolation_level ::= ISOLATION LAVEL <level>

level ::= READ COMMITTED | REPEATABLE READ | SERIALIZABLE transaction_access_mode ::= READ ONLY | READ WRITE

READ COMMITTED

Default This isolation level corresponds to the statement level read consistency. With statement level snapshot isolation, different statements in a transaction may see different snapshots of the system. The statement in a transaction sees consistent snapshots of the system. Each statement sees the changes that were committed when the execution of the statement started. Reading a row does not set any locks. When rows are inserted, updated or deleted, the system sets exclusive locks on the affected rows for the duration of the transaction. The system releases these locks at the end of the transaction. When rows are inserted, updated or deleted, the system also sets shared locks on the affected tables for the duration of the transaction. This guarantees that the table is not dropped or altered while some rows of the table are being updated.

REPEATABLE READ/SERIALIZABLE

This isolation level corresponds to transaction level snapshot isolation. All statements of a transaction see the same snapshot of the database. This snapshot contains all changes that were committed at the time the transaction started with the changes made by the transaction itself. Reading a row does not set any locks.

When rows are inserted, updated or deleted, the system sets exclusive locks on the affected rows for the duration of the transaction. The system releases these locks at the end of the transaction. When rows are inserted, updated or deleted, the system also sets shared locks on the affected tables for the duration of the transaction. This guarantees that the table is not dropped or altered while some rows of the table are being updated.

READ WRITE

Default An SQL-transaction access mode may be explicitly set by a SET TRANSACTION statement; otherwise, it is implicitly set to the default access mode.

READ ONLY

When read only access mode is set, then only read operation with SELECT statement is allowed and exceptions will be thrown if any update is tried.

Description

The system uses multi-version concurrency control (MVCC) to ensure consistent read operations. Concurrent read operations see a consistent view of the system without blocking concurrent write operations. Updates are implemented not by overwriting existing records, but by inserting new versions.

The isolation level specification determines the lock operation type. The system supports both statement level snapshot isolation and transaction level snapshot isolation.

For statement snapshot isolation use level READ COMMITTED.

For transaction snapshot isolation use REPEATABLE READ or SERIALIZABLE.

DDL statements always run in READ COMMITTED isolation level.

Example

SET TRANSACTION READ COMMITTED;

Access Control Statements ALTER SAML PROVIDER

ALTER SAML PROVIDER <saml_provider_name> WITH SUBJECT <subject_name> ISSUER <issuer _distinguished_name>

Description

The ALTER SAML PROVIDER statement changes the property of a SAML provider known to the SAP HANA database. <saml_provider_name> has to be an existing SAML provider.

Only database users having the system privilege USER ADMIN are allowed to change a SAML provider.

The <subject_name> and the <issuer_distinguished_name> are the corresponding names provided in the certificate of the SAML identity provider.

System and Monitoring Views

SAML_PROVIDERS: shows all SAML providers with their subject name and issuer_name.

Syntax ALTER USER Syntax

ALTER USER <user_name> <alter_user_option>

Syntax Elements

<alter_user_option> ::=

PASSWORD <password> [<user_parameter_option>]

| <user_parameter_option>

| IDENTIFIED EXTERNALLY AS <external_identity> [<user_parameter_option>]

| RESET CONNECT ATTEMPTS | DROP CONNECT ATTEMPTS | DISABLE PASSWORD LIFETIME | FORCE PASSWORD CHANGE | DEACTIVATE [USER NOW]

| ACTIVATE [USER NOW]

| DISABLE <authentication_mechanism>

| ENABLE <authentication_mechanism>

| ADD IDENTITY <provider_identity>...

| ADD IDENTITY <external_identity> FOR KERBEROS | DROP IDENTITY <provider_info>...

| DROP IDENTITY FOR KERBEROS <user_parameter_option> ::=

<set_user_parameters> [<clear_user_parameter_option>]

| <clear_user_parameter_option>

<clear_user_parameter_option> ::=

CLEAR PARAMETER CLIENT | CLEAR ALL PARAMETERS

<authentication_mechanism> ::= PASSWORD | KERBEROS | SAML <provider_info> ::= FOR SAML PROVIDER <provider_name>

Description

The ALTER USER statement modifies the database user. <user_name> must specify an existing database user.

Each user can do the ALTER USER statement for his own. But not all of the <alter_user_option>s can be specified by the user himself. The ALTER USER statement for other users with all <alter_user_option>s can only be done by users having the system privilege USER ADMIN.

Users created with PASSWORD cannot be changed to EXTERNALLY and vice versa: the users created with EXTERNALLY cannot be changed to PASSWORD. But their <password> or their <external_identity> can be changed.

You can change a user's password with this command. Password must follow the rules defined for the current database. The password rules include the minimal password length and the definition which of the character types ( lower, upper, digit, special characters ) have to be part of the password. The password for a user has to be changed regularly according to the password policy specified for the database instance or changed by the user himself when first connecting to the database instance.

You can change the external authentication. External users are authenticated using an external system, e.g.

a Kerberos system. Such users do not have a password, but , e.g. a Kerberos principal name. For detailed information about external identities, contact your domain administrator.

<user_parameter_option> can be used to set, change or clean the user parameter CLIENT. When using reports this user parameter CLIENT can be used to restrict the access rights of user <user_name> to info concerning the specified client.

The <user_parameter_option> can not be specified by the user himself.

If the number of MAXIMUM_INVALID_CONNECT_ATTEMPTS (see monitoring view M_PASSWORD_POLICY) is reached before a successful (correct user/password-combination) connect is done, then this user is locked for some minutes before being allowed to connect (even with correct user/password-combination) again. With the command

ALTER USER <user_name> RESET CONNECT ATTEMPTS

a user with system privilege USER ADMIN can reset the number of invalid attempts to 0 and therefore allow the user to connect immediately.

Information on invalid connect attempts having happened can be checked in system view INVALID_CONNECT_ATTEMPTS. With the command

ALTER USER <user_name> DROP CONNECT ATTEMPTS

a user with system privilege USER ADMIN or the user himself can delete the information of invalid connect attempts having happened.

With the command

ALTER USER <user_name> DISABLE PASSWORD LIFETIME

a user with system privilege USER ADMIN can exclude user <user_name> from all password-life-time-checks.

This should be used only for technical users, not for normal database users. Please check the description of configuration parameters concerning the password policy.

With the command

ALTER USER <user_name> FORCE PASSWORD CHANGE

a user with system privilege USER ADMIN can force user <user_name> to change his password immediately after the next connect before being allowed to work any further.

With the command

ALTER USER <user_name> DEACTIVATE USER NOW

a user with system privilege USER ADMIN can deactivate / lock the user account of user <user_name>. After the user account is deactivated / locked, the user cannot connect to the SAP HANA database. To re-activate / unlock user <user_name> a user with system privilege USER ADMIN has either to use the command ALTER USER <user_name> ACTIVATE USER NOW or, in case of a user with PASSWORD authentication mechanism, to reset the user's password with ALTER USER <user_name> PASSWORD <password>.

With the command

ALTER USER <user_name> ACTIVATE USER NOW

a user with system privilege USER ADMIN can re-activate / unlock the user account of user <user_name>

who had been deactivated before.

Configuration Parameter

Conguration parameters concerning the password can be checked with the monitoring view M_PASSWORD_POLICY. These parameter are stored in indexserver.ini, section 'password policy'.

The description of the parameters concerned can be found in SAP HANA Security Guide, Appendix, Password Policy Parameters.

System and Monitoring Views

USERS: shows all users, their creator, creation date and some info about their current states.

USER_PARAMETERS: shows the defined user_parameters; currently only CLIENT is available.

INVALID_CONNECT_ATTEMPTS: shows how many invalid connect attempts were made for each user.

LAST_USED_PASSWORDS: shows info about dates of last password-changes per user.

M_PASSWORD_POLICY: shows configuration parameters describing the allowed layout of the password and its lifetimes.

Example

A user with user_name NEW_USER had been created before with the possibility to connect using the given password and with an assertion of the existing SAML provider OUR_PROVIDER. The <mapped_user_name>

was set to ANY as the assertion will provide the database user name.

CREATE USER new_user PASSWORD Password1 WITH IDENTITY ANY FOR SAML PROVIDER OUR_PRO VIDER;

Now this user shall be forced to change his password. And to force him really hard, his possibility to connect using SAML is disabled.

ALTER USER new_user FORCE PASSWORD CHANGE;

ALTER USER user_new DISABLE SAML;

CREATE ROLE