GRANT READ ON SHARE

For restricted shares, use the GRANT command to explicitly give users access to the share. After a user has been GRANT-ed access they will still need to run an ATTACH command to be able to run queries against the shared database. Only the owner of the share can use the GRANT command to give access to others.

Syntax

GRANT READ ON SHARE <share name> TO <username> [, <username2>, <username3>];

Example usage

-- Owner: gives the user with username 'duck' access to the share 'birds'
GRANT READ ON SHARE birds TO duck; 

-- Owner: gives the users with usernames 'usr1' and 'usr2' access to the share 'taxis'
GRANT READ ON SHARE taxis TO user_1, user_2; 

If a username contains special characters, such as '@', it must be enclosed in double quotes (").

Complete workflow example

Below is a complete workflow showing how to share a database with a restricted audience and how recipients can access it. We will first create a share and grant access to specific users. Then, we will show how recipients can attach and query the shared database. For more information on each step, refer to the CREATE SHARE, LIST SHARES, and ATTACH share documentation.

1. Owner creates a share and grants access

-- Owner: create a restricted share of database 'analytics'
-- Using CREATE OR REPLACE allows updating the share if it already exists.
-- ACCESS RESTRICTED is required to use GRANT/REVOKE.
CREATE OR REPLACE SHARE analytics_share FROM analytics (ACCESS RESTRICTED);

-- Owner: Grant access to specific users.
-- If a username contains special characters like '@', enclose it in double quotes.
GRANT READ ON SHARE analytics_share TO user_1, "user_2@example-com";

-- Owner retrieves the share URL to provide to recipients.
-- The URL uniquely identifies the share.
LIST SHARES;
-- Example output contains URL like: md:_share/analytics/0a9a026ec5a55946a9de39851087ed81

-- Owner shares the full URL (e.g., 'md:_share/analytics/0a9a026ec5a55946a9de39851087ed81') with the granted users.

2. Recipient attaches and queries the shared database

-- Recipient: attach the shared database using the full URL provided by the owner.
-- Using the full URL prevents naming conflicts.
ATTACH 'md:_share/analytics/0a9a026ec5a55946a9de39851087ed81' AS analytics_data;

-- Recipient: switch to the attached database
USE analytics_data;

-- Recipient: query the shared database
SELECT * FROM customer_metrics LIMIT 10;

When the share is attached, it creates a read-only reference to the shared database that doesn't consume additional storage for the recipient. Recipients can query the data but cannot modify it.