as normally every developer takes care about it but even though there is possibility to use it in queries.
so if you wants that no query with "select *" should work then you can do it using just single statement.
add a column to table on which you don't want "select *" to be run.
like:
alter table tablename add column DummyColumn CHAR(1) NULL
now deny rights to run "select" our newly added column named "dummycolumn" for user.
DENY SELECT ON OBJECT:: dbo.yourTableName(DummyColumn) TO your_user;
now if you will try to run
Select * from tableName
you will encounter this error:
The SELECT permission was denied on the column 'DummyColumn' of the object 'YourTableName", database 'YourDatabaseName', schema 'dbo'.
The only way to use this table is by passing column names with select like
select firstcolumnName, secondecolumnName from yourTableName
There are a few things that this user cannot do such as: COUNT(*), COUNT(1) or even SELECT 1 from this table. But there is a way around it. Replacing the * or 1 with the primary key on that table gives the desired results.
I agree it seems too much work to make sure that the users don't use SELECT *, but if you really want to enforce it, here's one way to go about it. But if you know better then please suggest me..
No comments:
Post a Comment