Creating Views, Hiding Tables

Question

Creating Views, Hiding Tables

tobias

SSC Enthusiast

Points: 140
More actions
March 19, 2007 at 9:38 am

#116529

I have a fairly large (several hundred tables) fairly normalized database - one which breaks some of the end user tools for attaching to the database. The underlying tools are also quite, well, user histile. I am hoping to hide complexity by revealing to to the Read-Only users only the views that I want them to see, and hiding the underlying tables. The end user apps are all tied to our GIS systems so I cannot change them.
For reasons having to do with purchased software, the tables in question are not owned by DBO, but owned by Root. That cannot be changed.
1) Created a GisUser schema.
2) Created denormalizing and restricting views owned by the schema
3) Made some users members of the Gis Schema with SELECT writes on the Gis Views.
This produces the desired result of the end user connecting and seeing only the collection of read-only views that we want him to concern himself with.
Problem: When a GisUser queries the database, he gets select errors on each of the underlying tables. When I grant select rights on the underlying tables, then the ugly table names start to crop up, and the user gets to see data outside of his realm.
So, for testing purposes: How can I let an access ADP, say, attach to a SQL server and see only a few defined views, and not any of the underlying tables. How do I let a user have select rights only though the defined view?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

alonzo SSC-Addicted Points: 443 More actions · Answer 1

Have you thought about creating a Windows group? Give this group access to selected objects like views or tables then add the users to this group.

tobias SSC Enthusiast Points: 140 More actions · Answer 2

All access to the schema's is by windows groups.

THe problem is simple:

Under SQL 2000, I could create views for users who could not see the tables.

Under SQL 2005, the views fail if the users do not have select rights to the underlying tables.

I want to re-create the status quo ante.

Curtis Tamanaha Right there with Babe Points: 725 More actions · Answer 3

Access is a pain. Many of my users access the db via Access so sometimes I have to go in and figure this out then I forget it again.

What version of Access?

Prior to Access 2007, Access didn't know how to handle Schemas so you would get odd object listing in Access then it wouldn't let you access the table or views.

If all you want to do is allow your users to select (read) the data then you don't have to grant SELECT on the table. Granting SELECT on the view is all you need. BUT, if they need to insert, update, or delete then you are forced to at least grant SELECT on the table.

Test it out.

tobias SSC Enthusiast Points: 140 More actions · Answer 4

Thanks Curtis. That's what I thought, too.

Access could see views, but could not see any data ((or metadata)

After the usual cursing, I went to SQL Analyser, and enter "Select * from [view]"

Got an error "Unable to Select" on first table in view. Granted that

Got an error "Unable to Select" on second table in view. Granted that

Got an error "Unable to Select" on third table in view. Granted that

Now they can select on view, but they see three tables as well.

I wexpect to add many more views for this customer, soon