Scribd
Upload
4 views2 pages

SQL Employee Management and Privileges

The document outlines SQL commands for creating and managing an Employee table, including inserting, updating, and deleting records within a transaction. It also details how to grant and revoke privileges for a user named employee_manager on the Employee table, emphasizing the importance of access control in database security. The use of transactions and privilege management ensures data integrity and security in database operations.

Uploaded by

saranyadevijaybalamurugan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views2 pages

SQL Employee Management and Privileges

The document outlines SQL commands for creating and managing an Employee table, including inserting, updating, and deleting records within a transaction. It also details how to grant and revoke privileges for a user named employee_manager on the Employee table, emphasizing the importance of access control in database security. The use of transactions and privilege management ensures data integrity and security in database operations.

Uploaded by

saranyadevijaybalamurugan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

-++++++++++++++++++++++++++++++++++++++++++++++++++++++++

----------------------transaction------------------------

-- Create a table
CREATE TABLE Employee (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

-- Insert some values


INSERT INTO Employee (name, department, salary)
VALUES
('John', 'IT', 50000.00),
('Alice', 'HR', 60000.00),
('Bob', 'Finance', 55000.00);

_____________________________________________________________________
select * from employee;

-- Start a transaction
START TRANSACTION;

-- Insert a new employee


INSERT INTO Employee (name, department, salary) VALUES ('Emily', 'Marketing',
48000.00);

-- Update the salary of an employee


UPDATE Employee SET salary = 52000.00 WHERE name = 'John';

-- Delete an employee
DELETE FROM Employee WHERE name = 'Bob';

select * from employee;


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ROLLBACK ;
select * from employee;

COMMIT;
select * from employee;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Let's grant and revoke privileges on the Employees table for a hypothetical user
named employee_manager.

Assuming you want to grant SELECT, INSERT, UPDATE, and DELETE privileges to
employee_manager on the Employees table:

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON Employees TO employee_manager;
_________________________________________________________

Now, the employee_manager user has the necessary privileges to perform these
operations on the Employees table.

If you later decide to revoke these privileges from the employee_manager user:

-- Revoke privileges
REVOKE SELECT, INSERT, UPDATE, DELETE ON Employees FROM employee_manager;
--------------------------------------------------------------------

After revoking the privileges, the employee_manager user will no longer have
permission to perform these operations on the Employees table.

Granting and revoking privileges allows you to control access to database objects,
ensuring that only authorized users can perform specific actions on those objects.
It's an essential aspect of database security and access control.

You might also like