April 7, 2016 at 5:40 am
Hi to all. I am looking for some advice. I have been asked to create account to support execution of a SQL query.
Initially I created a stored procedure but have been advise the remote end is unable to pass a parameter to a stored proc but can to a SQL query (not sure why).
So I have a query that spans multiple tables. I do not want to give read access to all tables in the DB.
Question(s):
1) Do I have to give the account read access to Master?
2) Must I specify read permissions to all tables referenced in the query?
I am looking to apply best pratice.
Thansk to all.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
April 7, 2016 at 6:25 am
create a view that contains the query.
create a user, and grant the user access only to that single view.
as long as the query hits a single schema(ie dbo) in a single database, the user will be able to query only the view, without access to the underlying tables.
ownership chaining makes that possible, and is a great way to prevent access to tables directly
CREATE LOGIN MyRestrictedUser ...
CREATE USER MyRestrictedUser FOR LOGIN MyRestrictedUser
CREATE VIEW dbo.CustomView AS ....
GRANT SELECT ON dbo.CustomView TO MyRestrictedUser
Lowell
April 7, 2016 at 6:27 am
By default, a new login has access no databases, and once granted access, they cannot select from anything.
To maximize security, I'd suggest first creating a view that joins the required tables, selecting only the columns this special account needs, and also apply a filter to further restrict only those rows they need.
For example, let's assume they only need to see purchase orders for books entered within the last 45 days.
CREATE VIEW vBooksSales45Days AS
select
from Purchase as S
join Product as P on P.ProductID = S.ProductID
join Customer as C on C.CustomerID = S.CustomerID
where P.ProductCategory = 'BOOK'
and datediff(day,S.PurchaseDate,getdate()) <= 45;
GO
So we now create their login account, add them to the ContosoRetailDW database, and then grant them select permission on the view vBooksSales45Days. Once done, they can select from this view, but nothing else. By default they can't even see what other objects exist in the database.
CREATE LOGIN [TestUser] WITH PASSWORD=N'ABC123'
, DEFAULT_DATABASE=[ContosoRetailDW];
GO
USE [ContosoRetailDW];
CREATE USER [TestUser] FOR LOGIN [TestUser];
GRANT SELECT ON [dbo].[V_Customer] TO [TestUser];
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 7, 2016 at 6:31 am
Eric M Russell (4/7/2016)
By default, a new login has access no databases, and once granted access, they cannot select from anything.To maximize security, I'd suggest first creating a view that joins the required tables, selecting only the columns this special account needs, and also apply a filter to further restrict only those rows they need.
For example, let's assume they only need to see purchase orders for books entered within the last 45 days.
CREATE VIEW vBooksSales45Days AS
select
from Purchase as S
join Product as P on P.ProductID = S.ProductID
join Customer as C on C.CustomerID = S.CustomerID
where P.ProductCategory = 'BOOK'
and datediff(day,S.PurchaseDate,getdate()) <= 45;
GO
So we now create their login account, add them to the ContosoRetailDW database, and then grant them select permission on the view vBooksSales45Days. Once done, they can select from this view, but nothing else. By default they can't even see what other objects exist in the database.
CREATE LOGIN [TestUser] WITH PASSWORD=N'ABC123'
, DEFAULT_DATABASE=[ContosoRetailDW];
GO
USE [ContosoRetailDW];
CREATE USER [TestUser] FOR LOGIN [TestUser];
GRANT SELECT ON [dbo].[V_Customer] TO [TestUser];
GO
Agreed with the advice on how to set permission, but the view definition needs to be improved:
CREATE VIEW dbo.vBooksSales45Days AS
select List, the, columns
from dbo.Purchase as S
join dbo.Product as P on P.ProductID = S.ProductID
join dbo.Customer as C on C.CustomerID = S.CustomerID
where P.ProductCategory = 'BOOK'
and S.PurchaseDate >= dateadd(day,-45, getdate());
GO
(Changes made: Added column list, added schema to all object names to increase chance of plan reuse, made filter on PurcahseDate sargable to increase chance of index usage)
April 7, 2016 at 6:43 am
Many thanks for such swift and detailed responses.
Phil
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
April 7, 2016 at 6:57 am
I will re-post question regards 'Table Valued Function' as this question was regards access.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
April 7, 2016 at 3:48 pm
Hugo Kornelis (4/7/2016)
Agreed with the advice on how to set permission, but the view definition needs to be improved:
...
(Changes made: Added column list, added schema to all object names to increase chance of plan reuse, made filter on PurcahseDate sargable to increase chance of index usage)
Yeah, but mine will still run faster, because by excluding column list I insure it doesn't compile. 😀
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply