Table-level security

 

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.