Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to create an sql user with access to a view but not to the view definition

Posted on 2008-06-15
9
Medium Priority
?
281 Views
Last Modified: 2012-05-05
We have our database on sql server 2000. We are having some new software implementation and as part of that we are supposed to provide some data to the new vendor from our sql database.

We are planning to provide the data in a new view created in a different sql 2000 server. This view will be accessing the original server using openrowset and providing the necessary data. How can we create a user in the new sql server who has access only to the data in this view? The user should be able to retrieve the data from the view but should not be able to see the definition of the view ("sp_helptext viewname" should not work). This is to make sure the original server name is not exposed to the third party.
0
Comment
Question by:bijualex
  • 5
  • 2
  • 2
9 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 21788702
0
 

Author Comment

by:bijualex
ID: 21791765
Hi cs97jjm3, thanks for the details. This page gives information about creating views and related things but what I want is to create a user which can only select data from this view and cant see the definition of the view (this user need not do anything in the database except doing a select stmt on the view)
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 21791981
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:bijualex
ID: 21792258
This again talks a lot about accessing permissions on tables and views but doesnt address my issue. I will re iterate my requirement - user should not be able to write "sp_helptext viewname" and see the select statements written in the view. Is this possible?
0
 
LVL 8

Accepted Solution

by:
srnar earned 2000 total points
ID: 21792448
The best solution is to create your view with encryption. No user will be able to see its source code.

There is a general solution how to disable the sp_helptext but with huge impact - no regular user (perhaps except sysadmins) - will be able to see any source codes. I do not recommed it!!!

There are also similar threads here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1569739&SiteID=1

and here
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20331218.html


--encryption
CREATE VIEW aView
WITH ENCRYPTION  
AS
SELECT 0 Col0
 
sp_helptext 'aView'
 
--sp_helptext
USE [master]
 
DENY EXECUTE
ON sp_helptext
TO PUBLIC

Open in new window

0
 

Author Comment

by:bijualex
ID: 21792722
srnar - Excellent, I tried both the ways - DENY EXECUTE ON sp_helptext TO username, though prevented the user from doing sp_helptext, through enterprise manager the user could see the code. So I think I need to go with the ENCRYPTION option. Thanks for the help, another small question - is there any way for the sa user to retrieve the encrypted code or we need to keep this saved in a separate file? Thanks.
0
 

Author Closing Comment

by:bijualex
ID: 31467347
Many new things to be learned....Thank you !!!!
0
 
LVL 8

Expert Comment

by:srnar
ID: 21793019
Yes - administrator can use this utility ( there are some ways how to get the encrypted code) - but I strongly recommend to have source code externally - you can use e.g. Microsoft Source Safe for its versioning.

Decrypt utility:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5

Your restricted user should not be able to run ALTER VIEW required by DECRYPT utility.
0
 

Author Comment

by:bijualex
ID: 21793367
Perfect - Thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question