Roles and Permissions
Roles and permissions are powerful, and with that power comes a certain degree of complexity. There are several different models in use, and the union of these is precisely what the user has access to do once they authenticate to the server. The flip side of the complexity is that CodaServer's security model is much more robust than other database platforms, meaning developers will generally not need to build their own application-specific security models. All authorization, authentication, and access control is handled in one place and in one common manner across all applications built on Coda.
There are 6 types of permissions in Coda:
- Server Permissions: These control what users can do within the CodaServer environment, and include such things as creating users, defining datasources, etc. These are assigned per user.
- Application Permissions: These control what users can do with a particular application, such as managing users and managing each of the different environments. These are assigned per user for each application/environment combination.
- Table Permissions: The typical create, read, update, and delete permissions giving users access to database tables. These are assigned to roles, which can then be assigned to users.
- Form Permissions: These give users access to forms at different points in their lifecycle dictated by form statuses. These are assigned to roles, which can then be assigned to users.
- Procedure Permissions: These give the user the ability to execute stored procedures. These are assigned to roles, which can then be assigned to users.
- User-Defined Permissions: Application developers can define their own permissions and assign them to users. These permissions can then be checked by your application code at runtime to control web page rendering, module access, or anything else. These are assigned to roles, which can then be assigned to users.
The first two types of permissions relate mainly to the CodaServer environment. Who can do what in the database itself? It is assumed that most application end-users will not need to (for instance) set up cron jobs or create new datasources, so they will not generally need to have these permissions.
The latter four types are more interesting for application end users, since they relate to permissions within the apps themselves. The table, form, and procedure permissions are very much like their SQL counterparts; they indicate what can be done on particular database objects. The user-defined permissions are slightly different. They give the application developer a chance to define arbitrary privileges for users. For instance, if an application has a reports section that should only be available to senior managers, the developer can define a permission called VIEW_REPORTS and assign it to the SENIOR_MANAGERS role. Within application code, he can poll the database for the list of permissions to see if the reports should show up for a given user.
Coda is very strict about requiring the use of roles to abstract out access control. It is impossible to assign any of the four application permission types to users directly. This is because roles force the developer to think about design up front, and it also prevents sloppy permissions from falling through the cracks. As people move around a company, it is much easier to revoke their old role and assign a new one than to copy over lists of permissions and hope you catch them all.
grant_server_permissions := GRANT <server_permission_name> [ , ... ]* TO <username> revoke_server_permissions := REVOKE <server_permission_name> [ , ... ]* FROM <username> server_permission_name := CONNECT | MANAGE_USERS | MANAGE_USER_DATA | MANAGE_GROUPS | MANAGE_TYPES | MANAGE_APPLICATIONS | MANAGE_DATASOURCES | MANAGE_SESSIONS | QUERY_SYSTEM_TABLES
These statements grant and revoke server permissions from users. The permissions have the following implications:
- CONNECT: This is a basic privilege needed by users to connect to the database. All users should have it.
- MANAGE_USERS: Allows user to add and remove users and grant them permissions.
- MANAGE_USER_DATA: Allows users to modify the biographical data for other users.
- MANAGE_GROUPS: Allows user to add and remove groups and assign users to them.
- MANAGE_TYPES: Allows user to add, modify, and remove data types.
- MANAGE_APPLICATIONS: Allows user to add and remove applications.
- MANAGE_DATASOURCES: Allows user to add and remove datasources.
- MANAGE_SESSIONS: Allows user to manage the sessions or other users.
- QUERY_SYSTEM_TABLES: Allows user to directly query the system tables using the SYSSELECT command rather than using the standard SHOW and DESCRIBE syntax.
grant_app_permissions := GRANT <app_permission_name> [ , ... ]* ON <application_name>[ .<environment> ]? [ FOR GROUP <group_name> ]? TO <username> revoke_app_permissions := REVOKE <app_permission_name> [ , ... ]* ON <application_name>[ .<environment> ]? [ FOR GROUP <group_name> ]? FROM <username> app_permission_name := CONNECT | MANAGE_USERS | MANAGE_ROLES | DEVELOPER | MANAGE_CRONS environment := PROD | TEST | DEV
These statements grant and revoke application permissions to users. These roles have the following implications:
- CONNECT: This is a basic privilege needed by users to connect to the application. It can be granted either globally across environments and groups, to all groups in a specific environment, to a group in all environment, or any combination of groups an environments.
- MANAGE_USERS: Allows user to add and remove users. It can be granted either globally across environments and groups, to all groups in a specific environment, to a group in all environment, or any combination of groups an environments.
- MANAGE_ROLES: Allows users to grant roles to users. It can be granted either globally across environments and groups, to all groups in a specific environment, to a group in all environment, or any combination of groups an environments.
- DEVELOPER: Lets the user run DDL statements against the DEV datasource. As it only applies to that datasource, it is global amongst groups and environments.
- MANAGE_CRONS: Allows the user to add and remove crons for the application. Since crons apply to all groups, only the environment is relevant.
CONNECT, MANAGE_USERS, and MANAGE_ROLES are granular and can be applied to give the user access to specific environments and groups. People given these specific permissions can only use them in the manner and scope granted; someone with MANAGE_USERS for group HUMAN_RESOURCES in PROD can't add users to the INFORMATION_SERVICES group in TEST, for example. MANAGE_CRONS is an environment-wide permission, so it should not have a group specified. As crons are a server feature, they can affect all data within an environment.
Finally, DEVELOPER is a special permission that lets the user run DDL (data definition language) statements against the development datasource. This permission is only meant for people who are actually building applications because anyone possessing it can see (and in fact destroy) all data within the DEV environment for the specified application. It should never be granted to an end user.
create_role := CREATE ROLE <role_name> [ DISPLAYED AS '<display_name>' ]? [ IN APPLICATION <application_name> ]? alter_role := ALTER ROLE [ <application_name> . ]? <role_name> [ SET DISPLAY '<display_name>' | RENAME TO <role_name> ] drop_role := DROP ROLE [ <application_name> . ]? <role_name> grant_role := GRANT ROLE <role_name> ON <application_name> . <environment_name> [ FOR GROUP <group_name> ]? TO <username> revoke_role := REVOKE ROLE <role_name> ON <application_name> . <environment_name> [ FOR GROUP <group_name> ]? FROM <username>
These commands create, alter, and drop roles. Roles belong to a particular application, and, once created, an be assigned to users. They are not exclusive; one user can have many different roles in an application, just as one individual may wear many different hats in an office. It is up to the developer to choose roles that make sense for their application. Some prefer to define roles as job titles, enumerating specifically which permissions each functional position within their organization will need to do their job. Others would rather make roles building blocks, defining them granularly and then assigning several to each user as their jobs require.
Like most objects in Coda, roles have an optional display name that is useful for making objects human-readable. The application name is optional as long as the session application is set; it will default to the current session's application if not set explicitly.
The GRANT and REVOKE commands assign the roles to individual users. They DO require explicit acknowledgment of the application and environment, just to be sure.
grant_table_perm := GRANT <table_permission> [ , ... ]* ON TABLE [ <application_name> . ]? <table_name> TO <role_name> revoke_table_perm := REVOKE <table_permission> [ , ... ]* ON TABLE [ <application_name> . ]? <table_name> FROM <role_name> table_permission := INSERT | UPDATE | DELETE | SELECT
Grants table permissions to a role.
grant_form_perm := GRANT <form_permission> [ , ... ]* ON FORM [ <application_name> . ]? <form_name> : <adjective_status_name> TO <role_name> revoke_form_perm := REVOKE <form_permission> [ , ... ]* ON FORM [ <application_name> . ]? <form_name> : <adjective_status_name> FROM <role_name> form_permission := VIEW | UPDATE | CALL
Grants form permissions to a role.
The different form permissions behave as follows:
- VIEW: Similar to SELECT in table permissions, view is required to see records of the specified status.
- UPDATE: Lets the role update records while they are in the specified status.
- CALL: Lets the role process records into the specified status.
grant_proc_perm := GRANT EXECUTE ON PROCEDURE [ <application_name> . ]? <procedure_name> TO <role_name> revoke_proc_perm := REVOKE EXECUTE ON PROCEDURE [ <application_name> . ]? <procedure_name> FROM <role_name>
Grants the ability to execute a procedure to a role.
create_permission := CREATE PERMISSION <permission_name> [ DISPLAYED AS '<display_name>' ]? [ IN APPLICATION <application_name> ]? alter_permission := ALTER PERMISSION [ <application_name> . ]? <permission_name> [ SET DISPLAY '<display_name>' | RENAME TO <permission_name> ] drop_permission := DROP PERMISSION [ <application_name> . ]? <permission_name> grant_permission := GRANT <permission_name> [ , ... ]* [ IN <application_name> ]? TO <role_name> revoke_permission := REVOKE <permission_name> [ , ... ]* [ IN <application_name> ]? FROM <role_name>
These commands create, alter, and drop permissions, and assign them to roles.
User-defined permissions are a handy way to give users permissions that extend beyond database objects. For instance, if an application has a reporting function, the developer could create a permission called VIEW_REPORTS and assign it only to those roles that need to view the reports.