Inline:
On 1/3/07 11:54 PM, "B Miszka" <[EMAIL PROTECTED]> spoketh to all: > 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. Got it-- and the "admin" user context I referred to was indeed the sysadmin role you referenced - but I'm glad you cleared up any confusion with domain/local admin. > > 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. That's better than not, then... See below for more on thoughts about that- > > Regarding the ACL approach: if the user has sysadmin privaledges on > their local database installation, could they remove the deny > permissions themselves? Absolutely. Sysadmin has full rights on all objects in the db, both user and system. You But again, even if you took those permissions away, attaching to the db via another installation of SQL somewhere would grant someone full control over everything. I was once tasked with "breaking" the controls a company put around a local MLS database where they obviously went WAY out of their way to protect the SA account, db user account the application used, protecting data access, replication, permissions, etc -- I stopped MSDE and attached the db (sp_attach_single_file_db) and owned the entire db in a couple of minutes. This was particularly bad since the application developers decided to keep all *other* user data (username and passwords) in a table within the database. This was a total list of all users of the application in the entire county board of realtors. They did this because they thought the access to the local data was "safe." Not that you would do anything like that, but I tell you that so that you can see that any means to locally protect data like that can be easily subverted by anyone if they want to do it. I would take a hard look at not doing merge replication from any local source if possible-for this critical AppAdmin table at least- if it is that important. That or looking into app/server encryption methods to better ensure data integrity in that table. But even that won't really help if a malicious user has their hands on the app and the data together. > > 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. Bingo. If you give someone the data offline, at the end of the day, they can pretty much do whatever they want with it. If you are then going to take that data and merge it into your application for general consumption, you introduce a risk worthy of more consideration (which you are obviously doing here ;). If you really have to have merge/offline capabilities in the app for certain users in "admin" roles, you're probably going to need several methods to ensure data integrity (like encryption, checksums, hashes, etc) before you hork that data into your systems. 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/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. >>> >>> >> >> >> > >
