HAS_PERMS_BY_NAME( ) problem? incorrect? when explicit permission granted to columns

Question

HAS_PERMS_BY_NAME( ) problem? incorrect? when explicit permission granted to columns

James McCall

Old Hand

Points: 335
More actions
December 17, 2009 at 10:56 am

#214761

If I grant UPDATE on some table columns to another user or group but do not explicitly grant UPDATE on the table then it is possible to update the table.
However the result of HAS_PERMS_BY_NAME( '<tablename>', 'OBJECT', 'UPDATE' ) is 0! Surely this isn't correct? According to MSDN, HAS_PERMS_BY_NAME( ) "Evaluates the effective permission of the current user on a securable."
This is quite easy to see. Create a table and grant column level update permission to another user:
CREATE TABLE[test_perms_table] (
[abc]VARCHAR(5),
[xyz]VARCHAR(5)
)
;
GRANT UPDATE ON [test_perms_table] ( [abc] ) TO <another_user>
;
INSERT INTO [test_perms_table] VALUES ( 'a', 'x' )
;
Now, when connected as the other user ...
SELECT has_perms_by_name('[<table_owner>].[test_perms_table]', 'OBJECT', 'UPDATE' )
;
We see that the result is 0. Implying that we cannot update the table. But we can update it no problem:
UPDATE [test_perms_table] SET [abc] = 'b'
;
I have a workaround, which is to also check the column permissions, but surely HAS_PERMS_BY_NAME( ) is not working as advertised! Any insight into this? I tried Googling of course but after trying quite a few different search terms came up with nothing, which is surprising.
James

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

James McCall (12/17/2009)
If I grant UPDATE on some table columns to another user or group but do not explicitly grant UPDATE on the table then it is possible to update the table.
However the result of HAS_PERMS_BY_NAME( '<tablename>', 'OBJECT', 'UPDATE' ) is 0! Surely this isn't correct? According to MSDN, HAS_PERMS_BY_NAME( ) "Evaluates the effective permission of the current user on a securable."
This is quite easy to see. Create a table and grant column level update permission to another user:
CREATE TABLE[test_perms_table] (
[abc]VARCHAR(5),
[xyz]VARCHAR(5)
)
;
GRANT UPDATE ON [test_perms_table] ( [abc] ) TO <another_user>
;
INSERT INTO [test_perms_table] VALUES ( 'a', 'x' )
;
Now, when connected as the other user ...
SELECT has_perms_by_name('[<table_owner>].[test_perms_table]', 'OBJECT', 'UPDATE' )
;
We see that the result is 0. Implying that we cannot update the table. But we can update it no problem:
UPDATE [test_perms_table] SET [abc] = 'b'
;
I have a workaround, which is to also check the column permissions, but surely HAS_PERMS_BY_NAME( ) is not working as advertised! Any insight into this? I tried Googling of course but after trying quite a few different search terms came up with nothing, which is surprising.
James

Same situation, but you change the update to this:

UPDATE [test_perms_table] SET [xyz] = 'b'

Does the update fail in this case since the "other user" does not have update privs to the table or column?

James McCall Old Hand Points: 335 More actions · Answer 2

Lynn Pettis (12/17/2009)
Same situation, but you change the update to this:
UPDATE [test_perms_table] SET [xyz] = 'b'
Does the update fail in this case since the "other user" does not have update privs to the table or column?

Trying to update [xyz] fails as expected.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Okay, based on the fact that you are assigning permissions to the column level the value returned by the call to has_perms_by_name at the table level is correct. The user does not have update permissions to the table. Since you are assigning permissions to the column level, you need to test at the column level.