0

I have a stored procedure in a database called DEV1 which exists in a Schema called BI.

I am connecting with an ODBC connection that sets the default database of the session to a databased called DEV2. I am unable to modify the ODBC connection of this service account due to how it is setup in key vaults/secrets managers for various environments.

When calling the stored procedure in the format of call DEV1.BI.StoredProcedureName() from the service account which has it's session database set to DEV2, StoredProcedureName() the query fails if the stored procedure attempts to read from Views which do not fully qualify the database name and exist in the DEV1 database, which are only referenced by a Schema.Table reference instead of database.schema.table. In my case this service account looks in DEV2 for these objects, which fails due to them not existing.

Is there functionality within Stored Procedures to overcome this issue?

It has been an architectural decision to leave database names out of our views and stored procedures, which enables us to CLONE COPY a higher environment (Prod) to a lower one (UAT) for testing. This process breaks if we hard code views with CI/CD variables swapped at deployment time.

It's understood that using a different service account can overcome this issue by setting the default database, but when this requires creating somewhat redundant artifacts in key vaults, it seems unnecessary if there exists syntax that can set the Stored Procedure to the right database before execution. I have tried adding a USE [database]; statement as a first-step in the stored proc, unsuccessfully.

Are we chasing dragons or is it possible to achieve what we're looking for without additional service accounts?

We've tried overcoming this by wrapping the uncallable SP in a wrapper Stored Procedure which first changes the database context similar to this:

CREATE OR REPLACE PROCEDURE DEV1.BI.AStoredProcedure()
    RETURNS VARCHAR(16777216)
    LANGUAGE SQL
    EXECUTE AS CALLER
AS 'begin
 use DATABASE DEV1;
 call BI.AStoredProcedure()
end';

This executes successfully but does not return the data produced by the AStoredProcedure() execution. It feels like we're close but there's no documentation on such behaviour in the Snowflake documentation (that I've been able to find).

Thanks in advance for everyone's input!

dijikul
  • 1
  • Given the way you’ve set up you account, the only solution I can think of would be to pass the database name as a parameter into your SP and edit the SP code to use this value – NickW Dec 12 '22 at 20:54
  • Thanks @NickW it's working in most cases where I've hard-coded the database name in the Stored Procedure, which results in the behaviour I think you're leading me to. The issue is presenting itself when the Stored Procedure references views that reference other views, which lack those database refreences. The nested views don't have the fully-qualified database references, and it would take rewriting them all to pass the parameter all the way down. The hunt continues... – dijikul Dec 12 '22 at 21:11
  • 1
    The problem, as you probably realise, is that your chosen architecture doesn’t work for all you use cases - not fully qualifying names may help with cloning but then breaks your SPs. If you want to solve this fundamental issue then you need to fix your architecture rather than come up with point solutions for each issue you encounter. While it may not be suitable for your specific circumstances, my default recommendation is always to run separate accounts for Dev, Test, Prod rather than trying to run them in a single account – NickW Dec 12 '22 at 21:24
  • Thanks again @NickW. In this case we're using different accounts for each environment, but databases are further divided up by service account. In this case I was following a pattern for a different database, and with all else similar, it feels like redundant scaffolding to follow the same if we could just instead USE [database]; in the Stored Proc to change the session context after a session was established similar to how the UI behaves. Cheers – dijikul Dec 13 '22 at 16:58

0 Answers0