|
MySQL.RU - Webboard
 Вернуться
 Переменить доступ к существующим базам? (MadDogMcCre) 14/08/2003 - 16:28:53
 Re: Переменить доступ к существующим базам? (Eyfel) 14/08/2003 - 17:04:46
Re: Переменить доступ к существующим базам? (MadDogMcCre) 14/08/2003 - 18:06:26
Re: Переменить доступ к существующим базам? (Dinky) 14/08/2003 - 20:55:34
> Original message text:
> From: MadDogMcCre - 14/08/2003 - 16:28:53
> Subject:Переменить доступ к существующим базам?
> -----------------
> Допустим есть база, нужно к этой базе изменить пароль, и пользователя. Как это можно сделать. Заранее очень благодарен!
>
From: Eyfel - 14/08/2003 - 17:04:46
Subject:Переменить доступ к существующим базам?
-----------------
Все пароли и т.д. храняться в таблици user это можно сделать при помощи mysqladmin
4.3 MySQL User Account Management
4.3.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
GRANT is implemented in MySQL Version 3.22.11 or later. For earlier MySQL versions, the GRANT statement does nothing.
The GRANT and REVOKE commands allow system administrators to create users and grant and revoke rights to MySQL users at four privilege levels:
Global level
Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. REVOKE ALL ON *.* will revoke only global privileges. Database level
Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. REVOKE ALL ON db.* will revoke only database privileges. Table level
Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. REVOKE ALL ON db.table will revoke only table privileges. Column level
Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE you must specify the same columns that were granted.
For the GRANT and REVOKE statements, priv_type may be specified as any of the following:
ALL [PRIVILEGES] Sets all simple privileges except WITH GRANT OPTION
ALTER Allows usage of ALTER TABLE
CREATE Allows usage of CREATE TABLE
CREATE TEMPORARY TABLES Allows usage of CREATE TEMPORARY TABLE
DELETE Allows usage of DELETE
DROP Allows usage of DROP TABLE.
EXECUTE Allows the user to run stored procedures (MySQL 5.0)
FILE Allows usage of SELECT ... INTO OUTFILE and LOAD DATA INFILE.
INDEX Allows usage of CREATE INDEX and DROP INDEX
INSERT Allows usage of INSERT
LOCK TABLES Allows usage of LOCK TABLES on tables for which one has the SELECT privilege.
PROCESS Allows usage of SHOW FULL PROCESSLIST
REFERENCES For the future
RELOAD Allows usage of FLUSH
REPLICATION CLIENT Gives the right to the user to ask where the slaves/masters are.
REPLICATION SLAVE Needed for the replication slaves (to read binlogs from master).
SELECT Allows usage of SELECT
SHOW DATABASES SHOW DATABASES shows all databases.
SHUTDOWN Allows usage of mysqladmin shutdown
SUPER Allows one connect (once) even if max_connections is reached and execute commands CHANGE MASTER, KILL thread, mysqladmin debug, PURGE [MASTER] LOGS and SET GLOBAL
UPDATE Allows usage of UPDATE
USAGE Synonym for ``no privileges.''
GRANT OPTION Synonym for WITH GRANT OPTION
USAGE can be used when you want to create a user that has no privileges.
The privileges CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ..., SHOW DATABASES and SUPER are new for in version 4.0.2. To use these new privileges after upgrading to 4.0.2, you have to run the mysql_fix_privilege_tables script.
In older MySQL versions, the PROCESS privilege gives the same rights as the new SUPER privilege.
To revoke the GRANT privilege from a user, use a priv_type value of GRANT OPTION:
mysql> REVOKE GRANT OPTION ON ... FROM ...;
The only priv_type values you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER.
The only priv_type values you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT, and UPDATE.
MySQL allows you to create database level privileges even if the database doesn't exists to make it easy to prepare for database usage. Currently MySQL does however not allow one to create table level grants if the table doesn't exists. MySQL will not automatically revoke any privileges even if you drop a table or drop a database.
You can set global privileges by using ON *.* syntax. You can set database privileges by using ON db_name.* syntax. If you specify ON * and you have a current database, you will set the privileges for that database. (Warning: if you specify ON * and you don't have a current database, you will affect the global privileges!)
Please note: the `_' and `%' wildcards are allowed when specifying database names in GRANT commands. This means that if you wish to use for instance a `_' character as part of a database name, you should specify it as `\_' in the GRANT command, to prevent the user from being able to access additional databases matching the wildcard pattern, e.g., GRANT ... ON `foo\_bar`.* TO ....
In order to accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user_name value in the form user@host. If you want to specify a user string containing special characters (such as `-'), or a host string containing special characters or wildcard characters (such as `%'), you can quote the user or host name (for example, 'test-user'@'test-hostname').
You can specify wildcards in the hostname. For example, user@'%.loc.gov' applies to user for any host in the loc.gov domain, and user@'144.155.166.%' applies to user for any host in the 144.155.166 class C subnet.
The simple form user is a synonym for user@"%".
MySQL doesn't support wildcards in user names. Anonymous users are defined by inserting entries with User='' into the mysql.user table or creating an user with an empty name with the GRANT command.
Note: if you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user@localhost because otherwise the anonymous user entry for the local host in the mysql.user table will be used when the user tries to log into the MySQL server from the local machine!
You can verify if this applies to you by executing this query:
mysql> SELECT Host,User FROM mysql.user WHERE User='';
For the moment, GRANT only supports host, table, database, and column names up to 60 characters long. A user name can be up to 16 characters.
The privileges for a table or column are formed from the logical OR of the privileges at each of the four privilege levels. For example, if the mysql.user table specifies that a user has a global SELECT privilege, this can't be denied by an entry at the database, table, or column level.
The privileges for a column can be calculated as follows:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
In most cases, you grant rights to a user at only one of the privilege levels, so life isn't normally as complicated as above. The details of the privilege-checking procedure are presented in section 4.2 General Security Issues and the MySQL Access Privilege System.
If you grant privileges for a user/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE command. In other words, GRANT may create user table entries, but REVOKE will not remove them; you must do that explicitly using DELETE.
In MySQL Version 3.22.12 or later, if a new user is created or if you have global grant privileges, the user's password will be set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, it is replaced by the new one.
If you don't want to send the password in clear text you can use the PASSWORD option followed by a scrambled password from SQL function PASSWORD() or the C API function make_scrambled_password(char *to, const char *password).
Warning: if you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is insecure.
Passwords can also be set with the SET PASSWORD command. See section 5.5.6 SET Syntax.
If you grant privileges for a database, an entry in the mysql.db table is created if needed. When all privileges for the database have been removed with REVOKE, this entry is deleted.
If a user doesn't have any privileges on a table, the table is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement). The same is true for SHOW DATABASES.
The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the GRANT privilege, as two users with different privileges may be able to join privileges!
MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # and MAX_CONNECTIONS_PER_HOUR # are new in MySQL version 4.0.2. These options limit the number of queries/updates and logins the user can do during one hour. If # is 0 (default), then this means that there are no limitations for that user. See section 4.3.6 Limiting user resources. Note: to specify any of these options for an existing user without adding other additional privileges, use GRANT USAGE ... WITH MAX_....
You cannot grant another user a privilege you don't have yourself; the GRANT privilege allows you to give away only those privileges you possess.
Be aware that when you grant a user the GRANT privilege at a particular privilege level, any privileges the user already possesses (or is given in the future!) at that level are also grantable by that user. Suppose you grant a user the INSERT privilege on a database. If you then grant the SELECT privilege on the database and specify WITH GRANT OPTION, the user can give away not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can give away the INSERT, SELECT and UPDATE.
You should not grant ALTER privileges to a normal user. If you do that, the user can try to subvert the privilege system by renaming tables!
Note that if you are using table or column privileges for even one user, the server examines table and column privileges for all users and this will slow down MySQL a bit.
When mysqld starts, all privileges are read into memory. Database, table, and column privileges take effect at once, and user-level privileges take effect the next time the user connects. Modifications to the grant tables that you perform using GRANT or REVOKE are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables. See section 4.3.3 When Privilege Changes Take Effect.
The biggest differences between the SQL standard and MySQL versions of GRANT are:
* In MySQL privileges are given for an username + hostname combination and not only for an username.
* SQL-99 doesn't have global or database-level privileges, nor does it support all the privilege types that MySQL supports. MySQL doesn't support the SQL-99 TRIGGER or UNDER privileges.
* SQL-99 privileges are structured in a hierarchal manner. If you remove an user, all privileges the user has granted are revoked. In MySQL the granted privileges are not automatically revoked, but you have to revoke these yourself if needed.
* In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don't have the INSERT privilege will be set to their default values. SQL-99 requires you to have the INSERT privilege on all columns.
* With SQL99, when you drop a table, all privileges for the table are revoked. With SQL-99, when you revoke a privilege, all privileges that were granted based on the privilege are also revoked. In MySQL, privileges can be dropped only with explicit REVOKE commands or by manipulating the MySQL grant tables.
For a description of using REQUIRE, see section 4.3.9 Using Secure Connections.
4.3.2 MySQL User Names and Passwords
There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by Unix or Windows:
* User names, as used by MySQL for authentication purposes, have nothing to do with Unix user names (login names) or Windows user names. Most MySQL clients by default try to log in using the current Unix user name as the MySQL user name, but that is for convenience only. Client programs allow a different name to be specified with the -u or --user options. This means that you can't make a database secure in any way unless all MySQL user names have passwords. Anyone may attempt to connect to the server using any name, and they will succeed if they specify any name that doesn't have a password.
* MySQL user names can be up to 16 characters long; Unix user names typically are limited to 8 characters.
* MySQL passwords have nothing to do with Unix passwords. There is no necessary connection between the password you use to log in to a Unix machine and the password you use to access a database on that machine.
* MySQL encrypts passwords using a different algorithm than the one used during the Unix login process. See the descriptions of the PASSWORD() and ENCRYPT() functions in section 6.3.6.2 Miscellaneous Functions. Note that even if the password is stored 'scrambled', and knowing your 'scrambled' password is enough to be able to connect to the MySQL server! From version 4.1, MySQL employs a different password and login mechanism that is secure even if TCP/IP packets are sniffed and/or the mysql database is captured.
MySQL users and their privileges are normally created with the GRANT command. See section 4.3.1 GRANT and REVOKE Syntax.
When you login to a MySQL server with a command-line client you should specify the password with --password=your-password. See section 4.2.8 Connecting to the MySQL Server.
mysql --user=monty --password=guess database_name
If you want the client to prompt for a password, you should use --password without any argument
mysql --user=monty --password database_name
or the short form:
mysql -u monty -p database_name
Note that in the last example the password is not 'database_name'.
If you want to use the -p option to supply a password you should do so like this:
mysql -u monty -pguess database_name
On some systems, the library call that MySQL uses to prompt for a password will automatically cut the password to 8 characters. Internally MySQL doesn't have any limit for the length of the password.
4.3.3 When Privilege Changes Take Effect
When mysqld starts, all grant table contents are read into memory and become effective at that point.
Modifications to the grant tables that you perform using GRANT, REVOKE, or SET PASSWORD are noticed by the server immediately.
If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise, your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference!
When the server notices that the grant tables have been changed, existing client connections are affected as follows:
* Table and column privilege changes take effect with the client's next request.
* Database privilege changes take effect at the next USE db_name command.
* Global privilege changes and password changes take effect the next time the client connects.
4.3.4 Setting Up the Initial MySQL Privileges
After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. See section 2.3.1 Quick Installation Overview. The mysql_install_db script starts up the mysqld server, then initialises 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. See section 2.6.2.3 Running MySQL on 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> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
If you know what you are doing, you can also directly manipulate the privilege tables:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
-> WHERE user='root';
mysql> FLUSH PRIVILEGES;
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 when you connect to the server as root.
You may wish to leave the root password blank so that you don't need to specify it while you perform additional setup or testing. However, be sure to set it before using your installation for any real production work.
See the scripts/mysql_install_db script to see how it sets up the default privileges. You can use this as a basis to see how to add other users.
If you want the initial privileges to be different from those just described above, you can modify mysql_install_db before you run it.
To re-create the grant tables completely, remove all the `.frm', `.MYI', and `.MYD' files in the directory containing the mysql database. (This is the directory named `mysql' under the database directory, which is listed when you run mysqld --help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.
Note: for MySQL versions older than Version 3.22.10, you should not delete the `.frm' files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db.
4.3.5 Adding New Users to MySQL
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. See section 4.3.1 GRANT and REVOKE Syntax.
There are also a lot of contributed programs like phpmyadmin that can be used to create and administrate users.
The following examples 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 here.
You can add new users by issuing GRANT statements:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> 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 processlist . 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 user with no privileges. It is assumed that you will grant database-specific privileges later.
You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:
shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
-> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
Depending on your MySQL version, you may have to use a different number of 'Y' values above (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.
Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.
The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of 'N'. This is the same thing that GRANT USAGE does.
The following example adds a user custom who can connect from hosts localhost, server.domain, and whitehouse.gov. He wants to access the bankaccount database only from localhost, the expenses database only from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.
To set up this user's privileges using GRANT statements, run these commands:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO custom@localhost
-> IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO custom@whitehouse.gov
-> IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO custom@'%'
-> IDENTIFIED BY 'stupid';
The reason that we issue two grant statements for the user 'custom' is that we want the give the user access to MySQL both from the local machine with Unix sockets and from the remote machine 'whitehouse.gov' over TCP/IP.
To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES
-> ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES
-> ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.
If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:
mysql> GRANT ...
-> ON *.*
-> TO myusername@"%.mydomainname.com"
-> IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
-> PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
4.3.6 Limiting user resources
Starting from MySQL 4.0.2 one can limit certain resources per user.
So far, the only available method of limiting usage of MySQL server resources has been setting the max_user_connections startup variable to a non-zero value. But this method is strictly global and does not allow for management of individual users, which could be of particular interest to Internet Service Providers.
Therefore, management of three resources is introduced on the individual user level:
* Number of all queries per hour: All commands that could be run by a user.
* Number of all updates per hour: Any command that changes any table or database.
* Number of connections made per hour: New connections opened per hour.
A user in the aforementioned context is a single entry in the user table, which is uniquely identified by its user and host columns.
All users are by default not limited in using the above resources, unless the limits are granted to them. These limits can be granted only via global GRANT (*.*), using this syntax:
GRANT ... WITH MAX_QUERIES_PER_HOUR N1
MAX_UPDATES_PER_HOUR N2
MAX_CONNECTIONS_PER_HOUR N3;
One can specify any combination of the above resources. N1, N2 and N3 are integers and stands for count / hour.
If user reaches any of the above limits within one hour, his connection will be terminated or refused and the appropriate error message shall be issued.
Current usage values for a particular user can be flushed (set to zero) by issuing a GRANT statement with any of the above clauses, including a GRANT statement with the current values.
Also, current values for all users will be flushed if privileges are reloaded (in the server or using mysqladmin reload) or if the FLUSH USER_RESOURCES command is issued.
The feature is enabled as soon as a single user is granted with any of the limiting GRANT clauses.
As a prerequisite for enabling this feature, the user table in the mysql database must contain the additional columns, as defined in the table creation scripts mysql_install_db and mysql_install_db.sh in `scripts' subdirectory.
4.3.7 Setting Up Passwords
In most cases you should use GRANT to set up your users/passwords, so the following only applies for advanced users. See section 4.3.1 GRANT and REVOKE Syntax.
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(), generates an authentification vector based on encrypted password and a random number, obtained from server, and sends the result to the server. The server uses the password value in the user table (that is not encrypted value 'biscuit') to perform the same calculations, and compares results. 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() is different from Unix password encryption. See section 4.3.2 MySQL User Names and Passwords.
4.3.8 Keeping Your Password Secure
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 here, 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 initialisation 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. See section 4.1.2 `my.cnf' Option Files.
* 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 MYSQL_PWD. Even on systems without such a version of ps, it is unwise to assume there is no other method to observe process environments. See section F Environment Variables.
All in all, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected `.my.cnf' file.
[Это сообщение - спам!]
Последние сообщения из форума
Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL,
версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь.
10134
|
|
