[Commits] ea20a15: MDEV-5214 Status variables for number of global/db/table/column/role grants

vicentiu at mariadb.org vicentiu at mariadb.org
Thu Mar 19 11:06:21 EET 2015


revision-id: ea20a152802fc7eeadc6e908ce2bbe8689231ad7
parent(s): e28a241907aa7a511b65b196703efaeea71e1dc4
committer: Vicențiu Ciorbaru
branch nick: server
timestamp: 2015-03-18 20:45:30 +0200
message:

MDEV-5214 Status variables for number of global/db/table/column/role grants

Implemented the status variables for use with the feedback plugin.

---
 mysql-test/r/information_schema.result       |   1 +
 mysql-test/suite/roles/acl_statistics.result | 106 +++++++++++++++++++++++++++
 mysql-test/suite/roles/acl_statistics.test   |  66 +++++++++++++++++
 sql/mysqld.cc                                |   1 +
 sql/sql_acl.cc                               |  49 +++++++++++++
 sql/sql_acl.h                                |   2 +
 6 files changed, 225 insertions(+)

diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 1af6985..847c959 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -785,6 +785,7 @@ Database	Table	In_use	Name_locked
 mysql	user	0	0
 show status where variable_name like "%database%";
 Variable_name	Value
+Acl_database_grants	2
 Com_show_databases	3
 show variables where variable_name like "skip_show_databas";
 Variable_name	Value
diff --git a/mysql-test/suite/roles/acl_statistics.result b/mysql-test/suite/roles/acl_statistics.result
new file mode 100644
index 0000000..bf74cbf
--- /dev/null
+++ b/mysql-test/suite/roles/acl_statistics.result
@@ -0,0 +1,106 @@
+SHOW STATUS LIKE 'Acl%';
+Variable_name	Value
+Acl_column_grants	0
+Acl_database_grants	2
+Acl_function_grants	0
+Acl_procedure_grants	0
+Acl_proxy_users	2
+Acl_role_grants	0
+Acl_roles	0
+Acl_table_grants	0
+Acl_users	4
+SELECT count(*) COLUMN_GRANTS from mysql.columns_priv;
+COLUMN_GRANTS
+0
+SELECT count(*) DATABASE_GRANTS from mysql.db;
+DATABASE_GRANTS
+2
+SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION';
+FUNCTION_GRANTS
+0
+SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE';
+PROCEDURE_GRANTS
+0
+SELECT count(*) PROXY_USERS from mysql.proxies_priv;
+PROXY_USERS
+2
+SELECT count(*) ROLE_GRANTS from mysql.roles_mapping;
+ROLE_GRANTS
+0
+SELECT count(*) ROLES from mysql.user where is_role='Y';
+ROLES
+0
+SELECT count(*) TABLE_GRANTS from mysql.tables_priv;
+TABLE_GRANTS
+0
+SELECT count(*) USERS from mysql.user where is_role='N';
+USERS
+4
+CREATE USER u1;
+CREATE ROLE r1;
+CREATE ROLE r2;
+GRANT PROXY ON root TO u1;
+GRANT SELECT ON *.* to u1;
+GRANT SELECT ON *.* to r1;
+GRANT DELETE ON mysql.* to u1;
+GRANT DELETE ON mysql.* to r1;
+GRANT INSERT ON mysql.user to u1;
+GRANT INSERT ON mysql.user to r1;
+GRANT UPDATE (host) ON mysql.user to u1;
+GRANT UPDATE (host) ON mysql.user to r1;
+GRANT r1 to u1;
+GRANT r2 to r1;
+create procedure mysql.test_proc (OUT param1 INT)
+begin
+select COUNT(*) into param1 from mysql.roles_mapping;
+end|
+GRANT EXECUTE ON PROCEDURE mysql.test_proc TO r1;
+GRANT EXECUTE ON PROCEDURE mysql.test_proc TO u1;
+CREATE FUNCTION mysql.test_func (param INT) RETURNS INT
+RETURN (SELECT COUNT(*) FROM mysql.user);
+GRANT EXECUTE ON FUNCTION mysql.test_func TO r1;
+GRANT EXECUTE ON FUNCTION mysql.test_func TO u1;
+GRANT EXECUTE ON FUNCTION mysql.test_func TO r2;
+SHOW STATUS LIKE 'Acl%';
+Variable_name	Value
+Acl_column_grants	2
+Acl_database_grants	4
+Acl_function_grants	3
+Acl_procedure_grants	2
+Acl_proxy_users	3
+Acl_role_grants	4
+Acl_roles	2
+Acl_table_grants	2
+Acl_users	5
+SELECT count(*) COLUMN_GRANTS from mysql.columns_priv;
+COLUMN_GRANTS
+2
+SELECT count(*) DATABASE_GRANTS from mysql.db;
+DATABASE_GRANTS
+4
+SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION';
+FUNCTION_GRANTS
+3
+SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE';
+PROCEDURE_GRANTS
+2
+SELECT count(*) PROXY_USERS from mysql.proxies_priv;
+PROXY_USERS
+3
+SELECT count(*) ROLE_GRANTS from mysql.roles_mapping;
+ROLE_GRANTS
+4
+SELECT count(*) ROLES from mysql.user where is_role='Y';
+ROLES
+2
+SELECT count(*) TABLE_GRANTS from mysql.tables_priv;
+TABLE_GRANTS
+2
+SELECT count(*) USERS from mysql.user where is_role='N';
+USERS
+5
+DROP PROCEDURE mysql.test_proc;
+DROP FUNCTION mysql.test_func;
+DROP ROLE r2;
+DROP ROLE r1;
+DROP USER u1;
diff --git a/mysql-test/suite/roles/acl_statistics.test b/mysql-test/suite/roles/acl_statistics.test
new file mode 100644
index 0000000..c76d376
--- /dev/null
+++ b/mysql-test/suite/roles/acl_statistics.test
@@ -0,0 +1,66 @@
+# Test case for validating acl statistics for the feedback plugin.
+--source include/not_embedded.inc
+
+# First get a baseline of the initial statistics.
+SHOW STATUS LIKE 'Acl%';
+SELECT count(*) COLUMN_GRANTS from mysql.columns_priv;
+SELECT count(*) DATABASE_GRANTS from mysql.db;
+SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION';
+SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE';
+SELECT count(*) PROXY_USERS from mysql.proxies_priv;
+SELECT count(*) ROLE_GRANTS from mysql.roles_mapping;
+SELECT count(*) ROLES from mysql.user where is_role='Y';
+SELECT count(*) TABLE_GRANTS from mysql.tables_priv;
+SELECT count(*) USERS from mysql.user where is_role='N';
+
+# Next add some users, roles and privileges to them.
+CREATE USER u1;
+CREATE ROLE r1;
+CREATE ROLE r2;
+GRANT PROXY ON root TO u1;
+GRANT SELECT ON *.* to u1;
+GRANT SELECT ON *.* to r1;
+GRANT DELETE ON mysql.* to u1;
+GRANT DELETE ON mysql.* to r1;
+GRANT INSERT ON mysql.user to u1;
+GRANT INSERT ON mysql.user to r1;
+GRANT UPDATE (host) ON mysql.user to u1;
+GRANT UPDATE (host) ON mysql.user to r1;
+
+GRANT r1 to u1;
+GRANT r2 to r1;
+
+delimiter |;
+create procedure mysql.test_proc (OUT param1 INT)
+begin
+  select COUNT(*) into param1 from mysql.roles_mapping;
+end|
+delimiter ;|
+GRANT EXECUTE ON PROCEDURE mysql.test_proc TO r1;
+GRANT EXECUTE ON PROCEDURE mysql.test_proc TO u1;
+
+CREATE FUNCTION mysql.test_func (param INT) RETURNS INT
+  RETURN (SELECT COUNT(*) FROM mysql.user);
+GRANT EXECUTE ON FUNCTION mysql.test_func TO r1;
+GRANT EXECUTE ON FUNCTION mysql.test_func TO u1;
+# Extra grant to differentiate procedure from function grants.
+GRANT EXECUTE ON FUNCTION mysql.test_func TO r2;
+
+# Recheck how statistics are updated. Make sure that both the information
+# schema and the actualy physical rows are the same.
+SHOW STATUS LIKE 'Acl%';
+SELECT count(*) COLUMN_GRANTS from mysql.columns_priv;
+SELECT count(*) DATABASE_GRANTS from mysql.db;
+SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION';
+SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE';
+SELECT count(*) PROXY_USERS from mysql.proxies_priv;
+SELECT count(*) ROLE_GRANTS from mysql.roles_mapping;
+SELECT count(*) ROLES from mysql.user where is_role='Y';
+SELECT count(*) TABLE_GRANTS from mysql.tables_priv;
+SELECT count(*) USERS from mysql.user where is_role='N';
+
+DROP PROCEDURE mysql.test_proc;
+DROP FUNCTION mysql.test_func;
+DROP ROLE r2;
+DROP ROLE r1;
+DROP USER u1;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index f33df61..d050c75 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -8149,6 +8149,7 @@ int show_threadpool_idle_threads(THD *thd, SHOW_VAR *var, char *buff,
 SHOW_VAR status_vars[]= {
   {"Aborted_clients",          (char*) &aborted_threads,        SHOW_LONG},
   {"Aborted_connects",         (char*) &aborted_connects,       SHOW_LONG},
+  {"Acl",                      (char*) acl_statistics,          SHOW_ARRAY},
   {"Access_denied_errors",     (char*) offsetof(STATUS_VAR, access_denied_errors), SHOW_LONG_STATUS},
   {"Binlog_bytes_written",     (char*) offsetof(STATUS_VAR, binlog_bytes_written), SHOW_LONGLONG_STATUS},
   {"Binlog_cache_disk_use",    (char*) &binlog_cache_disk_use,  SHOW_LONG},
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 471dd0e..03e7ca9 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -10340,6 +10340,38 @@ applicable_roles_insert(ACL_USER_BASE *grantee, ACL_ROLE *role, void *ptr)
   return 0;
 }
 
+/**
+  Hash iterate function to count the number of total column privileges granted.
+*/
+static my_bool count_column_grants(void *grant_table,
+                                       void *current_count)
+{
+  HASH hash_columns = ((GRANT_TABLE *)grant_table)->hash_columns;
+  *(ulong *)current_count+= hash_columns.records;
+  return 0;
+}
+
+/**
+  SHOW function that computes the number of column grants.
+
+  This must be performed under the mutex in order to make sure the
+  iteration does not fail.
+*/
+static int show_column_grants(THD *thd, SHOW_VAR *var, char *buff,
+                              enum enum_var_type scope)
+{
+  var->type= SHOW_ULONG;
+  var->value= buff;
+  *(ulong *)buff= 0;
+  mysql_rwlock_rdlock(&LOCK_grant);
+  mysql_mutex_lock(&acl_cache->lock);
+  my_hash_iterate(&column_priv_hash, count_column_grants, buff);
+  mysql_mutex_unlock(&acl_cache->lock);
+  mysql_rwlock_unlock(&LOCK_grant);
+  return 0;
+}
+
+
 #else
 bool check_grant(THD *, ulong, TABLE_LIST *, bool, uint, bool)
 {
@@ -10347,6 +10379,23 @@ bool check_grant(THD *, ulong, TABLE_LIST *, bool, uint, bool)
 }
 #endif /*NO_EMBEDDED_ACCESS_CHECKS */
 
+
+SHOW_VAR acl_statistics[] = {
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+  {"column_grants",    (char*)show_column_grants,          SHOW_SIMPLE_FUNC},
+  {"database_grants",  (char*)&acl_dbs.elements,           SHOW_UINT},
+  {"function_grants",  (char*)&func_priv_hash.records,     SHOW_ULONG},
+  {"procedure_grants", (char*)&proc_priv_hash.records,     SHOW_ULONG},
+  {"proxy_users",      (char*)&acl_proxy_users.elements,   SHOW_UINT},
+  {"role_grants",      (char*)&acl_roles_mappings.records, SHOW_ULONG},
+  {"roles",            (char*)&acl_roles.records,          SHOW_ULONG},
+  {"table_grants",     (char*)&column_priv_hash.records,   SHOW_ULONG},
+  {"users",            (char*)&acl_users.elements,         SHOW_UINT},
+#endif
+  {NullS, NullS, SHOW_LONG},
+};
+
+
 int fill_schema_enabled_roles(THD *thd, TABLE_LIST *tables, COND *cond)
 {
   TABLE *table= tables->table;
diff --git a/sql/sql_acl.h b/sql/sql_acl.h
index 55b00a9..335a558 100644
--- a/sql/sql_acl.h
+++ b/sql/sql_acl.h
@@ -402,6 +402,8 @@ int acl_check_set_default_role(THD *thd, const char *host, const char *user);
 int acl_set_default_role(THD *thd, const char *host, const char *user,
                          const char *rolename);
 
+extern SHOW_VAR acl_statistics[];
+
 #ifndef DBUG_OFF
 extern ulong role_global_merges, role_db_merges, role_table_merges,
              role_column_merges, role_routine_merges;


More information about the commits mailing list