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.
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).
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.
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, calls CREATE USER to enter that account information directly into the database server's list of authorized users --or instead into a
Proxy (that transparently maps the new username/password to a shared database account that's never revealed to the user), or 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 (for example, in a single-row of two columns, one username and one password) which the iPhone app receives in an
NSDictionary and can then copy into the device's encrypted keychain for future reference, perhaps without the user even being aware of its existence. (
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 and to automatically reset their password on demand .) Unless a
Proxy feature is used, then after obtaining the mobile device's user new account, the registration
stored procedure must also GRANT the new account access to the specific other
stored procedures (or perhaps tables and views) that the iPhone app will need to access. Some database systems, such as MySQL also have tremendous security features that may be enabled at this same time, 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.
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).
