You want to limit permissions for the Fivetran user that writes data into your destination to only have access to specific schemas or datasets. The default option is to grant the Fivetran user full access at the database level but you need more granular control of permissions.
We recommend you re-create permissions in your destination following the principle of least privilege which allows the Fivetran user to do everything necessary to write data, which includes:
- Creating new schemas and tables
- Creating temporary tables
- Altering tables
Fivetran uses temporary tables that are named with a random string so you will also need to ensure that the Fivetran user has access to all future tables created in schemas we write to. Note that this is different for each destination, here are a couple examples:
GRANT ALL ON FUTURE TABLES IN SCHEMA <SCHEMA_NAME>
ALTER DEFAULT PRIVILEGES
Grant the Fivetran user access to the specific datasets that Fivetran writes into.
Implementing more granular permissions requires additional maintenance for your connectors to ensure that the Fivetran user in your destination can do the necessary actions to specific schemas and datasets.