Practical 5
[XI] Practical related questions
2.
SQL> create table Emp_11(empno number(3), empname varchar2(20), salary
number(5), phno number(10));
Table created.
SQL> desc Emp_11;
Name Null? Type
----------------------------------------- -------- -------
EMPNO NUMBER(3)
EMPNAME VARCHAR2(20)
SALARY NUMBER(5)
PHNO NUMBER(10)
SQL> create table Dept_1(deptno number(3), empno number(3), deptname
varchar2(20), location varchar2(20), jobtype varchar2(20));
Table created.
SQL> desc Dept_1;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(3)
EMPNO NUMBER(3)
DEPTNAME VARCHAR2(20)
LOCATION VARCHAR2(20)
JOBTYPE VARCHAR2(20)
1. SQL> create user Jay identified by mitpoly;
User created.
2. SQL> grant create table, create view to Jay;
Grant succeeded.
3. SQL> grant select ,insert, update on Emp_11 to Jay;
Grant succeeded.
4. SQL> grant select , update(deptno , empno) on Dept_1 to Jay;
Grant succeeded.
5. SQL> Alter user Jay identified by admin;
User altered.
6. SQL> Revoke create table, create view from Jay;
Revoke succeeded.
7. SQL> Revoke select, insert, update on Emp_11 from Jay;
Revoke succeeded.
8. SQL> create role empp_pvr1;
Role created.
9. SQL> grant create table, create view to empp_pvr1;
Grant succeeded.
10. SQL> Grant empp_pvr1 to Jay, John;
Grant succeeded.
[XII] Exercise :
1. SQL> create user Jay identified by mitpoly;
User created.
2. SQL> grant select ,insert, delete on dept_1 to Jay;
Grant succeeded.
3. SQL> grant update(empno, salary)on Emp_11 to Jay;
Grant succeeded.
4. SQL> Revoke select ,insert, delete on Dept_1 from Jay;
Revoke succeeded.
5. SQL> create role dept1_pvr;
Role created.
6. SQL> grant create table, create view to dept1_pvr;
Grant succeeded.
7. SQL> create user john1 identified by mitpoly;
User created.
SQL> grant create table, create view to Jay,john1;
Grant succeeded.
8. SQL> grant select, insert, delete on Dept_1 to dept1_pvr;
Grant succeeded.
9. SQL> grant select, insert, delete on Dept_1 to Jay,john1;
Grant succeeded.
