User Management Issues

[Former Member]

Hi Team,

I am practicing on User & Role Management. some exercises done for general approach for user & Roles..

i don't see following options from Administration through HDB studio.

• How to copy from one user to another user?

• Suppose i create one user with respective Roles, same user roles need to copy to another user. ( mass user creation)

*Similar Mass Role creation

I believe this can achieve through procedures, anyone done that procedures?

thanks & Regards

Rao

[tomas-krojzl]

Hello Ganesh,

I think "copy" of the user is little problematic - SAP HANA security concept is making big difference in who granted particular privilege or role.

In case user would execute copy - what exactly should be copied?

1.) In case that all privileges - then this would be considered as security issue as you can create new user with privileges that you are not authorized to grant (you simply cannot grant privilege on behalf of another user - this would make it possible)

2.) In case that only privileges that you granted - then this new user would not be identical to source user which if overlooked can cause issues (in other words such behavior would not be expected from users)

Anyway I found your request to create procedure quite interesting and here it is:

CREATE PROCEDURE SYSTEM.COPY_USER (IN V_SOURCE_USER NVARCHAR(256), IN V_TARGET_USER NVARCHAR(256), IN V_TARGET_PWD NVARCHAR(256)) LANGUAGE SQLSCRIPT AS
  V_FOUND INT := 1;
  CURSOR C_ROLE_LIST (V_SRC_USER NVARCHAR(256)) FOR
    SELECT ROLE_NAME, IS_GRANTABLE FROM "SYS"."GRANTED_ROLES" WHERE GRANTEE=:V_SRC_USER AND GRANTEE_TYPE='USER' AND GRANTOR=CURRENT_USER;
BEGIN
  SELECT COUNT(*) INTO V_FOUND FROM "SYS"."USERS" WHERE USER_NAME = :V_SOURCE_USER;
  IF :V_FOUND = 1 THEN
    EXEC 'CREATE USER ' || :V_TARGET_USER || ' IDENTIFIED BY ' || :V_TARGET_PWD;
    FOR V_LIST_ROW AS C_ROLE_LIST(:V_SOURCE_USER) DO
      IF V_LIST_ROW.IS_GRANTABLE='FALSE' THEN
        EXEC 'GRANT ' || V_LIST_ROW.ROLE_NAME || ' TO ' || :V_TARGET_USER;
      ELSE
        EXEC 'GRANT ' || V_LIST_ROW.ROLE_NAME || ' TO ' || :V_TARGET_USER || ' WITH ADMIN OPTION';
      END IF;
    END FOR;
  END IF;
END;

You can call this procedure like this:

CALL COPY_USER ('SOURCE_USER', 'TARGET_USER', 'INITIAL_PASSWORD')

But bear in mind following limitations:

1.) only roles are replicated (nothing else) - this should not be problem as you should not be granting privileges directly to users especially in case that you expect mass copy

2.) only roles where current user is grantor are copied - this is to preserve grantee - remaining roles must be added manually (if you do not like this behavior then remove "GRANTOR=CURRENT_USER" condition in select statement and all roles will be added)

I hope you will like it

[Former Member]

Hi Tomas,

Thank you, already i worked some kind of stuff like this, I hope this felicity should need in HANA studio, Hope SAP interoduce next patches.

Currently is not available in patch 17.

[Former Member]

Hello Tomas,

I tried this piece of code. I was able to create the stored procedure. But I'm facing an error while running it.

"Incorrect syntax near "BY" : line 1 col 32  ( at pos 32 ) "

Any thought about this ?

Regards,

Anil

[Former Member]

Hi Anil,

I have this issue too. I upgraded my HANA Studio or HANA I dont remember what I have upgraded but you can start to upgrade your studio. But as I said I am not sure what I did.    

[former_member215510]

Is there a way to put any debug statements in the procedure, I am getting a syntax error when I call it.

When I look at the procedure the line number and positions are not helping identifying the error as it is a dynamic SQL.

Poonam

[former_member184768]

Hi,

I think CREATE USER <user_name> IDENTIFIED BY <password> may not be the correct syntax.

You can try modifying the below line:

EXEC 'CREATE USER ' || :V_TARGET_USER || ' IDENTIFIED BY ' || :V_TARGET_PWD;

to

EXEC 'CREATE USER ' || :V_TARGET_USER || ' PASSWORD ' || :V_TARGET_PWD;

Also it would help if you can post the error message to understand the issue better.

Regards,

Ravi

[Former Member]

I extended this procedure a bit. Now it's also possible to copy granted privileges from the source to the target user.

CREATE PROCEDURE copy_user (IN V_SOURCE_USER NVARCHAR(256), IN V_TARGET_USER NVARCHAR(256), IN V_TARGET_PWD NVARCHAR(256)) 
  LANGUAGE SQLSCRIPT 
  AS 
  V_FOUND INT := 1;
  CURSOR C_ROLE_LIST (V_SRC_USER NVARCHAR(256)) FOR
    SELECT ROLE_NAME, IS_GRANTABLE FROM "SYS"."GRANTED_ROLES" WHERE GRANTEE=:V_SRC_USER AND GRANTEE_TYPE='USER' AND GRANTOR=CURRENT_USER;
  CURSOR C_PRIV_LIST (V_SCR_USER NVARCHAR(256)) FOR
    SELECT OBJECT_NAME, PRIVILEGE, IS_GRANTABLE FROM "SYS"."GRANTED_PRIVILEGES" WHERE GRANTEE=:V_SCR_USER AND GRANTEE_TYPE='USER' AND GRANTOR=CURRENT_USER;  
BEGIN
  SELECT COUNT(*) INTO V_FOUND FROM "SYS"."USERS" WHERE USER_NAME = :V_SOURCE_USER;
  IF :V_FOUND = 1 THEN
    EXEC 'CREATE USER ' || :V_TARGET_USER || ' PASSWORD ' || :V_TARGET_PWD;
    FOR V_LIST_ROW AS C_ROLE_LIST(:V_SOURCE_USER) DO
      IF V_LIST_ROW.IS_GRANTABLE='FALSE' THEN
        EXEC 'GRANT ' || V_LIST_ROW.ROLE_NAME || ' TO ' || :V_TARGET_USER;
      ELSE
        EXEC 'GRANT ' || V_LIST_ROW.ROLE_NAME || ' TO ' || :V_TARGET_USER || ' WITH ADMIN OPTION';
      END IF;
    END FOR;
    FOR V_LIST_ROW AS C_PRIV_LIST(:V_SOURCE_USER) DO
      IF V_LIST_ROW.IS_GRANTABLE='FALSE' THEN
        EXEC 'GRANT ' || V_LIST_ROW.PRIVILEGE || ' ON ' || V_LIST_ROW.OBJECT_NAME || ' TO ' || :V_TARGET_USER;
      ELSE
        EXEC 'GRANT ' || V_LIST_ROW.PRIVILEGE || ' ON ' || V_LIST_ROW.OBJECT_NAME || ' TO ' || :V_TARGET_USER || ' WITH GRANT OPTION';
      END IF;
    END FOR;
  END IF;
END;

[Former Member]

Hi,

I would like to create user XYZ copying existing user SYSTEM,in this case what all the things I need to change in above given procedure.

Regards

Uday

[Former Member]

Hi Uday,

Currently it's not possible to copy the SYSTEM user. The only option is to create a new user, assign the roles and objects manually and then copy this user to other users (with the above procedure).

Best regards,

Jeroen

[Former Member]

Hi Jeroen,

Fine I will create new user with same set of roles as like SYSTEM user,later if I want to copy that user to create a new one,how should the procedure (Say ABC has to be copied to XYZ) what all the things I need to change in the above procedure

Regards

Uday

[Former Member]

Hi Uday,

You don't have to change the procedure. Just insert the usernames in the call command:

CALL COPY_USER ('SOURCE_USER', 'TARGET_USER', 'INITIAL_PASSWORD')

your example:

CALL COPY_USER ('ABC', 'XYZ', 'Welcome123!')

Regards,

Jeroen

[former_member185165]

Hi Jeroen,

I tried this but only the user is created with Public role. The other roles are not copied from the source to target.

Thanks & Regards,

Vijay

[Prabhith]

Hi ,

Were you able to proceed with this 'COPY USER' automation activity.

The user is getting created automatically after executing the stored procedure but only the default role 'PUBLIC' is available for the new user.

I am also not able to get the desired result with the help of the SP mentioned below.

Am I missing some basics here?

, can you please help me here?

BR

Prabhith

[Former Member]

Hello Jeroen,

In HANA SQL Console, we can execute the statements: CREATE, COPY etc..

Do we first need to add your procedure in one of the folders of the catalog?

Thanks,

Sunny Doshi

[Former Member]

Hi Sunny,

I don't know if this procedure is still valid for the current HANA release. But you can try. You can execute 'CREATE PROCEDURE' in SQL Console to create the procedure and then call 'CALL COPY_USER' to execute the procedure.

I hope this helps.

Best regards,

Jeroen

[Vitaliy-R]

The lack (or lack of its obvious visibility) of Copy User function in the HANA Studio drives me nuts too. But I haven't looked at scripting possibilities yet. -VR

[deepak_chodha]

Hi All,

The problem that you all are facing due to the change in queries that we use to execute with early HANA Revisions and what we execute now. Earlier we use to make use of below query to create a user:

CREATE USER <username> IDENTIFIED BY <userpassword>;

But from SP03 onwards, this query has changed to

CREATE USER <username> password <userpassword>;

So try creating a procedure like:

CREATE PROCEDURE SYSTEM.COPY_USER (IN V_SOURCE_USER NVARCHAR(256), IN V_TARGET_USER NVARCHAR(256), IN V_TARGET_PWD NVARCHAR(256)) LANGUAGE SQLSCRIPT AS

  V_FOUND INT := 1;

  CURSOR C_ROLE_LIST (V_SRC_USER NVARCHAR(256)) FOR

    SELECT ROLE_NAME, IS_GRANTABLE FROM "SYS"."GRANTED_ROLES" WHERE GRANTEE=:V_SRC_USER AND GRANTEE_TYPE='USER' AND GRANTOR=CURRENT_USER;

BEGIN

  SELECT COUNT(*) INTO V_FOUND FROM "SYS"."USERS" WHERE USER_NAME = :V_SOURCE_USER;

  IF :V_FOUND = 1 THEN

    EXEC 'CREATE USER ' || :V_TARGET_USER || ' IDENTIFIED BY ' || :V_TARGET_PWD;

    FOR V_LIST_ROW AS C_ROLE_LIST(:V_SOURCE_USER) DO

      IF V_LIST_ROW.IS_GRANTABLE='FALSE' THEN

        EXEC 'GRANT ' || V_LIST_ROW.ROLE_NAME || ' TO ' || :V_TARGET_USER;

      ELSE

        EXEC 'GRANT ' || V_LIST_ROW.ROLE_NAME || ' TO ' || :V_TARGET_USER || ' WITH ADMIN OPTION';

      END IF;

    END FOR;

  END IF;

END;

and call it by:

CALL COPY_USER ('SOURCE_USER', 'TARGET_USER', 'INITIAL_PASSWORD')

Hope it helps.

Regards,

Deepak Chodha.