September 10, 2019 at 7:48 am
We set up a server with different databases. One for more financial data (ASAP_be), one for hr data (PRP_be), one for structural data (SARA_be). When I give users the right only to execute stored procedures (with no access rights directly to the tables themselves), this works well, when the stored procedure only uses tables from one database. But the minute they have to access tables from dîfferent databases I get error messages:
Msg 229, Level 14, State 5, Procedure usp_PLP_Aktualisierung_ASAP_BUD, Line 25 [Batch Start Line 2]
The DELETE permission was denied on the object 'tblASAP_Detail_v3', database 'ASAP_be', schema 'dbo'.
Thanks for your suggestions and also links. I started this job two years ago working with ms Access and Excel and now it has been migrated to SQL Server with me learning on the job ... making some stupid mistakes.
Christoph Kauflin
working in Controlling, but trying to program the tools we need to do that
September 10, 2019 at 11:31 am
A user is a database object, not a server object. When you execute an SP within a database, the permissions of the user, linked to your login in that database, is used. If you only have execute permissions on the SPs and other objects, such as a table, are referenced within the same database then something called Ownership Chaining "kicks in".
When running an SP, if the objects referenced by it have the same owner, and are in the same database, then permission to access those objects is granted for the context of the SP. There is/was documentation explaining how this works, but my Googlefu is failing me, or Microsoft have removed the document (it is touched on here though). For your set up, this is why the users can access the tables in the same database when executing the SP, as they all have the same owner (probably dbo).
When you reference an object in a different database, however, then ownership chaining does not come into effect. This is because something known as "Cross-Database Ownership Chaining" is Off By Default, and for good reason.
Ownership chaining across databases is turned off by default. Microsoft recommends that you disable cross-database ownership chaining because it exposes you to the following security risks:
Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases.
Users with CREATE DATABASE permission can create new databases and attach existing databases. If cross-database ownership chaining is enabled, these users can access objects in other databases that they might not have privileges in from the newly created or attached databases that they create.
One method that would allow you to therefore achieve what you're after is by using Cross-database ownership chaining, however, you need to consider the above before you even start progressing. The documentation explains better than I on how to implement this and the caveats and security concerns that come with it.
Another method is to sign the Stored Procedures: Signing Stored Procedures in SQL Server. This isn't something I have ever personally worked with, but depending on your requirements, this may well be the better (and more secure) route. Someone far more familiar than I with certificates will likely be happy to expand on the subject.
To answer the questions you had:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 10, 2019 at 1:18 pm
thank you for your good and quick answer. I just checked signing procedures and I think it is challenging ...
To make point two clearer (I hope):
To structure a single db containing all (financial, structure, hr ...) tables I would use schema. instead of having the financial tables on database A(sap) they would be in the same db as the rest but with the schema A(sap). HR Stuff would be under schema P(RP). Then executing a stored procedure under schema P (or altogether different one like X) would then be executed in the same db, but over different schema.
one "lurking" problem is, that as I understand it, you can have the same table name twice if it is under two different schema
Christoph Kauflin
working in Controlling, but trying to program the tools we need to do that
September 10, 2019 at 1:26 pm
Personally, if sap and RP, are the abbreviations you already use, I would created schemas with those names for the schemas, not A, and R.
one "lurking" problem is, that as I understand it, you can have the same table name twice if it is under two different schema
Why is that a '"lurking" problem'?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 10, 2019 at 3:20 pm
Probably the wrong word (I am not a native speaker). What I wanted to say was that there is the danger of creating (and using) the same table under two schema, normally ones login and dbo ... and then using or controlling the wrong one. We are "messy" developers, developing the tables, the stored procedures and the front end at the same time while learning how to do it. the applications we only use in "budget season" we revisit more or less yearly and there I can see what we have been learning, because now I see better and easier ways. And I started documenting from the beginning...
Christoph Kauflin
working in Controlling, but trying to program the tools we need to do that
September 10, 2019 at 3:38 pm
What I wanted to say was that there is the danger of creating (and using) the same table under two schema, normally ones login and dbo
Is that a question or a statement? There is no danger of having 2 objects on different schemas with different names no. Not unless you're giving different users different default schemas; and then the danger is either that your relying on the user's default schema to give the correct behaviour (which isn't the object's fault, and the behaviour should be expected if you're using that idea, but I'd recommend against it) or you're not defining your schema in your SQL (which is the developer's fault).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply