Configuring and Using Connection Links

User Advantages

1. Referring to a connection link in a command is more secure than specifying the connection details directly in the command. Users need the CREATE LINK permission to create connection links, and only those users need to know the connection details.

2. Commands such as BACKUP,RESTORE, CREATE PIPELINE, and SELECT support connection links. Users can run these commands without specifying the connection details. However, the user needs the SHOW LINK permission to use a connection link.

Using a Connection Link

Creation and use of a connection link is dependent on the permissions granted to a user. The following are the permissions that can be granted to users:

• CREATE LINK: A user with the CREATE LINK permission can create a connection link and only that user will know the connection details.

• SHOW LINK: A user with the SHOW LINK permission can view and use all connection links that exist in a SingleStore database.

• DROP LINK: A user with the DROP LINK permission can remove a connection link that exists in a SingleStore database.

Note: The listed permissions can be cluster or database scoped. For example, you can grant the CREATE LINK permission on *.* (cluster scoped) and database.* (database scoped) but not database.table. See the GRANT topic for more details.

Example

The following example demonstrates the steps performed by a user user1 to write all rows of the table t1 of the database productdb to an S3 bucket using a connection link. Azure/GCS/HDFS/KAFKA links are created similarly via CREATE LINK linkname AS {AZURE,GCS,HDFS,KAFKA} ... -- see CREATE LINK for more information.

1. On user request, the DBA (who has the CREATE LINK permission) creates an S3 connection link demouser_S3:

   SQL

   Copy

   CREATE LINK productdb.demouser_S3 AS S3
   CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}'
   CONFIG '{"region":"us-east-1"}'
   DESCRIPTION 'Product list';

2. The DBA grants the SHOW LINK permission to user1.

   SQL

   Copy

   GRANT SHOW LINK ON productdb.* TO 'user1';

   This allows user1 to use the S3 connection link demouser_S3 and any other connection links defined in the productdb database.

   user1 can run the SHOW LINKS command to view all the connection links in a database. For example, if a second connection link, demouser2_S3 had been created in the productdb database, running SHOW LINKS would return the following results:

   SQL

   Copy

   SHOW LINKS ON productdb;

   +-------------------------+--------+-----------------------------+
   | Link                    | Type   | Description                 |
   +-------------------------+--------+-----------------------------+
   | demouser_S3             | S3     | Product list                |
   | demouser2_S3            | S3     | Brand list                  |
   +-------------------------+--------+-----------------------------+

3. user1 runs the SELECT .. INTO LINK command to write the contents of the table t1, to the S3 bucket at the specified path, using the S3 connection link demouser_S3 stored in the productdb database.

   SQL

   Copy

   USE productdb;
   SELECT * FROM t1 INTO LINK demouser_S3 'testing/output';