Solved

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

Posted on 2008-06-15
9
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

635 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