Use views to give users access to only one portion of a table.
You can define a portion in terms of rows or columns. For example, you may want to disallow a group of users from seeing the Salary column of an Employees table, or you may want to allow a user to see only the rows of a table that he or she have created.
The sales manager needs access to information in the database concerning employees in the department. However, there is no reason for the manager to have access to information about employees in other departments.
Create a user ID for the sales manager, create views that provide the information needed, and grant the appropriate privileges to the sales manager user ID.
CONNECT "DBA" IDENTIFIED by sql; GRANT CONNECT TO SalesManager IDENTIFIED BY sales
CREATE VIEW emp_sales AS SELECT EmployeeID, GivenName, Surname FROM "DBA".Employees WHERE DepartmentID = 200
GRANT SELECT ON emp_sales TO SalesManager
This example creates a view, which allows the sales manager to look at a summary of sales orders. This view requires information from more than one table for its definition:
CREATE VIEW order_summary AS SELECT OrderDate, Region, SalesRepresentative FROM "GROUPO".SalesOrders KEY JOIN "GROUPO".Customers
GRANT SELECT ON order_summary TO SalesManager
CONNECT SalesManager IDENTIFIED BY sales ; SELECT * FROM "GROUPO".emp_sales ; SELECT * FROM "GROUPO".order_summary ;
No privileges have been granted to SalesManager to look at the underlying tables. Therefore, these commands produce privilege errors:
SELECT * FROM "DBA".Employees ; SELECT * FROM "DBA".SalesOrders;
These examples show how to use views to tailor SELECT privileges. You can grant INSERT, DELETE, and UPDATE privileges on views in the same way.