Creating and Managing VIEWS and User Access

1. Creating and Managing VIEWSand User Access Spring 2014

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 FAA_FLT_HOURS AS SELECT Emp_ID, Emp_Name, Wage_Rate, Flight_No, Flight_Date, (Chk_Out_Time - Chk_In_Time) * 24 as Hours_Worked FROM EMPLOYEE NATURAL JOIN EMP_FLT_HRS; • Now we can treat this like a Table & select from IT SELECT Emp_Name, Hours_Worked FROM FAA_FLT_HOURS WHERE FLIGHT_Date = '13-JAN-14';

7. Simplifying VIEWS Continued • From the User’s Perspective their view is the table DESCRIBE FAA_FLT_HOURS Name Null Type ------------------ -------- ------------------------- EMP_ID NOT NULL NUMBER(4) EMP_NAME NOT NULL VARCHAR2(20) WAGE_RATE NUMBER(7,2) FLIGHT_NO NOT NULL NUMBER(4) FLIGHT_DATE NOT NULL DATE HOURS_WORKED 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_FREQ_FLYER AS SELECT * FROM FREQUENT_FLYER WHERE State = ‘AZ’ WITH CHECK OPTION; update jnm2.az_freq_flyer set l_name = ‘Green’ where ff_No = 1003; 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 ; e.g. GRANT SELECT ON AZ_Freq_flyer 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 INSERT ON FLIGHT TO jg7; • Rights to a table or view are revoked through the REVOKE statement REVOKE INSERT ON FLIGHT FROM jg7;

13. GRANTing Rights To Only Certain Columns • GRANT Statement syntax becomes: GRANT action ON object(column, . . .) TO user; • E.g. GRANT UPDATE ON FLIGHT(Meal) TO Cheffy;

14. 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.

15. ROLE BASED SECURITYStatement Syntax CREATE ROLE role_name; CREATE ROLE Sales_Rep; • 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_Freq_Flyer TO Sales_Rep; & GRANT Sales_Rep TO jg7;

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

17. GRANTING RIGHTS WITH AND WITHOUT ROLES • Based upon our sales rep 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

18. 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