Friday, November 18, 2011

Wish List IV - Read only and mean it

Did you know that the SELECT privilege actually allows you to do a SELECT ... FOR UPDATE ? Yes, that means a user with just that privilege could lock an entire table.

I want a SELECT_NOLOCK privilege. The user can still do a SELECT, but they cannot have a lock on the table. Okay, while an SQL is executing, I guess that get the shared table lock. But that's it.

1 comment:

Anonymous said...

One workaround is to create view with a dummy aggregated column and grant SELECT on the view instead of a table.

CREATE VIEW V AS SELECT * FROM(SELECT t.*,count(1) over (partition by
1) x FROM t)