--creating logins
create login abcd with password='abc',check_expiration=off
--removing/deleting logins
drop login abcd
--removing database users form specific database
--first select your database, then excute the following command
drop user abc
--creating/mapping users for databases
create user haymanot for login haymanot with default_schema=[dbo]
--giving permissions for users using grant
--first select the database and excute the grant statement
--in this example i have a table inside the selectedc company database called EMPLOYEE
grant select on [Link] to haymanot
--Granting on views
--first create views and create user for views
CREATE VIEW [IT_STUDENTS] AS SELECT stud_id, stud_sex
FROM student where Dept_ID='IT'
grant select on IT_STUDENTS to haymanot
--revoking permissions
revoke select on EMPLOYEE from haymanot
፱፯'s ✨:
CREATE DATABASE CompanyDB;
GO
USE CompanyDB;
GO
-- Create EMPLOYEE table
CREATE TABLE EMPLOYEE (
Fname VARCHAR(50),
Minit CHAR(1),
Lname VARCHAR(50),
Ssn CHAR(9) PRIMARY KEY,
Bdate DATE,
Address VARCHAR(100),
Sex CHAR(1),
Salary DECIMAL(10,2),
Super_ssn CHAR(9),
Dno INT,
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
);
-- Create DEPARTMENT table
CREATE TABLE DEPARTMENT (
Dname VARCHAR(50),
Dnumber INT PRIMARY KEY,
Mgr_ssn CHAR(9),
Mgr_start_date DATE,
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
);
-- Create DEPT_LOCATIONS table
CREATE TABLE DEPT_LOCATIONS (
Dnumber INT,
Dlocation VARCHAR(50),
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)
);
-- Create PROJECT table
CREATE TABLE PROJECT (
Pname VARCHAR(50),
Pnumber INT PRIMARY KEY,
Plocation VARCHAR(50),
Dnum INT,
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)
);
-- Create WORKS_ON table
CREATE TABLE WORKS_ON (
Essn CHAR(9),
Pno INT,
Hours DECIMAL(5,2),
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)
);
-- Create DEPENDENT table
CREATE TABLE DEPENDENT (
Essn CHAR(9),
Dependent_name VARCHAR(50),
Sex CHAR(1),
Bdate DATE,
Relationship VARCHAR(20),
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)
);
-- Add foreign key constraint from EMPLOYEE to DEPARTMENT
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber);
-- Grant privileges on all tables except DEPENDENT
GRANT SELECT, INSERT, UPDATE, DELETE ON
EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT, WORKS_ON
TO A WITH GRANT OPTION;
-- Alternatively grant individually for clarity
GRANT ALL PRIVILEGES ON EMPLOYEE TO A WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON DEPARTMENT TO A WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON DEPT_LOCATIONS TO A WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON PROJECT TO A WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON WORKS_ON TO A WITH GRANT OPTION;-- Create view for EMPLOYEE without
sensitive attributes
CREATE VIEW EMPLOYEE_PUBLIC AS
SELECT Ename, Minit, Lname, Ssn, Bdate, Address, Sex, Super_ssn, Dno
FROM EMPLOYEE;
-- Create view for DEPARTMENT without sensitive attributes
CREATE VIEW DEPARTMENT_PUBLIC AS
SELECT Dname, Dnumber
FROM DEPARTMENT;
-- Grant SELECT on these views to B
GRANT SELECT ON EMPLOYEE_PUBLIC TO B;
GRANT SELECT ON DEPARTMENT_PUBLIC TO B;-- Grant full privileges on WORKS_ON
GRANT SELECT, INSERT, UPDATE, DELETE ON WORKS_ON TO C;
-- Create views for limited EMPLOYEE and PROJECT attributes
CREATE VIEW EMPLOYEE_LIMITED AS
SELECT Ename, Minit, Lname, Ssn
FROM EMPLOYEE;
CREATE VIEW PROJECT_LIMITED AS
SELECT Pname, Pnumber
FROM PROJECT;
-- Grant SELECT on these views to C
GRANT SELECT ON EMPLOYEE_LIMITED TO C;
GRANT SELECT ON PROJECT_LIMITED TO C;
-- For part B (if column-level privileges are supported)
GRANT SELECT (Ename, Minit, Lname, Ssn, Bdate, Address, Sex, Super_ssn, Dno)
ON EMPLOYEE TO B;
GRANT SELECT (Dname, Dnumber) ON DEPARTMENT TO B;
-- For part C (if column-level privileges are supported)
GRANT SELECT (Ename, Minit, Lname, Ssn) ON EMPLOYEE TO C;
GRANT SELECT (Pname, Pnumber) ON PROJECT TO C;
GRANT SELECT, INSERT, UPDATE, DELETE ON WORKS_ON TO C;
-- Create logins (server-level) and users (database-level)
CREATE LOGIN A WITH PASSWORD = 'password_A';
CREATE LOGIN B WITH PASSWORD = 'password_B';
CREATE LOGIN C WITH PASSWORD = 'password_C';
-- Switch to your database context
USE YourDatabaseName;
-- Create database users mapped to logins
CREATE USER A FOR LOGIN A;
CREATE USER B FOR LOGIN B;
CREATE USER C FOR LOGIN C;
WEGDERES~NSW:
CREATE DATABASE CompanyDB;
USE CompanyDB;
-- Create EMPLOYEE table
CREATE TABLE EMPLOYEE (
Fname VARCHAR(50),
Minit CHAR(1),
Lname VARCHAR(50),
Ssn CHAR(9) PRIMARY KEY,
Bdate DATE,
Address VARCHAR(100),
Sex CHAR(1),
Salary DECIMAL(10,2),
Super_ssn CHAR(9),
Dno INT,
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
);
-- Create DEPARTMENT table
CREATE TABLE DEPARTMENT (
Dname VARCHAR(50),
Dnumber INT PRIMARY KEY,
Mgr_ssn CHAR(9),
Mgr_start_date DATE,
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
);
-- Create DEPT_LOCATIONS table
CREATE TABLE DEPT_LOCATIONS (
Dnumber INT,
Dlocation VARCHAR(50),
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)
);
-- Create PROJECT table
CREATE TABLE PROJECT (
Pname VARCHAR(50),
Pnumber INT PRIMARY KEY,
Plocation VARCHAR(50),
Dnum INT,
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)
);
-- Create WORKS_ON table
CREATE TABLE WORKS_ON (
Essn CHAR(9),
Pno INT,
Hours DECIMAL(5,2),
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)
);
-- Create DEPENDENT table
CREATE TABLE DEPENDENT (
Essn CHAR(9),
Dependent_name VARCHAR(50),
Sex CHAR(1),
Bdate DATE,
Relationship VARCHAR(20),
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)
);
-- Add foreign key constraint from EMPLOYEE to DEPARTMENT
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber);
-- Create logins (server-level) and users (database-level)
CREATE LOGIN A WITH PASSWORD = 'password_A';
CREATE LOGIN B WITH PASSWORD = 'password_B';
CREATE LOGIN C WITH PASSWORD = 'password_C';
-- Switch to your database context
USE CompanyDB;
-- Create database users mapped to logins
CREATE USER A FOR LOGIN A;
CREATE USER B FOR LOGIN B;
CREATE USER C FOR LOGIN C;
-- Grant privileges on all tables except DEPENDENT
GRANT SELECT, INSERT, DELETE ON
EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT, WORKS_ON
TO A WITH GRANT OPTION;
-- Alternatively grant individually for clarity
GRANT ALL PRIVILEGES ON EMPLOYEE TO A WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON DEPARTMENT TO A WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON DEPT_LOCATIONS TO A WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON PROJECT TO A WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON WORKS_ON TO A WITH GRANT OPTION;-- Create view for EMPLOYEE without
sensitive attributes
CREATE VIEW EMPLOYEE_PUBLIC AS
SELECT Ename, Minit, Lname, Ssn, Bdate, Address, Sex, Super_ssn, Dno
FROM EMPLOYEE;
-- Create view for DEPARTMENT without sensitive attributes
CREATE VIEW DEPARTMENT_PUBLIC AS
SELECT Dname, Dnumber
FROM DEPARTMENT;
-- Grant SELECT on these views to B
GRANT SELECT ON EMPLOYEE_PUBLIC TO B;
GRANT SELECT ON DEPARTMENT_PUBLIC TO B;-- Grant full privileges on WORKS_ON
GRANT SELECT, INSERT, UPDATE, DELETE ON WORKS_ON TO C;
-- Create views for limited EMPLOYEE and PROJECT attributes
CREATE VIEW EMPLOYEE_LIMITED AS
SELECT Ename, Minit, Lname, Ssn
FROM EMPLOYEE;
CREATE VIEW PROJECT_LIMITED AS
SELECT Pname, Pnumber
FROM PROJECT;
-- Grant SELECT on these views to C
GRANT SELECT ON EMPLOYEE_LIMITED TO C;
GRANT SELECT ON PROJECT_LIMITED TO C;
-- For part B (if column-level privileges are supported)
GRANT SELECT (Ename, Minit, Lname, Ssn, Bdate, Address, Sex, Super_ssn, Dno)
ON EMPLOYEE TO B;
GRANT SELECT (Dname, Dnumber) ON DEPARTMENT TO B;
-- For part C (if column-level privileges are supported)
GRANT SELECT (Ename, Minit, Lname, Ssn) ON EMPLOYEE TO C;
GRANT SELECT (Pname, Pnumber) ON PROJECT TO C;
GRANT SELECT, INSERT, UPDATE, DELETE ON WORKS_ON TO C;