MariaDB Crash Course - Ben Forta [82]
A default character set and collation are defined (usually by the system administration at installation time). In addition, when databases are created, default character sets and collations may be specified too. To determine the character sets and collations in use, use these statements:
Input
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
In practice, character sets can seldom be serverwide (or even databasewide) settings. Different tables, and even different columns, may require different character sets, and so both may be specified when a table is created.
To specify a character set and collation for a table, CREATE TABLE (seen in Chapter 21, “Creating and Manipulating Tables”) is used with additional clauses:
Input
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
Analysis
This statement creates a two column table, and specifies both a character set and a collate sequence.
In this example both CHARACTER SET and COLLATE were specified, but if only one (or neither) is specified, this is how MariaDB determines what to use:
• If both CHARACTER SET and COLLATE are specified, those values are used.
• If only CHARACTER SET is specified, it is used along with the default collation for that character set (as specified in the SHOW CHARACTER SET results).
• If neither CHARACTER SET nor COLLATE is specified, the database default is used.
In addition to being able to specify character set and collation tablewide, MariaDB also allows these to be set per column, as seen here:
Input
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
Analysis
Here CHARACTER SET and COLLATE are specified for the entire table as well as for a specific column.
As mentioned previously, the collation plays a key role in sorting data that is retrieved with an ORDER BY clause. If you need to sort specific SELECT statements using a collation sequence other than the one used at table creation time, you may do so in the SELECT statement itself:
Input
SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;
Analysis
This SELECT uses COLLATE to specify an alternate collation sequence (in this example, a case-sensitive one). This obviously affects the order in which results are sorted.
* * *
Tip: Occasional Case Sensitivity
The SELECT statement just seen demonstrates a useful technique for performing case-sensitive searches on a table that is usually not case sensitive. And of course, the reverse works just as well.
* * *
* * *
Note: Other SELECT COLLATE Clauses
In addition to being used in ORDER BY clauses, as seen here, COLLATE can be used with GROUP BY, HAVING, aggregate functions, aliases, and more.
* * *
One final point worth noting is that strings may be converted between character sets if absolutely needed. To do this, use the Cast() or Convert() functions.
Summary
In this chapter, you learned the basics of character sets and collations. You also learned how to define the character sets and collations for specific tables and columns, and how to use alternate collations when needed.
28. Managing Security
Database servers usually contain critical data, and ensuring the safety and integrity of that data requires that access control be used. In this chapter you learn about MariaDB access control and user management.
Understanding Access Control
The basis of security for your MariaDB server is this: Users should have appropriate access to the data they need, no more and no less. In other words, users should not have too much access