Views: Limit Access to Data

You can use views to focus, simplify, and customize each user’s perception of the tables in a particular database. Views also provide a security mechanism by allowing users access only to the data they require.

A view is a named select statement that is stored in a database as an object. A view allows you to display a subset of rows or columns in one or more tables. Use a view by invoking its name in Transact-SQL statements.

A view is an alternative way of looking at the data in one or more tables. For example, suppose you are working on a project that is specific to the state of Utah. You can create a view that lists only the authors who live in Utah:

create view authors_ut
as select * from authors
where state = "UT"

To display the authors_ut view, enter:

select * from authors_ut

When the authors who live in Utah are added to or removed from the authors table, the authors_ut view reflects the updated authors table.

A view is derived from one or more real tables for which the data is physically stored in the database. The tables from which a view is derived are called its base tables or underlying tables. A view can also be derived from another view.

The definition of a view, in terms of the base tables from which it is derived, is stored in the database. No separate copies of data are associated with this stored definition. The data that you view is stored in the underlying tables.

A view looks exactly like any other database table. You can display it and operate on it almost exactly as you can any other table. There are no restrictions on querying through views and fewer than usual on modifying them.

When you modify the data in a view, you are actually changing the data in the underlying base tables. Conversely, changes to data in the underlying base tables are automatically reflected in the views that are derived from them.

• Advantages of Views
  You can use views to focus, simplify, and customize each user’s perception of the database; views also provide an easy-to-use security measure.
• Security
  Through a view, users can query and modify only the data they can see. The rest of the database is invisable and inaccessible.
• Logical Data Independence
  Views can shield users from changes in the structure of the real tables if such changes become necessary.
• Create Views
  You can build views on other views and procedures that reference views. You can define primary, foreign, and common keys on views. However, you cannot associate rules, defaults, or triggers with views or build indexes on them. You cannot create temporary views, or views on temporary tables.
• Retrieve Data Through Views
  When you retrieve data through a view, SAP ASE verifies that all the database objects referenced anywhere in the statement exist and that they are valid in the context of the statement. If the checks are successful, SAP ASE combines the statement with the stored definition of the view and translates it into a query on the view’s underlying tables.
• Modify Data Through Views
  Although SAP ASE places no restrictions on retrieving data through views, and although Transact-SQL places fewer restrictions on modifying data through views than other versions of SQL, certain rules still apply to various data modification operations.
• Drop Views
  To delete a view from the database, use drop view.
• Use Views as Security Mechanisms
  Data in an underlying table that is not included in the view is hidden from users who are authorized to access the view but not the underlying table.
• Get Information About Views
  System procedures, catalog stored procedures, and SAP ASE built-in functions provide information from the system tables about views.