Copying users in SAP HANA

Hi all,

as most readers will be aware, database users in SAP HANA are non-copiable entities. The most important reason for the lack of a generic user copy mechanism is in the course of a user copy, all privilege and roles assignments shall be copied as well. While this is not a real challenge for role grantings, the privileges granted directly to end-users are what makes a generic user copy mechanism impossible.

As most simple example, consider that you have two users, Jim and Bob. Jim owns a database schema named “JIM”, and for whatever reason has given Bob the permission to work in that schema, that is, Bob has object privileges sucha s SELECT on schema JIM.

Now assume another database user, let’s call her SYSTEMA wants to copy user Bob. In that process, she would also need to assign the SELECT privielge on schema JIM to the new user – but does SYSTEMA have the required privileges? She probably won’t, and thus the process of copying the user would fail.

In the SAP HANA studio, there is since about SPS 7 a possibility to copy database users – with one restriction: the only user properties that will be copied are the assignemts of repository roles (aka design-time roles). If you are operating a well-managed HANA system, this will be fine, because SAP’s recommendation is to only grant repository roles to end-users; do not make use of catalog roles; and never grant privileges directly to end users.

The copy process is purely a UI functionality, and thus cannot be automated.

If you need to automate copying of users, you might find the procedure below helpful. It allows copying a user to a single target user. The copying fill fail under the following circumstances:

• user to be copied does not exist
• user to be created does already exist
• user to be copied carries catalog roles (except for PUBLIC)
• user to be copied has direct privilege assignments (except for privileges on the user’s own schema or on other schemas owned by the user)

The procedure is prepared as a repository object – you can basically paste the source code into the procedure editor of the editor in the SAP HANA studio or the Web IDE. Then you’ll have to replace in the procedure header the <schema> (that’s the database schema into which the activated version of the procedure will be placed) and the <package1>.<package2> repository path to your procedure. And you will probably want to change the dummy password that we give to the user.

Have fun,

Richard

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;