[Bug] multi schema materializations not supported without administrative privileges

[geoHeil]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

multi schema materializations not supported without administrative privileges

Expected Behavior

DBT should be able to populate pre created schemata without administrative permissiones required to execute a CREATE table in these schematas.

A proxy connect is not a viable workaround as it is explicit and cannot implicitly be inferred - as far as I understand.

Steps To Reproduce

DBT can nicely generate schemata in other databases (postgres, snowflake, ...). However, this is tricky as my DBT account has no administrative permissions. Rather, it is confined to 3 schemata a_raw, b_staging, c_mart. However, while my user foo foo[a_raw] has proxy-connect privileges to all a,b,c schemata DBT only has a single target (i.e. foo[a_raw] ) that can be registered. This means I would need to break down the dbt run into 3 separate callls to the cli and also that the nice selector for upstream models no longer works due to insufficient permissions. How can I change the situation to allow the dbt user (foo) to read/write all a,b,c schemata? My DBA is telling me that Oracle cannot grant CREATE permissions to any other user besides the specific schema user (and administrators) but he is not going to hand me administrative privileges. What can I do to return back to a dbt native situation?

This means when changing the target user from foo[a_raw] to foo[b_staging] to foo[c_mart] (explicitly) CREATE permissions are there. Otherwise not.

Relevant log output using --debug flag enabled

No response

Environment

- OS: windows
- Python: 3.10
- dbt: 1.3.1

What Oracle database version are you using dbt with?

11 and hopefully soon 19c

Additional Context

No response