My SQL Interview Questions and Answers

Answer - 51 : - Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes. In discussing security, we emphasize the necessity of fully protecting the entire server host (not simply the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here. MySQL uses Access Control Lists (ACLs) security for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications. When running MySQL, follow these guidelines whenever possible: DON'T EVER GIVE ANYONE (EXCEPT THE MySQL ROOT USER) ACCESS TO THE mysql.user TABLE! The encrypted password is the real password in MySQL. If you know this for one user you can easily login as him if you have access to his 'host'. Learn the MySQL access privilege system. The GRANT and REVOKE commands are used for restricting access to MySQL. Do not grant any more privileges than necessary. Never grant privileges to all hosts. Checklist: Try mysql -u root. If you are able to connect successfully to the server without being asked for a password, you have problems. Any user (not just root) can connect to your MySQL server with full privileges! Review the MySQL installation instructions, paying particular attention to the item about setting a root password. Use the command SHOW GRANTS and check to see who has access to what. Remove those privileges that are not necessary using the REVOKE command. Do not keep any plain-text passwords in your database. When your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead use MD5() or another one-way hashing function. Do not use passwords from dictionaries. There are special programs to break them. Even passwords like ``xfish98'' are very bad. Much better is ``duag98'' which contains the same word ``fish'' but typed one key to the left on a standard QWERTY keyboard. Another method is to use ``Mhall'' which is taken from the first characters of of each word in the

Answer - 52 : - When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication. All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in MySQL Version 3.22 and above) to make things much harder. To make things even more secure you should use ssh (see http://www.cs.hut.fi/ssh). With this, you can get an encrypted TCP/IP connection between a MySQL server and a MySQL client. To make a MySQL system secure, you should strongly consider the following suggestions: Use passwords for all MySQL users. Remember that anyone can log in as any other person as simply as mysql -u other_user db_name if other_user has no password. It is common behavior with client/server applications that the client may specify any user name. You can change the password of all users by editing the mysql_install_db script before you run it, or only the password for the MySQL root user like this: shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES; Don't run the MySQL daemon as the Unix root user. It is very dangerous as any user with FILE privileges will be able to create files as root (for example, ~root/.bashrc). To prevent this mysqld will refuse to run as root unless it is specified directly via --user=root option. mysqld can be run as any user instead. You can also create a new Unix user mysql to make everything even more secure. If you run mysqld as another Unix user, you don't need to change the root user name in the user table, because MySQL user names have nothing to do with Unix user names. You can edit the mysql.server script to start mysqld as another Unix user. Normally this is done with the su command. If you put a password for the Unix root user in the mysql.server script, make sure this script is readable only by root. Check that the Unix user that mysqld runs as is the only

Answer - 53 : - The following mysqld options affect networking security: --secure IP numbers returned by the gethostbyname() system call are checked to make sure they resolve back to the original hostname. This makes it harder for someone on the outside to get access by pretending to be another host. This option also adds some sanity checks of hostnames. The option is turned off by default in MySQL Version 3.21 because sometimes it takes a long time to perform backward resolutions. MySQL Version 3.22 caches hostnames and has this option enabled by default. --skip-grant-tables This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.) --skip-name-resolve Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost. --skip-networking Don't allow TCP/IP connections over the network. All connections to mysqld must be made via Unix sockets. This option is unsuitable for systems that use MIT-pthreads, because the MIT-pthreads package doesn't support Unix sockets. --skip-show-database SHOW DATABASE command doesn't return anything. --safe-show-database SHOW DATABASE only returns databases for which the user have some kind of privilege.

Answer - 54 : - It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed below, along with an assessment of the risks of each method: Never give a normal user access to the mysql.user table. Knowing the encrypted password for a user makes it possible to login as this user. The passwords are only scrambled so that one shouldn't be able to see the real password you used (if you happen to use a similar password with your other applications). Use a -pyour_pass or --password=your_pass option on the command line. This is convenient but insecure, because your password becomes visible to system status programs (such as ps) that may be invoked by other users to display command lines. (MySQL clients typically overwrite the command-line argument with zeroes during their initialization sequence, but there is still a brief interval during which the value is visible.) Use a -p or --password option (with no your_pass value specified). In this case, the client program solicits the password from the terminal: shell> mysql -u user_name -p Enter password: ******** The `*' characters represent your password. It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs non-interactively, there is no opportunity to enter the password from the terminal. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password! Store your password in a configuration file. For example, you can list your password in the [client] section of the `.my.cnf' file in your home directory: [client] password=your_pass If you store your password in `.my.cnf', the file should not be group or world readable or writable. Make sure the file's access mode is 400 or 600. You can store your password in the MYSQL_PWD environment variable, but this method must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes; your password will be in plain sight for all to see if you set

Answer - 55 : - The MySQL privilege system ensures that all users may do exactly the things that they are supposed to be allowed to do. When you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. The system grants privileges according to your identity and what you want to do. MySQL considers both your hostname and user name in identifying you because there is little reason to assume that a given user name belongs to the same person everywhere on the Internet. For example, the user bill who connects from whitehouse.gov need not be the same person as the user bill who connects from microsoft.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: you can grant bill one set of privileges for connections from whitehouse.gov, and a different set of privileges for connections from microsoft.com. MySQL access control involves two stages: Stage 1: The server checks whether or not you are even allowed to connect. Stage 2: Assuming you can connect, the server checks each request you issue to see whether or not you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server makes sure you have the select privilege for the table or the drop privilege for the database. The server uses the user, db, and host tables in the mysql database at both stages of access control. The fields in these grant tables are shown below: Table name user db host Scope fields Host Host Host User Db Db Password User Privilege fields Select_priv Select_priv Select_priv Insert_priv Insert_priv Insert_priv Update_priv Update_priv Update_priv Delete_priv Delete_priv Delete_priv Index_priv Index_priv Index_priv Alter_priv Alter_priv Alter_priv Create_priv Create_priv Create_priv Drop_priv Drop_priv Drop_priv Grant_priv Grant_priv Grant_priv References_priv Reload_priv Shutdown_priv Process_priv File_priv For the second stage of access control (request verification), the server may, if the request involves tables, additionally consult the tables_priv and columns_priv tables.

Answer - 56 : - Once you establish a connection, the server enters Stage 2. For each request that comes in on the connection, the server checks whether you have sufficient privileges to perform it, based on the type of operation you wish to perform. This is where the privilege fields in the grant tables come into play. These privileges can come from any of the user, db, host, tables_priv, or columns_priv tables. The grant tables are manipulated with GRANT and REVOKE commands. The user table grants privileges that are assigned to you on a global basis and that apply no matter what the current database is. For example, if the user table grants you the delete privilege, you can delete rows from any database on the server host! In other words, user table privileges are superuser privileges. It is wise to grant privileges in the user table only to superusers such as server or database administrators. For other users, you should leave the privileges in the user table set to 'N' and grant privileges on a database-specific basis only, using the db and host tables. The db and host tables grant database-specific privileges. Values in the scope fields may be specified as follows: The wild-card characters `%' and `_' can be used in the Host and Db fields of either table. A '%' Host value in the db table means ``any host.'' A blank Host value in the db table means ``consult the host table for further information.'' A '%' or blank Host value in the host table means ``any host.'' A '%' or blank Db value in either table means ``any database.'' A blank User value in either table matches the anonymous user. The db and host tables are read in and sorted when the server starts up (at the same time that it reads the user table). The db table is sorted on the Host, Db, and User scope fields, and the host table is sorted on the Host and Db scope fields. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds. The tables_priv and columns_priv tables grant table- and column-specific privileges. Values in the scope fields may be specified as follows: The wild-card characters `%' and `_' can be used in the Host field of either table. A '%' or blank Host value in either table means ``any host.'' The Db, Table_na

Answer - 57 : - After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. The mysql_install_db script starts up the mysqld server, then initializes the grant tables to contain the following set of privileges: The MySQL root user is created as a superuser who can do anything. Connections must be made from the local host. NOTE: The initial root password is empty, so anyone can connect as root without a password and be granted all privileges. An anonymous user is created that can do anything with databases that have a name of 'test' or starting with 'test_'. Connections must be made from the local host. This means any local user can connect without a password and be treated as the anonymous user. Other privileges are denied. For example, normal users can't use mysqladmin shutdown or mysqladmin processlist. NOTE: The default privileges are different for Windows. Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function): shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES; You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement: shell> mysql -u root mysql mysql> SET PASSWORD FOR root=PASSWORD('new_password'); Another way to set the password is by using the mysqladmin command: shell> mysqladmin -u root password new_password Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password'). Note that if you update the password in the user table directly using the first method, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise. Once the root password has been set, thereafter you must supply that password whe

Answer - 58 : - you can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone. The examples below show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands below. You can add new users by issuing GRANT statements: shell> mysql --user=root mysql mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysqlgt; GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysqlgt; GRANT USAGE ON *.* TO dummy@localhost; These GRANT statements set up three new users: monty A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order. admin A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin process list . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.) dummy A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a

Answer - 59 : - In most cases you should use GRANT to set up your users/passwords, so the following only applies for advanced users. The examples in the preceding sections illustrate an important principle: when you store a non-empty password using INSERT or UPDATE statements, you must use the PASSWORD() function to encrypt it. This is because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely to attempt to set passwords like this: shell> mysql -u root mysql mysql> INSERT INTO user (Host,User,Password) VALUES('%','jeffrey','biscuit'); mysql> FLUSH PRIVILEGES; The result is that the plaintext value 'biscuit' is stored as the password in the user table. When the user jeffrey attempts to connect to the server using this password, the mysql client encrypts it with PASSWORD() and sends the result to the server. The server compares the value in the user table (the encrypted value of 'biscuit') to the encrypted password (which is not 'biscuit'). The comparison fails and the server rejects the connection: shell> mysql -u jeffrey -pbiscuit test Access denied Passwords must be encrypted when they are inserted in the user table, so the INSERT statement should have been specified like this instead: mysql> INSERT INTO user (Host,User,Password) VALUES('%','jeffrey',PASSWORD('biscuit')); You must also use the PASSWORD() function when you use SET PASSWORD statements: mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit'); If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, the PASSWORD() function is unnecessary. They both take care of encrypting the password for you, so you would specify a password of 'biscuit' like this: mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit'; or shell> mysqladmin -u jeffrey password biscuit NOTE: PASSWORD() does not perform password encryption in the same way that Unix passwords are encrypted. You should not assume that if your

Answer - 60 : - One way replication can be used is to increase both robustness and speed. For robustness you can have two systems and can switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case. Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates and an index file to binary logs to keep track of log rotation. The slave, upon connecting, informs the master where it left off since the last successfully propagated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates. Note that if you are replicating a database, all updates to this database should be done through the master! On older servers one can use the update log to do simple replication. Another benefit of using replication is that one can get live backups of the system by doing a backup on a slave instead of doing it on the master.