SQL & how different databases implement it

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.