Für die vollständige Unterstützung von MySQL in Django ist Version 1.2.1p2 oder neuer erforderlich. ...
CREATE DATABASE <dbname> CHARACTER SET utf8; ...
#!/usr/bin/bash
mysql mysql;
UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
FLUSH PRIVILEGES;
Setting the password:
1. From Unix:
shell> mysql -u username -h hostname -p password
mysql> SET PASSWORD FOR username@localhost=PASSWORD('new_password');
2. Directly manipulate the privilege tables:
shell> mysql -u username -h host -u username -p
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES;
3. Using the mysqladmin command:
shell> mysqladmin -u username password new_password
In our case we were able to change password specifying host name along with user name:
shell> bin/myslqadmin u username h localhost
MySQL Permissions & Grant Tables
In order to add a new user or update user's privileges in mysql grant tables login to mysql as a root user.
There are two options: use GRANT/REVOKE command or manipulating the MySQL grant tables directly.
The preferred method is to use GRANT statements - more concise and less error-prone.
If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute
a FLUSH PRIVILEGES statement to tell the server to reload the grant tables.
To remove user: mysql> delete from user where user='username';
mysql> FLUSH PRIVILEGES;
Examples adding a new user with different level of privileges:
dummy: A user who can connect without a password, but only from the local host.
mysql> GRANT USAGE ON *.* TO dummy@localhost;
myUser : A full superuser who can connect to the server from anywhere,
but who must use a password 'pass' to do so.
GRANT statements should be for both myUser@localhost and myUser@"%".
to prevent the anonymous user entry for localhost take precedence.
mysql> GRANT ALL PRIVILEGES ON *.* TO myUser@localhost
IDENTIFIED BY 'pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO myUser@"%"
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
"%" - is a wildcard in mysql. If you are defining your DB table and in the 'host' field
enter '%', that means that any host can access that database (Of course, that host
must also have a valid db user).
admin: A user who can connect from localhost without a password and who is granted
the RELOAD and PROCESS administrative privileges.
No database-related privileges are granted.
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
Add a user that has full rights to his database only but cannot see other database:
mysql> GRANT USAGE ON *.* TO 'user'@'host' GRANT Select, Insert, Update, Delete,
Create, Drop ON `database`.* TO 'user'@'host' FLUSH PRIVELEGS;
The FILE privelege and WITH GRANT OPTION may not be the best way to include, it is
only in case of creating another superuser with full set of privileges or
giving privileges to load data using mysql command INLOAD DATA.
GRANT TABLE FIELDS EXPLANATION:
TABLE USER: Everything after "password" is a privelege granted with values 'Y' or 'N'.
This table controls individual user global access rights.
'host','user','password','select','insert','update','delete','index','alter'
,'create','drop','grant','reload','shutdown','process','file'
TABLE DB: This controls access of USERS to databases.
'host','db','user','select','insert','update','delete','index','alter',
'create','drop','grant'
TABLE HOST: This controls which HOSTS are allowed what global access rights.
'host','db','select','insert','update','delete','index','alter',
'create','drop','grant'
HOST, USER, and DB table are very closely connected - if an authorized USER
attempts an SQL request from an unauthorized HOST, it is denied.
If a request from an authorized HOST is not an authorized USER, it is denied.
If a globally authorized USER does not have rights to a certain DB, it is denied.
drop table if exists t1;
delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
flush privileges;
grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant delete on mysqltest.* to mysqltest_1@localhost;
select * from mysql.user where user="mysqltest_1";
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections
localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT, DELETE ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke delete on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant select on mysqltest.* to mysqltest_1@localhost require NONE;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB";
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA'
delete from mysql.user where user='mysqltest_1';
flush privileges;
grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
flush privileges;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION;
flush privileges;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
flush privileges;
grant usage on test.* to mysqltest_1@localhost with grant option;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT USAGE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
GRANT USAGE ON `test`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
delete from mysql.tables_priv where user='mysqltest_1';
delete from mysql.columns_priv where user='mysqltest_1';
flush privileges;
create table t1 (a int);
GRANT select,update,insert on t1 to mysqltest_1@localhost;
GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
table_priv column_priv
Select,Insert,Update Select,Insert,Update,References
REVOKE select (a), update on t1 from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
GRANT select,references on t1 to mysqltest_1@localhost;
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
table_priv column_priv
Select,References References
grant all on test.* to mysqltest_3@localhost with grant option;
revoke all on test.* from mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
Grants for mysqltest_3@localhost
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
GRANT USAGE ON `test`.* TO 'mysqltest_3'@'localhost' WITH GRANT OPTION
revoke grant option on test.* from mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
Grants for mysqltest_3@localhost
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
grant all on test.t1 to mysqltest_2@localhost with grant option;
revoke all on test.t1 from mysqltest_2@localhost;
show grants for mysqltest_2@localhost;
Grants for mysqltest_2@localhost
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
GRANT USAGE ON `test`.`t1` TO 'mysqltest_2'@'localhost' WITH GRANT OPTION
revoke grant option on test.t1 from mysqltest_2@localhost;
show grants for mysqltest_2@localhost;
Grants for mysqltest_2@localhost
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
flush privileges;
drop table t1;
GRANT FILE on mysqltest.* to mysqltest_1@localhost;
Wrong usage of DB GRANT and GLOBAL PRIVILEGES
select 1;
1
1
insert into mysql.user (host, user) values ('localhost', 'test11');
insert into mysql.db (host, db, user, select_priv) values
('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
alter table mysql.db order by db asc;
flush privileges;
show grants for test11@localhost;
Grants for test11@localhost
GRANT USAGE ON *.* TO 'test11'@'localhost'
GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'
GRANT SELECT ON `a%`.* TO 'test11'@'localhost'
alter table mysql.db order by db desc;
flush privileges;
show grants for test11@localhost;
Grants for test11@localhost
GRANT USAGE ON *.* TO 'test11'@'localhost'
GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'
GRANT SELECT ON `a%`.* TO 'test11'@'localhost'
delete from mysql.user where user='test11';
delete from mysql.db where user='test11';
create database db6123;
grant usage on db6123.* to test6123 identified by 'magic123';
select host,db,user,select_priv,insert_priv from mysql.db where db="db6123";
host db user select_priv insert_priv
delete from mysql.user where user='test6123';
drop database db6123;
create database mysqltest_1;
create database mysqltest_2;
create table mysqltest_1.t1 select 1 a, 2 q;
create table mysqltest_1.t2 select 1 b, 2 r;
create table mysqltest_2.t1 select 1 c, 2 s;
create table mysqltest_2.t2 select 1 d, 2 t;
grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;
grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;
grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost;
grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
Grants for mysqltest_3@localhost
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
GRANT SELECT (b) ON `mysqltest_1`.`t2` TO 'mysqltest_3'@'localhost'
GRANT SELECT (c) ON `mysqltest_2`.`t1` TO 'mysqltest_3'@'localhost'
GRANT UPDATE (a) ON `mysqltest_1`.`t1` TO 'mysqltest_3'@'localhost'
GRANT UPDATE (d) ON `mysqltest_2`.`t2` TO 'mysqltest_3'@'localhost'
update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1;
UPDATE command denied to user: 'mysqltest_3@localhost' for column 'q' in table 't1'
update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1;
select command denied to user: 'mysqltest_3@localhost' for table 't1'
update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1;
UPDATE command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1'
update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2;
SELECT command denied to user: 'mysqltest_3@localhost' for column 's' in table 't1'
update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10;
update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20;
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
a q b r
10 2 1 2
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
c s d t
1 2 10 2
revoke all on mysqltest_1.t1 from mysqltest_3@localhost;
revoke all on mysqltest_1.t2 from mysqltest_3@localhost;
revoke all on mysqltest_2.t1 from mysqltest_3@localhost;
revoke all on mysqltest_2.t2 from mysqltest_3@localhost;
grant all on mysqltest_2.* to mysqltest_3@localhost;
grant select on *.* to mysqltest_3@localhost;
flush privileges;
use mysqltest_1;
update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600;
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
UPDATE command denied to user: 'mysqltest_3@localhost' for column 'a' in table 't1'
use mysqltest_2;
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
Access denied for user: 'mysqltest_3@localhost' to database 'mysqltest_1'
update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200;
Access denied for user: 'mysqltest_3@localhost' to database 'mysqltest_1'
update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200;
Access denied for user: 'mysqltest_3@localhost' to database 'mysqltest_1'
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
a q b r
10 2 1 2
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
c s d t
500 2 600 2
delete from mysql.user where user='mysqltest_3';
delete from mysql.db where user="mysqltest_3";
delete from mysql.tables_priv where user="mysqltest_3";
delete from mysql.columns_priv where user="mysqltest_3";
flush privileges;
drop database mysqltest_1;
drop database mysqltest_2;
-- DetlevLengsfeld 2008-11-05 07:57:17 
Linux/MySql/Administration (last edited 2009-07-07 12:37:33 by DetlevLengsfeld)
