February 11, 2021 at 8:10 am
I use SYNONYM for objects in other databases, typically using a SCHEMA to indicate this e.g.
CREATE SYNONYM [OTHER_DATABASE].[TheTableName]
FOR [OTHER_DATABASE].[dbo].[TheTableName]
GO
and then in my code I can do this:
SELECTCol1, Col2
FROM[OTHER_DATABASE].[TheTableName]
however, if I accidentally reference the database direct (given that the naming is very similar this does happen 🙁 )
SELECTCol1, Col2
FROM[OTHER_DATABASE].[dbo].[TheTableName]
I'd like to find any such code.
Seems to me the easiest way would be to DENY permission on the underlying object if accessed directly, but GRANT access only via the SYNONYM.
Is this possible? (Hopefully this isn't a trivial question with a simple answer ...)
Typically the only SYNONYMS I have are for tables, in other DBs, which are then referenced from SProcs in my database / application. The permissions currently provided, on the tables in the other database, are for full access - SELECT, UPDATE, DELETE - on the relevant tables
February 11, 2021 at 11:46 am
Enabling Cross Database 'Ownership Chaining' is probably a bad idea.
Certificates work well with stored procedures.
For application queries it is probably easiest to leave the security model as it is.
https://www.mssqltips.com/sqlservertip/2549/options-for-cross-database-access-within-sql-server/
etc
February 11, 2021 at 1:58 pm
I do not have "cross db ownership chaining" turned on ...
I have explicit permissions on the tables, in Other Database, that the APP needs access to.
But what I want to avoid is any direct access to e.g. a table in Other Database using 3-part naming
[OTHER_DATABASE].[dbo].[TheTableName]
and only allow access using SYNONYM which, for me, is a 2 part name
[OTHER_DATABASE].[TheTableName]
The reason for this is so that I can change the SYNONYM and be 100% certain that there are no hardwired statements which directly access Other Database and bypass the SYNONYM
For example, for QA Testing I take a copy of the PRODUCTION database, and I also have copies of e.g. QA_OTHER_DATABASE
I change the SYNONYM for OTHER_DATABASE to reference the QA version, and not the "live" version.
If there is ANY hardcoded reference to OTHER_DATABASE in my code then the TEST will inadvertently access / update the LIVE database 🙁
So to broaden my question, any solution that would catch / prevent direct reference to OTHER_DATABASE (inadvertently introduced into my code) would solve my problem. This needs to be a runtime check, not a batch syntax check, in case some dynamic SQL has "assembled a Table Name" in e.g. an assignment
February 11, 2021 at 3:07 pm
>>I do not have "cross db ownership chaining" turned on ...
I never implied you did it is just what some people do to get around ownership changing problems.I suggest you use certificates on you SPs.
While I keep all test dbs on different servers, so have not tried this, you could look at having different certificates for production and qa databases and consequentially the certificate associated with the user in the other database would also have to be different for production and qa. This would mean when you copied your production db to qa you would need to change the certificate on all your SPs. As your qa certificate would not have permissions in the production other database any attempt to access it would result in an exception.
ps You might find it useful to look for articles on ownership chaining by Solomon Rutzky and Erland Sommarskog.
February 11, 2021 at 4:42 pm
Sorry, was only intending to clarify that I am not using cross DB chaining.
I had considered using different logon for QA database, which would have permission for QA_OTHER_DATABASE and no permission for Live OTHER_DATABASE, which would then weed out any hard wired access direct to OTHER_DATABASE at runtime ... provided that that code was exercised in our QA test (whilst I'm confident on that it is the rare exception that I am wanting to catch / guard against)
I am sure I am over thinking it, and the chances are slim to miniscule, but I then considered that would open the possibility of a hardwired link to the QA version of the database ... which will then surface once we rollout the code to PRODUCTION.
I've not used certificates and will now investigate that, and the ownership chaining links, thanks.
Would certificates give me something that was more targeted than that just changing the login?
We do have a completely isolated TEST system, but the amount of extra effort to use it is horrendous. We use for it to test 3rd party upgrades, where we cannot segregate them any other way, but for our own inhouse code we have relied on SYNONYMS and hosting DEV and QA much closer to PRODUCTION because it provides us with an extremely quick DEV-QA-PRODUCTION path. But thanks for mentioning segregated testing, we could replace our under-powered TEST Server with a fire-breathing-dragon to match our Production Servers; I would prefer to spend that large sum on something else! but its definitely worth considering with senior management.
February 11, 2021 at 5:31 pm
A different login for the qa database should work. When you copied Other_Db to QA_Other_Db you would then only have to alter the login associated with the user which had the permissions in QA_Other_Db.
Certificates are more secure as only objects with the certificate (eg SPs) can access the permissions on the user with the certificate in the other database. They would mean more work when moving prod to qa etc. I presume you have already scripted repointing the synonyms so there is no reason why re-assigning certificates cannot be done as well.
We have fully scripted solutions for copying prod to dev, test, qa etc. These are usually scheduled overnight so we do not care how long a copy etc takes. You could look at having a named instance on the prod server for your non-prod dbs to at least give some separation.
With regards to promoting code, ddl etc to qa and prod, I prefer this to be fully scripted with the scripts in source control. If something goes wrong a script is a much better starting point than someone's vague recollections of what they did.
February 11, 2021 at 9:35 pm
The reason for this is so that I can change the SYNONYM and be 100% certain that there are no hardwired statements which directly access Other Database and bypass the SYNONYM
For example, for QA Testing I take a copy of the PRODUCTION database, and I also have copies of e.g. QA_OTHER_DATABASE
I change the SYNONYM for OTHER_DATABASE to reference the QA version, and not the "live" version.
If there is ANY hardcoded reference to OTHER_DATABASE in my code then the TEST will inadvertently access / update the LIVE database 🙁
It appears that you are performing QA testing on the production system - which to me is the root of the problem. For QA testing - a secondary system where you have a copy of production and a copy of other_database avoids this issue. There is no way your tests could then be accessing the production system.
I don't believe there is any way to isolate permissions to only the synonym. Any user attempting to access the synonym would also need access to the object in other_database. Therefore, that user can use either the synonym or 3-part naming to access that object.
The only way around that would be to isolate access to only stored procedures - and use certificates to grant access across databases. The user(s) then would not have access to either the synonym or the other database - only access to the stored procedures.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 11, 2021 at 9:53 pm
It appears that you are performing QA testing on the production system - which to me is the root of the problem. For QA testing - a secondary system where you have a copy of production and a copy of other_database avoids this issue. There is no way your tests could then be accessing the production system.
I totally agree. At the very least, it should be a separate instance but a separate "box" is the best way to go, IMHO.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2021 at 10:09 am
Certificates are more secure as only objects with the certificate (eg SPs) can access the permissions on the user with the certificate in the other database. They would mean more work when moving prod to qa etc.
Thanks, that's very helpful.
Several valid points here about separate test environment, or separate instance as a minimum. Its a long time since we took the current decision, so it is worth reviewing - thanks for the nudge. The completely isolated test system we have is a right royal pain to use. It has old/slow/limited-resource hardware. The isolation makes it (deliberately) a pain to move files to - absolutely nothing is common / accessible between the Test environment and the Live one. We only use it for testing of 3rd party upgrades, and that is typically only once every couple of years - hence there has been little incentive to spend $$$ on the hardware. Conversely for internal DEV work we have a formal release every month, and anything critical will get from Request through DEV, QA, Automated Regression test built and run, and out to Production by this afternoon ...
But since those bad old days we now have very slick VM servers, so may well be able to implement a much better isolated-test-environment than we could "way back then".
But my underlying problem is that I want to be 100% sure that we have not hardcoded [OTHER_DATABASE].[dbo].[TheTableName] instead of SYNONYM [OTHER_DATABASE].[TheTableName] so that we can easily change the location of OTHER_DATABASE for any reason (QA testing being only one of them; the ability to "move" a database is also very important, and as part of that I want to have the ability to discover if any 3-part-names are hard-coded)
I don't believe there is any way to isolate permissions to only the synonym. Any user attempting to access the synonym would also need access to the object in other_database. Therefore, that user can use either the synonym or 3-part naming to access that object.
Confirmation that is the case is helpful, thanks, as I can stop trying to find a way to do that and figure out a different solution. Clearly this sort of thing should be caught by Peer Review of code ... although I'd prefer to have a mechanical barrier too.
I got excited thinking that I could put an (invalid) Synonym on a 3-part name, but turns out only 2-part names are supported 🙁
I think my best bet is going to be:
Also, I could set up the DEV environment with database names different to Production. Anything that uses 3-part-name would then break when it got into Production (assuming that went unnoticed by our QA tests)
February 12, 2021 at 5:23 pm
You can write code to search stored procedures, views and functions for specific strings - or use SQL Search from Redgate. It would be quite easy to search all objects for 'OTHER_DATABASE' to identify all of the objects where that string exists.
If you get any objects that are not synonyms then you have a problem that needs to be addressed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 12, 2021 at 5:48 pm
You can write code to search stored procedures, views and functions for specific strings
Thanks. However enough of the SQL within our Sprocs is mechanically generated, bolting together various things, including from meta data in tables in the database, that a simple code search is not necessarily going to encounter any such culprits.
My first choice would be an "active" system that would raise an error at runtime when this was encountered. All the processed we have for code analysis which are after-the-fact we find less useful than things that alert us whilst we run the code during DEV (and will catch them during QA too [which for us is automated, and run daily])
I suspect a code search (where culprits do exist) would be fairly easy for us as we only ever use "dbo" schema (other than for SYNONYMS)
OTHER_DATABASE.dbo.OBJECT
or
[OTHER_DATABASE].[dbo].[OBJECT]
so probably anything preceding "dbo." or "[dbo]." is likely to be direct-access database code.
This RegEx seemed to work in my Code Editor.
[A-Z0-9\]].[[]*dbo
but that found matches in comments ... clearly I'd need to figure out a more sophisticated RegEx to weed those out, might even need something to parse the code to filter the comments ...
February 16, 2021 at 2:40 pm
You can write code to search stored procedures ...
I had a bit of a rethink on this. My dynamic SQL issue needs solving, but no harm in sorting out any hardwired links in the SProc / VIEW code in the meantime ..
In case of interest here's what I did:
My quick search revealed a huge number of hardwired links, all the ones I looked at (until I got bored) were in comments.
Typically looking like this
SELECT
Col1,
Col2,
...
-- [OTHER_DATABASE].dbo.sp_help 'TheTableName'
FROM[OTHER_DATABASE].[TheTableName]
WHERE...
which is why I am getting false-hits on a global search, where these are in comments
That sort of "helper" code is in a comment to make it easy to EXEC to see what the (physical) columns are when reviewing / changing the SELECT query. (I dunno if that is a good idea, but that is how we do it ... we actually use our own Helper Sproc instead of sp_help which provides oven-ready-metadata in the formats we want it , so sp_help used here just for example - but that is the cause of the direct-reference to the database in the comments)
We have enough of these that it makes it hard to fine the (very rare) accidental-hardwired-direct-reference to database in the code
I also found a few database references that we don't have SYNONYMS for ... e.g. TEMPDB and MSDB (probably ought to have Synonyms for those ... but I am not expecting those DB names to change 🙂 )
So ... it happens that I have a "Source code compression" routine that removes extra whitespace, comments, etc. (We use it for obfuscating our code, to make it a bit harder for someone who might steal the code to then make use of it)
I used SSMS to create a script of the database objects. I choose "One file per object" so that an unclosed comment in one script didn't then cause accidental removal of follow-on material (not sure that is possible ... so just Belt and Braces)
(A number of the objects were encrypted ... 🙁 )
I then "compressed" the files and THEN searched them for the hardwired database link. Pleased to say that didn't find any that were using "dbo.", so looks like all of them are indeed using the SYNONYM.
But the risk remains ... given how similar the two are
FROM[OTHER_DATABASE].[TheTableName]
FROM[OTHER_DATABASE].[dbo].[TheTableName]
February 18, 2021 at 12:15 pm
I had a chat with our IT Services and the world has moved on such that we can see a way to a very straightforward segregated DEV / TEST environment ... but I would appreciate any thoughts you have. I've started a new thread:
Request for review of proposed test environment
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply