Welcome to EMC Consulting Blogs

A couple of years ago I worked on an issue with my colleague Steve Wright about an issue with merge replication.  You can read Steve’s original post here.  However, in summary, the issue boiled down to a security check that merge replication was performing in a generated view called MSMERGE_REPL_VIEW_<PUBGUID>_<ARTGUID>.

This week a colleague of mine, fellow MVP and owner of www.replicationanswers.com Paul Ibison, contacted myself and Steve and asked us to explain the issue in more detail for an article he was writing on the topic of NonConvergence in Merge Replication.  That article can be found here.  If you have the time it’s well worth a read as these issues when they hit you can take some time to get to grips with. Knowing that these issues exist is half the battle.  I digress.

The Objective

The objective of this post was to prove the scenario to Paul with a series of tests of the code that he had provided from that view in a comment on Steve’s post.

The line of code was:

where   ( 
        {fn ISPALUSER('BE1FE0F5-6804-4E23-84ED-84962FECB1F1')} = 1
        or permissions(2137058649) & 0x1b <> 0
        )

The ISPALUSER function is a straight check to see if the user in question is in the PAL.  The PAL stands for Publication Access List.  The MSDN article for managing the PAL can be found here.  If your user with whom you are connecting to the SQL Server Database isn’t a member of the PAL then you will fail this test and get back 0 rows from the view.  Well that is what we said but you can clearly see that there is also an OR statement there. Unfortunately, we didn’t deal with the OR statement thoroughly in the post and that was what Paul was interested in.

The OR statement performs a lookup of the permissions on the base table using the now deprecated function PERMISSIONS.  The MSDN article for the PERMISSIONS function can be found here.   It then uses the result of the PERMISSIONS function (an integer value) and performs a Bitwise AND comparison to see what permissions the current user has against the base table.  If the result of the permissions function has any one of the permissions that could be evaluated from the Bitmask hex value then the result would be non-zero and we would pass the security check.

So what does the 0x1b bitmask stand for.  As you will see in a minute when converted to an integer this equates to 27.  A quick look in the MSDN article gives me the following table.

Therefore the bitwise ADD was saying I needed any ONE of SELECT(ALL Columns), UPDATE (ALLL Columns), INSERT or DELETE to pass the security check. This is the only way to get the decimal bits to add up to 27.

N.B. As the PERMISSIONS function will be removed in a future version of SQL Server please do not use the permissions function in any of your new code.  You should use FN_MY_PERMISSIONS or HAS_PERMS_BY_NAME(TSQL) instead.

Scenario

Our scenario was that the application fronting this database was making changes at the Publisher.  However it did so via stored procedures. The application’s login was mapped to a user who had only been granted EXEC permissions on the procedures.  There it is. We already know how this is going to pan out.  We didn’t have the IsPalUser and our user didn’t have any of the permissions needed to pass the OR statement.

I therefore built a little test rig to confirm how this OR condition worked. The first thing was to create something safe and isolated.  The script below therefore creates a new database and a user in that db.  It converts 0x1b to decimal and then populates a small table (imaginative naming I grant you) and creates a stored procedure.  It then runs a suite of eight tests.  Once run it drops the database.

The Tests

• Test 1: No permissions set on table. (our scenario part 1)
• Test 2: 1 permission (DELETE) set on table.
• Test 3: EXEC permissions on Stored Procedure (our scenario part 2)
• Test 4: Using a non-applicable permission on the table
• Test 5: Partial Select Permission (1 column only)
• Test 6: db_owner Permission
• Test 7: db_datareader Permission
• Test 8: db_datawriter Permission

Test Code

CREATE DATABASE MergeTest
GO
USE MergeTest

CREATE USER TESTUSER WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo

CREATE TABLE dbo.TEST
(COL1 INT
,COL2 INT
,COL3 INT
)
INSERT INTO dbo.test
VALUES   (1,1,1)
        ,(2,2,2)
        ,(3,3,3)
GO
CREATE PROCEDURE testproc
as
    Select * from test
GO

EXECUTE AS USER = 'TESTUSER'

SELECT CONVERT(INT, 0x1b) AS BITWISE_AND_COMPARISON
--i.e. SELECT ALL,UPDATE ALL, INSERT AND DELETE

/*Scenario 1 no permissions set */
REVOKE ALL ON dbo.TEST TO TESTUSER

IF PERMISSIONS(OBJECT_ID('dbo.test')) & 0x1b <> 0
BEGIN
    Select 'PASS' AS TEST1_RESULT
END
ELSE
BEGIN
    select 'FAIL' AS TEST1_RESULT
END

/*Scenario 2 1 permission set */
REVERT
REVOKE ALL ON dbo.TEST TO TESTUSER
GRANT DELETE ON dbo.Test TO TESTUSER

EXECUTE AS USER = 'TESTUSER'
IF PERMISSIONS(OBJECT_ID('dbo.TEST')) & 0x1b <> 0
BEGIN
    Select 'PASS' AS TEST2_RESULT
END
ELSE
BEGIN
    select 'FAIL' AS TEST2_RESULT
END

/*Scenario 3 Stored Procedure as ObjectID */
REVERT
REVOKE ALL ON dbo.TEST TO TESTUSER
GRANT EXECUTE ON dbo.TESTPROC TO TESTUSER

EXECUTE AS USER = 'TESTUSER'
IF PERMISSIONS(OBJECT_ID('dbo.TEST')) & 0x1b <> 0
BEGIN
    Select 'PASS' AS TEST3_RESULT
END
ELSE
BEGIN
    select 'FAIL' AS TEST3_RESULT
END

/*Scenario 4 References as Permission */
REVERT
REVOKE ALL ON dbo.Test TO TESTUSER
GRANT REFERENCES ON dbo.TEST TO TESTUSER

EXECUTE AS USER = 'TESTUSER'
IF PERMISSIONS(OBJECT_ID('dbo.TEST')) & 0x1b <> 0
BEGIN
    Select 'PASS' AS TEST4_RESULT
END
ELSE
BEGIN
    select 'FAIL' AS TEST4_RESULT
END

/*Scenario 5 partial Select permission */
REVERT
REVOKE ALL ON dbo.Test TO TESTUSER
GRANT SELECT(COL1) ON dbo.TEST TO TESTUSER

EXECUTE AS USER = 'TESTUSER'
IF PERMISSIONS(OBJECT_ID('dbo.TEST')) & 0x1b <> 0
BEGIN
    Select 'PASS' AS TEST5_RESULT
END
ELSE
BEGIN
    select 'FAIL' AS TEST5_RESULT
END

/*Scenario 6 db_owner Permission */
REVERT
REVOKE ALL ON dbo.Test TO TESTUSER
Exec sp_addrolemember db_owner,testuser 

EXECUTE AS USER = 'TESTUSER'
IF PERMISSIONS(OBJECT_ID('dbo.TEST')) & 0x1b <> 0
BEGIN
    Select 'PASS' AS TEST6_RESULT
END
ELSE
BEGIN
    select 'FAIL' AS TEST6_RESULT
END

/*Scenario 7 table but with different bitmask evaluation */
REVERT
REVOKE ALL ON dbo.Test TO TESTUSER
EXEC sp_droprolemember db_owner,TESTUSER
Exec sp_addrolemember db_datareader,testuser 

EXECUTE AS USER = 'TESTUSER'
IF PERMISSIONS(OBJECT_ID('dbo.TEST')) & 0x1b <> 0
BEGIN
    Select 'PASS' AS TEST7_RESULT
END
ELSE
BEGIN
    select 'FAIL' AS TEST7_RESULT
END

/*Scenario 8 table but with different bitmask evaluation */
REVERT
REVOKE ALL ON dbo.Test TO TESTUSER
EXEC sp_droprolemember db_datareader,TESTUSER
Exec sp_addrolemember db_datawriter,TESTUSER 

EXECUTE AS USER = 'TESTUSER'
IF PERMISSIONS(OBJECT_ID('dbo.TEST')) & 0x1b <> 0
BEGIN
    Select 'PASS' AS TEST8_RESULT
END
ELSE
BEGIN
    select 'FAIL' AS TEST8_RESULT
END


REVERT
USE master;

DROP DATABASE MergeTest;

The Results

The results were as follows:

As expected for our scenario Tests 1 and 3 fail.  We didn’t have permissions set against the base table and we only had EXEC rights on a procedure that was acting on the table.  Our options were therefore to 1) Add our user to the PAL 2) Grant table level privileges to the user.

Test 2 passes because delete is one of the valid hashes.

Test 4 fails because the permission isn’t one of those in the bitmask

Test 5 fails because having only partial select rights means you don’t have the SELECT ALL permission

Tests 6,7 and 8 pass because by virtue of being members of those roles a user would have the required permissions.

Finally (Slightly OT)

I’d like to close with one final comment on Bit masking. You might be wondering what would happen if the PERMISSIONS function had returned a NULL and the bitwise AND had NULL & 0x1b to contend with.  Well we had that very scenario in Test 1! The PERMISSIONS function returned NULL and so the answer of the bitwise AND was in fact NULL.  However, we didn’t get a failure. Bear in mind that PERMISSIONS returns an integer.

So whilst this:

SELECT NULL & 0x1b

Generates the following error:

This does not:

SELECT CAST(NULL AS INT) & 0x1b

Happy Bitmasking!

Cheers, James

(Don’t forget about my earlier post on bitmasking which can be found here).