The table level is where the table administrator (and occasionally database administrator) manages table-level security by assigning privileges to accounts. Table-level privileges control what an account can do with records and fields in one table in a database.
| Privilege | Description |
|---|---|
| Create | The create table privilege allows an account to create a uniquely named table. |
| Grant | The grant table privilege allows an account to modify the row privileges of any record on the table as well as give other accounts or roles some or all table privileges. This privilege can turn an account into a table administrator account. |
| Alter | The alter table privilege allows an account to modify a table's field structure, its file attributes, and its metadata. |
| Delete | The delete table privilege allows an account to remove a table from the database or specific rows from a table. |
| Join | The join table privilege allows an account to join a table to another table. |
| Read | The read table privilege allows an account to read all rows in a table. To restrict access to specific rows, use row level security. |
| Insert | The insert table privilege allows an account to insert rows into a table. |
| Update | The update table privilege allows an account to update all rows of a table. |
TLS and Table Owner
TLS has the built-in concept of a table owner. The account that creates a table becomes its owner. A table owner automatically becomes a table administrator. A table administrator always has all table privileges. This is useful for end users who work directly with tables to do analytics. For better application security, it is best for a DBA to create tables and then assign limited privileges to the application account.
TLS and SQL
SQL can manage most of the table-level security privileges. It cannot manage the RLS Admin privilege. SQL can GRANT or REVOKE the following table privileges to an account or role:
- SELECT
- INSERT
- UPDATE
- DELETE
- WITH GRANT matches TLS_GRANT
Note that SQL is constrained by RLS, but cannot configure it.
The JOIN privilege is similar to the SQL REFERENCE privilege except that JOIN uses a named graph rather than specifying which fields can be in joins. The database engine ignores the SQL REFERENCE privilege and enforces the JOIN privilege.
TLS Implementation Details
Table-level security is defined in JSON and stored inside each table as a resource. Within the JSON, account and role names are stored as strings. This makes table-level security more portable between databases.
FairCom adds the following JSON metadata to a resource record in each table. This resource defines the privileges granted or denied to each account or role. FAIRCOM.FCS also contains this information. The database’s SQL dictionary is a view on top of FAIRCOM.FCS. This information is stored in the table for portability and stored in FAIRCOM.FCS for centralized management and compatibility with SQL.