We help IT Professionals succeed at work.

Cross Server SQL Views Security Concern

Medium Priority
727 Views
Last Modified: 2012-05-06
I have two database servers at a data center. One for test and one for production.
I would like to create a view in the staging server that looks at a table in the production server.
I don't want any of the tables in the production server modifed by users on the staging server.
What are the security risks with having a "View" in one db access a table on another server?
It would seem to me the name view inplies only read capabilities.
So really what I'm asking is : Is it safe to do that and how can it be done since it is on a differnet server?

Thanks.

Comment
Watch Question

Author

Commented:
added correct section "MS SQL Server"
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Hi Fasteddie,

You do not mention which version of SQL Server...

You do need to create a linked server, and part of that is establishing a secured connection by nominating the user that the remote server will recognise. You can create a fairly tightly controlled user identity on that remote server and so the linked server itself has limited capability.

Generally speaking views are regarded as "read only", but strictly speaking, they can be used to update data if they are not calculated / computed columns, groups etc.

To be "safe" you can add the keyword DISTINCT and any column which is part of the DISTINCT cannot be updated, or, create a FUNCTION to present back the data.

e.g. (using examples from a MS business Contact manager instance)


-- set up a linked server - using code - or - can use SSMS. Normally a once off task, or on startup of "this" server
 
EXEC sp_addlinkedserver   
   @server='My_Linked_SqlSvr', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='MY_Server_Name\My_Named_SQL_Svr_Instance',
   @catalog='MSBusinessContactManager'                -- optional says what the default database will be.
go
 
-- set up a login user to access that remote server (once off as above) - must exist on remote server and can control most security using this user
 
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'My_Linked_SqlSvr' 
     ,  @useself = 'FALSE' 
     ,  @locallogin =  NULL  
     ,  @rmtuser =  'My_User'             
     ,  @rmtpassword =  'My_Password'  
go
 
-- optional, but a reasonable test and a handy piece of code to look at the linked server
 
exec sp_tables_ex My_Linked_SqlSvr           -- lists tables / databases in linked server
go
 
-- now can use the four part identifier of <server>.<database>.<schema>.<table> to access a table
 
select distinct * from My_Linked_SqlSvr.MSBusinessContactManager.dbo.ContactMainTable
go
 
-- let's now create a view on that 
 
create view vw_myview_1
as
select distinct * from My_Linked_SqlSvr.MSBusinessContactManager.dbo.ContactMainTable
go
 
-- and select data from that view
 
select * from vw_myview_1
go
 
-- or create a function to retrieve selected data from that datasource and can even use this to mask the real columns and tables
 
create function udf_myfunction ()
returns @tbl table (id int, col1 varchar(60), col2 varchar(60), col3 varchar(60))
as
begin
   insert @tbl 
   select contactserviceid,modifiedby,createdby,entryguid from My_Linked_SqlSvr.MSBusinessContactManager.dbo.ContactMainTable
   return
end
go
 
-- and we can select or join to that function just like a table
 
select * from dbo.udf_myfunction()
go
 
-- or even create a view over that function
 
create view vw_myview_2
as
select * from dbo.udf_myfunction()
go
 
-- and use it exactly as we would any view
 
select * from vw_myview_2
go
 
-- and for the puposes of testing, can now get rid of this linked server - or leave it there for next time.
 
exec sp_dropserver my_linked_SQLSvr, 'droplogins'

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you Mark. This is most usefull. I'm glad to find out that it's possible to provide limited and secure access to the production data.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Glad you found it usefull, but obviously something was still missing. How could I have better answered this question to get an "A" rather than a "B", or, what was missing ?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.