8.2.2 creating users and empowering them as MySQL administrators, the most common task is to maintain user information--adding and removing users and managing their permissions in MySQL. Manage user permissions by using the GRANT and REVOKE commands in the MySQL console.
The grant command for the 1.grant command MySQL almost exactly follows the SQL92 syntax, and its general format is:
Grant <privilege> on <object> to <user> [identified by User-password] [with GRANT option];
The privilege values that can be given are as follows:
Value Description
Alter ALTER TABLE and INDEX
Create DATABASE and tables
Delete deletes data from the database
Drop Delete database and table
Index management Indexes
Insert adds data to the database
Lock tables allows locking of tables
Select Extract Data
Update modifies data
All above
Some commands also have other options, such as CREATE view, which grants the user the privilege of creating views.
The objects that are granted privileges are identified as:
Databasename.tablename
In a Linux system, * represents a wildcard character, so *. * represents each object in each database, and foo.* represents each table in the database foo.
If the specified user already exists, its privileges are edited to reflect the modifications. If the user does not exist, it is created with the specified privileges.
In SQL syntax, the special character% represents a wildcard, which acts exactly like the * symbol in the shell environment. If you want to grant users Rick from the wiley.com domain and host access permissions, you can describe Rick as:
[Email protected] '%.wiley.com '
Any time you use a% wildcard, you must enclose it in quotation marks to separate it from other text.
You can also use the ip/netmask identifier (N.N.N.N/M.M.M.M) to set a network address for access control.
As previously used [email protected] ' 192.168.0.0/255.255.255.0 ' to grant Rick the privilege of connecting to any machine on the local network, you can also specify [email protected] ' 192.168.0.1 ' To restrict Rick's access to a workstation, or specify [email protected] ' 192.0.0.0/255.0.0.0 ' to extend the scope to include 192 of all machines in this Class A network.
Here is another example:
Mysql> GRANT all on foo.* to [e-mail protected] '% ' identified by ' bar;
This will create the user Rick, which has all the permissions on the database foo and can connect from any machine with the initial password bar.
If the database foo does not already exist, then user Rick will now have permission to create the database using the SQL command.
The identified by clause is optional, but it is best to make sure that they are both set with a password while creating the user.
An underscore in SQL is a pattern that matches any single character, which is very similar to the% match for a string.
In general, with GRANT option will only be used to create level two administrators. However, it can also allow a newly created user to grant its privileges to other users.
2. Revoke command administrators can not only grant user permissions, but also to deprive users of permissions, which is done by the revoke command.
Revoke <a_privilege> on <an_object> from <a_user>
This is similar to the format of the grant command. For example:
Mysql> REVOKE INSERT on foo.* from [email protected] '% ';
However, the revoke command cannot delete users, and if you want to completely remove a user, do not just modify their permissions, but apply revoke to remove them. You can then switch to the internal MySQL database to completely remove a user by removing the corresponding row from the user table:
mysql> use MySQL;
Mysql> DELETE from user WHERE user = "Rick";
mysql> FLUSH privileges;
Because no host is specified, you can ensure that every instance of the MySQL user you want to delete (in this case, Rick) is deleted. After completing this, be sure to return to your own database (using the use command), otherwise it will still be in MySQL's own internal database.
Delete is not in the same category as Grant and revoke, and because of the need for MySQL to handle permissions, the SQL syntax here is required. The modification is done effectively by updating the MySQL permissions table directly (and therefore invoking the command using MySQL first).
After updating the table, you must use the command flush privileges to tell the MySQL server that it needs to reload its permissions table.
8.2.3 Password
If you need to change the password, you need to connect to the MySQL server as the root user, and then update the user information directly. For example:
mysql> use MySQL;
Mysql> SELECT Host, user, password from user;
If you want to specify a password bar for user foo, you can do this:
mysql> UPDATE user SET password = password (' bar ') WHERE user = ' foo ';
8.2.4 Creating a database if you want a database named Rick, you created a user with the same name. The first thing you need to do is give the user Rick a wide range of permissions to allow it to create a new database, which is especially useful for a development system, because it gives the user greater flexibility.
Mysql> GRANT All on * * to [e-mail protected] identified by ' Secretpassword ';
Now log in as the Rick user and create the database to test the permission settings:
$mysql-U rick-p
Enter Password:
...
Mysql> CREATE DATABASE Rick;
Tell MySQL to use the new database:
mysql> use Rick;
Now you can add the desired tables and information to the database. In future logins, you can specify the database at the end of the command line, and you no longer need the use command:
$mysql-U rick-p Rick
After you follow the prompts to enter a password, as part of the connection process, by default, you will automatically switch to using the database Rick.
8.2.5 data types Now, there is a MySQL server that can run, a secure user login, and a prepared database. Next, you need to create some tables that contain columns to hold the data. However, prior to this, you need to understand the data types supported by MySQL.
MySQL's data types are very standard:
1. A Boolean type can use the keyword bool to define a Boolean sequence, which will have true and false, or it can hold null.
2. Character types are available in a variety of character types, the first 3 are standard, and the last 3 are MySQL-specific:
Definition description
CHAR single character
CHAR (n) is a string of exactly n characters, populated if necessary with a space character. The limit is 255 characters.
A variable-length array of VARCHAR (n) n characters. The limit is 255 characters.
Tinytext similar to varchar (N)
3. Numeric type numeric types are categorized as Integer and floating-point. As shown below:
Define type description
TINYINT integer 8-bit data type
SMALLINT integer 16-bit data type
int integer 32 data type, which is the standard type.
Double (d,n) float signed double-precision floating-point number with D-digits and N-decimals
NUMERIC (p,s) The total floating-point type is the true number of P-bit, with the S digit after the decimal point. Unlike a double, this is an accurate number, so it is suitable for storing currency values, but processing is less efficient.
4. The time type has 5 time data types that you can choose to use, as follows:
Definition description
Date is stored from January 1, 1000--before December 31, 9999
Time stored from -838:59:59--838:59:59
DateTime Store date from January 1, 1000-the last second of December 31, 9999
TIMESTAMP store timestamp from January 1, 1970--2037
Year storage years
8.2.6 Creating a table now that you have run the database server, learned how to assign user permissions and how to create a database and some basic database types, you are ready to create a table.
A database is simply a series of rows, and each row is made up of a fixed number of columns. It is much like a spreadsheet, except that each row must contain columns of the same number and type, and each row must be in some way different from the other rows in the table.
The complete SQL syntax for creating database objects is called DDL (data Definition Language).
The basic syntax for creating a table is:
CREATE TABLE <table_name> (
Colum Type[null | Not NULL] [auto_increment] [PRIMARY KEY]
[, ...]
[, PRIMARY KEY (Colum [, ...])]
)
You can delete a table using the drop table syntax:
DROP TABLE <table_name>;
Keyword description
Auto_increment This keyword tells MySQL that, in any case, when a null value is written to the column, an assigned increment number is automatically filled into row the data. This is a very useful feature that can be used by MySQL to assign a unique number to a row in a table, although it can only be applied to columns that belong to the primary key.
Null a special database value, which is often used to denote "unknown" or "irrelevant", and syntax not NULL means that the row cannot store null values, which is useful for blocking some columns from holding null values.
PRIMARY KEY indicates that the data for this column must be unique, and the table should have a different value for the column in each row. There can be only one primary key per table.
Now create a table named children, which stores a unique number, name, and age for each child. Take the child's number as the primary key:
CREATE TABLE Children (
Children INTEGER auto_increment not NULL PRIMARY KEY,
FName VARCHAR (30),
Age INTEGER
);
Note that, unlike most programming languages, the column name children before the column data type.
You can also use a different syntax to separate the column definition from the primary key definition:
Mysql> use Rick
mysql> CREATE TABLE Children (
Childno INTEGER auto_increment not NULL,
fname varchar (30),
Age INTEGER,
PRIMARY KEY (Childno)
);
The prompt for MySQL is on the line that is on the continuation. End the SQL command with a semicolon, indicating that the input has been completed and ready for the database to process the request.
If an error occurs, MySQL allows fallback to the previous command, edit it, and reenter it by pressing the ENTER key.
Now add data to the table, add the data using the SQL command insert, because the definition childno column is auto_incrment, so you don't need to provide data for this column, just let MySQL assign a unique number.
Mysql> INSERT into children (fname, age) VALUES ("Jenny", 21);
Mysql> INSERT into children (fname, age) VALUES ("Andrew", 17);
Use Select to extract data from the table to check that the data was added correctly:
Mysql> SELECT Childno, Fanme, age from children;
You can also use the * sign to represent the column, which lists all the columns in the table, as opposed to explicitly listing the columns you want to select. This is convenient for interactive use. However, in the product code, you should always explicitly specify the column you want to select.
Start an interactive session to the database server and switch to the Rick database. Then, enter the SQL command to create the table and create the columns with the rows that meet your needs. Once the SQL command is terminated with a semicolon, MySQL creates the table. Use the INSERT statement to add data to the new table, allowing the Childno column to be automatically assigned a number. Finally, use Select to display the data in the table.
Linux Programming--mysql Management (eighth chapter)
