Controlling Data Access With Views in SQL

Data Access,Database views allow you to effortlessly reduce the complexity of the give up user experience and restrict their capacity to get admission to records,

contained in database tables by means of restricting the information provided to the give up person.

Essentially, a view uses the outcomes of a database query to dynamically populate the contents of an synthetic database table.

Data Access,Why Use Views?

Data Access,There are two primary reasons to offer users with get entry to to statistics.

via perspectives rather than supplying them with direct access to database tables:

Views provide simple, granular safety. You can use a view to limit the statistics that a person is permitted to peer in a table.

For example, when you have an employees desk and wish to offer a few customers,

with get right of entry to to the facts of full-time personnel,

you may create a view that incorporates simplest those information.

This is tons less complicated than the alternative (growing and preserving a shadow desk) & guarantees the integrity of the statistics. Views simplify the person enjoy. Views conceal complex info of your database tables from give up customers who do not need to see them.

If a user dumps the contents of a view, they gained’t see the desk columns,

that aren’t selected through the view and that they won’t apprehend.

This protects them from the confusion resulting from poorly named columns, particular identifiers and desk keys.

Data Access,Creating a View

Data Access,Creating a view is quite trustworthy: you simply need to create a query,

that incorporates the restrictions you desire to put into effect and region it inside the CREATE VIEW command. Here’s the syntax:


For example, if you wish to create the full-time employee’s view, you would issue the following command:

SELECT first_name, last_name, employee_id
FROM employees
WHERE status='FT'

Modifying a View

Changing the contents of a view makes use of the precise identical syntax because the creation of a view,

however you operate the ALTER VIEW command in place of the CREATE VIEW command.

For example, in case you wanted to feature a limit to the fulltime view that adds the employee’s smartphone variety to the results,

you will problem the following command:

ALTER VIEW fulltime AS
SELECT first_name, last_name, employee_id, telephone
FROM employees
WHERE status='FT'

Deleting a View

It’s simple to dispose of a view from a database the use of the DROP VIEW command. For example, if you want to delete the overall-time employee’s view, you’ll use the following command:

DROP VIEW fulltime