SQL Server 2000 And the IS_MEMBER() function.
I have implemented row-based security via checking whether a user is a member of a role that has permission to see certain rows that are flagged as secure. The implementation consists of checking if the user is a member of the appropriate role inside the stored procedure that serves the data via the IS_MEMBER() function. If the user is a member of the secure role, then the procedure returns the appropriate rows. If not, they can still use the same procedure to see rows that were not secured.
After installing a beta of the software at the client's site, I found a strange problem. None of the users that were assigned the appropriate permissions could get into the secure data. I checked and double-checked and triple-checked the permissions to make sure that they were a member of the appropriate role. No problems there. Then I noticed that my DBA had set everyone up as a db_owner to start with. I started doing some testing with the IS_MEMBER() function and found this:
If user is made a member of dbo or db_owner, then IS_MEMBER always returns 0 for any role other than dbo, regardless of whether the user is a member of that role or not.
Example Script:
SETUSER ‘MyDboUser’
PRINT 'The Current User is: ' + CAST(CURRENT_USER as varchar)
DECLARE @i int
SET @i = IS_MEMBER(‘MyCustomRole’)
IF @i = 0 BEGIN
PRINT 'The User is not a member of the role.'
END
ELSE IF @i = 1 BEGIN
PRINT 'The User is a member of the role.'
END
ELSE IF @i IS NULL BEGIN
PRINT 'The role was not found'
END
exec sp_addrolemember @rolename= ‘MyCustomRole’, @membername= ‘MyDboUser’
SET @i = IS_MEMBER('JUA')
IF @i = 0 BEGIN
PRINT 'The User is not a member of the role.'
END
ELSE IF @i = 1 BEGIN
PRINT 'The User is a member of the role.'
END
ELSE IF @i IS NULL BEGIN
PRINT 'The role was not found'
END
Output:
The Current User is: dbo
The User is not a member of the role.
‘MyDboUser’ added to role 'MyCustomRole'.
The User is not a member of the role.
This is very strange to me. I would think if IS_MEMBER was going to return a default value for dbo’s, it would be that they were a member of every role—the way this is set up now defies my understanding. Perhaps you know why IS_MEMBER was designed this way—if so, please enlighten the rest of us! J
The solution to this problem is simple--either grant db_owner access to the secure rows (bad solution) or finish the script to assign role-based security correctly, abandoning the db_owner for basic users (good solution). I still say the behaviour of the IS_MEMBER() function is strange in this case though. I thought about this some more, and came up with a--
Question: What happens when the user is a member of a role that is a parent of a child role?
Suppose I have a role called Parent, and a role called Child. I create a user ‘MyUser’, and add him to the Child Role. Then I add the Parent role to the Child role, granting the parent role all permissions of the child, plus additional permissions. Now, I call SETUSER ‘MyUser’. Finally, I call IS_MEMBER(‘Parent’) and IS_MEMBER(‘Child’).
SELECT IS_MEMBER(‘Parent’) – returns 0
SELECT IS_MEMBER(‘Child’) –returns 1
Now, I change the role assignments so that MyUser is a member of Parent, but not Child.
SELECT IS_MEMBER(‘Parent’) –returns 1
SELECT IS_MEMBER(‘Child’) –returns 1
So it appears that even though you cannot tell if a dbo has been assigned to specific roles, you can tell if one role has been assigned to specific roles. Strange.