October 3, 2012 at 11:33 am
Jason-299789 (10/3/2012)
sql-noob (10/3/2012)
The user who is able to access the view belongs to a group which is added to the sql server from ADS(active directory services). Now the irony is other users belonging to the same group are not able to view the data from the view(although command executed successfully).In addition the sa's are also not able to get the data from the view.
CONFESSION: I am very sorry, but i think i won't be able to post the view definition because i think its against the company's policy and i am a fresher here! Please understand my concerns.
Do you work in a Financial institute (hedge fund, investment bank etc)?
As your description fits the type of thing that I've seen before at such institutions, such that only select people are able to access certain data, and even the SA account is forbidden from seeing that data as its commercially sensitive.
The project is related to infrastructure, do u have any views on which way i should go on to troubleshoot this problem.
October 3, 2012 at 11:36 am
MysteryJimbo (10/3/2012)
I apologize. I misread the original post that suggested there was an issue with permissions.Data is not restricted on a permission basis. Either you can read the data or you cannot and you get a select error (unless the application handles the error).
Either the queries being ran are different or they are looking at another database. I should look at running a trace against the database in question to confirm that
a) that the queries are being ran against the same database
n) that the queries are identical
John Mitchell-245523 (10/3/2012)
OK, understood. Does the definition contain "user_name", "user_id", "sys.database_principals", "current_user" or anything else that suggests it's returning data according to the user running the query?John
This is probably an outside chance but is the only way data "might" be filtered by user.
Are you using a bespoke application with custom security levels?
I personally checked that the query and the database on which the query was executed were the same for both the user(sa and the privilaged user), but as u suggested i will surely run a trace for more information.
October 3, 2012 at 11:39 am
MysteryJimbo (10/3/2012)
Are you using a bespoke application with custom security levels?
No we dont use bespoke application.
TRUTH: I really didn't know what a bespoke application is, but i surely checked it out and checked if we are using it or not. Thanxs for the new terminology.
October 3, 2012 at 11:49 am
my findings
1. when i execute the select statement in the view without the where clause it gives the results but more than the actual results it should give(naturally), not the same case with the user who gets the result.
2. The default schema which the sa follow is of DBO but the default schema of the group which the user belongs to is blank.I tested from the user end(not application level), executed the below query
select * from dbo.filteredproduct
and it still works!
3. The view exists from a long time(years) and there was no complaints untill the another user of the same group reported that he/she is not able to get any data(query exected successfully though).
question
Isn't the sa suppose to have full control over the database?
thanks to all of u for answering the questions with limited resources, i really appreciate it.
October 3, 2012 at 12:06 pm
sql-noob (10/3/2012)
question
Isn't the sa suppose to have full control over the database?
thanks to all of u for answering the questions with limited resources, i really appreciate it.
As you are discovering, sa has access to everything, can get the definition of the views and query the core tables itself..., but anytime a WHERE statement is involved, sa's results are filtered just like anyone else.
would it be correct of you ran a query like this, got all rows, instead of just the rows matching the WHERE statement?
select * from SomeTable WHERE ID = 1
the answer is, of course not....sa has a need to filter data just like normal users...
sa simply has more abilities to access OBJECTS...once the access to objects are established, data is filtered based on WHERE statements and joins.
Lowell
October 3, 2012 at 12:27 pm
Lowell (10/3/2012)
sql-noob (10/3/2012)
question
Isn't the sa suppose to have full control over the database?
thanks to all of u for answering the questions with limited resources, i really appreciate it.
As you are discovering, sa has access to everything, can get the definition of the views and query the core tables itself..., but anytime a WHERE statement is involved, sa's results are filtered just like anyone else.
would it be correct of you ran a query like this, got all rows, instead of just the rows matching the WHERE statement?
select * from SomeTable WHERE ID = 1the answer is, of course not....sa has a need to filter data just like normal users...
sa simply has more abilities to access OBJECTS...once the access to objects are established, data is filtered based on WHERE statements and joins.
I understand, but what if the same view queried gives two different results for a user at database level and the system administrator.
October 4, 2012 at 1:22 am
It might help you to understand what's going on if you run the following just before selecting from the view:
SELECT SUSER_NAME()
SELECT USER_NAME()
It doesn't matter whether you're a sysadmin, a database owner or anything else. A WHERE clause is a WHERE clause. Members of sysadmin have permission to see all data, but it doesn't follow that any particular query will return all data when run by those users.
John
October 4, 2012 at 2:50 am
John Mitchell-245523 (10/4/2012)
It might help you to understand what's going on if you run the following just before selecting from the view:
SELECT SUSER_NAME()SELECT USER_NAME()
It doesn't matter whether you're a sysadmin, a database owner or anything else. A WHERE clause is a WHERE clause. Members of sysadmin have permission to see all data, but it doesn't follow that any particular query will return all data when run by those users.
John
I understand, will do the check and keep you posted.
October 4, 2012 at 2:55 am
MysteryJimbo (10/3/2012)
Data is not restricted on a permission basis. Either you can read the data or you cannot and you get a select error (unless the application handles the error).Either the queries being ran are different or they are looking at another database. I should look at running a trace against the database in question to confirm that
I agree data cannot be restricted, BUT, you can programaticaly enforce restrictions, consider this structure which is similar to one I've seen used financial systems to restrict the data that people can view based on thier login.
CREATE TABLE Users
(UserId int
,UserLogin varchar(100)
,UserName varchar(100))
CREATE TABLE UserPermissions
(UserId int
,ProductId int)
CREATE TABLE Product
(ProductId int
ProductName varchar(100)
)
Insert into Users
Values (1,'xx\test1','Test user 1')
Insert into Users
Values (2,'xx\test2','Test user 2')
Insert into Product
values (1,'Widget 1')
Insert into Product
values (2,'Widget 2')
Insert into Product
values (3,'Widget 3')
Insert into Product
values (4,'Widget 4')
Insert into UserPermissions
Values (1,1)
Insert into UserPermissions
Values (1,2)
Insert into UserPermissions
Values (1,3)
Insert into UserPermissions
Values (2,4)
CREATE View FilteredProduct
AS
Select Prod.*
From Product Prod
JOIN UserPermissions Perms on Prod.ProductId=Perms.ProductId
JOiN Users Usr on Usr.UserId=Perms.UserId
Where Usr.UserLogin=SUSER_NAME()
GO
What result set do you get when you run the query
Select * from FilteredProduct
1) when logged in as xx\test1?
2) when logged in as SA ?
The results sets will be different, as the original poster stated he is extracting data from a view which we cannot see, and is named FilteredProduct it was a reasonable question to ask if there were restrictions internally in the view that caused the different result sets in the queries based on individual user.
If this type of restriction is in place its entirely possible that SA rights have been lowered below the standard SysAdmin level in order to prevent someone from cracking the SA account and gaining access to sensitive data, and yes some organisations are that paranoid.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply