Securing access to External Tables / Files with Unity Catalog

By default, Unity Catalog will create managed tables in your primary storage, providing a secured table access for all your users.

In addition to these managed tables, you can manage access to External tables and files, located in another cloud storage (S3/ADLS/GCS).

This give you capabilities to ensure a full data governance, storing your main tables in the managed catalog/storage while ensuring secure access for for specific cloud storage.

Working with External Locations

Accessing external cloud storage is easily done using `External locations`.

This can be done using 3 simple SQL command:


1. First, create a Storage credential. It'll contain the IAM role/SP required to access your cloud storage
1. Create an External location using your Storage credential. It can be any cloud location (a sub folder)
1. Finally, Grant permissions to your users to access this Storage Credential

1/ Create the STORAGE CREDENTIAL

The first step is to create the `STORAGE CREDENTIAL`.

To do that, we'll use Databricks Unity Catalog UI:

1. Open the Data Explorer in DBSQL
1. Select the "Storage Credential" menu
1. Click on "Create Credential"
1. Fill your credential information: the name and IAM role you will be using

Because you need to be ADMIN, this step has been created for you.

2/ Create the EXTERNAL LOCATION

We'll then create our `EXTERNAL LOCATION` using the following path:

`s3a://databricks-e2demofieldengwest/external_location/`

Note that you need to be Account Admin to do that, it'll fail with a permission error if you are not. But don't worry, the external location has been created for you.

You can also update your location using SQL operations:


```ALTER EXTERNAL LOCATION `xxxx` RENAME TO `yyyy`; ```

```DROP EXTERNAL LOCATION IF EXISTS `xxxx`; ```

3/ GRANT permissions on the external location

All we have to do is now GRANT permission to our users or group of users. In our demo we'll grant access to all our users using `account users`

We can set multiple permissions:

1. READ FILES to be able to access the data
1. WRITE FILES to be able to write data
1. CREATE TABLE to create external table using this location

To revoke your permissions, you can use ```REVOKE WRITE FILES ON EXTERNAL LOCATION `field_demos_external_location` FROM `account users`;```

Accessing the data

That's all we have to do! Our users can now access the folder in SQL or python:

we can also write data using SQL or Python API:

Setting the Permissions can also be done using the Data Explorer UI:



*Note: because we have set all users to OWNER for the demo, all users have full READ/WRITE permissions as OWNER (even without the GRANT). In a real setup, a single admin would be the OWNER, granting specific access to group of users or specific users.*

Conclusion

With Unity Catalog, you can easily secure access to external locations and grant access based on users/groups.

This let you operate security at scale, cross workspace, and be ready to build data mesh setups.