Accessing SQL tables

From DreamFactory
Jump to: navigation, search
DreamFactoryTutorialsAccessing SQL tables
 
(18 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
### Tutorial
+
=== Tutorial ===
  
−
Setting up role-based access to SQL tables
+
Setting up role-based access to SQL tables.
  
−
### Background
+
=== Background ===
  
−
Roles govern HTTP access to the REST API endpoints in DreamFactory. When you set up a SQL database, no tables are accessible by default (unless you are a DreamFactory Admin).
+
Roles govern HTTP access to the REST API endpoints in DreamFactory. When you [set up a SQL database](https://wiki.dreamfactory.com/DreamFactory/Tutorials/Connecting_to_SQL) in the 'Services' tab, no tables are accessible by default (unless you are a DreamFactory Admin).
  
−
You can expose table access by [[DreamFactory/Tutorials/Setting up user roles|setting up user roles]].  
+
You can give access to specific SQL tables by following the examples below.
  
−
an end user authenticates and receives a JWT token, her role determines which API endpoints she can access.
+
=== Example - Enable HTTP access to SQL tables for a role ===
  
−
Roles tie together end users, applications, and services (REST API endpoints) in DreamFactory. More specifically, end users are associated with roles, and roles are associated with both applications and services (REST API endpoints). This data model grants explicit end user access to services in the context of specific applications.  
+
:1. Log into the DreamFactory admin console as an Admin.
 +
:2. Click on 'Roles' > 'Create'.
 +
:3. In the 'Access' tab, select your SQL database API for 'Service', the table name for 'Component', HTTP verbs for 'Access', and API for 'Requestor'. Repeat for each table you are exposing to this role. <br><br>
  
−
Applications in DreamFactory can have multiple roles. For example, your application might have different classes of users (i.e. user roles) with different API permissions. Applications can also have a default role, which specifies public API access. For example, you might expose some API endpoints without requiring any authentication.  
+
Note: You can also allow script-only access to tables. This allows you to grant access to a table by server-side scripts invoked by the API call, while not exposing direct access to the table by the API.
  
−
Likewise, end users can have multiple roles. For example, end user John Doe might have different API permissions for different applications.
+
<br>
  
−
### Example - Create a new role in the DreamFactory admin console
+
[[File:Roles-sql.png|1000px]]
  
−
1. Log into the DreamFactory admin console as an Admin.
+
=== Example - Creating and updating role permissions with the REST API ===
−
2. Click on 'Roles' > 'Create'.
+
−
3. Fill out role information, service access, and lookup keys (optional). Click to create the role. <br><br>
+
  
−
[[File:Create-role.png|1000px]]
+
You can create and update role permissions directly with the API. Click on 'API Docs' in the DreamFactory Admin Console and view the '/system/role' API calls. <br>
  
−
### Example - Create a new role with the REST API
+
[[File:Swagger-role-cu.png|1000px]]
  
−
You can operate on roles directly with the API. Click on API docs and view the 'System' API calls. <br><br>
+
For example, the POST below to the /system/role API will create a role called 'ios-address-book' with full HTTP API access to the contact table and the contact_group_relationship table.
  
−
[[File:Swagger-role.png|1000px]]
+
<source lang="javascript">
 +
{
 +
    "resource": [
 +
        {
 +
            "name": "ios-address-book",
 +
            "description": "Role for users accessing the iOS address book app",
 +
            "is_active": true,
 +
            "role_service_access_by_role_id": [
 +
                {
 +
                    "service_id": 6,
 +
                    "component": "_table/contact",
 +
                    "verb_mask": 31,
 +
                    "requestor_mask": 1
 +
                },
 +
                {
 +
                    "service_id": 6,
 +
                    "component": "_table/contact_group_relationship",
 +
                    "verb_mask": 31,
 +
                    "requestor_mask": 1
 +
                }
 +
            ]
 +
        }
 +
    ]
 +
}
 +
</source>

Latest revision as of 10:49, 2 September 2020

Tutorial

Setting up role-based access to SQL tables.

Background

Roles govern HTTP access to the REST API endpoints in DreamFactory. When you [set up a SQL database](https://wiki.dreamfactory.com/DreamFactory/Tutorials/Connecting_to_SQL) in the 'Services' tab, no tables are accessible by default (unless you are a DreamFactory Admin).

You can give access to specific SQL tables by following the examples below.

Example - Enable HTTP access to SQL tables for a role

1. Log into the DreamFactory admin console as an Admin.
2. Click on 'Roles' > 'Create'.
3. In the 'Access' tab, select your SQL database API for 'Service', the table name for 'Component', HTTP verbs for 'Access', and API for 'Requestor'. Repeat for each table you are exposing to this role.

Note: You can also allow script-only access to tables. This allows you to grant access to a table by server-side scripts invoked by the API call, while not exposing direct access to the table by the API.


Roles-sql.png

Example - Creating and updating role permissions with the REST API

You can create and update role permissions directly with the API. Click on 'API Docs' in the DreamFactory Admin Console and view the '/system/role' API calls.

Swagger-role-cu.png

For example, the POST below to the /system/role API will create a role called 'ios-address-book' with full HTTP API access to the contact table and the contact_group_relationship table. 

{
    "resource": [
        {
            "name": "ios-address-book",
            "description": "Role for users accessing the iOS address book app",
            "is_active": true,
            "role_service_access_by_role_id": [
                {
                    "service_id": 6,
                    "component": "_table/contact",
                    "verb_mask": 31,
                    "requestor_mask": 1
                },
                {
                    "service_id": 6,
                    "component": "_table/contact_group_relationship",
                    "verb_mask": 31,
                    "requestor_mask": 1
                }
            ]
        }
    ]
}
Retrieved from "https://wiki.dreamfactory.com/index.php?title=DreamFactory/Tutorials/Accessing_SQL_tables&oldid=34555"