Provide access to View Vs Tables

Question

Post reply

Provide access to View Vs Tables

river1

SSC-Insane

Points: 24309
More actions
September 19, 2023 at 3:13 pm

#4289702

Dears,
Hope this thread finds you well
I have been creating DALs ( Data Acess Points) in SQL using Views not tables. This because I heard that views are better for it due to:
1 - Tables behind can be changed without the need to change the View it self to which the user connects to to import information
2 - Better from a security perspective as the permissions are given to the view but not to the table it self
Do you agree with the above statement and reasons? Do you see any other reasons I can point as well in terms of using Views instead of tables for direct access of information?

Thank you,

Pedro

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

If the table changes, the view doesn't change, this could cause you issue.

So you had this table

CREATE TABLE Table1 (
ID INT,
Firstname VARCHAR(50),
Lastname VARCHAR(50)
)

You create a view over this.

Then you add a new column

ALTER TABLE Table1 ADD DOB DATE NOT NULL

The view still thinks it has ID, Firstname, Lastname.

You try to insert into that view giving it ID, Firstname, Lastname, DOB it will fail.

But then say you wanted to change the size of the VARCHAR columns

ALTER TABLE Table1 ALTER COLUMN Firstname VARCHAR(10)

The view metadata still thinks the column is a VARCHAR(50), but the table is now actually a VARCHAR(10).

Try inserting a string longer than 10 and you'll end up with truncation issues.

So point 1 is mute, if you change the table you must also ensure you refresh the view(s) that those tables touch otherwise you end up with all sorts of behaviour issues.

For a security perspective yeah having an abstraction layer is good, as you move the security else where, but you need to ensure the abstraction layer keeps up with the metadata of the underlying tables.

Not saying views are bad, just watch out for when the metadata changes, may be one to get into a habit of running "sp_refreshview" to update the metadata when you change the table definitions to ensure things are always aligned with the base tables.

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 2

I prefer giving access to views, for the reasons you give in 1 & 2.

View definition only needs to change when it is no longer compatible with its tables

DesNorton SSC-Insane Points: 24386 More actions · Answer 3

We create a separate schema. Permissions are provided to the schema.

Then for every object that needs to be exposed, we create a synonym under the exposed schema that maps to that maps to the underlying object.

How to post data/code on a forum to get the best help.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

On one of our systems we don't give any permissions to any tables or views to users, users only get permissions to a specific list of stored procedures.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Post withdrawn.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)