MS SQL Permissions
-
Cue the music from Scream.
-
I should hope not. It's not like you're a special snowflake.
-
Why am I getting the impression that this is seen as a lot more complicated than it is?
DROP and CREATE is really quite simple:
If the object exists, drop it (otherwise, do nothing). This guarantees a consistent initial state, whether the object already exists or not.
Create the object.Such a script can be run any number of times in any environment. It can be used for rollback (as @Jaime pointed out) or it can be modified in order to provide a new version of the object.
This is completely aside from @blakeyrat's original issue.
However, on reflection, there's actually no good reason why he should need to see the object in question (if it is a sproc/view) - or even know it exists - when using such a procedure - provided he's not doing the naming.
The main danger stems from the fact that a script of this sort will work whether an object exists or not. The objects themselves, however, are cheap - as in: quick and easy to replace with "last known good" scripts. If a developer accidentally overwrites a different object than he was supposed to, because he got the name wrong - it is a question of developer negligence, but easily rectified.
On the permissions side of things, if we look at it from a minimum privilege perspective, all a developer needs to write such scripts are CREATE and DROP permissions. As long as the names are provided by someone who knows the entire schema, there's no need for a developer to see the objects he is creating.
(Note that all of the above only applies to easily replacable objects like sprocs and views. If you try it with tables, any damage will be purely self-inflicted.)
Isn't this level of permission anal-retentiveness pointless if you have CREATE rights anyway, since you could simply brute-force-enumerate the namespace by creating dummy sprocs and dropping them again? (This holds true for all nameable objects, not just stored procs: being able to create a name in the namespace implies the ability to enumerate existing names provided that you get a meaningful failure back when trying to create an object with a name that already is in use.)
-
It's "pointless" because you can defeat it by creating a bot and letting it slam your DB server for 500 hours looking for names.
In any case, maybe the bug is that objects can be delisted from Object Explorer at all.
-
Yes, but that's a point how?
-
Who are you talking to? I run ALTER / CREATE scripts the exact same way you do. I just take the time to look and figure out which one it should be rather than just dropping whatever was there before.
Also, who has multiple people editing the same deployment script at the same time? Do you have no source control at all?
Since I'm the one who bring up source control in like every second post I make - yes I use source control.As for the deployment script, there's a difference between a deployment script and "let's try this" object changes. Everything I do works fine. For every development effort that touches a given database, we make a dev instance of the database itself and a branch in source control that maps to it. The list of changes is simply a source control change history on the branch. When code review time comes along, the scripts might get changed. During initial testing, the scripts might get changed. None of this is a deployment script, so no, we don't have multiple people touching deployment scripts.
When it come time for QA testing, we merge the branch and resolve any conflicts. If any of the conflict are beyond trivial, we test internally again and fix whatever pops up. The deployment script is simply a compilation of all of the scripts that were modified. We concatenate all of them, in their entirety, and order them based on dependencies. This is all done with a tool. This is the deployment script, and no, it never gets modified. The tool also spits out a rollback script, which is simply a concatenation of the previous versions of the objects scripts along with a few DROP statements for files that are new for this deployment.
The point is... in order for this to work, all database object scripts need to be in source control and they need to be "deployment ready" at all times. No one really concerns themselves with "does this object exist" or "is this a change or a create" because we all have better things to worry about.
-
So your argument is... what? Nothing you're talking about impacts the OP.
-
No one really concerns themselves with "does this object exist" or "is this a change or a create" because we all have better things to worry about.
We have a similar, though less rigorous and automated process as you do.
So your argument is... what? Nothing you're talking about impacts the OP.
What was the OP, again? Wow, this may be the most on topic thread evar.
-
I never claimed to be on topic. I was simply responding to the question "Do you have no source control at all?". The question was one of those "Did you kill you mother?" type questions designed to be phrased in such a way that a lack of response looks incriminating. So, I responded to it.
-
I never claimed to be on topic. I was simply responding to the question "Do you have no source control at all?".
Yes, I know...I think you were really responding to @Matches . Nevertheless, you were still remarkably on topic. AT POST 158.
-
BTW, this whole discussion really is on topic (or maybe topic adjacent) since the behaviors blakeyrat observed are pretty common while working with MSSQL and the practices that a lot of us are bringing up are put in place so we can be as productive as possible in an environment that behaves this way.
-
If you want to get really naughty, let me know, and I'll show you all sorts of cool shit.
I want to get really naughty.
Also, I want to learn cool SQL shit.
-
If I can't even see that something exists, I shouldn't be able to overwrite it.
Then you could do some kind of dictionary attack, recreating views and dropping them until you got some permission denied error, to probe out all the hidden objects.
Filed under: If someone already said this, I'm sorry. I'm Doing It Wrong and replying as I read.
-
-
Also:
- If you really have sensitive information in your schema, you are doing it wrong and should be seriously ashamed of yourself.
- Security by obscurity is a myth, for the umpteen zillonth time.
-
Playing Devil's advocate: security by obscurity lessens the obvious attack surface, because you have to know something exists before you try to exploit it. Nobody said it has to be the only form of security (unless they're really clueless).
Filed under: even having a fuckton of armour on your tank is no reason to paint it bright red
-
An invisible tank would be pretty fucking cool.
-
But I also think running a blind DELETE or REPLACE statement is even more retarded.
Not all environments are like yours.
I am a production DBA and when I'm given scripts it's my job to execute them, first in test and then in production. When I'm given a script for a view definition by a third party vendor it invariably has an "if exists drop" at the start which makes sense. The alternative would be for the vendor to maintain both ALTER and DROP/CREATE versions of scripts and then supply both and get the DBA to execute the appropriate one.
Obviously a DBA should be able to work out the correct one to execute. Also, I have the permissions to see all objects and would be able to make the appropriate call. But is there an advantage to this approach? The databases and view definitions are the customer's and it's my job to execute the scripts they provide. I have a professional duty to:
- advise them if they're doing something that's against best practices;
- stop them from breaking the system;
- stop them from degrading performance on shared systems;
- etc
but I don't see this duty extending to stopping them from blowing away view definitions and, at the end of the day, they are their databases not ours.
-
shudder MSSQL could have a bug that killed my entire family and I'd still probably use it over Oracle or MySQL.
In the spirit of Trying New Things™ circa 2008, I decided to give MySQL a whirl (with the flashy new InnoDB engine that supported row-level locking!). After making a tiny toy database, I concluded that featurewise, it was comparable to about SQL Server 2000. I believe MS's licensing on SQL Server Express is still more generous than MySQL if you plan on making money from your app.
And how did they stop the developer from creating a "new" view that wasn't in fact new but overlapped with an older one by the same name and breaking everything?
People are ignoring the extremely dangerous accidental data loss problem here.
Production Support Guy: There's a page in the app that's erroring out in the new build.
Developer/DBA: Hmm, let's see the error. Oh, the page is expecting columns in the view to exist that don't. Some asshole must have overwritten the view. Here, let me revert it.
Production Support Guy: Thank you so much, you saved our asses! The phones were ringing off the hook for a whole five minutes!Now, it might be a more subtle bug that doesn't get noticed until months later - after it has done a fair amount of damage, but changing a view definition in a haphazard way is much more likely to break something and thus very likely to get noticed quickly.
You don't need access to the view data to be able to do this. You just need to check the sys.objects table. That's a pretty standard way of doing things.
I'm pretty sure* Object Explorer's implementation just looks at
sys.objectsanyhow, so it would be subject to whatever whims and access controls therein. I would be leery of any Object Explorer implementation that did not produce identical results to the system catalogs.He wants the MSSQL developers to figure out blakeyrat's list of pathological permission settings so that people can then complain about why the system is fucked up and won't let them do what they want to do.
An unrestricted matrix of combinations of various securables is also less error-prone and easier to test and implement than an equally large matrix with special "dead zones" for all the "stupid" configurations.
*Or information_schema.tables, or sysobjects, or whatever. Not in the mood tonight to hook up a trace to verify.
-
That's why he said "View or Sproc". Those things don't contain data. Notice the lack of "Table" in his list.
And yet some years ago much hilarity ensued when one of our developers (not me, thank you) ran a delete against a product view, not realising that it would actually delete the data from the underlying product table.Cue frantic shutdown of external access, and a morning of priority 1 support calls to find out if we would be supported in just restoring that one table from backup (rather than the whole database, which would wipe out several hours' worth of data entered by customers across the country).
He wasn't fired, but very shortly after that the access rights on views were changed.
-
-
I believe they're working on it...
-
I decided to give MySQL a whirl (with the flashy new InnoDB engine that supported row-level locking!).
Ooh, must research this. I haven't done any transactions yet in my MUD code but there's the chance of stuff becoming inconsistent if two players happen to load the main page at the exact same moment. Row-level locking would be sufficient for the few cases where this could be a serious flaw (mainly the inventory system).
-
Switching your tables to InnoDB will give you instant row level locking but it's not the whole story. You really need to be looking at transactions for that kind of thing (which InnoDB also gives you)
-
There's a bit of history to this. Years ago when I was in college I was working on a MUD engine then too, but it was in C#/ASP.NET/SQL Server Express. I wasn't nearly as experienced as I am now (my PHP one is far more developed on just a fraction of the time) and I'd basically wrapped the entire page in a transaction. The problem was if two people loaded the page at the same time, usually one of them would get an error about data changing during the transaction, never mind that it was stuff that couldn't deadlock such as the chat table.
So far I only anticipate issues with inventory, say there are 250 gold coins on the ground and two players manage to get the "pick up 250 gold coins" command to process at the exact same moment. Without transactions or locking, both of them will succeed :scratch_head:
The ideal solution IMO would be to somehow pause/block other instances of the PHP script if they hit a section where a transaction is already in progress. If locking could accomplish this, one of those players would get the row lock, player 2 would be blocked, #1's inventory would update, the room inventory would update, the lock would be released, #2 gets unblocked, and then player 2 is unable to pick up the coins because they're now gone.
There are probably other cases where weird things could happen but I don't think they'd be particularly abusable. Stuff like being able to talk to someone who's in a different room because he moved at the same time the chat messaging code is processing. No big deal there IMO.
The short story is I know I need to get something in, just haven't researched it yet because I wanted to get features in and with 2 - 3 people online the chances of error are pretty slim.
Filed Under: Can we get any actually useful emoji over here?
-
Php mutex lock
-
bakalah! mohammad jihad durka durka durka
Or something?
Start right at the 1:40 mark, run for 10 seconds...
-
An invisible tank would be pretty fucking cool.
They'd have to find a way to hide the tracks it leaves when it moves, too...
-
Nah, it's a fucking tank. Not a big deal.
-
So far I only anticipate issues with inventory, say there are 250 gold coins on the ground and two players manage to get the "pick up 250 gold coins" command to process at the exact same moment. Without transactions or locking, both of them will succeed :scratch_head:
Now, I don't know how much control you have with it being PHP and all, but I would strongly recommend making every change dealing with inventory to be transactional or at the very least serialized - it limits the potential for weird race conditions.
In my own project, I have a client to server RPC to request an inventory transaction. The server does some basic sanity checking on the request, and enters it into a queue for the requesting character server-side. Every tick, each character's queue on the server gets processed (one transaction at a time and one character at a time), and each inventory transaction is again validated and processed if valid. The updated character inventories are transmitted to all connected players.
-
That seems highly inefficient to do that much processing and retransmitting. Only public inventory should be transmitted. Other players don't need that data. The server should keep tabs on the inventory - but every tick seems excessive unless an event triggers a recheck.
Store the last known state, have a periodic check if you do gifting type transactions, trigger an event for validation of inventory when the player picks up/drops/gets out/puts away any item.
-
That seems highly inefficient to do that much processing and retransmitting. Only public inventory should be transmitted. Other players don't need that data. The server should keep tabs on the inventory - but every tick seems excessive unless an event triggers a recheck.
Character updates are already more or less delta compressed on a reliable update channel, so there won't be traffic except when the transaction occurs. I also neglected to mention that the server broadcasts back the inventory transaction itself rather than each item all over again, on the understanding that the client will apply them in the same sequence. The only times items themselves are transmitted are during character initialization, purchase, and monster drop.
I do have a distinction of "public" and "private" inventory (equipment slots vs backpack slots), so lazy initialization of remote inventory items is feasible, but it would complicate things. If the server sends a transaction like "move item ID #5736 in character 1234's backpack to helmet slot (0, 0)," the server would also have to establish the item at that point for all other players in range and keep track of who knows of which items. The same goes for when players place items in a trade window. RakNet's
ReplicaManager3makes this pretty easy, but it's still a bit of work setting up the rules for replication and delta compression.There is a chance that some items in the inventory will never see the light of day, and omitting them from character initialization could save some significant bandwidth. However, I have enough to work on as it is, so I'm going to put that one off until I have a hundred simultaneous players or more on the server, and saving a few hundred bytes per character initialization becomes worth serious money.
-
The ideal solution IMO would be to somehow pause/block other instances of the PHP script if they hit a section where a transaction is already in progress.
Only if you can tightly bound the amount of time they have to wait, otherwise you'll have to do something else (e.g., failing at the DB and trying again a little bit later). You'll also need to audit the order in which locks are acquired so that you don't get deadlocks. The DB ought to deal with that stuff for you, but this is an appropriate area to be very paranoid; with locking, problems are global because they spread and can involve locks in many different processes. (For some people, it's their first exposure to a global correctness property. They're always tricky.)
Aside from that, the main thing is to make sure that the DB has the Definitive Truth and that each update corresponds to a single transaction that simply fails or succeeds… You know, basic DB stuff.
-
-
So far I only anticipate issues with inventory, say there are 250 gold coins on the ground and two players manage to get the "pick up 250 gold coins" command to process at the exact same moment. Without transactions or locking, both of them will succeed :scratch_head:
Use BitCoin, it's supposed to prevent that kind of shenanigans
-
Use BitCoin, it's supposed to prevent that kind of shenanigans
That belongs in the Evil Ideas thread. A MUD that uses Bitcoin for in-game currency.
:evil: (Okay I know there's an evil/devil emoji but I can't find it...)
-
-
Or
or 
will do.
-
-
Full quotes work for emoticons. Ship it.
-
Oh, you can't full quote on mobile? Sucks to be you!
-
-
Has anybody else asked yet why a developer was running anything in production? Where were the DBAs? Was the script tested? Forgotten "WHERE" clause happens occasionally (EVERY GODDAMN TIME. I AM TEH STUPID) but shouldn't make it to production. Why no DR/hot spare to recover from? Why no automated deployment?
There are many WTFs in such a short story.
-
-
It is imp.
Not quite. I think you were looking for smiling imp to truly communicate the sense of evil:
-
So, all of you people who just open Management Studio and directly modify objects in the development environment - how do you know what changes to deploy?
Project A (upgrade/added functionality to an existing system): existing database in production, so whenever we finished a feature, we sent the SQL update script over Lync to team leader, who then would paste it into a big Notepad file full of ALTER TABLEs, which at the end was run on the QA / prod database.
Project B (years-long and continuous effort): every commit which makes changes to the database includes an SQL script with ALTERs, INSERTs, UPDATEs, etc. Every file is numbered with increments of 10, so that if someone goes out of order and needs his script ran before 0060, and after 0050, he'd just name it 0051, in honor of glorious BASIC days.
Sanity is overrated.
-
@Jaime said:
So, all of you people who just open Management Studio and directly modify objects in the development environment - how do you know what changes to deploy?
@Maciejasjmj said:...we sent the SQL update script over Lync to team leader...
Your answer is incompatible with the question. My original question was about how you turn your development effort into a script if you made your changes directly in Management Studio. You responded by telling me where you send the script to. I'm sure there's plenty of WTF's in your deployment process, but it still doesn't answer the question.
-
My original question was about how you turn your development effort into a script if you made your changes directly in Management Studio
I copy it from Management Studio and paste it into Lync, duh.
Edit: ah, nevermind. I never realized there was an option not to express your alterations as a script.
-
The question was clearer ten years ago, when one tool (Query Analyzer) was used to run scripts and another tool (Enterprise Manager) was used for point-and-click administration. Nowadays, a lot of people don't even realize that Management Studio does all a whole bunch of things that they have never even thought about doing.
-
Most DBAs are shocked when I show them that you can change the color for different connections.
Emoji Cheat Sheet
