Skip to Content

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;
To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

    1. Richard Bremer Post author

      Hello Tong Ning,

      if you take a look at the SYSTEM user and at my discussion why generically it is not possible to copy users, you will trivially find out that the SYSTEM user cannot be copied using the provided script. In fact, there is no way at all to copy the SYSTEM user.

      You are, however, probably asking the wrong question. What you really want to ask is “how can I create a generic database administration user that will work in all regular circumstances” – for this you may take a look at the roles how-to guide and read the parts on security setup – including the online appendix – of the Book on SAP HANA Database Administration that Lars Breddemann and I have written.

      Best,

      Richard

      (0) 
    1. Richard Bremer Post author

      Hi Christoforos,

      database users cannot be copied in HANA.

      We have two types of roles: Design-time roles and runtime roles. runtime roles cannot be copied, but design-time roles are repository artefacts and can thus be transported (and they can be copied using export import). Objects and privileges referenced in a design time role must of course also exist in the target system (e.g.: if the design time role grants “SELECT” on table <X>, but that table doesn’t exist in the target system, the transport or copy action will fail).

      If you want to copy database users or runtime roles, you can build programs (java, sqlscript, XSJS, anything that can talk SQL with the database) to list users with their parameters and privileges, then copy those lists and have the reverse coding that creates or updates the users in the target (similar for roles). This is far from trivial because of the complexities of being able to grant privileges in a HANA system. It’s pretty easy to copy users in this style if the only type of privilege granted to users are design time roles. It’s really difficult otherwise.

      Best,

      Richard

      (0) 

Leave a Reply