How can views be used to help improve security

Database Legacy. Relational Databases. Any Platform, Any Time. No Strings Attached. In Numbers Strength. Object and User-Defined Data Types. Other Data Types. Aliases and Synonyms. Other Implementation-Specific Objects. Chapter 7: Sessions, Transactions, and Locks.

Understanding Locks. Combining the Results of Multiple Queries. Chapter 9: Multitable Queries. Inner Joins. Joins Involving Inline Views. Multitable Joins with Correlated Queries. Improving Efficiency of Multitable Queries. Chapter SQL Functions. Chapter SQL Operators. Arithmetic Operators. Logical Operators. Operator Precedence. Assignment Operator. Comparison Operators. Bitwise Operators. User-defined Operators. Defining a Database User. Managing Security with Privileges.

Indexed Views An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table. You index a view by creating a unique clustered index on it. Indexed views can dramatically improve the performance of some types of queries.

Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated. Partitioned Views A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers.

This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view. System Views System views expose catalog metadata. You can use system views to return information about the instance of SQL Server or the objects defined in the instance.

For example, you can query the sys. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. A global role only applies to one database, but it can be granted to an enterprise role defined in the enterprise directory. Although a global role is managed in a directory, its privileges are contained within a single database--the database in which it is defined.

You define the global role locally in the database by granting privileges and roles to it, but you cannot actually grant the global role to any user or to any other role in the database. When an enterprise user attempts to connect to the database, the directory is queried to obtain any global roles associated with the user.

An enterprise role is a directory structure which can contain global roles on multiple databases, and which can be granted to enterprise users. By storing and managing enterprise roles in an LDAP-based directory service, you can centralize management of user-related information, including authorizations.

For example, the enterprise role clerk could contain the global role hrclerk with its unique privileges on the Human Resources database, and the analyst role with its unique privileges on the Payroll database. An enterprise role can be granted to or revoked from one or more enterprise users. For example, you could grant the enterprise role clerk to a number of enterprise users who hold the same job. This information is protected in the directory, and only you, as the administrator, can manage users and grant and revoke their roles.

A user can be granted local roles and privileges in a database, in addition to enterprise roles. Chapter 6, "Administering Enterprise User Security". A long-standing security problem has been that of limiting how users access data, to prevent users from bypassing application logic to access data directly. For example, in web-based applications, even if users are known to the database, it may not be desirable to allow them to have direct access to data.

To date, this has been a very difficult security problem to solve, because there has been no secure way to validate which application is used to access data. For example, a malicious user could write a program that appears to be a valid human resources application.

One way to address this challenge is through a secure application role: a role implemented by a package. The package can perform any desired validation to ensure that the appropriate conditions are met before the user can exercise privileges granted to the role in the database.

The database ensures that it is only the trusted package implementing the role that determines the correct access conditions. A secure application role is used by an application, can only be enabled by the application, and does not need a password. Through stored procedures you can restrict the database operations that users can perform. You can allow them to access data only through procedures and functions that execute with the definer's privileges. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself.

When a user invokes the procedure, the procedure executes with the privileges of the procedure's owner. Users who have only the privilege to execute the procedure but not the privileges to query, update, or delete from the underlying tables can invoke the procedure, but they cannot manipulate table data in any other way.

Database roles can potentially be mapped to external services such as DCE groups and RADIUS authorizations so that you can centrally manage and administer privileges for all network resources--of which databases are only one piece.

Chapter 3, "Protecting Data in a Network Environment". Rather than granting users privileges on a particular table, you can give them access to a view of the table. Views add two more levels of security:. To use a view requires appropriate privileges only for the view itself.

The user need not be given privileges on base objects underlying the view. Figure shows an example of a view called staff derived from the base table emp. Notice that the view shows only five of the columns in the base table. A much more granular form of data access is row level access. For any table with data, access to particular rows can be based on such considerations as the department to which employees belong, their job responsibility or title, or other significant factors.

In the past, complex and dynamic views have been used to implement row level security. There are, however, two more effective approaches to this problem: Virtual Private Database VPD , in which you create your own implementation of row level security; and label-based access control, in which you customize a ready-made VPD policy to accomplish this. This section describes these alternative approaches. Complex views and dynamic views are among the historical approaches to row level security.

Complex view definitions result when application designers build their own user security tables and join the application tables with the new security table based on the name of the application user. This approach usually requires many complex view definitions which must be maintained as security requirements change.

Another approach is dynamic view creation. This approach uses dynamic DDL execution utilities to define new view definitions based on the identity of the application user. Using dynamic views, however, is costly and time consuming. Virtual Private Database is the ability to perform query modification based on a security policy you have defined in a package, and associated with a table, view, or synonym.

Virtual private database provides fine-grained access control which is data-driven, context-dependent, and row-based. It is a key enabling technology in building three-tier systems which expose mission-critical resources to customers and partners.

Label-based access control allows organizations to assign sensitivity labels to data rows, control access to data based on those labels, and ensure that data is marked with the appropriate sensitivity label. The most familiar example of this is perhaps the security classification system used by the United States and other governments.

Access to data labeled at a certain level such as SECRET is restricted to those users who have been granted that level of access or higher. While e-businesses do not typically have label data classification systems, they almost always have data labeling requirements. For example, an e-business may differentiate between Company Confidential information and Public information.