Title Untitled document - 2024-08-30T142143.116.pdf ✅

Experiment No. 5: Execute DCL commands to control the access to data using SQL Practical Related Questions 1. State the use of the ‘with grant options’ clause in the grant command. Answer: Click Here 2. Consider table EMPLOYEE and DEPARTMENT with following schema: EMP (empno, empname, salary, phno) Dept (deptno, empno, deptname, location, jobtype) Write the output of the following queries: • Create user Jay identifies by any admin; • Grant create table, create view to Jay; • Grant select, insert, update on Emp to Jay; • Grant select, update (deptno, empno) on Dept to Jay; • Alter user Jay identified by admin; • Revoke create table, create views from Jay; • Revoke select, insert, update on Emp from Jay; • Create role emp_pvr; • Grant create table, create views to emp_pvr; • Grant emp_pvr to Jay, John; Answer: 1. Create User Jay CREATE USER Jay IDENTIFIED BY admin; Output: User created. This command creates a new user Jay with the password admin. 2. Grant Create Table, Create View to Jay GRANT CREATE TABLE, CREATE VIEW TO Jay; Practicalkida.com


Grant succeeded. This command grants the CREATE TABLE and CREATE VIEW privileges to the role emp_pvr. 10. Grant Role emp_pvr to Jay and John GRANT emp_pvr TO Jay, John; Output: Grant succeeded. Exercise 1. Create the user Jay and implement the following commands on table EMP and Dept. Answer: 1. Create User Jay CREATE USER 'Jay'@'localhost' IDENTIFIED BY 'admin'; 2. Grant CREATE TABLE and CREATE VIEW Privileges to Jay GRANT CREATE TABLE, CREATE VIEW ON *.* TO 'Jay'@'localhost'; 3. Grant SELECT, INSERT, UPDATE Privileges on the EMP Table to Jay GRANT SELECT, INSERT, UPDATE ON your_database.EMP TO 'Jay'@'localhost'; 4. Grant SELECT and UPDATE Privileges on Specific Columns in the Dept Table to Jay GRANT SELECT, UPDATE (deptno, empno) ON your_database.Dept TO 'Jay'@'localhost'; 5. Alter the User Jay's Password Practicalkida.com