Assign Schema to multiple users

[Former Member]

Hello All,

I am on SAP HANA SPS6. I have created a schema with name "NLA" under SYSTEM ID. I have also created multiple user ID's in SAP HANA studio. I now want to assign the schema "NLA" which is under SYSTEM to all the other users. All the other users should access the same schema and create tables nly in that schema.

Please let me know how can i do that.

regards

Nilesh

[rindia · Accepted solution]

Hi Nilesh,

One way is by adding the schema name in Object privileges for respective user.

Other way is to create a role for the above scenario and assign this role to respective user.

Regards

Raj

[former_member184768]

Hi Nilesh,

Just one side note, avoid using SYSTEM user for any application development activities. You should have created NLA user with its own default schema and should assign the privileges on this schema to other users.

SYSTEM user is created for specific purpose of system maintenance and it is not a good practice to use it for generic application development activities.

Regards,

Ravi

[patrickbachmann]

Hey Ravi,

We have one particular schema that we share among developers and because we didn't want anybody to ever inadvertently deleting the schema (ie: by deleting the OWNER and cascading) we purposely assigned the owner of the schema as SYSTEM so that it would take a lot of effort for somebody to accidentally delete the schema now.  It is much less likely for somebody to sign on as SYSTEM user and delete the schema now since only few people have this system user access.

-Patrick

[former_member184768]

Hi Patrick,

In our landscape, ONLY SYSTEM user has the privileges to create / modify / delete other user schema. As a matter of fact, it is not the SYSTEM user, but another SYSTEM like user has been created for such admin activities. The access control is quite tightly governed by the admin team. Although it increases the workload on them, they are ok with the additional work, rather than providing system level access or OS level access, to the application users.

I come from traditional Oracle database background and the concept of SYSTEM or SYS user access for application development is not very convincing.

Finally access authorization is defined by the Organization Governance policy. There could be pros and cons of each approach and the decision should be taken considering all the aspects.

Regards,

Ravi

[patrickbachmann]

Ravi, thanks for the feedback.  That seems like a good idea the way you are handling deletions and perhaps we will try a similar approach.

Thanks!

-Patrick