October 19, 2006 at 8:02 am
I have a function call fn_CubicVolume that is owned by the schema COMMON
When I log in with a user whose default_schema is COMMON, I can execute this query fine.
DECLARE
@MyDecimalVar decimal(12,3)
EXEC
@MyDecimalVar = fn_CubicVolume @CubeLength = 12.3, @CubeHeight = 4.5, @CubeWidth = 4.5
select
@MyDecimalVar
However, when I use the function call in a select statement like below, default_schema information is NOT used. It forces you to qualify the owner.
select
fn_CubicVolume (12.3,4.5,4.5)
Must run this instead: select COMMON.fn_CubicVolume (12.3,4.5,4.5)
Is there a reason for this? Is this a bug?
October 19, 2006 at 11:58 pm
No, it's not a bug. It's the whole reason that schema's are used. It's the purpose that schemas provide. To separate objects.
As a general rule of thumb, you should specify the schema even when you don't have to.
October 20, 2006 at 6:52 am
I disagree.
That is why MS implemented default schemas. If I have to (or should) specify the schema in all queries, I get zero code reuse. By using the default schema, I can have 2 views that look at different data for security purposes, owned by two different schemas. If the user has the proper default schema setup, they will select from their respective view. My query remains the same for both users. It is a very nice feature and works everywhere except in the case above.
October 20, 2006 at 11:39 am
To implement a view like that for all users, you would have to have a view for each schema being used not to mention a version of the underlying tables for each schema as well. To implement it for 5 users each with a different default schema, you would have to have 5 views with 5 sets of underlying tables.
That doesn't sound like it has made anything easier or better. How I would do it would be to have a single view and a single table. I would (virtually, not physically) partition the data based on what schema was allowed to see each record by adding a column that stores to which schema the record belongs (let's call it schema_allowed). Then in the view, I would include the following in the view:
Where schema_allowed = schema_name(schema_id())
Or if I wanted the schemas to be able to belong to mutliple schemas, I would use a map table nd join on it.
This method would also handle new schemas better. If you add a new schema, then users witht hat default schema would get an empty record set rather than an error stating that the objects do not exist.
Furthermore, if schemas did work the way you mentioned in your initial post, then security would go right out the window. Not to mention, how would SQL Server decide which function to use if there are 2 completely different functions with the same name, but different schema?
If a user with the default schema of WEB was running your query without specifying a schema name and SQL Server used the function in the COMMON schema, what happens if someone in the SALES schema decides to create a function with the same name? Should everything the WEB member set up suddenly stop working? Or should SQL Server choose one at random?
What if COMMON.fn_CubicVolume returns a generic mathematical result whereas the new SALES.fn_CubicVolume returns client creidt card nformation? Now someone enters parameters for calculating cubic volume at your website, and the result they get back is a list of credit card numbers of your clients. How much fun would that be?
October 24, 2006 at 9:11 am
First....
You don't need a table for each view. I have one set of tables all owned by 'dbo'. I can have multiple views on these tables all owned by different schemas.
create table dbo.MyTable (State CHAR(2), TestData varchar(50))
create view MO.vMyTable as select TestData from dbo.MyTable where State = 'MO'
create view IL.vMyTable as select TestData from dbo.MyTable where State = 'IL'
Suppose I have 2 users. User1's default schema is 'MO' and User2's default schema is 'IL'. They can both use the same query and get code reuse. SELECT TestData FROM vMyTable.
You wrote: "If there are two different functions with the same name but different functionality, how does SQL decide which one to use?"
It uses the one belonging to the default schema of the user. This is the the beauty of it.
Say I have a function that is owned by COMMON. COMMON.MyFunction. I can create a synonym for the schema's MO and IL that point to this function. User1 and User2 can reference the function without schema qualification.
If the function needs to be different for the schema IL all of a sudden, I can drop the synonym and create IL.MyFunction. All the code that referenced the function will still work and doesn't need to change, even though the functionality of the function does.
User1 references MyFunction, which looks at the synonym MO.MyFunction that points to COMMON.MyFunction.
User2 references MyFunction, which looks at the function is owns IL.MyFunction.
This all works great. The only issue I originally had was that in a SET or SELECT statement, you MUST specify the schema owner. I can however, get around that by using EXEC and returning the function result to a local variable.
October 24, 2006 at 9:36 am
Now, you're contradicting yourself.
I wrote: "If there are two different functions with the same name but different functionality, how does SQL decide which one to use?"
You wrote: "It uses the one belonging to the default schema of the user. This is the the beauty of it."
Your whole point is about what happens if the function is not use the user's default schema. So your reply that it uses the default schema is completely nonsensical. You want it to call the one not contained within the user's default schema without having to specify the schema name, so how would SQL Server handle having two function with the same name, but different functionality, and no version of it in the user's default schema?
The answer is that it can't without great risk of returning completely unexpected results; therefore, it doesn't use either one.
You said: "By using the default schema, I can have 2 views that look at different data for security purposes, owned by two different schemas."
Then you said: "
You don't need a table for each view. I have one set of tables all owned by 'dbo'. I can have multiple views on these tables all owned by different schemas.
create table dbo.MyTable (State CHAR(2), TestData varchar(50))
create view MO.vMyTable as select TestData from dbo.MyTable where State = 'MO'
create view IL.vMyTable as select TestData from dbo.MyTable where State = 'IL'"
This isn't partitioning data based on security, it's partitioning based on an arbitrary value in the table. In this example, you only have to create 52 views to cover all 52 states instead of my example that only requires 1 view for all 52.
Also, I got a nice chuckle out of the fact that your view depends on specifying the schema name of the table. So much for making code reusable by not specifying the schema. Your example would not work without specifying the schema of the table. The only way it would work without specifying the schema name of the table in the view is if you had an MO.MyTable and an IL.MyTable.
One interesting thing that comes to mind; however, is that if your schema names actually are the state abbreviations and that is the column value you use in the view, then you could make the view generic like so:
create view dbo.vMyTable as select TestData from dbo.MyTable where State = Schema_Name()
And then have all code reference dbo.vMyTable. It's scalable and reusable.
So, yes, your code is reusable. My code is scalable without having to use the same code over and over again. I guess it's a matter of preference as to which works best for an individual.
October 24, 2006 at 12:19 pm
You wrote:
" Your whole point is about what happens if the function is not use the user's default schema. So your reply that it uses the default schema is completely nonsensical. You want it to call the one not contained within the user's default schema without having to specify the schema name, so how would SQL Server handle having two function with the same name, but different functionality, and no version of it in the user's default schema?"
That was never my point. If I need to do that I use a synonym that points to the other function or view or whatever.
If MO doesn't have a function called MyFunction, but I want to use the one in the IL schema, I create a synonym in the MO schema that points to the IL version. Pretty simple.
I was using States as an example.
We are partitioning the data based on security. The actual views and where clauses are much more complex than that. I don't want to muck up the data with storing the schema in the data itself, because some data rows are valid for multuple schemas. I would either need to create a bitmap or a mapping table. Ugly and performance would degrade.
My solution works just fine. I have no problems with anything I have described or done.
My only issue is that SQL Server makes you specify a schema owner on a function call in a SET or SELECT statement. No place else does it do this. It will infer the schema from the default_schema of the user.
Again, you can get around this by using an EXEC for the function as opposed to a SET or SELECT.
Just seems like MS missed implementing the default_schema functionality on functions in SET or SELECT statements.
October 24, 2006 at 12:40 pm
Maybe they did miss something. Perhaps what they need in addition to a default schema for a user is a default schema for the database. So it would assume the user's default schema, if it existed, and then assume the database default schema next if it existed ..... and make it work with functions as well.
At the very least, I agree that handling of all object types should be consistent.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply