Lecture 16
-----------

VIEWS:
--------


SELECT *
FROM
   (SELECT baker, fullname, age
    FROM bakers
    WHERE baker not in (select baker
                        from results
                        where result = 'eliminated')
   ) as noteliminated
WHERE noteliminated.age < 45;

WITH noteliminated AS
(SELECT baker, fullname, age
    FROM bakers
    WHERE baker not in (select baker
                        from results
                        where result = 'eliminated')
)
SELECT *
FROM noteliminated
WHERE age < 45;


CREATE VIEW noteliminated(baker, name, age)
AS
SELECT baker, fullname, age
FROM bakers
WHERE baker not in (select baker
                    from results
                    where result = 'eliminated');




SELECT *
FROM noteliminated
WHERE age < 45;

---> Simplify application logic/ access control rules

---> View may hide certain optimizations or logic


CREATE VIEW  lt40(baker, fullnamename, age)
AS
SELECT baker, fullname, age
FROM bakers
WHERE age < 40 ;

-- updateable views: views in which tuples in the base relation can
be changed through the view

-> view has no joins, distinct, group by



UPDATE lt40 SET age = 40 WHERE baker = 'Manon' ;



-- check option

CREATE VIEW  lt40(baker, fullnamename, age)
AS
SELECT baker, fullname, age
FROM bakers
WHERE age < 40
WITH CHECK OPTION;



-------------

System Tables


select viewname, definition from pg_views where viewowner = 'sibeladali' ;

-------------

DB Server:

Users: have login privileges

Roles: do not have login privileges


Users/Roles: have access control privileges

DATABASES

Data objects: tables/functions/etc.


CREATE ROLE dbstudent;
GRANT dbstudent to sibelstudent ;

GRANT dbstudent to dbta ;

GRANT dbstudent to dbclass ;


GRANT CONNECT ON DATABASE baking TO dbstudent ;

GRANT SELECT ON bakers TO dbstudent ;

GRANT INSERT ON bakers TO dbta ;

grant/revoke on: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES,
TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

---------------------

CREATE ROLE joe LOGIN INHERIT;


CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;

--------------

REVOKE INSERT FROM dbta ;


GRANT INSERT ON bakers TO dbta WITH GRANT OPTION ;

------------------