Creating and Managing VIEWS and User Access

1. Creating and Managing VIEWSand User Access Spring 2013

2. Create View Statement SYNTAX: CREATE [OR REPLACE] VIEW view_name AS Select … (any valid select statement here) [WITH CHECK OPTION] • A View is logically equivalent to a saved query in ACCESS

3. VIEWs Retrieve Data Dynamically • What is saved is the SELECT statement and that statement is re-executed each time the view is retrieved. • Thus, if I add, delete or modify data that is retrieved by the SELECT statement of the view, the new data will be used the next time I retrieve the view. • Also, selecting the view causes its select statement to be re-executed each time.

4. Purposes of views • Present end users with the data they need for decision-making while hiding the complexities of the database. • E.g. a view that joins several tables and uses aggregate functions but looks to end users like a single simple table. • Limit and control user access to data. • E.g. a user needs to be able to see and manipulate some rows and/or columns in a table, but not others.

5. View Type 1 - Simplification • The view’s Select statement generally involves complex functions and joins, but should use simple column aliases. • Once created the view can be treated much like a table • Can describe it, can SELECT from it • Generally, cannot insert, update, or delete this type of view

6. Simplifying View Example CREATE OR REPLACE VIEW HR_WORK_HOURS AS Select Emp_id, emp_name, wage_rate, trunc(start_time, ‘DD’) as Work_date, (end_time-start_time)*24 as hours from employee e NATURAL JOIN EMP_HOURS ; • Now we can treat this like a Table & select from IT SELECT emp_name, hours FROM HR_WORK_HOURS where Work_date = ‘14-SEP-13’;

7. Simplifying VIEWS Continued • From the User’s Perspective their view is the table SQL> describe HR_WORK_HOURS Name Null? Type ------------------------- -------- ------------------ EMP_ID NOT NULL NUMBER(3) E_NAME NOT NULL VARCHAR2(15) WAGE_RATE NUMBER(7,2) WORK_DATE DATE HOURS NUMBER • Can query the view as if it were a table thus hiding complexity • Can also provide derived attributes to the user as if they were stored

8. View Type 2 – Access Control • The view will be used to allow users access to selected parts of a table, generally for purposes that include inserts, updates and deletes • Each row in the view must map cleanly to a single row in an underlying table

9. Access Control Views - Rules • Generally must address just a single table in the outer SELECT • Can’t use table aliases in column specifications • Can’t use any aggregate functions • Should include the WITH CHECK OPTION clause • This clause ensures that any table rows INSERTedor UPDATEd thru the view continue to meet its WHERE clause criteria

10. Access Control View Example CREATE OR REPLACE VIEW AZ_CUSTOMER AS SELECT * FROM CUSTOMER WHERE State = ‘AZ’ WITH CHECK OPTION; update jnm2.az_customer set l_name = ‘Green’ where Cust_No = 10; Works if user has UPDATE privilege on the VIEW and if the Cust_no identified here lives in Arizona

11. GRANTing Rights to Access Control Views • By default only the creator of a view (or a table) has rights to access it. • Rights are given to others thru the GRANT statement: GRANT action ON object TO user GRANT SELECT ON AZ_Customer TO jg7; • Granting a user access to the view and not the table it comes from lets them see and manipulate only the parts of the table for which they need to have access.

12. GRANTs and REVOKEs • Rights can also be granted to tables: GRANT SELECT, INSERT ON CUSTOMER TO jg7; • Rights to a table or view are revoked through the REVOKE statement REVOKE INSERT ON CUSTOMER FROM jg7;

13. ROLE BASED SECURITY • Most multi-user database use role-based security • A user’s needs to access database objects are determined by her/his ROLE in the organization, so: • GRANT rights to database objects to ROLEs • Then GRANT these ROLEs to users who occupy these roles.

14. ROLE BASED SECURITYStatement Syntax CREATE ROLE role_name; CREATE ROLE Sales_Clerk; • Then use the GRANT statement covered on a previous slide • Rights can be GRANTed to a ROLE & • ROLEs can be GRANTed to Users. E. G. GRANT SELECT ON AZ_Customer TO Sales_Clerk; & GRANT Sales_Clerk TO jg7;

15. Role Based Security – Why? • There may be many users performing the same role • e.g. 25 sales clerks • A given role may require access to many database objects • E.g. sales clerks need access to 20 different tables and views • Using ROLEs simplifies assignment of rights

16. GRANTING RIGHTS WITH AND WITHOUT ROLES • Based upon our sales clerk example: • With ROLES • 1 CREATE ROLE Statement • 20 GRANT statements (1 for each object) • 25 statements GRANTing the ROLE to users • Without Roles • 20 GRANT statements for each of the 25 users • 20*25 or 500 GRANT statements required

17. REVOKING RIGHTS WITH AND WITHOUT ROLES • If one of the sales clerks moves on to a different job (ROLE) • With ROLEs • 1 REVOKE statement to revoke the Sales_Clerk ROLE from the user • Without ROLEs • 20 revoke statements • 1 for each object to which the user had been granted rights