iOS Open Database Connectivity SDK

 Follow AugSoft on Twitter

ODBC App Security

vs. Legacy Web App Security

In legacy web apps, each script usually had access to a shared username and password embedded somewhere in the code so that all transactions, on behalf of all web users, ran under more or less the same MySQL account. Typically, when such servers are hacked (via SQL Injection or bugs in the scripts or PHP/Ruby interpreter), the hackers may setup ongoing database monitoring, safe in the knowledge that admins are unable to easily update the database password embedded in such scripts without exhaustive retesting.
In the new generation of ODBC apps, each desktop or mobile device receives its own account, perhaps created by a simple CALL to a Registration (stored) procedure on first launch after app installation (ie, upon detecting that NSUserDefaults has nil values). A CFUUID token may be passed as an argument to the Registration (stored) procedure in exchange for the unique username and password that the app then stores in the device's Keychain for pass back to the server in all subsequent connections. In this way, no database password is ever hard-coded into the client or server side app and attempts to analyze the app binary will fail to reveal any password.

SQL Security

vs. Legacy Web Server Security

On the world-wide-web and sometimes on internal networks, a whole array of dangerous programs may anonymously connect and use well-known protocols over open firewall ports to interact with the php/ruby/python/asp/cfm interpreter and scripts (both off-the-shelf and custom), that have known and unknown issues of varying severity. A web app compromise of any kind usually reveals the master database password (see Web App Security above), allowing the attacker to easily login to the database.
In the world of ODBC, apps call a closed-source industry-standard API that internally connects to a router using a proprietary protocol on a custom port that, in turn, connects to the database using the vendor's official high-speed ODBC driver. It is therefore never necessary to open ports on your firewall to allow direct connection to your database (except perhaps from the single IP address of the ODBC Router).

Using Stored Procedures to Maximize Security

Objective C based apps work best when they are combined with easily created stored procedures that are remotely invoked by the app via ODBC using standard CALL queries. These stored procedures typically provide the only way for an app to interact with the underlying tables and views in the database (which remain protected from any direct access by any user via database permissions settings). The stored procedures may also add a WHERE username=USER() type clause to their internal queries to ensure that they only operate upon data in the underlying tables and views pertaining to the currently logged-in database account. (Alternatively, rather than adding such a username type column to each of the app's underlying tables and views, the stored procedures may instead be configured to accept the mobile device's username/password as arguments so they can be validated against an internal table of authorized users via a nested stored function.) In some database systems, such as ORACLE, it is also possible to use so-called row-level security to achieve a similar, albeit less portable, effect. In this way, if a device is lost, stolen or hacked, it will only ever have access to the specific server data that pertained to the device's owner and only until the password is changed, or the device is remotely wiped; the device will not ever have access to the entire database as typically happens when a web app is hacked.

Setting up an (optional) Registration Server

If it is not practical to assign a unique username and password to each user ahead of time (as is often the case), it is possible to create a simple "new-user registration" server using a single stored procedure that programmatically generates a unique username/password, passes it into a Proxy User Map (that transparently maps potentially hundreds of thousands of unique usernames/passwords to a shared database account that's never revealed to any remote device or script), or simply calls the traditional CREATE USER to enter the new account directly into the database server's list of authorized users, or otherwise that INSERTs the new username/password into a application table that's internally checked by all other stored procedures using a common stored function. From there the new username/password is then emitted from the registration stored procedure as a result set which the iPhone app receives in an NSDictionary and can then copy into the device's encrypted keychain for future sessions, without the user even being aware they have a username and password. (Tip: When such "internally generated" usernames are chosen, be sure to provide a way for the user to discover the username from another iOS device they own, perhaps using Apple's iCloud Key-Value store.) Unless a Proxy feature is used, then after obtaining the mobile device's user new account, the registration stored procedure you create on your database must also GRANT the new account access to the specific other stored procedures (or perhaps tables and views) that the iPhone app will query using standard SQL (CALL, INSERT, UPDATE and/or DELETE). Some database systems, such as MySQL also have tremendous security features that may be enabled at this same point in your code, such as limits on the number of queries that the user may issue per hour, the number of UPDATEs that the user may perform per hour, the number of connections that the user may make to the server per hour and the number of simultaneous connections the user may have to the server, features not typically available in the web apps of yesteryear. In this way, connecting to the database without specifying a username/password will only allow access to the Registration stored procedure, effectively restricting unauthorized use to just creating bogus accounts that only have access to their own data and that are easily identified and removed via a periodic DELETE query on the server (much like spammers may frequently register easily removed bogus accounts on most web sites).
© August Software, USA., 1990-2017. All Rights Reserved.