Improved Data Security with MySQL Privileges and DreamFactory

DreamFactory and MySQL

All MySQL installations naturally include a root account and offer the ability to create restricted user accounts. However, otherwise sane developers will often use these root accounts for application-level communication, dramatically raising the likelihood of data theft, data exfiltration, and other security issues. For that reason the DreamFactory team always recommends users take care to create restricted MySQL users before using the platform to generate APIs.

In this tutorial, you’ll learn how to create a non-root MySQL user and then further restrict this user’s privileges to a specific database and even table subset. You’ll also learn how to subsequently revoke a user’s privileges to reflect changing requirements.

Continue reading “Improved Data Security with MySQL Privileges and DreamFactory”

Changing An API Key For One of Your Apps In DreamFactory

So what happens if you make a mistake and expose your admin app api_key or just need to change api_key associated with one of your apps?  We have an easy workaround that doesn’t require you to have to change any of your endpoints or having to recreate an app, etc.  This article shows you how to access all of your app API keys via MySQL or, if you haven’t fully started exploring DreamFactory yet, the default SQLite database.

Continue reading “Changing An API Key For One of Your Apps In DreamFactory”

How To Configure An ELK Stack With DreamFactory

DreamFactory has had support for Logstash since version 2.3 for our Gold Tier version.  Elastic makes some great tools to support very robust logging.  Incorporating Elasticsearch, Logstash and Kibana into your powerful, scalable DreamFactory instance is a no brainer, especially for users who have a lot of data being pushed and pulled through various endpoints.  This will make the lives of your admins so much easier with the amount of detail they can grab to troubleshoot issues. Continue reading “How To Configure An ELK Stack With DreamFactory”

Automatically build APIs from your Oracle database using DreamFactory

DreamFactory will save you and your company hundreds of hours by auto-generating an API automatically for every major SQL database, including Oracle. In this tutorial, we’re going to walk through the process of connecting DreamFactory to your Oracle database, review the features automatically built into DreamFactory that’ll help you manage and customize your API, and go over scripting support and security features. Continue reading “Automatically build APIs from your Oracle database using DreamFactory”

NoSQL, No Problem!

 
LeeHicksYou may have already seen that DreamFactory Service Platform (DSP) supports SQL databases with its REST API, but did you know that we also support most of the popular NoSQL databases? Currently, DSPs support Amazon Web Services’ SimpleDB and DynamoDB, Windows Azure’s Tables, MongoDB, MongoHQ, and CouchDB.

While the technology behind, and the interface in front of, each of these services vary, some dramatically, they each support storing data and lots of it. Some already have a REST or somewhat  REST-like HTTP interface to access them, which may mean storing your access keys or authorization information on the client and passing them along with every call. Some do not have an HTTP interface at all and must be accessed through server-side code that you have to go find and install or write yourself. Some allow you to design the primary keys or indexes of your storage tables, some do not. Some support JSON natively, some do not. In fact, the formats supported for data entry and retrieval (think filtering) vary almost as widely as the number of vendors. Get the drift? Different creators, different creations. But you say “I just want a simple way for my app to get my data in and out, quickly and easily.” That’s where I think we can help.

NoSQL as a Service

DSP Services allow you to “on-board” web-based or locally running services that you want to use from your app and access from one authenticated REST API. The access to these services can be controlled by app and by user role. There is more on this and how to provision these services in our documentation here.

The same goes for the supported NoSQL services. We securely store any credentials required to access your storage on the server-side, relieving your app from maintaining it. We remove any cross-site hoops your app would have to handle by making it a “native” REST service on the DSP. We even go a step further and provide a common, fully REST-based API to each of the supported NoSQL service types and use a common data format layout, using JSON, for data entry and retrieval. Where we were able, we also provide a common SQL-like filtering language, making it dirt simple to find the data you need.

We like to call this our “blend layer”. The idea is, just like a DAO (data access object), we simplify the process to use your data. In fact, we have made this so “blended” it is nearly identical to our SQL DB REST API, which we think is powerful but simple to use. So how does this help you? Well, you write the app once, and store your data in your favorite storage. If or when your “favorite” storage changes (i.e. if something cheaper or faster comes along), your app and data will only require minor changes (if any) to keep chugging along.

So what does this wonderful interface look like? Well, since you asked so nice and all. Once you get your service setup on your DSP (using the documentation mentioned earlier, or the vendor-specific follow-up blogs to this blog), you can use our Live API documentation interface to see how it works. Of course, you can use cURL or your favorite REST testing browser app, it is up to you. Since I am such the promotionalist, I’ll use the Swagger interface in this blog to show you.

As with the other DSP services, we put a mostly standard meaning to the HTTP verbs in our REST API. Here is the how they are used for NoSQL services (using “mongo” as our demo service).

DreamFactory MongoDB Rest API

Note that the ‘POST’ is used for creating new entities, ‘PUT’ is used for replacing the whole content of an entity and ‘PATCH’ is used to merge in changes to an entity (‘PATCH’ and ‘MERGE’ HTTP verbs are used interchangeably in the REST API). Also, as with the other DSP services, if your transport layer only accepts ‘GET’ and ‘POST’ verbs, using a ‘POST’ command while setting either a url parameter, ‘method=PATCH’, or a header, X-HTTP-Method = PATCH will “tunnel” the command through properly.

Table Administration

Azure calls them “tables”. So does AWS, that is for DynamoDb, but for SimpleDb, they are called “domains”. For CouchDb, they are called “databases”, not to be confused with MongoDb’s “databases” which house what they call “collections”. See what I mean?? While each vendor calls them something different, we will refer to them collectively as “tables”. These are the things that group your sets of data together or partition them, however you see it. You can actually manage most aspects of the table administration through the REST API.

To get a list of currently available tables, just send a GET request to the service’s root, which, if you are using one of our free-trial DSPs, looks like this.

https://dsp-mydspname.cloud.dreamfactory.com/rest/my_service_api_name_here

Every DSP service, when queried at its root with no extra url parameters, returns an array of resources available by that service. In the SQL and NoSQL case, these resources are table names.

{
   "resource": [
      {
         "name": "test"
      },
      {
         "name": "zipcodes"
      }
   ]
}

To include vendor-specific table information, if any is available, you can send ‘include_properties=true’ as a url parameter in the request. You can also limit the returned data by specifying the tables by name, using the ‘names=table1,table2,table3’ url parameter. The returned data will include a table array, where each object in the array includes at least the ‘name’ field and value, as well as, any available vendor-specific properties of the table, i.e. keys, etc.

https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo?include_properties=true&names=test%2Cuploads
{
   "table": [
       {
           "name": "test",
           "indexes": [
               {
                   "v": 1,
                    "key": {
                       "_id": 1
                   },
                   "ns": "test.test",
                   "name": "_id_"
               }
           ]
       },
       {
           "name": "uploads",
           "indexes": []
       }
   ]
}

Use this same data format for creating new tables, or updating and deleting existing tables. You can also use the ‘names’ url parameter in a delete request to delete existing tables.

Record Administration

Again, whether they are called “entities”, “documents”, or “items”, we collectively call them “records”. These are the groups of name-value pairs that make up your app’s data. The DSP REST API provides the following ways for your app to retrieve, create, update and delete data from the tables.

Retrieving Records

When it comes to making your NoSQL data available to your app, our REST API provides several different methods to get just the data sets you want, when you want it. Here are the available options for a GET request on a particular table.

table get request

The table name is sent as part of the url, while the other options are sent as url parameters. If url parameters are not your cup of tea, you can send most of them as posted data (using POST request with X-HTTP-METHOD = GET as mentioned earlier. Note that the “record” parameter can only be sent in this way.). Most of them are self explanatory, but a few may need clarification.

  • ids” – This is a comma delimited list of unique identifiers (think primary key) values for retrieving multiple records at a time. If passed as POST data, this can also be an array of ids. In the case where they is no static identifier field (DynamoDb), use  of the “id_field” parameter is necessary to indicate which field these values belong to.
  • filter” – This is where you use our simple SQL-like filter string. Comparison operators supported on every platform are ‘ = ‘, ‘ != ‘, ‘ > ‘, ‘ >= ‘, ‘ < ‘, ‘ <= ‘; or as their SQL short form ‘ eq ‘, ‘ ne ‘, ‘ gt ‘, ‘ ge ‘, ‘ lt ‘, ‘ le ‘. Depending on the db vendor, others such as ‘ contains ‘, ‘ like ‘, and ‘ begins_with ‘. Spaces surrounding the operator is required. String values on the right side of the comparison must be within single of double quotes. Depending on the db vendor, logical comparisons (AND, OR, NOT) are also supported. More on the differences in later blogs. The whole filter string must be url-encoded.
  • record” – Used only in POST data, this option allows you to send partial (minimally the identifier fields) or complete records back to the database to be “refreshed” with the latest values. This is also helpful in instances where the table has multiple indexes, i.e. DynamoDb and Azure Tables.

For example, if we want to find the first 3 records in the zipcodes table information that have a population of over 20,000 people, returning only the city, state and percentage over 40 years of age. If you are familiar with SQL, this would look like “SELECT city,state,PopulationOver40 from zipcodes WHERE pop > 20000;”. Here is what it looks like in our REST API.

https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes?filter=pop+%3E+20000&limit=3&fields=city%2Cstate%2CPopulationOver40

And this is what gets returned…

{
 "record": [
   {
     "_id": "28659",
     "city": "NORTH WILKESBORO",
     "state": "NC",
     "PopulationOver40": 9.19
   },
   {
     "_id": "31201",
     "city": "HUBER",
     "state": "GA",
     "PopulationOver40": 99.877
   },
   {
     "_id": "71291",
     "city": "WEST MONROE",
     "state": "LA",
     "PopulationOver40": 80.099
   }
 ]
}

Creating Records

Use this same data format above for creating new records. One could post an array of records, or a single record like this…

POST https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes
   {
     "_id": "29684",
     "city": "STARR",
     "state": "SC",
     "pop": 2889,
     "PopulationOver40": 82.4
   }

If the DB vendor does not automatically create the primary key, it must be included in the POST request. The applicable keys are always returned for successfully created records on a create request.

Updating and Merging Into Records

The same array of records or a single record format with changes is supported for updating records. Using the PUT HTTP verb as mentioned above will replace the whole record with the posted data if found by matching identifiers in the record.

PUT https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes
   {
     "_id": "29684",
     "city": "STARR",
     "state": "SC",
     "pop": 2890,
     "PopulationOver40": 79.4
   }

If you only want to merge changes into a record without having to reset everything, then use the MERGE or PATCH HTTP verb and send only the changes along with the identification fields.

PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes
   {
     "_id": "29684",
     "pop": 2890,
     "PopulationOver40": 79.4
   }

If you only want to update or merge data for one record, and it can be identified by a single key field, then you could also add the id to the end of the url, and pass only the fields that require change.

PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/29684
   {
     "pop": 2890,
     "PopulationOver40": 79.4
   }

Two other methods of merging data are by id list or filter (using “ids” or “filter” url parameters mentioned above for retrieving records). In these cases, if not natively supported, the server will query the table for the filtering results, merge in the record changes and push the changes back in an update request. This is an easy way of updating multiple records with the same field-value changes.

Deleting Records 

Deleting is similar to updating records but no posted data is required.

More to Come…

Stay tuned for a series of blogs on each of the supported NoSQL services and how to use them in your next awesome app.

Adding a Remote Web Service to Your DSP

 
ToddAppletonThe DreamFactory Services Platform (DSP) provides a flexible and powerful backend for your mobile apps. If you don’t have a DreamFactory account, sign up here. One of the key features is the ability to integrate any number of remote web services, and access them directly through the DreamFactory REST API. In this blog post we’ll walk through the process of setting up and invoking a remote web service. Continue reading “Adding a Remote Web Service to Your DSP”

Connecting your DSP to External File Storage

 
Sometimes your application needs to access to external file repositories.  We’ve added a super simple way to communicate with Amazon S3 and Windows Azure Blob Storage by setting up a Remote File Service. In this short tutorial, we’ll show you how to quickly set up a Remote File Service. First log into your DreamFactory Services Platform (or “DSP” for short). If you don’t have a DreamFactory account, you can sign up for one here. Set Up a Remote File Service in 60 Seconds Once you’re in the DSP Admin Console, click on the Services menu. Then click on the “Create New Service” DSP services menu Then enter information about the service.
  1. In the Type dropdown select “Remote File Storage.”
  2. Enter a Service Name. The Service Name will be displayed in the Admin Console. You can edit the service later after you’ve created it.
  3. Enter an API Name. The API Name will be used when making REST calls within the DSP.
  4. Enter a Description of the service (optional).
  5. In the Storage Name field enter the folder name where your content is located on either Amazon S3 or Windows Azure.
  6. In the Storage Type dropdown, select either Amazon S3 or Windows Azure. See the directions below for adding your S3 or Azure key credentials.
  7. Check the Active box to enable the service.
Amazon S3 Users: Add Your Amazon S3 Keys For Amazon S3 you’ll need to provide your Access Key and Secret Key. Amazon S3 You can find these values by logging into your AWS account and selecting Security Credentials from the My Account / Console dropdown menu. AWS security credentials Windows Azure Users: Add Your Windows Azure Access Keys Windows Azure Storage requires an Account Name and Account Key. Windows Azure You can find these values in the Windows Azure Portal.  Once you’ve logged into your portal, select the Storage menu item, then select Manage Access Keys on the bottom of the screen.  Then copy the Storage Account Name and Primary Access Key into the Account Name and Account Key fields in your DSP. windows azure portal DSP access keys Now Test Your Connection! Now it’s time to test your connection to remote file storage. Select the Manage Files menu item in the Admin Console.  You will now see a new entry for the DSP service name you just created. DSP admin console Double click on the service name to view your remote files. DSP remote files And you’re done! Now your application can access all the remote files located in the specified Amazon S3 or Windows Azure folder. Learn More For more information on DreamFactory Services Platform or to set up your own Free DSP visit http://www.dreamfactory.com.