Simple and Complex Views in SQL:-
Using grant & revoke we can restrict user operations. |
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}