Two SQL Server DB For Two Purposes

I have a SQL Server Express database for use with a program that uses plugins. The users of this system are pretty reliable, as they'd be a highly specialized group of people who wouldn't be writing any deliberately malicious plugins (the only people they'd hurt would be themselves). Still, I'd like to make it easier for people not to make mistakes.

In the database, there are a series of core tables that should only be written to by some core models, should never be altered, and should never have deletions (except during testing). Additionally, there are some number of other tables for use by the plugins. That set of tables can grow, but normally, they are only used for reading and writing. The plugins might also need to read from the core tables under some circumstances.

This got me thinking that I should split the DB in twain, with one DB holding the core tables and the other DB holding the other tables. There could then be one connection for the core models, which would be read/write on both databases, while there would be a second connection that would be read/write on the extra tables and read only on the core tables.

The one issues with that is that all changes are done in a transaction. The transaction gets passed around to all who need to participate, and transactions come from connections. So, there would be cases where the core models would need to write something to the core tables, while in the same transaction, plugins would need to write to the extra tables. If the connection that got passed around was based on the core connection, then it would have a login with read/write permission to the core tables, so plugins would be able to write to the core tables, which is not desirable. It's not horrible because of the trust in the users, but it does allow people to do bad things by mistake.

One alternative would be to use the plugin credentials for the connection and transaction, but that would mean that the core models wouldn't be able to write to the core tables, which they would need to do, so that alternative wouldn't work.

The only other alternative that I see would be to start two transactions, one for the core models, and one for the plugins. There's a good means in the program to do that, so that wouldn't be an organizational problem. If either transaction decided to rollback, then the other would rollback as well. Since the core would only be writing to the core tables, and the plugins would only be writing to the extra tables, it seems like deadlocks should be avoidable.

What I'm wondering is whether or not there is a better way to handle this, and if there's something obvious I'm overlooking? Starting a connection would start two transactions. If one rolled back, the other would roll back. If one committed, the other committed. One would only write to one DB (though it might read from the other DB), the other would write to the other DB, and wouldn't interact with the other DB in any way.

It still means two transactions, though. Is there a better way?

I'd use one DB... but multiple owners.... So maybe the core items belong to the CORE owner, and the plugin ones belong to the default dbo
We use this concept in the app I'm working on. Wee have a REPO owner (called schema in Oracle) so the main tables belong to that owner. Then there's another DRAFT where we copy items from REPO to while they are worked. This keeps REPO clean while the service that accesses it can still use it, and if anything breaks, it happens in DRAFT. Once it's complete in DRAFT, it then goes through a workflow that pushes the changeset back over to REPO.
Something similar could be done in your case. By having different owners, you can segregate the tables and control how they are interacted with. It would also limit visibility using the SQL Server Explorer. And since it's all in one database, transactions are a breeze. The big change would be in how you access tables... instead of

Code:

---

SELECT * FROM someTable

---

You would do

Code:

---

SELECT * from core.someTable st inner join anotherTable aTbl on st.id = aTbl.Fkey

---

There someTable is in the core owner, while anotherTable is dbo.


-tg
(disclaimer - I think I have tht right, it's been a while since I've done this with SQL Server)

Yeah, that's a much better idea. I really haven't worked with owners much, having only recently been exposed to them. That solution would solve all the issues I had with my proposal, and would be VASTLY easier to implement (since the DB already exists as a single DB).

Agreed. Avoid multiple databases like the plague, particularly if you're going to have transactions that span them These will have to be have to be "distributed" transactions which are very performance hungry (have a google for "three stage commits" if you're curious) and will mean you'll have to admin some sort of distributed transaction coordinator (MSDTC in the case of SQL Server). It's a load of baggage you'd be better off without.

Personally I'd go for multiple schemas rather than multiple owners as it's a bit more light weight and should give you everything you need (obvious separation of business areas, ability to set access levels etc). It's kinda a subjective preference though.

Well, cool. Guess I have a bit of research to do.

Well, that's been interesting. Thanks to the changes to schema and ownership over the years, and the immortality of the internet, I was able to read well into a few sites that were kind of confusing, only to find that they were writing about SQL Server 2000. We really need date stamps on things.

Anyways, after a bit of thought, I split the DB tables into three schema, leaving the vast majority in dbo, but adding two others. Eventually, I realized that I probably would have been fine with just one, but what's done is done.

However, let me confirm something: If a connection starts a transaction, then every SQL statement within that transaction will operate at the level of privilege of the connection. I can live with that, but it's not totally ideal. What would be best would be if I could open a transaction, and within that transaction, some code would be allowed to write to table A, B, and C, while other code would only be able to write to A and B, but not C.

What this means is that, if ANY user of the transaction has to write to C, then ALL users of the transaction must be allowed to write to C. What I CAN do is prevent any user of the transaction from altering C, or even deleting from C, but they can insert or update, if they want to. It would be better if I could say "you can write" or "you can't write" for different code within a single transaction, but there's no place for such a gate keeper to live, that I can see.

Quote:

---

What would be best would be if I could open a transaction, and within that transaction, some code would be allowed to write to table A, B, and C, while other code would only be able to write to A and B, but not C.

---

That sounds... wrong. In a transaction, either the whole thing completes or the whole thing fails. It's not possible for a only bit of it to work. It sounds like you want different behaviour depending on a user's level of privilege but that's not really a transaction thing.

Can you give a more concrete use case of what you're trying to do?

No, it's not quite like that. It's a plugin system, so I don't even necessarily know who will be doing what. The way it works is that somebody calls the HandleMove method (as an example, but it's practically the ONLY example), which is part of one of the core models. If all goes well, this will start a transaction, perform a series of writes and raise a series of events, at least one will be in the transaction, while the rest come after the transaction completes. The reason for the event while the transaction is open is that other mods may also want to write to their own tables as part of the transaction, such that if anybody rejects something (which should have been checked for earlier, but I won't necessarily have a say in it), then their inserts or updates will roll back, as well.

It's not as much of a free-for-all as it might sound. By the time anybody writes anything, it should have all been checked. Every module has ample opportunity to check and reject any proposed action before the transaction opens, so anybody rejecting something in the transaction would be bad form, at best.

The thing is that there are some core tables that nobody other than the core models should write to. Anybody can read them, but only certain people should write to them. This can work on the honor system, which is what I've always been counting on. The data is far from sensitive, and the users are specialized....and perhaps apathetic, so I'm not trying to prevent malicious actors, I'm just trying to nudge people towards doing the right thing. Letting any mod do whatever is the way I did have it, and now I have it that only core models can alter/delete from any of the core tables, while mods can write only to the dbo tables, but in that transaction, anybody can write to any table.

The alternative would be to not allow anybody else to write in the transaction aside from the core models (mods are plugins, it's not short for model). Then, everybody else writes when the core model raises the Transaction Complete event. However, if at that time, one of the mods found that they couldn't write, for whatever reason, they'd have to tell everybody else to undo whatever they had done. This would potentially be a race condition, and would be something of a nightmare. Doing all crucial writes that need to all succeed or all fail, in the same transaction is a far better solution.

Writing for plugins can be entertaining.

There's lots of stuff in there I'm not understanding.

I get the impression that more than one person would be doing work in the same transaction, have I understood that correctly? If so there's a fundamental miss-understanding there. Transactions belong to one user (on one connection) and allow a set of database operation to complete in their entirety of fail in their entirety. There's simply no facility to have multiple users in one transaction.

I also get the impression that you're expecting some of the operations in the transaction to pass and some to fail. That's not right. Either everything passes or everything fails.

I think I might still be miss-understanding what you're trying to do but it sounds like you want something that's a higher level than a database transaction. More like a business process where different users are moving a piece of work through a process by interacting with it as different events?

Anyway, here's some thoughts that you might find useful (or I might be teaching granny to suck eggs):-

• Your security can be policed at various levels of granularity and you can mix them up. So you could give everyone access to your whole core schema, or access to individual tables, and you can separate it by read and write. Similarly, you could give access to other schemas to individual users.
• A transaction exists on a connection - so all operations in a single transaction will have to be on the same connection.
• Your plugins can connect under the identity of the user who's using them or under their own "application" identity. If they connect as the user then they could all work in the same transaction if they're sharing the same connection. If they connect under different identities then there's no way you could achieve this.
• Note that security doesn't have anything to do with security per-se, but if security prevents one operation in a transaction from committing then the whole transaction must roll back.

Huh, FD's take away was different from mine. My impression is that there's two use cases. One for the primary system and one for plugin usage.

The way I understood it is that there are a set of tables, which we'll call CORE, that the system will need to read from and write to. At the same times, there are plugins to the system that also need tables in the database. But he doesn't want these plugins to be able to write to these CORE tables, only read from them. But they do need to be able to write to other tables that belong to the plugin.

Possible solution would be to have two users: one for the system itself, and one for use by the plugin. Then, don't let the plugins initiate the connection directly. Have the plugin request a connection from the application, which would then return a connection using the plugin user. The plugin user would then only have read permissions to the CORE tables, while having R/W permissions to the other tables.

As long as any given transaction is under either any ONE of those two connections, it should work. But as FD noted, a transaction can't be split across two user connections. (there's actually a caveat to this, but doesn't apply in this case).

-tg

Quote:

---

FD's take away was different from mine

---

Ah, yours makes more sense than mine though.

Assuming you're right then you just need to make sure that the plugins log in under a user that doesn't have write access to Core. I like your idea of having the main application manage the connections for both itself and the plug ins. It's basically behaving as a connection factory and keeps things neat.

TG has it pretty much right, and there actually IS a connection factory already.

However, in the interim, I did a lot of studying to put the schemas in place and realized I was kind of wrong about what I was doing: There are darn near NO tables that can be restricted as to who writes to them. I was able to find only about three out the entire database that would only be written by core models, and those three tables are too trivial to worry about. Heck they can be freely wiped clean without harming anything (they'll just rebuild as a side effect, if that happens).

So, this has still been pretty useful. For one thing, I spent the entire weekend tinkering with things, which allowed me to find and fix more than a dozen bugs that were waiting around to bite me. I also did implement two schemas and the connection factory now spits out a third connection. Things are better organized this way.

What I ended up doing is this:

The core connection string is the only one that can alter or delete from all tables. The only tables it will ever delete from are those trivial tables that will rebuild, but that's a different matter. There's no reason for it to alter anything, either, but it CAN.

The other schema is just dbo. The other two connections can be obtained by any plugin, and gives the ability to read/write to any table. They can also alter/delete from dbo tables, but not core tables. They may well alter the tables, but only if other plugins don't object (it will be up to the plugin writers to play nice with each other, and since this will be a relatively small sandbox, the users will be totally willing to knock sense into any writer who doesn't play well with others).

As to why there are two connections, the idea is that a set of plugins will need only read access. Some installations should not be allowed to write (managers), so there will be installations that only have read-only access, but can still use plugins that only require read access.

Basically, the program is for managing fish hatcheries. No two are the same as to what data they care about and how they track it. Making a one-size-fits-all program has proven to be pretty costly (millions), which is beyond the reach of most who would like to manage that sort of data. My goal is something that will act like Mr. Potato Head: You get a base unit and put whatever face you want on it. The bases unit manages a few models for how rearing units work (in a general sense) and how animals move through the system (also in a generalized fashion), beyond that, it's all plugins. If you want to measure the fins on fish, it's a plugin (don't laugh, I can point to examples, and even explain why it was done), if you want to have the program feed the fish for you, that's two plugins. If I'm not saying, "this is all you can measure, and this is the way you have to record it", then it is better for everyone. It's proven to be entertaining.

Every step of the way, I wanted to expose functionality such that a person could opt to replace elements as much as was possible. So, while there is a means of tracking growth, people can swap in different growth functions for temperature dependent growth, density dependent growth, and anything else that suits them. Also, plugins are generally tiny and quick to write. Most have taken me a day, or so, with many taking an hour, or even less, to add significant functionality.

I've learned alot, too...I just wonder if anybody else will ever benefit from it?

Plug ins are an excellent way to scale this up:D

I'm surprised he went with plugins when plug fins are clearly the way to go.

-tg

Quote:

---

Originally Posted by techgnome

I'm surprised he went with plugins when plug fins are clearly the way to go.

-tg

---

There's a much nastier inside pun to be made there, but it's waaayyy too inside for anyone to get it. Basically, FINS is a group that has done this poorly.

However, nobody has ever been able to refrain from making pun acronyms about anything fish related. It is greatly helped by Fishery Information providing the F and I of either Fish, Fin, or fiduciary incompetence.