SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

06 June 2007


SQL Server demands SELECT privilege to update data

Did you know that in order for a user of Microsoft SQL Server to INSERT data into a table, or to UPDATE or DELETE existing rows, not only does that user need the appropriate INSERT, UPDATE or DELETE privilege to be GRANTed to them, but also the SELECT privilege too.

This is not the case for other DBMSs. To INSERT data into an Oracle table, you only need the INSERT privilege. Take an example of an audit table which is just recording rows that contain information about the actions users are performing in the application. It seems entirely reasonable to restrict the database user associated with the application to just INSERTing data - that is all that it is required to do. Increasing the privileges of this user by also GRANTing SELECT privilege decreases application security.

This also highlights an important aspect of designing application security. The database user that is being used by the application to access the tables in the database should not be the owner of those tables, but instead be a specific user that is only granted the appropriate SELECT, INSERT, UPDATE and DELETE privileges. Do not grant all of these privileges on all of the tables in the database - be selective and you will increase application security by reducing the scope of what can go wrong if the application is abused or is in error.

This comment has been removed by a blog administrator.
Post a Comment

Subscribe to Post Comments [Atom]

<< Home



September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]