Make it impossible to delete Production Database

Question

I am trying to setup snowflake and need some help.Data Engineers in the organisation have clone DBs to get their work done and they frequently drop the clone DB. Someone can accidentally delete the production DB and I want to prevent that.

I want to make it impossible to delete production database.At the same time we have clone database which the user should be able to delete. I know about "undrop" function but dont want matters to escalate till that.

Data Engineers should be able to do the rest of the queries on the Production DB like drop table/view etc. They test the code in clone and once satisfied update the code in the production. I want to restrict just the "drop function" of the whole production DB.

-- when running the below query snowflake should throw a notification that the user don't have privileges to run the below query (this is production DB). (Owner sysadmin) DROP DATABASE PRODUCTION_DB;

--Run the below code without any issues (Below is a clone of the production DB).(Owner sysadmin) DROP DATABASE PRODUCTION_DB_CLONE;

Role of data engineers (sysadmin).

Answer 1

Only the database owner (i.e. the role with the OWNERSHIP privilege on the database) can drop the database.

Granting the required privileges to a different role(for data engineers) to perform the operations other than the database owner(Role which owns the OWNERSHIP privilege) will help avoid these unexpected issues.