Hey there...
First off, how about give us some perspective as to the true meaning/impact
of "regular users must not be allowed to change this table." Do you mean in
the context of regular, trusted, non-malicious data access as in "it
shouldn't be easy for users to change this data by accident" or do you mean
"no matter what, the AppAdmin table cannot ever be changed by anyone other
than our admins, particularly against malicious intent as doing so has
significant risk to other replicated members"? Also is there any risk to a
single installation where someone changes the AppAdmin data, even if it is
not replicated?
If your answer is the latter of the two, then you're right to consider not
trusting the data in the client AppAdmin table for non-admin users and
having one way transactional/snapshot replication. Regardless of how
in-depth your security model for the client is, all any user would have to
do is take the MDE file over to another installation and attach the db,
change the data, and copy it back (would require stopping and starting the
MSDE service, of course). Then whatever data they altered would get
replicated. Note that the propensity for this type of thing possible for
your non-AppAmind approved *users* but also for anyone who can physically
manipulate the MDE file itself, whether they are an app admin user or not.
In other words, if they can get to the MDE file somewhere, they can "poison"
the data.
If this AppAdmin table is critical, it may be wise to remove it from the
merge model altogether, and only allow changes at the server itself and to
push out those changes via trans/snapshot replication as you state earlier.
I'm sure you could also construct some sort of permission structure to
support your "stored procedure launching the replication" idea that but
that doesn't really prevent the poisoning of data as described above.
Now, if it's more simple or "standard" acl's you're looking at, remember
that explicit deny permissions would take precedence over implicit
permissions inherited from role membership - so, even if your model requires
escalated role privileges to initiate merge replication, you could use
windows group membership structures to deny write access to the AppAdmin
table to the user of the application.
And there's always the "ghetto" approach of programmatically imposing write
restrictions by something like an update trigger that does a transaction
ROLLBACK if the user does not belong to a particular group that you would
check via code... Something like that would silently drop changes to the
table unless your membership criteria were met. But again, that's strictly
an application-level approach that could be easily bypassed by the right
people.
T
Support ' or 1=1 --
and help secure SQL installations while ending legislative idiocy!
Visit http://www.apostropheOr1equals1dashdash.com to find out how.
********* RSA Training! *********
If you've got any interest in hard-core firewall/DMZ configuraitons,
Then check out Thor's "Hammer of God" Training at RSA 2007!
ISA Ninjitsu: Designing, Building, and Maintaining Enterprise Firewall and
DMZ Topologies with Microsoft ISA Server
https://cm.rsaconference.com/US07/catalog//profile.do?SESSION_ID=2434&form=s
earchform&ts=1167885409370
On 1/3/07 5:42 AM, "B Miszka" <bwmiszka@gmail.com> spoketh to all:
Hi Everyone
I am involved in a development project where we are hoping to use
Microsoft SQL Server replication. The system will consist of clients
running a custom application and MSDE 2000. The client MSDE
installations will be subscribed to a merge publication on a central
server SQL 2000 server. The users will typically work in disconnected
mode and be able to initiate synchronization from the custom
application when they can connect to the main server. The replication
is being configured using Enterprise Manager and every effort is being
made to stick with Windows Authentication (although most replication
guides/documents on the Internet seems to use SQL authentication).
For the purpose of this explanation, assume there is a table in the
database called AppAdmin containing data that certain users must be
allowed to change (insert, update and delete via stored procedures
created for this purpose). These changes need to be propagated to all
users. Regular users must not be allowed to change this table. Anyone
is allowed to make changes to the data in other tables and these
changes must be propagated to all users.
The difficulty is that the users (please correct me if I am wrong)
need to have the sysadmin role on their local database to initiate the
synchronization. They can be prevented from making certain changes to
their local databases via the application, but there is nothing to
prevent them from using another database access tool, making changes
and then synchronizing with the central database - and the changes
then being propagated to other users.
The AppAdmin table cannot be removed from the publication as then the
required users would not be able to update it - and additionally the
other users would not receive the changes.
A couple solutions considered so far (that did not seem optimal) are as
follows:
1. Create 3 publications instead of 1: A merge publication without the
AppAdmin table for regular users. 2. A snapshot publication of only
the AppAdmin table for regular users. 3. A merge publication of the
whole database for the 'admin' users. Set the allowed users for each
of these publications.
2. Use a stored procedure to initiate the synchornization that a non
sysadmin user would have permission to execute. (Not sure how to do
this or if this is possible).
What is the best way to deal with this situation? Any help, advice or
references to useful documentation would be appreciated. Thanks.
