How about using proxy users to avoid "create any table"?

[MarcelOCHH]

How about using proxy users and post_hooks to avoid the authorisation ‘Create any Table’ when using numerous different schemas.
Currently it is quite ugly that you either have to work with existing tables in other schemas or you have to grant the ‘create any table/view’ permission. DBAs are extremely reluctant to grant this right.
The idea is to open and use a connection with the respective proxy user depending on the target schema of the model. The target schema is known and easily accessible via the syntax username[proxy_account] without further configuration.
If the ‘select any table’ right is also to be avoided, a PostHook can simply be configured for each model, which grants the corresponding ‘select’ right to the DownStream schemas (e.g. Stage=>Core=>Mart). The route via a role (e.g. dbt_oper) is unfortunately not possible, as tables or views are also created via CTAS using this grant. In my opinion, however, this is not possible.
The code customisation would be minimal.

Following modifications need to be done:

1. add new Class OracleConnection(Connection) to connections.py
   This is only needed to store the proxy_user-property (~schema differs from username of connection)
2. modify set_connection_name to return an OracleConnection
   and determine the need of an proxy-user for this connection:

t_user = self.profile.credentials.user if self.profile and self.profile.credentials else None\n
node_info = get_node_info()
t_node_schema = node_info.get("node_relation", {}).get("schema") if isinstance(node_info, dict) else None
proxy_user = None if t_node_schema is None or t_user.lower() == t_node_schema.lower()  else f"{t_user}[{t_node_schema}]"

Afterwards find out if the connection fits our purposes (username=proxy_user), otherwise reconnect with correct credentials
3. modify open(cls, connection)
use proxy_user for username when given:

username = credentials.user if connection.proxy_user is None else connection.proxy_user

Should work like a charme :-)

[aosingh]

@MarcelOCHH

Apologies for the late reply, I just came back from parental leave

Let me check and test what you have proposed here.

I think the ANY privilege was introduced in 23ai. Are you using 23ai ?

[MarcelOCHH]

Hi @aosingh,
the "create any table" privilege is quite old. I would expect, it exists since oracle 7 :-)
I do not remeber any project in my history, where a technical user (executing a kind of "dynamic sql") like the dbt_operator was granted the privilege "create any table". The answer from dba was always: "you have to find another way to implement this"
I'm using 23ai, but as a docker image.

Regards
Marcel

[aosingh]

Oh, I am referring to schema-level privilege which was introduced in 23ai. Not on the whole database.

-- Tables, views, materialized views
grant select any table on schema app_schema to testuser2;
grant insert any table on schema app_schema to testuser2;
grant update any table on schema app_schema to testuser2;
grant delete any table on schema app_schema to testuser2;

-- Procedures, functions and packages
grant execute any procedure on schema app_schema to testuser2;
grant execute any procedure on schema app_schema to t1_schema_role;

[MarcelOCHH]

Hi @aosingh,
that's a fantastic feature in 23ai. Didn't know that it exists! Thanks for you reply.

But for those companies using Oracle < 23ai the use of proxy-users gives them the ability to use dbt-oracle without the limitation of "one schema for all" or the "create any table"-privilege.

Regards
Marcel