December 11, 2023 at 3:26 pm
This is something I've never seen before and I can't think of the right way to search properly for this, so I'd like to throw it out to this group.
We have an audit table that stores the old password value when someone changes it. There is a DENY on this column for all but the DBAs. There is also a view (ViewA) that, among other tables, selects from this audit table. ViewA does not use a SELECT * or explicitly select the password column. When a user runs a query that joins ViewA to ViewB, it fails with reference to column we have denied select on. If the user runs a SELECT * FROM ViewA, no errors. If the user runs the SQL that is the definition of ViewA, no errors. ViewB does not reference the audit table.
Maybe a simpler way of saying this is:
-- good
-- this is the view where a base table has a DENY on a column
-- the column is not part of the view definition
SELECT * FROM ViewA
-- good
SELECT * FROM ViewB
-- fails on a column not used in the query or the view definition
SELECT A.col1, A.col, B.col1, B.col2
FROM ViewA A
INNER JOIN ViewB B ON A.Col1 = B.col1
If anyone wants to see code, I can post it. The view definitions are a bit long and clunky. Any thoughts on this are much appreciated.
December 11, 2023 at 4:01 pm
My thoughts are that any password columns should be encrypted. No need to worry about DENY permissions if you do that.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 11, 2023 at 4:07 pm
I agree with you, but that's not possible at this time for us. I'm trying to solve the permissions problem I posted.
December 11, 2023 at 6:02 pm
What is the specific error message (and error number and status) you get? (Using dummy column name(s) as needed, of course).
What is the query plan? Does it give any help as to why the error is occurring? Looking at the query plan might help (or might not).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 11, 2023 at 6:18 pm
Msg 230, Level 14, State 1, Line 8
The SELECT permission was denied on the column 'PasswordColumn' of the object 'AuditTable', database 'MyDB', schema 'MySchema'.
Nowhere in the query or view definition is the column PasswordColumn selected or referenced.
I ran the query as myself (that works) to get the plan. Searched the XML of the query plan for PasswordColumn and it does not exist.
December 11, 2023 at 8:39 pm
Personally I will only use DENY permissions when I really have no option.
Are all the tables and views in the same schema or at least in schemas with the same owner?
December 11, 2023 at 9:08 pm
You need to look at the "Estimated" plan for the one that doesn't work, just to be sure. See if anywhere SQL is referencing or "output"ing the restricted column.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 11, 2023 at 9:14 pm
@ken - The 2 views are in the same schema, but the underlying tables are in 2 different schemas. That said, if I run "SELECT * FROM ViewA" and "SELECT * FROM ViewB", both run successfully. ViewA uses the the table that contains PasswordColumn. Note even the SELECT * runs successfully, because PasswordColumn is not part of the view definition.
@scott - The estimated plan fails as well, with the same Msg 230 error as above.
December 12, 2023 at 9:13 am
@Ken - The 2 views are in the same schema, but the underlying tables are in 2 different schemas. That said, if I run "SELECT * FROM ViewA" and "SELECT * FROM ViewB", both run successfully. ViewA uses the the table that contains PasswordColumn. Note even the SELECT * runs successfully, because PasswordColumn is not part of the view definition.
@scott - The estimated plan fails as well, with the same Msg 230 error as above.
Umm... You do not really seem keen to get an answer. You have provided very little information and are giving poor answers to questions.
1. I was trying to determine if Ownership Chaining is part of the problem so is the owner of all your schemas the same?! (Normally dbo)
SELECT S.[name] AS SchemaName
,U.[name] AS SchemaOwner
FROM sys.schemas S
JOIN sys.sysusers U
ON S.principal_id = U.[uid]
ORDER BY SchemaName;
2. If you really cannot get a graphical plan you could have at least provide the results of SHOWPLAN_ALL ON.
EXECUTE AS USER = 'YourNonSysadminUser';
SET SHOWPLAN_ALL ON;
SELECT A.col1, A.col, B.col1, B.col2
FROM ViewA A
JOIN ViewB B
ON A.Col1 = B.col1;
SET SHOWPLAN_ALL OFF;
REVERT;
December 12, 2023 at 9:53 am
It would likely be helpful for you to provide some kind of reproducible example, as that'll help us understand what's going on. Can you perhaps post some DDL for some sample objects (tables and views) and a query that replicates the problem when used against the views when a DENY is applied against a column in one of the tables? We don't, at least, need any data for the objects, it's just the definitions that are important.
I did give it a go, based on the information provided, but couldn't reproduce the problem:
USE Sandbox;
GO
CREATE USER SomeUser WITHOUT LOGIN;
GO
CREATE TABLE dbo.SomeTable (SomeID int CONSTRAINT PK_SomeTable PRIMARY KEY,
SomeColumn varchar(10) NOT NULL,
SomeSecureColumn varbinary(20) NOT NULL);
GO
--GRANT SELECT ON dbo.SomeTable TO SomeUser;
DENY SELECT ON dbo.SomeTable(SomeSecureColumn) TO SomeUser;
GO
CREATE TABLE dbo.AnotherTable (AnotherID int CONSTRAINT PK_AntherTable PRIMARY KEY,
SomeID int CONSTRAINT FK_AnotherTable_SomeTable FOREIGN KEY REFERENCES dbo.SomeTable (SomeID),
AnotherColumn varchar(10));
GO
CREATE VIEW dbo.SomeView AS
SELECT SomeID,
SomeColumn
FROM dbo.SomeTable;
GO
CREATE VIEW dbo.AnotherView AS
SELECT SomeID,
AnotherColumn
FROM dbo.AnotherTable;
GO
GRANT SELECT ON dbo.SomeView TO SomeUser;
GRANT SELECT ON dbo.AnotherView TO SomeUser;
GO
EXECUTE AS USER = 'SomeUser';
GO
SELECT *
FROM dbo.SomeTable; --Fails
GO
SELECT *
FROM dbo.SomeView; --Works
GO
SELECT SV.SomeID,
SV.SomeColumn,
AV.AnotherColumn
FROM dbo.SomeView SV
JOIN dbo.AnotherView AV ON SV.SomeID = AV.SomeID; --Works
GO
REVERT;
GO
--Clean up
DROP VIEW dbo.AnotherView;
DROP VIEW dbo.SomeView;
DROP TABLE dbo.AnotherTable;
DROP TABLE dbo.SomeTable;
DROP USER SomeUser;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2023 at 2:24 pm
Can you provide usable DDL of your tables + indexes + views + test data + the failing query?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 12, 2023 at 3:14 pm
I'll try to answer the questions all in one post here.
I'll see what I can do about a repro. Thank you all for chiming in so far...
December 12, 2023 at 5:38 pm
I'll try to answer the questions all in one post here.
- I'm not posting the full DDL of objects because there are only 2 views that are joined. However, one view uses 4 tables, the other uses 14. That is an awful lot of DDL and I would never expect any of you to try and sift thru it all. I was trying to be a simple and concise as possible with what I provided.
- I can try and create a reproducible script. That would be better than what I've given so far.
- All of our schemas are owned by dbo, so no ownership chaining issues.
- Showplan - graphical or SET SHOWPLAN_ALL ON - cannot be displayed. I can provide screen shots if required. Permissions are verified before the query is passed to the optimizer.
- I didn't realize my answers were poor. I was trying to be as descriptive and concise as possible, without overloading you all with DDL (see #1).
I'll see what I can do about a repro. Thank you all for chiming in so far...
Okay. Ignoring the bad code smells I would start with the following two options, on a copy of a the database, just in case they resolve the problem:
1. If either of the views are indexed try NOEXPAND:
SELECT A.col1, A.col, B.col1, B.col2
FROM ViewA A
JOIN ViewB B
ON A.Col1 = B.col1
WITH (NOEXPAND);
2. If there are any indexes containing your password column, drop them and try the query again.
Problem solving now becomes more time consuming:
1. Create two new views in the same view schema, based on your originals, and refine them until they contain the minimum number of tables that reproduce the problem.
2. Now create a test rig based on the new views and the underlying tables.
3. Try to simplify the test rig by reducing the number of tables in the views while still reproducing the problem. (eg No need to do lots of joins to get the view join column.)
4. Post the test rig as DLL and test data.
December 18, 2023 at 8:23 am
My advice is to work on the suggestions of sscoach. Setup some tables in your test system that reproduce your problem with the minimum number of tables and data.
Also follow the suggestion of looking at index definitions to see if the column is included anywhere.
If no solution can be found then contact Microsoft. You may have hit a bug. They will want you to send ddl and data to allow them to reproduce the problem, so all of the suggested work above will be needed if you get Microsoft support involved.
Remember that MS have outsourced their support. My experience when reporting a problem is the easy things get fixed quickly, but anything that might ge a bug can take many weeks before finally getting escalated to MS. I suspect that support gets a bonus if they can resolved the issue themselves and may get marked down for anything they have to escalate.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 18, 2023 at 3:23 pm
I've stripped the whole thing down to isolate the one line that is causing the problem. It's this line that selects from the table I put the DENY on. It selects the most recent login date for a given user. It never touches the password column. As a workaround, in our query that selects from ViewA and ViewB, if we select that login date, it works without issue. This query is run interactively, so if they select it and ignore it, no big deal.
I'm convinced this is a bug. We are getting ready to do some updates/upgrades to our systems, so because we have a valid workaround, I'm putting it on the back burner until after our maintenance. Then I'll retest and submit if I have to. I did not realize that about MS support, so thanks for the heads up.
Thanks for all the suggestions. I was hoping someone else had run into this bizarre behavior, but given the insanity of our environment, it shouldn't surprise me I'm on an island with this one.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply