Describe how to create user accounts and manage security using access control. Describe in detail.
User accounts in Mysql can be created in two ways:1. Using statements like CREATE USER or GRANT exclusively used for creating accounts.
2.
CREATE USER creates new SQL accounts. For each user a new row is added to mysql.user accounts.
3.
GRANT can be used to create MySQL user accounts and to grant rights to accounts Granted permissions can be REVOKED as well.
Example: GRANT SELECT | INSERT | ALL ON database_name To user_name
4. Another mechanism would be to manipulate the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE or use third part programs like PhpMyAdmin.
What is RELOAD privileges in MySQL used for?
Reload privileges are used to apply changes made to a newly created user without having the need to restart the MySql. The MySql server re-reads the grant tables into memory. Flush-privileges can also be used.
How can we encrypt and decrypt a data present in a MySQL table?
Encryption and decryption of the data is done using the Advanced Encryption Standard (AES) algorithm. Functions like aes_encrypt() and aes_decrypt() are used. Aes_encrypt() is used to encrypt a string and returns a binary string. aes_decrypt() decrypts the encrypted string and returns the original string. The input parameters can be of any length. If the decrypt function detects any invalid data or incorrect padding, it returns NULL.
Example:Insert into employee values (1, aes_encrypt(‘text’,’password’);
Explain how to take a backup of a MySQL table and how can we restore it?
A Mysql table can be backed up using mysqldump program. The dump typically contains SQL statements to create the table, populate it, or both. From shell, mysqldump can be invoked as follows:
Shell > mysqldump [options] db_name[tables]
A MySQL table can also be backed up using BACKUP_TABLE.
Syntax:BACKUP TABLE tbl_name[, table_name] To ‘path’.
The tables can be resroed using RESTORE_TABLE from a backup.
Existing tables are not overwritten; if restore is done over an existing table, an error occurs.
Explain the syntax of Grant and Revoke commands?
1. Grant : Grant is used to grant or give access permission to a user. These permissions can be given to a user or a role. Grant can be used to give system privileges or object privileges.
Syntax:Grant ALL | system_privelege on object_name to user_name
Example:Grant update on customer to steve
2. Revoke : Removes or takes away a previously assigned or granted privilege.
Syntax:Revoke ALL | privilege_name
On object_name
From user_name
Example:Revoke update
On customer
From steve
Explain how to load data from a text file into a table?
Data from a text file can be loaded using the LOAD_DATA_INFILE. This function reads data from a text file and inserts into a table.
The command that is used to read and insert is:
LOAD DATA LOCAL INFILE ‘/path/sample.txt’ INTO table SAMPLE.
\r\n can be used as a line terminator. If LOCAL is specified, the file is read by the client program on the client host and sent to the server.
How to create MYSQL new users?
Users in MYSQL can be created using CREATE USER syntax. The creator of these accounts must have the global CREATE USER privilege. An error occurs if the account already exists,
CREATE USER ‘user_name’ @ ‘localhost’ IDENTIFIED BY ‘password1’;
State some security recommendations while using MYSQL.
1. Access to the user table should never be given to avoid SQL injection attacks.
2. Privileges such as GRANT and REVOKE must be made use of.
3. SHOW GRANTS can be used to see the list of users who have access.
4. Never run the MySQL server as the Unix root user.
5. Validation of data and queries should be thoroughly checked.
Explain about a security flaw which is present while creating a username.
The database name should not start with the word test. Test name databases can be accessed from anywhere in the network.
What are the two ways to create MySQL accounts?
1. Users in MYSQL can be created using CREATE USER syntax. The creator of these accounts must have the global CREATE USER privilege. An error occurs if the account already exists,
CREATE USER ‘user_name’ @ ‘localhost’ IDENTIFIED BY ‘password1’
2. MySQL user accounts can also be created by manipulating the grant tables with statements like INSERT, DELETE or UPDATE.
How can we removing User Accounts from MySQL?
Using the DROP USER command, user accounts created can be removed. To use this command user must have the GRANT USER privilege.
Syntax:DROP USER user;
