Simple and Complex Views in SQL:-

  • Grant & Revoke

Using grant & revoke we can restrict user operations.

  • Views

Using views we can restrict data [particular column]

A view is based on one table is called “simple views”.

Syntax :-

Create view <name> [<with options>] as select statement [where<condition>]

Example :-

Select user_name –> Dbo

Example of simple view   :-

Create view  v1 As Select empno, ename, sal from emp.

When the above view is created,the select statement is stored in database that is why view is also called  “stored-query”.

  • Grant select,insert,update,delete on v1 to NIT.
  • Go to nit user
  • Select * from v1

Once permission are granted to user the user can perform DML operators on base table through view.

{  Note  :-What ever changes the user, it is effected on base table {i:e.dbo}  }

Example  :-

  • Create view v2 as (it can be created on base table only) select * from emp where dept no=10.
  • Grant select, insert, update, delete on v2 to NIT. 
  • Create  view v3 as Select empno, ename, sal, deptno, from emp where deptno=10.

with check option in view in SQL:-

  • If view created with check option any DML operation through that view violates that where condition than DML operation is not accepted.

Example  :-  create view v4 As Select empno,ename,sal,deptno From  emp Where deptno =10 With check option

  • Insert into v4 values(8787,’abc’,6000,10)
  • Insert into v4 values (8788,’xyz’,9000,20) {invalid,because view created by with check option}