Visual Basic® .NET Database Programming

Controlling Access with Views

Using privileges and roles, you can control exactly which users have access to which columns in a table. Another common method for controlling access to data is to create a view that selects specific fields from one or more tables. Then you can grant permission to use the view without granting permission to use the underlying table.

For example, suppose the Clerks role needs to view the Customers table’s CompanyName field but nothing else. The following SQL script code creates a view named CustomerNames that selects only that field. It then grants select access to the view for the Clerks role:

 # Create the view. CREATE VIEW CustomerNames AS SELECT CompanyName FROM Customers; # Give Clerks permission to select ...