Administrator guide

Administrators manage Cloud Certainty SQLark from Administration in the portal (PortalUrl): connections, grants, roles, settings, usage, the audit log, and the licence. Everything is stored in the stack's DynamoDB table in your account, and every change is written to the audit log.

Administration has the pages Connections, Grants, Roles, Settings, Usage, Audit log and Licence and about.

#Roles

• Everyone who signs in without a role entry is a User, and sees no databases until a grant applies to them.
• Roles and grants match the user's email or groups. The email must be trusted: asserted by your SAML IdP, verified by your OIDC IdP (email_verified), or marked verified in the Cognito user pool (Identity providers).
• In Administration › Roles, under Grant a role, choose User or Group, enter the email or the group name exactly as your IdP sends it (group names are case-sensitive), choose Administrator or User, and save. A role for a user wins over a role for one of their groups, so you can make a group administrators and keep one member a User. A change applies on the user's next request.
• You can't remove your own Administrator role. Grant it to at least one more person or group.

#The bootstrap admin

BootstrapAdminEmail (a stack parameter) is the bootstrap admin. The first person who signs in with that email is bound to it by their immutable user ID (the Cognito sub) and is always an Administrator, which prevents lock-out. The Roles page shows the entry as set by the stack. The binding is recorded in the audit log (bootstrap.bind). Someone else who later signs in with the same email address but a different user ID is not the bootstrap admin.

#Recovering the bootstrap admin

Reset the binding when the bootstrap admin's user ID changes (the user was recreated, the IdP issues a new subject, or you switched IdentityProvider). Either:

• Use a different person: update the stack and change BootstrapAdminEmail. The first sign-in with the new email takes over the binding.
• Keep the same email: update the stack with BootstrapAdminEmail set to another address you can sign in with, and sign in with it once. Then set it back and sign in with the original email, which takes the binding over again.

If another Administrator can still sign in, they can simply grant the Administrator role to the person by email under Administration › Roles.

#Connections

A connection is one IAM role SQLark assumes in one AWS account, with the Regions it covers (Administration › Connections).

#Access levels

The access level of a connection's role is the ceiling for everyone, administrators included. Grants can narrow it per user or group but never widen it.

Whatever the level, the role reads table data only from the buckets in the stack's DataBucketNames parameter (every bucket by default) and uses only the KMS keys in DataKmsKeyArns besides the AWS managed keys. With Query it writes only query results, to the SQLark results buckets and the buckets in ResultBucketNames; with Full it may also write to the data buckets, for CREATE TABLE AS and INSERT (parameter reference). If your tables are governed by AWS Lake Formation, grant the connection's role data permissions there; Lake Formation then applies to every SQLark query in that account.

Use Query unless teams really need to create or change tables through SQLark.

#Add a connection

1. Deploy the spoke role in the account (installation guide). How to connect another AWS account at the bottom of the page shows the steps, the template URL and an AWS CLI command. Set TrustedPrincipal to the hub stack's SpokeTrustedPrincipal output and choose an ExternalId secret of 16 or more characters.
2. Choose Add connection and enter:
   • Id: a short name used in links, for example production. It can't be changed later.
   • Name and AWS account id.
   • Access level: the same as the spoke stack's AccessLevel.
   • Role ARN: the spoke stack's RoleArn output, arn:aws:iam::<account>:role/sqlark/<RoleName>. It must be in that account and under the /sqlark/ path.
   • External ID: the same secret you gave the spoke stack. It is stored and never shown again; when you edit the connection, leave it empty to keep it.
   • Regions to show.
3. Save connection, then choose Test. The test assumes the role and, for each Region, reports the workgroups and databases it found, or the AWS error.

For more Regions of the same account, deploy the spoke template again in each Region with CreateRole set to Disabled and the same WorkgroupName: it adds a SQLark workgroup and results bucket there, and the existing role may use them. Then add the Region to the connection.

Each distinct AWS account counts against your plan's account limit, the hub account included (3 on Starter, 10 on Team, 50 on Business, unlimited on Enterprise). When the limit is reached, Add connection is disabled. Deleting a connection makes SQLark stop using the role, and grants on it stop applying; the role stays in the account until you delete its stack.

#Workgroups

Every query runs in an Athena workgroup, which decides where results are written, the engine version and the most a query may scan.

#The SQLark workgroup

With CreateWorkgroup set to Enabled (the default), the hub stack creates a workgroup in its Region named after NamePrefix (sqlark by default), and the spoke stack one named WorkgroupName (sqlark by default) in each Region you deploy it to. Each has:

• its own encrypted results bucket, <name>-results-<account>-<Region>, which keeps result files for ResultsRetentionDays (30 by default; SQLark's own history is kept longer);
• enforced settings, so no client can change its result location, encryption or limits;
• a per-query scan limit, BytesScannedCutoffPerQuery (1 TB by default, about $5): Athena cancels a query once it has scanned that much;
• CloudWatch metrics.

People see it only once a grant names it, for example with the workgroup pattern sqlark.

#Existing workgroups

SQLark can use your existing workgroups too:

• If the workgroup writes results to an S3 bucket, list that bucket in the stack's (or spoke stack's) ResultBucketNames parameter, so the role may write there. Without it, queries in that workgroup fail with an access error.
• A workgroup whose results are managed by Athena needs no bucket. Results can't be downloaded as a file there (people export them from the grid instead), are kept for 24 hours, and can't be reused by Athena.
• A workgroup with neither a result location nor managed results is refused ("This workgroup has no query result location. Ask an administrator to set one.").
• Disabled workgroups aren't shown.

We recommend workgroups that enforce their settings and set a per-query scan limit. SQLark never overrides a workgroup's result location.

#Grants

A grant gives a user or a group permissions on the databases matching some patterns, in the workgroups matching other patterns, on a connection (or all) in some Regions (or all) (Administration › Grants). A new grant, or a change, applies on the user's next request.

In both kinds of pattern, * matches any run of characters and ? exactly one.

#How grants combine

• A user's permissions are the union of every grant that matches them, through their email or any of their groups, capped by the connection's access level and the plan.
• Browsing ignores workgroups: a user sees every database matched by any of their grants in that connection and Region.
• Querying is decided per workgroup. In a given workgroup, a user can query the databases of the grants whose workgroup patterns match that workgroup, and no others. A grant on workgroup A with database X plus a grant on workgroup B with database Y lets the user query X in A and Y in B, but never X in B, and a query in A can't read tables of Y.
• Within one workgroup, a query can join tables from every database the user may query there.
• A user with no matching grant gets "not found" for a database or workgroup: SQLark doesn't reveal that it exists. A database they can see but not use in that way answers "forbidden".
• Administrators have every permission everywhere, still bounded by each connection's access level and the plan.

#How grants are enforced

Grants are enforced twice. SQLark checks them on every request, and every AWS call runs with temporary credentials (15 minutes) that a per-request session policy limits to the user's grants: the Athena workgroup, the Glue databases and tables the user may query in that workgroup, the workgroup's result location, and, only with ddl, the Glue actions that change those databases. Even if SQLark's own check had a bug, AWS would refuse anything outside the grants. Athena checks the same credentials when it reads the Glue Data Catalog, your S3 data and KMS keys.

If someone's grants would make a session policy larger than AWS allows, SQLark refuses the request ("too many grants") rather than widening it. Combine grants: use groups, and patterns such as sales_* instead of many single names.

#Examples

#Settings

Administration › Settings:

#Usage

Administration › Usage shows what your teams ran through SQLark over the last 30 days:

• Per day: queries, failed, cancelled, reused, data scanned, engine time and the estimated cost, with the total estimated cost of the period.
• Per user and per workgroup (Business and above): the 50 users and the 50 workgroups that scanned the most data over the period, with their queries, data scanned and estimated cost.

Each query is counted once, when it reaches its final state (succeeded, failed or cancelled), however many people or panels were watching it. A query Athena answered from a reused result counts as reused and scanned nothing; a result SQLark showed again from someone's own earlier run starts no query and isn't counted again. Estimated costs use the Price per TB scanned setting and Athena's 10 MB minimum; your AWS bill is the record. Usage is kept for 400 days.

#Audit log

Administration › Audit log lists events by UTC day, newest first, with the time, the actor, the action, the target and the source IP. Open an event for its details. Filter this page searches the events shown.

Events are kept for your plan's audit days: 30 on Starter, 90 on Team, 365 on Business and 3,650 on Enterprise. Each event is also written to the API's CloudWatch log group, /aws/lambda/<NamePrefix>-api, kept for the stack's LogRetentionDays, so you can forward it to your SIEM.

Every query, read-only ones included, is in the query history, which administrators can see for everyone, day by day, under History › Everyone.

For a full trail, CloudTrail in each account records every Athena, Glue and S3 call SQLark makes with sourceIdentity set to the user's email and the role session name sq-<email> (S3 object-level calls need CloudTrail data events).

#Licence and about

Administration › Licence and about shows your licence, your plan and the installed version.

#Licence

SQLark reads your AWS Marketplace licence from AWS License Manager in the hub account. The page shows the status, the plan, the entitlements, when the licence is valid until and when it was last checked. Check licence now reads it again (SQLark otherwise rechecks every 10 minutes).

Without a valid licence every page except this one says a licence is required. Nothing is deleted; subscribing again unlocks SQLark.

#Plan

What your plan includes lists the limits and features of your plan:

Features your plan doesn't include show a plan badge, and SQLark refuses them. To change plan, see Changing plan.

#Version and updates

The version card shows the installed version and, when update checks are on (UpdateChecks), the latest release, its summary and release notes, whether it is a security release, the oldest supported version, and How to upgrade with the template URL and an AWS CLI command. A banner on the Administration pages announces a newer release once. See Upgrading.