Common MySQL permissions settings

A Instructions for use

1. mysql is an open source relational database management system (RDBMS), which uses the most common database management language-Structured Query Language (SQL) for database management.

Two Development Step Description

1. Installation

Go to https://www.mysql.com/download MySQL,

Then always click Next to install.

2. Click Mysql.exe to open the MySQL command window

If you already have the following actions:

To create a database s-t command:

Create Database s_t;

Create student Table Student

CREATE TABLE Student

(Sno CHAR (9) PRIMARY KEY,

Sname CHAR (a) UNIQUE,

Ssex CHAR (2),

Sage SMALLINT,

Sdept CHAR (20));

Create a curriculum course

CREATE TABLE Course

(Cno CHAR (4) PRIMARY KEY,

Cname CHAR (40),

Cpno CHAR (4),

Ccredit SMALLINT,

FOREIGN KEY (cpno) REFERENCES Course (Cno));

Create selected Timetable SC

CREATE TABLE SC

(Sno CHAR (9),

Cno CHAR (4),

Grade SMALLINT,

PRIMARY KEY (SNO,CNO),

FOREIGN KEY (Sno) REFERENCES student (Sno),

FOREIGN KEY (Cno) REFERENCES course (Cno));

INSERT INTO student

VALUES (' 200215121 ', ' Li Yong ', ' Male ', ' CS '),

(' 200215122 ', ' Liu Chen ', ' female ', ' CS '),

(' 200215123 ', ' Wang Min ', ' female ', ' MA '),

(' 200215125 ', ' Zhang Li ', ' Male ', ' is ');

The following actions are required:

Create user U1 to log on to the local host with password 4444;

Command: Create user ' username ' @ ' localhost ' identified by ' password ';

Grant the query permission of the student table to U1;

Grant all permissions for course and SC tables to U1;

Grant the Modify permission of SC table to all users;

Grant the insert permission of the student table to U1 and allow it to delegate the permissions it holds;

Create user U2, the query student table and modify the student number of the permission to grant U2;

The permission to delete the Student student table and modify the student's name is granted to the user U1.

Recover the insert permission of student from U1;

U1 all permissions on the course table;

The user U2 modify the student number of the authority to recover;

What is a role?

In the database, to facilitate the management of users and permissions, a group of users with the same permissions can be organized together, a group of users with the same permissions is called role.

Create the role R1, Grant R1 the student query, modify permissions, and grant U1 the role R1;

Create role R1;

Grant select,update on table student to R1;

Grant R1 to [email protected];

Remove the permissions of the role R1 query student;

Revoke update on student from R1;

Create the Information student View Is_student, and grant the query permission of the student view Is_student to U1;

Three Related reference URLs

MySQL Tutorial:

http://www.oracle-dba.cn/yingjia/#001408

Common MySQL permissions settings