Where Views are used in sql server

Question

0.00/5 (No votes)

See more:

Hi,

I want to about the real use of views in sql server. In which situation i can prefer views for my project also In which situation views are used?. Can you explain this with one real time example.

Most of them are said views are used to restrict some users to see the contents of real tables.

But I have a doubt... When we creating a web application the database user of this application may be a single user. Then how this views are used in web applications.

The database user of web application should be a administrator then we will restrict which user to prevent to see the original tables.

Thanks,
Velkumar.

Posted 22-Oct-12 19:19pm

Velkumar Kannan

Add a Solution

Comments

Sergey Alexandrovich Kryukov 23-Oct-12 1:22am

This is very productive way of thinking. You need to learn how views work, that's it. If you don't see the situations where you need them, remember about the existing possibility and recall it later, when you see the need in it, which is pretty obvious. I think if you read about it, you will see a need.

And the doubt about roles is just irrelevant to the topic. You can have a similar concern on any changes in database, any at all. Views are no special.
--SA

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gautam Raithatha · Answer 1 · 2012-10-22T19:32:00

Solution 2

Hi...

Consider that your database has many number of tables and there may be a situation that you need data from many tables at a time by using JOINS.

SQL

select *
from a inner join b on a.col1 = b.col1
    inner join c on b.col2 = c.col2
    inner join d on c.col3 = d.col3
    inner join e on d.col4 = e.col4
    inner join f on e.col5 = f.col5
    inner join g on f.col6 = g.col6
where a.col1 = 10

Now it may happen that you need this data at many places, say in 10 SPs. Then instead of using JOINS in each SP, you can create a view which contains data from multiple tables.

SQL

create view View1 AS
select *
from a inner join b on a.col1 = b.col1
    inner join c on b.col2 = c.col2
    inner join d on c.col3 = d.col3
    inner join e on d.col4 = e.col4
    inner join f on e.col5 = f.col5
    inner join g on f.col6 = g.col6

Then depending on situation, you can get data from View:

SQL

select * from View1 where col1 = 10

Now consider that in all the SPs u need to add one more table in the join. In that case instead of modifying all the SPs, you just need to modify one view.

This was just a simple example of view, but there are many more advantages of using it.

Posted 22-Oct-12 19:32pm

Gautam Raithatha

Updated 22-Oct-12 19:37pm

v2

Comments

Velkumar Kannan 23-Oct-12 2:06am

Ok good example.

Can u explain the how views are used for security mechanism such as preventing some users to access the tables, columns or rows of a table.

Gautam Raithatha 23-Oct-12 2:14am

In sql server, there are inbuilt security mechanisms which can be used to prevent or allow some users the access of some or all objects (objects means tables, views, stored procedures etc.). By using such mechanisms, some users can be denied permission on tables, but permission can be granted on view. The view may contain only data from some of the columns of some tables.

So the user will not be able to access all the data and is unaware of table structure.

You can refer below or many other articles are present:
http://msdn.microsoft.com/en-us/library/ms188371.aspx

Velkumar Kannan 23-Oct-12 2:27am

Can u tell which user you are mentioning about?
DB user or any other user.
We are writing views in DB. Then which has a restriction to access these views?

Gautam Raithatha 23-Oct-12 2:43am

DB user, identified from username and password used to login in sql server.

Velkumar Kannan 23-Oct-12 3:33am

k. i accept this.

A web application should have one user. This user should have all permission to access the resources. Then which user has restrictions to access this resources.
This may be common for all web application.

Gautam Raithatha 23-Oct-12 3:42am

It depends on Connection string. If u have used common database connection string containing username and pwd that has all access, then any user of web application can access all data. For each user of web application, u can use different connection strings with different db username and password as per user role. u have to decide dynamically which connection string to use depending on user role.

Velkumar Kannan 23-Oct-12 4:44am

Good example. Nice example. Thanks for your post.

I also have one more doubt.

The web application users are authenticated in IIS level and asp.net level.

The connection strings and database access controls are in the hand of developers.
The web application users just interact with web application so the authentication process are handled in asp.net level.

If we want to access different data from table we can use the C# code to check the conditions. (Using same connection string for all users.)

The end users do nothing with the database since they just interact with the web site.

Can you explain one situation with this dynamic connection string?

Gautam Raithatha 23-Oct-12 5:44am

Consider your web app has three roles. You can add three connection strings in web.config:

<connectionstrings>
<add name="ConnStr1" connectionstring="Server=<server_name>;Database=<DatabaseName>;User Id=DbUser1;Password=Pwd1;" providername="System.Data.SqlClient">
<add name="ConnStr2" connectionstring="Server=<server_name>;Database=<DatabaseName>;User Id=DbUser2;Password=Pwd2;" providername="System.Data.SqlClient">
<add name="ConnStr3" connectionstring="Server=<server_name>;Database=<DatabaseName>;User Id=DbUser3;Password=Pwd3;" providername="System.Data.SqlClient">

Then when user login in web application u can identify and set role in Session. Then for displaying data, suppose u use SqlConnection.

SqlConnection conn;

if(int.Parse(Session["Role"].ToString()) == 1)
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ToString());
else if(int.Parse(Session["Role"].ToString()) == 2)
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr2"].ToString());
else if(int.Parse(Session["Role"].ToString()) == 3)
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr3"].ToString());

Then with other sql classes and objects u can manipulate data.

Velkumar Kannan 23-Oct-12 5:51am

This is not my question.

We can use same connection string for all users and roles. But just use the conditions to access data from DB for different users and roles.

My question is

Why we should use many connection strings for different users and roles?
The developer can write the code to check the user type and roles Depends on this the developer can write the code of accessing data from DB.

What is the use of using different users in DB for one application?

Can you tell a real time example for using different users in DB for one web application?

Sergey Alexandrovich Kryukov · Answer 2 · 2012-10-22T19:24:00

Solution 1

Please see:
http://en.wikipedia.org/wiki/View_%28database%29[^].

Isn't it quite enough to get the idea?

Anyway, I think the most principle part of my answer is in my comment to the question. Just think about it.

—SA

Posted 22-Oct-12 19:24pm

Sergey Alexandrovich Kryukov

Comments

Velkumar Kannan 23-Oct-12 1:27am

I have to know the situation where it can be used

Sergey Alexandrovich Kryukov 23-Oct-12 2:04am

Of course you have. Just think about it -- it will be the best answer. The motivation is explained. Any questions?
--SA

vijay bisht · Answer 3 · 2012-10-22T20:42:00

Solution 3

We Use Views in the places where the no of columns are not known to us
then we use a view to keep data in it .. it can not be used as a permanent table

Posted 22-Oct-12 20:42pm

vijay bisht

Comments

Velkumar Kannan 23-Oct-12 4:45am

I cant understand. Can you give an explanation