Hi Thank you for the feedback. The answers to your first two questions are as follows: 1. "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"? is the most accurate description although it would be acceptable if the changes are not replicated (see 2 below). Additionally the 'admin' users of the application would also use the application as normal users and they would not be administrators in the AD context - that is their admin tasks would only be to make changes to the data in the AppAdmin table.
2. The risk of invalid data or data loss in the application database of the single installation does exist. This would be acceptable as long as it is not replicated. The database could be restored by (manually) pushing a snapshot from the central server. It would still be best to at least prevent or warn regular users from doing this via the application. Regarding the ACL approach: if the user has sysadmin privaledges on their local database installation, could they remove the deny permissions themselves? Lastly, as an aside, we are not using annonymous subscriptions and the subscriber machines have to be added manually to the publisher's subscriber list (not every machine on the network/subnet will be added to this list). Although from what you have said about the database files below, it would seem that these things only reduce the risk of data poisoning and cannot prevent the application users doing it. On 1/4/07, Thor (Hammer of God) <[EMAIL PROTECTED]> wrote:
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" <[EMAIL PROTECTED]> 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. > >
