• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

My SQL Interview Questions Answers

Question - 71 : - MySQL - General Security

Answer - 71 : - 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

Question - 72 : - Setting Up Passwords?

Answer - 72 : - 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

Question - 73 : - How can you see all indexes defined for a table?

Answer - 73 : - SHOW INDEX FROM techpreparation_questions;

Question - 74 : - Replication Implementation Overview ?

Answer - 74 : - MySQL replication is based on the server keeping track of all changes to your database (updates, deletes, etc) in the binary log. and the slave server(s) reading the saved queries from the master server's binary log so that the slave can execute the same queries on its copy of the data. It is very important to realize that the binary log is simply a record starting from a fixed point in time (the moment you enable binary logging). Any slaves which you set up will need copies of all the data from your master as it existed the moment that you enabled binary logging on the master. If you start your slaves with data that doesn't agree with what was on the master when the binary log was started, your slaves may fail. A future version (4.0) of MySQL will remove the need to keep a (possibly large) snapshot of data for new slaves that you might wish to set up through the live backup functionality with no locking required. However, at this time, it is necessary to block all writes either with a global read lock or by shutting down the master while taking a snapshot. Once a slave is properly configured and running, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect every master-connect-retry seconds until it is able to reconnect and resume listening for updates. Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up-to-date at any given time.

Question - 75 : - How the Privilege System Works ?

Answer - 75 : - 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.

Question - 76 : - How do I find out all databases starting with ‘exam’ to which I have access to?

Answer - 76 : - SHOW DATABASES LIKE ‘exam%’;

Question - 77 : - Speed of DELETE Queries ?

Answer - 77 : - If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache.

Question - 78 : - How MySQL Optimizes LIMIT ?

Answer - 78 : - In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING: If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan. If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table. When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows. In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY's. As soon as MySQL has sent the first # rows to the client, it will abort the query. LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns. The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query

Question - 79 : - `--' as the Start of a Comment

Answer - 79 : - MySQL doesn't support views. Some other SQL databases use `--' to start comments. MySQL has `#' as the start comment character, even if the mysql command-line tool removes all lines that start with `--'. You can also use the C comment style /* this is a comment */ with MySQL. MySQL Version 3.23.3 and above supports the `--' comment style only if the comment is followed by a space. This is because this degenerate comment style has caused many problems with automatically generated SQL queries that have used something like the following code, where we automatically insert the value of the payment for !payment!: UPDATE tbl_name SET credit=credit-!payment! What do you think will happen when the value of payment is negative? Because 1--1 is legal in SQL, we think it is terrible that `--' means start comment. In MySQL Version 3.23 you can, however, use: 1-- This is a comment The following discussion only concerns you if you are running a MySQL version earlier than Version 3.23: If you have a SQL program in a text file that contains `--' comments you should use: shell> replace " --" " #" < text-file-with-funny-comments.sql \ | mysql database instead of the usual: shell> mysql database < text-file-with-funny-comments.sql You can also edit the command file ``in place'' to change the `--' comments to `#' comments: shell> replace " --" " #" -- text-file-with-funny-comments.sql Change them back with this command: shell> replace " #" " --" -- text-file-with-funny-comments.sql

Question - 80 : - How to Make MySQL Secure Against Crackers ?

Answer - 80 : - 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

Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners