create view

Creates or replaces a view, which is an alternative way to look at the data in one or more tables.

Syntax

create [or replace] view [owner.]view_name
	[(column_name[, column_name]...)] 
	as 
	select [distinct] select_statement
	[with check option]

Parameters

Examples

Usage

For getting information about views:
  • To create a report of the tables or views on which a view depends, and of objects that depend on a view, execute sp_depends.

  • To display the text of a view, which is stored in syscomments, execute sp_helptext with the view name as the parameter.

See also:
  • Identifiers in Reference Manual: Building Blocks

  • sp_depends, sp_help, sp_helptext, sp_rename in Reference Manual: Procedures

Standards

ANSI SQL – Compliance level: Entry-level compliant.

The use of more than one distinct keyword and the use of “column_heading = column_name” in the select list are Transact-SQL extensions.

Permissions

When you create a view, the SAP ASE server makes no permission checks on objects, such as tables and views, that are referenced by the view. Therefore, you can create successfully a view even if you do not have access to its objects. All permission checks occur when a user invokes the view.

When a view is invoked, permission checks on its objects depend on whether the view and all referenced objects are owned by the same user.
  • If the view and its objects are not owned by the same user, the invoker must have been granted direct access to the objects. For example, if the view performs a select from a table the invoker cannot access, the select statement fails.

  • If the view and its objects are owned by the same user, special rules apply. The invoker automatically has implicit permission to access the view’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables, you can give them restricted access with a view. In this way, a view can be a security mechanism. For example, invokers of the view might be able to access only certain rows and columns of your table. A detailed description of the rules for implicit permissions is discussed in the System Administration Guide.

  • If a column in the table is encrypted, you must have decrypt permission to select from the view. If the view and its objects are not owned by the same user, you must have decrypt permission on the encrypted column in the table to select from the view. If the view and its objects are owned by the same user, it is sufficient to grant decrypt permission to the user who must select from the view on the view column that corresponds to the encrypted column in the table.

Any user who impersonates the view owner through an alias or setuser cannot replace the view.

SettingDescription
Enabled

With granular permission enabled, you must have the create view privilege to create a view. To create a view for another user, you must have the create any view privilege.

You must be the view owner to replace the view.

Disabled

With granular permissions disabled, you must be the database owner, a user with sa_role, or have the create view privilege to create a view. To create a view for another user, you must have sa_role.

You must be the view owner to replace the view.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

16

Audit option

create

Command or access audited

create view

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

  • or replace – for create or replace

Related reference
create schema
drop view
update
select
group by and having Clauses
set