| List: | Commits | « Previous MessageNext Message » | |
| From: | Alexander Nozdrin | Date: | February 1 2006 1:02am |
| Subject: | bk commit into 5.1 tree (anozdrin:1.2099) BUG#9412 | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.1 repository of alik. When alik does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet 1.2099 06/02/01 03:02:14 anozdrin@stripped +16 -0 Fix for BUG#9412: Triggers: should have trigger privilege. Implement table-level TRIGGER privilege to control access to triggers. Before this path global SUPER privilege was used for this purpose, that was the big security problem. In details, before this patch SUPER privilege was required for: - at CREATE TRIGGER time to create a new trigger; - at DROP TRIGGER time to drop the existing trigger; - at trigger activation time to execute the trigger; - if the definer loses SUPER privilege, all its triggers become unavailable; This patch changes the behaviour in the following way: - TRIGGER privilege on the subject table for trigger is required for: - at CREATE TRIGGER time to create a new trigger; - at DROP TRIGGER time to drop the existing trigger; - at trigger activation time to execute the trigger; - if the definer loses TRIGGER privilege on the subject table, all its triggers on this table become unavailable. - SUPER privilege is still required for: - at CREATE TRIGGER time to explicitly set the trigger definer to the user other than CURRENT_USER(). When the server works with database of the previous version (w/o TRIGGER privilege), or if the database is being upgraded from the previous versions, TRIGGER privilege is granted to whose users, who have CREATE privilege. sql/sql_yacc.yy 1.448 06/02/01 03:02:09 anozdrin@stripped +1 -0 Add TRIGGER privilege. sql/sql_trigger.cc 1.44 06/02/01 03:02:09 anozdrin@stripped +25 -24 Check TRIGGER privilege instead of SUPER. sql/sql_show.cc 1.295 06/02/01 03:02:09 anozdrin@stripped +1 -0 Add TRIGGER privilege. sql/sql_acl.h 1.46 06/02/01 03:02:09 anozdrin@stripped +10 -6 Add TRIGGER privilege. sql/sql_acl.cc 1.176 06/02/01 03:02:09 anozdrin@stripped +30 -2 Add TRIGGER privilege. scripts/mysql_fix_privilege_tables.sql 1.36 06/02/01 03:02:09 anozdrin@stripped +14 -0 Add TRIGGER privilege. scripts/mysql_create_system_tables.sh 1.36 06/02/01 03:02:09 anozdrin@stripped +12 -9 Add TRIGGER privilege. mysql-test/t/trigger-grant.test 1.4 06/02/01 03:02:09 anozdrin@stripped +21 -8 1. Grant table-level TRIGGER privilege instead of global SUPER one. 2. Update the test case to check that SUPER is required to specify the user other than the current as a definer. mysql-test/t/trigger-compat.test 1.4 06/02/01 03:02:09 anozdrin@stripped +1 -2 Grant table-level TRIGGER privilege instead of global SUPER one. mysql-test/r/trigger-grant.result 1.3 06/02/01 03:02:09 anozdrin@stripped +12 -3 Update the result file. mysql-test/r/trigger-compat.result 1.3 06/02/01 03:02:09 anozdrin@stripped +1 -2 Update the result file. mysql-test/r/sp.result 1.181 06/02/01 03:02:08 anozdrin@stripped +2 -0 Update the result file. mysql-test/r/ps.result 1.60 06/02/01 03:02:08 anozdrin@stripped +3 -3 Update the result file. mysql-test/r/lowercase_table_grant.result 1.4 06/02/01 03:02:08 anozdrin@stripped +4 -4 Update the result file. mysql-test/r/information_schema.result 1.105 06/02/01 03:02:08 anozdrin@stripped +1 -0 Update the result file. mysql-test/r/grant.result 1.48 06/02/01 03:02:08 anozdrin@stripped +14 -13 Update the result file. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: anozdrin # Host: booka.home # Root: /mnt/hda4/home/alik/MySQL/devel/5.1-bug9412 --- 1.175/sql/sql_acl.cc 2006-01-13 19:04:32 +03:00 +++ 1.176/sql/sql_acl.cc 2006-02-01 03:02:09 +03:00 @@ -242,6 +242,17 @@ if (host.access & CREATE_ACL) host.access|=REFERENCES_ACL | INDEX_ACL | ALTER_ACL | CREATE_TMP_ACL; } + + if (table->s->fields <= 19) + { + /* + There is no field for TRIGGER privilege. Grant TRIGGER privilege if + CREATE privilege is granted. + */ + + if (host.access & CREATE_ACL) + host.access|= TRIGGER_ACL; + } #endif VOID(push_dynamic(&acl_hosts,(gptr) &host)); } @@ -361,6 +372,12 @@ if (table->s->fields <= 37 && (user.access & CREATE_ACL)) user.access|= EVENT_ACL; + /* + if it is pre 5.1.6 privilege then map TRIGGER privilege on CREATE. + */ + if (table->s->fields <= 38 && (user.access & CREATE_ACL)) + user.access|= TRIGGER_ACL; + user.sort= get_sort(2,user.host.hostname,user.user); user.hostname_length= (user.host.hostname ? (uint) strlen(user.host.hostname) : 0); @@ -476,6 +493,17 @@ if (db.access & CREATE_ACL) db.access|=REFERENCES_ACL | INDEX_ACL | ALTER_ACL; } + + if (table->s->fields <= 21) + { + /* + There is no field for TRIGGER privilege. Grant TRIGGER privilege if + CREATE privilege is granted. + */ + + if (db.access & CREATE_ACL) + db.access|= TRIGGER_ACL; + } #endif VOID(push_dynamic(&acl_dbs,(gptr) &db)); } @@ -4070,13 +4098,13 @@ "ALTER", "SHOW DATABASES", "SUPER", "CREATE TEMPORARY TABLES", "LOCK TABLES", "EXECUTE", "REPLICATION SLAVE", "REPLICATION CLIENT", "CREATE VIEW", "SHOW VIEW", "CREATE ROUTINE", "ALTER ROUTINE", - "CREATE USER", "EVENT" + "CREATE USER", "EVENT", "TRIGGER" }; static uint command_lengths[]= { 6, 6, 6, 6, 6, 4, 6, 8, 7, 4, 5, 10, 5, 5, 14, 5, 23, 11, 7, 17, 18, 11, 9, - 14, 13, 11, 5 + 14, 13, 11, 5, 7 }; --- 1.45/sql/sql_acl.h 2006-01-10 21:50:18 +03:00 +++ 1.46/sql/sql_acl.h 2006-02-01 03:02:09 +03:00 @@ -43,6 +43,7 @@ #define ALTER_PROC_ACL (1L << 24) #define CREATE_USER_ACL (1L << 25) #define EVENT_ACL (1L << 26) +#define TRIGGER_ACL (1L << 27) /* don't forget to update 1. static struct show_privileges_st sys_privileges[] @@ -57,12 +58,12 @@ (UPDATE_ACL | SELECT_ACL | INSERT_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | \ GRANT_ACL | REFERENCES_ACL | INDEX_ACL | ALTER_ACL | CREATE_TMP_ACL | \ LOCK_TABLES_ACL | EXECUTE_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL | \ - CREATE_PROC_ACL | ALTER_PROC_ACL | EVENT_ACL) + CREATE_PROC_ACL | ALTER_PROC_ACL | EVENT_ACL | TRIGGER_ACL) #define TABLE_ACLS \ (SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | \ GRANT_ACL | REFERENCES_ACL | INDEX_ACL | ALTER_ACL | CREATE_VIEW_ACL | \ - SHOW_VIEW_ACL) + SHOW_VIEW_ACL | TRIGGER_ACL) #define COL_ACLS \ (SELECT_ACL | INSERT_ACL | UPDATE_ACL | REFERENCES_ACL) @@ -79,7 +80,7 @@ REFERENCES_ACL | INDEX_ACL | ALTER_ACL | SHOW_DB_ACL | SUPER_ACL | \ CREATE_TMP_ACL | LOCK_TABLES_ACL | REPL_SLAVE_ACL | REPL_CLIENT_ACL | \ EXECUTE_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL | CREATE_PROC_ACL | \ - ALTER_PROC_ACL | CREATE_USER_ACL | EVENT_ACL) + ALTER_PROC_ACL | CREATE_USER_ACL | EVENT_ACL | TRIGGER_ACL) #define DEFAULT_CREATE_PROC_ACLS \ (ALTER_PROC_ACL | EXECUTE_ACL) @@ -97,7 +98,7 @@ #define DB_CHUNK3 (CREATE_VIEW_ACL | SHOW_VIEW_ACL | \ CREATE_PROC_ACL | ALTER_PROC_ACL ) #define DB_CHUNK4 (EXECUTE_ACL) -#define DB_CHUNK5 (EVENT_ACL) +#define DB_CHUNK5 (EVENT_ACL | TRIGGER_ACL) #define fix_rights_for_db(A) (((A) & DB_CHUNK0) | \ (((A) << 4) & DB_CHUNK1) | \ @@ -114,12 +115,15 @@ #define TBL_CHUNK0 DB_CHUNK0 #define TBL_CHUNK1 DB_CHUNK1 #define TBL_CHUNK2 (CREATE_VIEW_ACL | SHOW_VIEW_ACL) +#define TBL_CHUNK3 TRIGGER_ACL #define fix_rights_for_table(A) (((A) & TBL_CHUNK0) | \ (((A) << 4) & TBL_CHUNK1) | \ - (((A) << 11) & TBL_CHUNK2)) + (((A) << 11) & TBL_CHUNK2) | \ + (((A) << 15) & TBL_CHUNK3)) #define get_rights_for_table(A) (((A) & TBL_CHUNK0) | \ (((A) & TBL_CHUNK1) >> 4) | \ - (((A) & TBL_CHUNK2) >> 11)) + (((A) & TBL_CHUNK2) >> 11) | \ + (((A) & TBL_CHUNK3) >> 15)) #define fix_rights_for_column(A) (((A) & 7) | (((A) & ~7) << 8)) #define get_rights_for_column(A) (((A) & 7) | ((A) >> 8)) #define fix_rights_for_procedure(A) ((((A) << 18) & EXECUTE_ACL) | \ --- 1.294/sql/sql_show.cc 2006-01-30 15:15:05 +03:00 +++ 1.295/sql/sql_show.cc 2006-02-01 03:02:09 +03:00 @@ -267,6 +267,7 @@ {"Show view","Tables","To see views with SHOW CREATE VIEW"}, {"Shutdown","Server Admin", "To shut down the server"}, {"Super","Server Admin","To use KILL thread, SET GLOBAL, CHANGE MASTER, etc."}, + {"Trigger","Tables", "To use triggers"}, {"Update", "Tables", "To update existing rows"}, {"Usage","Server Admin","No privileges - allow connect only"}, {NullS, NullS, NullS} --- 1.447/sql/sql_yacc.yy 2006-01-30 15:31:21 +03:00 +++ 1.448/sql/sql_yacc.yy 2006-02-01 03:02:09 +03:00 @@ -10237,6 +10237,7 @@ | ALTER ROUTINE_SYM { Lex->grant |= ALTER_PROC_ACL; } | CREATE USER { Lex->grant |= CREATE_USER_ACL; } | EVENT_SYM { Lex->grant |= EVENT_ACL;} + | TRIGGER_SYM { Lex->grant |= TRIGGER_ACL; } ; --- 1.2/mysql-test/r/trigger-compat.result 2006-01-12 03:02:48 +03:00 +++ 1.3/mysql-test/r/trigger-compat.result 2006-02-01 03:02:09 +03:00 @@ -7,8 +7,7 @@ CREATE DATABASE mysqltest_db1; CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; -GRANT SUPER ON *.* TO mysqltest_dfn@localhost; -GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; +GRANT CREATE, TRIGGER ON mysqltest_db1.* TO mysqltest_dfn@localhost; ---> connection: wl2818_definer_con CREATE TABLE t1(num_value INT); --- 1.2/mysql-test/r/trigger-grant.result 2006-01-12 03:02:48 +03:00 +++ 1.3/mysql-test/r/trigger-grant.result 2006-02-01 03:02:09 +03:00 @@ -7,8 +7,7 @@ CREATE DATABASE mysqltest_db1; CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; -GRANT SUPER ON *.* TO mysqltest_dfn@localhost; -GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; +GRANT CREATE, TRIGGER ON mysqltest_db1.* TO mysqltest_dfn@localhost; ---> connection: wl2818_definer_con CREATE TABLE t1(num_value INT); @@ -175,6 +174,17 @@ TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = 0; +ERROR 42000: Access denied; you need the SUPER privilege for this operation + +---> connection: default +use mysqltest_db1; +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con2 +CREATE DEFINER='mysqltest_inv'@'localhost' + TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @new_sum = 0; CREATE DEFINER='mysqltest_nonexs'@'localhost' TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW @@ -182,7 +192,6 @@ Warnings: Note 1449 There is no 'mysqltest_nonexs'@'localhost' registered INSERT INTO t1 VALUES(6); -ERROR 42000: Access denied; you need the SUPER privilege for this operation SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 SET @new_sum = 0 BEFORE NULL mysqltest_inv@localhost --- 1.3/mysql-test/t/trigger-compat.test 2005-12-14 23:39:15 +03:00 +++ 1.4/mysql-test/t/trigger-compat.test 2006-02-01 03:02:09 +03:00 @@ -35,8 +35,7 @@ CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; -GRANT SUPER ON *.* TO mysqltest_dfn@localhost; -GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; +GRANT CREATE, TRIGGER ON mysqltest_db1.* TO mysqltest_dfn@localhost; # # Create a table and the first trigger. --- 1.3/mysql-test/t/trigger-grant.test 2005-12-14 23:39:15 +03:00 +++ 1.4/mysql-test/t/trigger-grant.test 2006-02-01 03:02:09 +03:00 @@ -44,8 +44,7 @@ CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; -GRANT SUPER ON *.* TO mysqltest_dfn@localhost; -GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; +GRANT CREATE, TRIGGER ON mysqltest_db1.* TO mysqltest_dfn@localhost; # # Check that triggers are executed under the authorization of the definer: @@ -362,10 +361,6 @@ # # Check DEFINER clause of CREATE TRIGGER statement. # -# NOTE: there is no dedicated TRIGGER privilege for CREATE TRIGGER statement. -# SUPER privilege is used instead. I.e., if one invokes CREATE TRIGGER, it should -# have SUPER privilege, so this test is meaningless right now. -# # - Check that SUPER privilege required to create a trigger with different # definer: # - try to create a trigger with DEFINER="definer@localhost" under @@ -391,7 +386,25 @@ DROP TRIGGER trg1; # Check that SUPER is required to specify different DEFINER. -# NOTE: meaningless at the moment + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +CREATE DEFINER='mysqltest_inv'@'localhost' + TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @new_sum = 0; + +--connection default +--echo +--echo ---> connection: default + +use mysqltest_db1; + +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; + +--connect (wl2818_definer_con2,localhost,mysqltest_dfn,,mysqltest_db1) +--connection wl2818_definer_con2 +--echo +--echo ---> connection: wl2818_definer_con2 CREATE DEFINER='mysqltest_inv'@'localhost' TRIGGER trg1 BEFORE INSERT ON t1 @@ -407,7 +420,7 @@ # Check that trg2 will not be activated. ---error ER_SPECIFIC_ACCESS_DENIED_ERROR +# --error ER_SPECIFIC_ACCESS_DENIED_ERROR INSERT INTO t1 VALUES(6); # --- 1.43/sql/sql_trigger.cc 2006-01-13 19:04:32 +03:00 +++ 1.44/sql/sql_trigger.cc 2006-02-01 03:02:09 +03:00 @@ -177,12 +177,20 @@ DBUG_ASSERT(tables->next_global == 0); /* - TODO: We should check if user has TRIGGER privilege for table here. - Now we just require SUPER privilege for creating/dropping because - we don't have proper privilege checking for triggers in place yet. + Check that the user has TRIGGER privilege on the subject table. */ - if (check_global_access(thd, SUPER_ACL)) - DBUG_RETURN(TRUE); + { + bool err_status; + TABLE_LIST **save_query_tables_own_last= thd->lex->query_tables_own_last; + thd->lex->query_tables_own_last= 0; + + err_status= check_table_access(thd, TRIGGER_ACL, tables, 0); + + thd->lex->query_tables_own_last= save_query_tables_own_last; + + if (err_status) + DBUG_RETURN(TRUE); + } /* There is no DETERMINISTIC clause for triggers, so can't check it. @@ -1151,24 +1159,10 @@ if (sp_change_security_context(thd, sp_trigger, &save_ctx)) return TRUE; - /* - NOTE: TRIGGER_ACL should be used below. - */ - - if (check_global_access(thd, SUPER_ACL)) - { - sp_restore_security_context(thd, save_ctx); - return TRUE; - } - - /* - If the trigger uses special variables (NEW/OLD), check that we have - SELECT and UPDATE privileges on the subject table. - */ - - if (is_special_var_used(event, time_type)) { TABLE_LIST table_list, **save_query_tables_own_last; + int wanted_access = TRIGGER_ACL; + bzero((char *) &table_list, sizeof (table_list)); table_list.db= (char *) table->s->db.str; table_list.db_length= table->s->db.length; @@ -1178,9 +1172,17 @@ table_list.table= table; save_query_tables_own_last= thd->lex->query_tables_own_last; thd->lex->query_tables_own_last= 0; + + /* + If the trigger uses special variables (NEW/OLD), check that we have + SELECT and UPDATE privileges on the subject table. + */ + + if (is_special_var_used(event, time_type)) + wanted_access|= SELECT_ACL | UPDATE_ACL; + + err_status= check_table_access(thd, wanted_access, &table_list, 0); - err_status= check_table_access(thd, SELECT_ACL | UPDATE_ACL, - &table_list, 0); thd->lex->query_tables_own_last= save_query_tables_own_last; if (err_status) { @@ -1188,7 +1190,6 @@ return TRUE; } } - #endif // NO_EMBEDDED_ACCESS_CHECKS thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER); --- 1.104/mysql-test/r/information_schema.result 2006-01-30 15:15:03 +03:00 +++ 1.105/mysql-test/r/information_schema.result 2006-02-01 03:02:08 +03:00 @@ -406,6 +406,7 @@ 'mysqltest_1'@'localhost' NULL test CREATE ROUTINE YES 'mysqltest_1'@'localhost' NULL test ALTER ROUTINE YES 'mysqltest_1'@'localhost' NULL test EVENT YES +'mysqltest_1'@'localhost' NULL test TRIGGER YES select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL test t1 SELECT NO --- 1.3/mysql-test/r/lowercase_table_grant.result 2006-01-10 21:16:42 +03:00 +++ 1.4/mysql-test/r/lowercase_table_grant.result 2006-02-01 03:02:08 +03:00 @@ -6,8 +6,8 @@ GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' select * from db where user = 'mysqltest_1'; -Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv -localhost mysqltest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv +localhost mysqltest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y update db set db = 'MYSQLtest' where db = 'mysqltest' and user = 'mysqltest_1' and host = 'localhost'; flush privileges; show grants for mysqltest_1@localhost; @@ -15,8 +15,8 @@ GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' select * from db where user = 'mysqltest_1'; -Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv -localhost MYSQLtest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv +localhost MYSQLtest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y delete from db where db = 'MYSQLtest' and user = 'mysqltest_1' and host = 'localhost'; flush privileges; drop user mysqltest_1@localhost; --- 1.47/mysql-test/r/grant.result 2006-01-10 21:50:16 +03:00 +++ 1.48/mysql-test/r/grant.result 2006-02-01 03:02:08 +03:00 @@ -11,8 +11,8 @@ 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 Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_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 N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 0 +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 Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_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 N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 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' @@ -42,15 +42,15 @@ flush privileges; grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10; 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 Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_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 N N N N N N 10 0 0 0 +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 Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_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 N N N N N N N 10 0 0 0 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30; 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 Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_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 N N N N N N 10 20 30 0 +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 Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_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 N N N N N N N 10 20 30 0 show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 @@ -85,7 +85,7 @@ 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, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER 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; @@ -465,6 +465,7 @@ Show view Tables To see views with SHOW CREATE VIEW Shutdown Server Admin To shut down the server Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. +Trigger Tables To use triggers Update Tables To update existing rows Usage Server Admin No privileges - allow connect only create database mysqltest; @@ -491,8 +492,8 @@ PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; TABLE_SCHEMA TABLE_NAME PRIVILEGES -mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, UPDATE -mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, UPDATE +mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE +mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE FLUSH PRIVILEGES; SHOW GRANTS FOR dummy@localhost; Grants for dummy@localhost @@ -503,8 +504,8 @@ PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; TABLE_SCHEMA TABLE_NAME PRIVILEGES -mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, UPDATE -mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, UPDATE +mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE +mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE SHOW FIELDS FROM mysql.tables_priv; Field Type Null Key Default Extra Host char(60) NO PRI @@ -513,7 +514,7 @@ Table_name char(64) NO PRI Grantor char(77) NO MUL Timestamp timestamp YES CURRENT_TIMESTAMP -Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view') NO +Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') NO Column_priv set('Select','Insert','Update','References') NO use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; @@ -594,7 +595,7 @@ flush privileges; set @user123="non-existent"; select * from mysql.db where user=@user123; -Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv set names koi8r; create database ÂÄ; grant select on ÂÄ.* to root@localhost; --- 1.35/scripts/mysql_fix_privilege_tables.sql 2006-01-30 15:31:16 +03:00 +++ 1.36/scripts/mysql_fix_privilege_tables.sql 2006-02-01 03:02:09 +03:00 @@ -603,3 +603,17 @@ ALTER TABLE event DROP PRIMARY KEY; ALTER TABLE event ADD PRIMARY KEY(definer, db, name); +-- +-- TRIGGER privilege +-- + +SET @hadTriggerPriv := 0; +SELECT @hadTriggerPriv :=1 FROM user WHERE Grant_priv LIKE '%'; + +ALTER TABLE user add Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; +ALTER TABLE host add Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; +ALTER TABLE db add Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; + +UPDATE user SET Trigger_priv=Create_priv WHERE @hadTriggerPriv = 0; +UPDATE host SET Trigger_priv=Create_priv WHERE @hadTriggerPriv = 0; +UPDATE db SET Trigger_priv=Create_priv WHERE @hadTriggerPriv = 0; --- 1.180/mysql-test/r/sp.result 2006-01-19 12:28:59 +03:00 +++ 1.181/mysql-test/r/sp.result 2006-02-01 03:02:08 +03:00 @@ -2197,6 +2197,7 @@ Show view Tables To see views with SHOW CREATE VIEW Shutdown Server Admin To shut down the server Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. +Trigger Tables To use triggers Update Tables To update existing rows Usage Server Admin No privileges - allow connect only Variable_name Value @@ -2250,6 +2251,7 @@ Show view Tables To see views with SHOW CREATE VIEW Shutdown Server Admin To shut down the server Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. +Trigger Tables To use triggers Update Tables To update existing rows Usage Server Admin No privileges - allow connect only Variable_name Value --- 1.59/mysql-test/r/ps.result 2006-01-26 16:36:22 +03:00 +++ 1.60/mysql-test/r/ps.result 2006-02-01 03:02:08 +03:00 @@ -533,13 +533,13 @@ prepare my_stmt from @aux; execute my_stmt; COUNT(*) -38 +39 execute my_stmt; COUNT(*) -38 +39 execute my_stmt; COUNT(*) -38 +39 deallocate prepare my_stmt; drop procedure if exists p1| drop table if exists t1| --- 1.35/scripts/mysql_create_system_tables.sh 2006-01-30 15:31:16 +03:00 +++ 1.36/scripts/mysql_create_system_tables.sh 2006-02-01 03:02:09 +03:00 @@ -75,14 +75,15 @@ c_d="$c_d Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_d="$c_d Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_d="$c_d Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," + c_d="$c_d Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_d="$c_d PRIMARY KEY Host (Host,Db,User)," c_d="$c_d KEY User (User)" c_d="$c_d ) engine=MyISAM" c_d="$c_d CHARACTER SET utf8 COLLATE utf8_bin" c_d="$c_d comment='Database privileges';" - i_d="INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y'); - INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y');" + i_d="INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y'); + INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y');" fi if test ! -f $mdata/host.frm @@ -111,6 +112,7 @@ c_h="$c_h Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_h="$c_h Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_h="$c_h Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," + c_h="$c_h Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_h="$c_h PRIMARY KEY Host (Host,Db)" c_h="$c_h ) engine=MyISAM" c_h="$c_h CHARACTER SET utf8 COLLATE utf8_bin" @@ -154,6 +156,7 @@ c_u="$c_u Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_u="$c_u Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_u="$c_u Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," + c_u="$c_u Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL," c_u="$c_u ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL," c_u="$c_u ssl_cipher BLOB NOT NULL," c_u="$c_u x509_issuer BLOB NOT NULL," @@ -169,22 +172,22 @@ if test "$1" = "test" then - i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); - INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); - REPLACE INTO user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); + i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); + INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); + REPLACE INTO user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); INSERT INTO user (host,user) values ('localhost',''); INSERT INTO user (host,user) values ('$hostname','');" else - i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);" + i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);" if test "$windows" = "0" then i_u="$i_u - INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); + INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); INSERT INTO user (host,user) values ('$hostname',''); INSERT INTO user (host,user) values ('localhost','');" else i_u="$i_u - INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);" + INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);" fi fi fi @@ -234,7 +237,7 @@ c_t="$c_t Table_name char(64) binary DEFAULT '' NOT NULL," c_t="$c_t Grantor char(77) DEFAULT '' NOT NULL," c_t="$c_t Timestamp timestamp," - c_t="$c_t Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view') COLLATE utf8_general_ci DEFAULT '' NOT NULL," + c_t="$c_t Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL," c_t="$c_t Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL," c_t="$c_t PRIMARY KEY (Host,Db,User,Table_name)," c_t="$c_t KEY Grantor (Grantor)"
| Thread | ||
|---|---|---|
| • bk commit into 5.1 tree (anozdrin:1.2099) BUG#9412 | Alexander Nozdrin | 1 Feb |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.688 sec. using MySQL 5.1.14-beta-log