Beware of End-User Permissions in Databases

Beware of giving end users direct access to your databases, e.g. by using SQL Server integrated security in single-layer or Client/Server architectures.

If end users have permissions on database tables they may bypass your application logic and read or update the data directly (ex: via accessing them from Excel or attaching tables to MS Access).

Using app specific (user-neutral) credentials and hiding them on the client machine (in Registry, encrypted) is no safe solution either, because you cannot hide secrets on client machines (where do you store the encryption key?), if you do not have extremely strict control over them.

Generally I consider it best to use an architecture with 3+ layers, having the client communicate with services (having permissions in the database) running on managed servers. A 3-layer architecture can even be deployed securely on a single machine by running the service under a separate (domain) account (with credentials not known to the end users) and connecting to the database via integrated security.

If you really need to give end users direct access to your database you should restrict access to your data via a complete stored procedure layer (see Stored Procedures Pros & Cons).  Users might still misuse these in wrong sequence.

About Peter Meinl

IT Consultant
This entry was posted in Computers and Internet and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s