The database layer is where the database administrator (DBA) manages database-level security by assigning privileges to protect objects such as roles, tables, indexes, graphs, views, synonyms, and actions in single databases. Database administrators can also enable accounts to be database administrators for specific databases or to manage specific database objects. Some objects have additional privileges, such as index rebuild and procedure execute.
A database is a security container. Thus, each database has its own independent security settings. Database level security (DLS) maps roles to privileges on objects in the database.
DLS cannot span across multiple databases, but synonyms allow specific objects from other databases to be treated as local database objects. A synonym can be used to assign multiple names to the same object within a database and it can reference an object outside the database, which makes the external object appear as part of the database.
DLS privileges manage which accounts can create DBAs and other types of database objects.
Note Server administrators and the ADMIN account can also manage database-level security, though because they have server-wide privileges, it is best to manage database security with a DBA account.
An action privilege can be applied to any object that can run code, such as a stored procedure, trigger, function, job, task, etc.
Accounts and roles are server-level objects that are visible at the database level but not managed at the database level.
DLS and graph objects
A graph is a new type of database object that is fundamental to the graph data model. It defines relationships between two or more tables. It is a named join, or a named set of relationships between tables. It makes it easy for queries to navigate across tables. SQL should be enhanced to reference a named join in its FROM clause.
Named joins can improve database performance and speed the evaluation of SQL queries. The database can track the performance of named joins. It can optimize named joins in advance. It can recommend things a DBA can do to optimize join performance. The metadata in the relationship object defines which fields in each table contain the foreign keys that can join across the tables.
Graph objects along with their metadata are stored in FAIRCOM.FCS, which makes this information visible in the database’s SQL dictionary.
The GRAPH privilege is similar to the SQL REFERENCE privilege except that a graph identifies specific fields in multiple tables to create specific relationships across any number of tables.
The tables within a graph must exist within the same database. A synonym can reference a table outside the database and this allows the external table to be treated as if it were part of the same database.
DLS and SQL
SQL only provides the DBA, CREATE_RESOURCE, and REFERENCE database-level security privileges. These SQL grant and revoke commands are ignored. In FairCom DB, these privileges are managed outside of SQL. SQL throws an error when an account does not have the appropriate privileges to do an operation.
SQL can manage the following database-level security privileges:
- ALTER
- INDEX
- REFERENCES
- WITH GRANT
Note that SQL is constrained by DLS, but has limited ability to configure it.
Database objects
Role
Role objects can grant the create, grant, delete, alter, or impersonate privileges to accounts. Roles exist at the server level or the database level and are unique within the level they are defined. This means that server-level roles are only used to defined the privileges assigned to server-level administrators to manage accounts, roles, and databases, whereas database-level roles are used to define the privileges assigned to database-level accounts.
| Privilege | Description |
|---|---|
| Create | The create role privilege allows the account to create a uniquely named role. The account that creates a role owns the role. The role owner can assign and remove accounts to and from the role, modify the role's metadata, delete the role, give the role's admin privilege to other accounts, and impersonate a role. |
| Grant | The grant role privilege allows an account to grant role privileges to another account to manage a specific role or roles. |
| Delete | The delete role privilege allows an account to delete a role. Deleting a role immediately revokes its privileges form all accounts that have been assigned to that role. |
| Alter | The alter role privilege allows an account to add or remove accounts, privileges, and metadata from a role. An account with the alter privilege can add any account to a role, but can only add an object privilege to a role when it is a server administrator account or when it has the create or admin privilege for the objects. A role's name cannot be changed since that is its unique identifier. |
| Impersonate | The impersonate role privilege allows an account to temporarily behave as if it only had the privileges assigned to the role. |
Table
Table objects can grant the read, insert, update, delete, create, alter, join, or grant privileges to accounts.
| 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 give another account some or all table privileges. |
| 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. |
Index
Index objects can grant the rebuild, create, alter, delete, or grant privileges to accounts.
Note The create, alter, delete, and admin privileges are only available to table and field owners of the table on which the indexes are created.
| Privilege | Description |
|---|---|
| Rebuild | The rebuild index privilege allows accounts to optimize database performance by rebuilding it. |
| Create | The create index privilege allows an account to create a uniquely named index. |
| Alter | The alter index privilege allows an account limited privileges to alter indexes. |
| Delete | The delete index privilege allows an account to remove an index from a database. |
| Admin | The admin index privilege allows an account to grant other accounts admin status over the specified index. |
View
View objects can grant the read, create, alter, delete, or grant privileges to accounts.
| Privilege | Description |
|---|---|
| Read | The read view privilege allows an account to read the data in the specified view. |
| Create | The create view privilege allows an account to create a uniquely named view. |
| Alter | The alter view privilege allows an account to modify the stored query that produces the view and to modify its metadata. |
| Delete | The delete view privilege allows an account to remove a view from the database. |
| Grant | The grant view privilege allows an account to give another account some or all view privileges. |
Synonym
Synonym objects can grant the same privileges as the object for which it is a synonym. For example, a synonym for a table has table privileges. This allows an external database object to appear as if it were a local object.
Action
Action objects can grant the execute, create, alter, delete, or grant privileges to accounts.
| Privilege | Description |
|---|---|
| Execute | The execute action privilege allows an account to take the specified action. If an account triggers an action and does not have the execute privilege, the action will not run but instead logs an error. |
| Create | The create action privilege allows an account to create a uniquely named action. |
| Alter | The alter action privilege allows an account to modify the specified action. |
| Delete | The delete action privilege allows an account to remove an action from the database. |
| Grant | The grant action privilege allows an account to give another account some or all action privileges. |