set schema

[Former Member]

Hi

In Oracle I used to set my current schema to a different user, and then I can run SQL commands as that user, example: "ALTER SESSION SET CURRENT_SCHEMA=INTDATA"

I'm trying to duplicate this in HANA, but it is not working:

SELECT CURRENT_USER "current user" FROM DUMMY;

current user

SYSTEM

Could not execute 'GRANT SELECT ON SCHEMA "INTDATA" TO _SYS_REPO'

SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

hdbsql PCM=> SET SCHEMA INTDATA;

0 rows affected (overall time 5089 usec; server time 320 usec)

hdbsql PCM=> GRANT SELECT ON SCHEMA "INTDATA" TO _SYS_REPO;

* 258: insufficient privilege: Not authorized SQLSTATE: HY000

hdbsql PCM=> SELECT CURRENT_USER "current user" FROM DUMMY;

1 row selected (overall time 7755 usec; server time 323 usec)

current user

SYSTEM

My current user stays SYSTEM.  I'm trying to solve the issue of granting users access to data, but I'm not authorized as the SYSTEM user.

I notice in HANA the SET SCHEMA sets only the prefix of database object names, do we have an equivalent of the Oracle functionality?  Other advise on how the DBA may grant the requested user rights?

Best Regards

Willem

[muthuram_shanmugavel2 · Accepted solution]

Hi Willem,

If System user is Owner of the Schema "INTDATA" only,

you can able to Grant Privileges to "_SYS_REPO" user.

Please check Owner of the "INTDATA"  schema using SQL,

Select SCHEMA_OWNER, HAS_PRIVILEGES FROM "SYS"."SCHEMAS"

WHERE SCHEMA_NAME = 'INTDATA'

Then login by Schema Owner and Execute the sql,

'GRANT SELECT ON SCHEMA "INTDATA" TO _SYS_REPO'

You can able to Grant Privilege successfully.

Regards,

Muthuram

[Former Member · Accepted solution]

Hi Muthuram

Thanks!

On Development, I notice the schema owner is BODS_DH1:

SCHEMA_OWNER;HAS_PRIVILEGES

BODS_DH1;TRUE

and as the SYSTEM user I do not have privileges to grant:

Could not execute 'GRANT SELECT ON SCHEMA "INTDATA" TO _SYS_REPO'

SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

I checked on Production, and there the schema owner is SYSTEM, and the grant works:

Select SCHEMA_OWNER, HAS_PRIVILEGES FROM "SYS"."SCHEMAS"

WHERE SCHEMA_NAME = 'INTDATA'

SCHEMA_OWNER;HAS_PRIVILEGES

SYSTEM;TRUE

On DEV, grants should be done by BODS_DH1 user?  Should schema owner be changed to match on Dev and Prod, which user is recommended to be the schema owner?

Thanks for the aid

[muthuram_shanmugavel2 · Accepted solution]

Hi Willem,

//On DEV, grants should be done by BODS_DH1 user? 

In DEV Environment, You can able to Grant Privilege successfully using BODS_DH1 user.

//Should schema owner be changed to match on Dev and Prod//

"CREATE SCHEMA <>" command - the user executes this command will be Owner for the Schema.

For your case,

In DEV, BODS_DH1 user execute the "CREATE SCHEMA" command.

In Prod, SYSTEM user execute the "CREATE SCHEMA" command

So the owners are changed in DEV and Prod Environments.

//which user is recommended to be the schema owner?

Create schema using "*.hdbschema". The owner will be "_SYS_REPO" in all environments.

Regards,

Muthuram

[lbreddemann]

The Oracle behavior is due to the fact that Oracle mixes schema (logical namespace) with user privilege management. In Oracle you cannot have a schema without a corresponding user, while in SAP HANA (and many other DBMS) the schema concept is less restricted in this regard,

So, to change the current default namespace, the SET SCHEMA command is the correct one.

In order to impersonate another user, there is only the option to logon as this user.

Also: SYSTEM really shouldn't be used for productive system access or any non-bootstrap activity.