- Master various integrity definition methods of basic tables.
- Master the creation and use of users.
- Master the method of SQL statement for security control of database and table.
- Use the graphical user interface to add the following constraints and indexes to the Student library, and verify the default treatment of the database system in case of violation of relevant integrity.
(1) Non empty constraint of s table: add non empty constraint for birth date.
(2) Primary key constraint of table s: set student number (sno) as primary key.
(3) Unique constraint of s table: add a unique constraint (only one key) for the name (sname).
(4) Default constraint of table s: add a default value for gender (sgender), whose value is "male".
- Using the graphical user interface, add foreign key constraints to the SC table for the Student library, and set SnO and CNO as foreign keys. The reference tables are S table and C table respectively, and the foreign key names are: sc_fk_sno and sc_fk_cno. Try different strategies that violate foreign key constraints and verify NO action/ restrict/ cascade/ set null. (10 points)
NO ACTION
CASCADE
SET NULL
- Delete the three tables in the student library and recreate the tables with the following constraints in SQL language. (10 points)
- Table S: the basic constraint requirements are the same as those in sub question 1.
- Table C: cpno is the foreign key of table C, which is referenced from cno of table C.
- SC table: the foreign key constraint is as in the second sub question, and the valid value of grade is required to be [0100]. In addition, a new column id is added to the SC table: set the id as the primary key and add a field automatically. Each time a course selection record is inserted, it will be automatically + 1.
S table creation
CREATE TABLE student.s ( SNO VARCHAR(45) NOT NULL, SNAME VARCHAR(45) NULL, SGENDER VARCHAR(45) NULL DEFAULT 'male', SBIRTH VARCHAR(45) NOT NULL, SDEPT VARCHAR(45) NULL, PRIMARY KEY (`SNO`), UNIQUE INDEX `SNAME_UNIQUE` (`SNAME` ASC) VISIBLE);
C table creation
CREATE TABLE student.c ( CNO VARCHAR(45) NOT NULL, CNAME VARCHAR(45) NULL, CPNO VARCHAR(45) NULL, CREDIT INT NULL, PRIMARY KEY (CNO), FOREIGN KEY (CPNO) REFERENCES student.c (CNO) );
SC table creation
CREATE TABLE SC ( ID INT NOT NULL AUTO_INCREMENT, SNO VARCHAR(45) NULL, CNO VARCHAR(45) NULL, GRADE INT NULL CHECK(GRADE >= 0 AND GRADE <= 100), PRIMARY KEY (ID), CONSTRAINT sc_fk_cno FOREIGN KEY (CNO) REFERENCES c (CNO), CONSTRAINT sc_fk_sno FOREIGN KEY (SNO) REFERENCES s (SNO) );
- Add or remove the following integrity constraints for student library in SQL language. (10 points)
(1) Add sgender value constraint: the sgender value in table S can only be male or female.
ALTER TABLE S ADD CHECK(SGENDER IN ('male','female') );
(2) Delete the sgender value constraint created in sub question (1).
ALTER TABLE S DROP CONSTRAINT S_CHK_1;
(3) Delete the foreign key constraint of the SC table.
ALTER TABLE SC DROP CONSTRAINT sc_fk_cno, DROP CONSTRAINT sc_fk_sno;
(4) A new column tname (indicating the name of the student'S tutor) is added to the student table S, and the tutor name must be all letters (both uppercase and lowercase) and no less than 8 characters in length.
ALTER TABLE S ADD COLUMN TNAME VARCHAR(45) NULL CONSTRAINT C CHECK(char_length(TNAME) >= 8 AND TNAME like '^[A-Za-z]+$');
- Create new users in the graphical user interface and empower the student database. (20 points)
(1) Create two local users who can log in: Wang Ming and Li Yong.
(2) Complete the following permissions:
① User Wang Ming has the right to select and insert all tables.
② User Li Yong has select, insert, delete, update and create permissions on the database.
After entering, select Add Account to create a local user, enter Schema Privileges and select Add Entry to grant permissions.
- Authorize and withdraw permissions with SQL statements and verify permissions. (40 points)
Requirements: firstly, create employee table e and department table d. the table structure is as follows:
Employee form e (employee number, name, age, position, salary, department number)
Department table d (department number, name, manager name, address, telephone number)
Then create the users involved in each question, complete the authorization and verify the permission for each small question, and then withdraw the permission and verify the permission.
(1) User Wang Ming has query permission on the two tables.
CREATE USER 'wangming'@'localhost'; GRANT SELECT ON TABLE e TO 'wangming'@'localhost'; GRANT SELECT ON TABLE d TO 'wangming'@'localhost'; REVOKE SELECT ON TABLE e FROM 'wangming'@'localhost'; REVOKE SELECT ON TABLE d FROM 'wangming'@'localhost';
(2) User Li Yong has insert and delete permissions on the two tables.
CREATE USER 'liyong'@'localhost'; GRANT INSERT,DELETE ON TABLE e TO 'liyong'@'localhost'; GRANT INSERT,DELETE ON TABLE d TO 'liyong'@'localhost'; SHOW GRANTS FOR 'liyong'@'localhost'; REVOKE INSERT,DELETE ON TABLE e FROM 'liyong'@'localhost'; REVOKE INSERT,DELETE ON TABLE d FROM 'liyong'@'localhost';
(3) Each employee only has the right to query his own records.
CREATE VIEW view_employee AS SELECT * FROM e WHERE CONCAT(ENAME,'@localhost') = user(); GRANT SELECT ON view_employee TO 'wangming'@'localhost' , 'liyong'@'localhost' , 'liuxing'@'localhost' , 'zhangxin'@'localhost' , 'zhouping'@'localhost' , 'yanglan'@'localhost';
(4) User Liu Xing has query permission on the employee table and update permission on the salary field.
CREATE USER 'liuxing'@'localhost'; GRANT SELECT ON TABLE e TO 'liuxing'@'localhost'; GRANT UPDATE(salary) ON TABLE d TO 'liuxing'@'localhost'; REVOKE SELECT ON TABLE e FROM 'liuxing'@'localhost'; REVOKE UPDATE(salary) ON TABLE d FROM 'liuxing'@'localhost';
(5) User Zhang Xin has permission to modify the structure of these two tables.
CREATE USER 'zhangxin'@'localhost'; GRANT ALTER ON TABLE e TO 'zhangxin'@'localhost'; GRANT ALTER ON TABLE d TO 'zhangxin'@'localhost'; REVOKE ALTER ON TABLE e FROM 'zhangxin'@'localhost'; REVOKE ALTER ON TABLE d FROM 'zhangxin'@'localhost';
(6) User Zhou Ping has all permissions on the two tables and has the permission to authorize other users.
CREATE USER 'zhouping'@'localhost'; GRANT ALL ON TABLE e TO 'zhouping'@'localhost' WITH GRANT OPTION; GRANT ALL ON TABLE d TO 'zhouping'@'localhost' WITH GRANT OPTION; REVOKE ALL PRIVILEGES ON TABLE e FROM 'zhouping'@'localhost'; REVOKE ALL PRIVILEGES ON TABLE d FROM 'zhouping'@'localhost'; REVOKE GRANT OPTION ON TABLE e FROM 'zhouping'@'localhost'; REVOKE GRANT OPTION ON TABLE d FROM 'zhouping'@'localhost';
(7) User Yang Lan has the permission to query the maximum wage, minimum wage and average wage from employees in each department. He cannot view everyone's salary.
CREATE VIEW view_yanglan AS SELECT max(salary),min(salary),avg(salary) FROM e; CREATE USER 'yanglan'@'localhost'; GRANT SELECT ON view_yanglan TO 'yanglan'@'localhost'; REVOKE SELECT ON view_yanglan FROM 'yanglan'@'localhost';