grant.pl

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

safe_query("grant update(b),delete on $opt_database.test to $user");
safe_query("revoke update(a) on $opt_database.test from $user",1);
user_query("delete from $opt_database.test where a=2",1);
user_query("update test set b=5 where b>0",1);
safe_query("grant select(a),select(b) on $opt_database.test to $user");
user_query("delete from $opt_database.test where a=2");
user_query("delete from $opt_database.test where A=2");
user_query("update test set b=5 where b>0");
user_query("update test set a=11 where b>5",1);
user_query("update test,test2 SET test.b=5 where b>0",1);
user_query("update test,test2 SET test.a=11 where b>0",1);
user_query("update test,test2 SET test.b=test2.a where b>0",1);
user_query("update test,test2 SET test.b=11 where test2.a>0",1);
user_query("select a,A from test");

safe_query("select $tables_cols from mysql.tables_priv");
safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user");
safe_query("select $tables_cols from mysql.tables_priv");
safe_query("revoke GRANT OPTION on $opt_database.test from $user",1);
safe_query("drop table $opt_database.test2");
safe_query("revoke create,update on $opt_database.test2 from $user");

#
# Test grants on database level
#

safe_query("grant select(a) on $opt_database.test to $user");
user_query("show full columns from test");
safe_query("grant insert (b), update (b) on $opt_database.test to $user");

user_query("select count(a) from test");
user_query("select count(skr.a) from test as skr");
user_query("select count(a) from test where a > 5");
user_query("insert into test (b) values (5)");
user_query("insert into test (b) values (a)");
user_query("update test set b=3 where a > 0");

user_query("select * from test",1);
user_query("select b from test",1);
user_query("select a from test where b > 0",1);
user_query("insert into test (a) values (10)",1);
user_query("insert into test (b) values (b)",1);
user_query("insert into test (a,b) values (1,5)",1);
user_query("insert into test (b) values (1),(b)",1);
user_query("update test set b=3 where b > 0",1);

safe_query("select $tables_cols from mysql.tables_priv");
safe_query("select $columns_cols from mysql.columns_priv");
safe_query("revoke select(a), update (b) on $opt_database.test from $user");
safe_query("select $tables_cols from mysql.tables_priv");
safe_query("select $columns_cols from mysql.columns_priv");

user_query("select count(a) from test",1);
user_query("update test set b=4",1);

safe_query("grant select(a,b), update (a,b) on $opt_database.test to $user");
user_query("select count(a),count(b) from test where a+b > 0");
user_query("insert into test (b) values (9)");
user_query("update test set b=6 where b > 0");

safe_query("flush privileges");	# Test restoring privileges from disk
safe_query("select $tables_cols from mysql.tables_priv");
safe_query("select $columns_cols from mysql.columns_priv");

# Try mixing of table and database privileges

user_query("insert into test (a,b) values (12,12)",1);
safe_query("grant insert on $opt_database.* to $user");
user_connect(0);
user_query("insert into test (a,b) values (13,13)");

# This grants and revokes SELECT on different levels.
safe_query("revoke select(b) on $opt_database.test from $user");
user_query("select count(a) from test where a+b > 0",1);
user_query("update test set b=5 where a=2");
safe_query("grant select on $opt_database.test to $user");
user_connect(0);
user_query("select count(a) from test where a+b > 0");
safe_query("revoke select(b) on $opt_database.test from $user");
user_query("select count(a) from test where a+b > 0");
safe_query("revoke select on $opt_database.test from $user");
user_connect(0);
user_query("select count(a) from test where a+b > 0",1);
safe_query("grant select(a) on $opt_database.test to $user");
user_query("select count(a) from test where a+b > 0",1);
safe_query("grant select on *.* to $user");
user_connect(0);
user_query("select count(a) from test where a+b > 0");
safe_query("revoke select on *.* from $user");
safe_query("grant select(b) on $opt_database.test to $user");
user_connect(0);
user_query("select count(a) from test where a+b > 0");


safe_query("select * from mysql.db where user = '$opt_user'");
safe_query("select $tables_cols from mysql.tables_priv where user = '$opt_user'");
safe_query("select $columns_cols from mysql.columns_priv where user = '$opt_user'");

safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user");
user_query("select count(a) from test",1);
user_query("select * from mysql.user order by hostname",1);
safe_query("select * from mysql.db where user = '$opt_user'");
safe_query("select $tables_cols from mysql.tables_priv where user = '$opt_user'");
safe_query("select $columns_cols from mysql.columns_priv where user = '$opt_user'");

#
# Clear up privileges to make future tests easier

safe_query("delete from user where user='$opt_user'");
safe_query("delete from db where user='$opt_user'");
safe_query("flush privileges");
safe_query("show grants for $user",1);

#
# Test IDENTIFIED BY
#

safe_query("grant ALL PRIVILEGES on $opt_database.test to $user identified by 'dummy',  ${opt_user}\@127.0.0.1 identified by 'dummy2'");
user_connect(0,"dummy");
safe_query("grant SELECT on $opt_database.* to $user identified by ''");
user_connect(0);
safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user identified by '', ${opt_user}\@127.0.0.1 identified by 'dummy2'");
safe_query("revoke ALL PRIVILEGES on $opt_database.* from $user identified by ''");

safe_query("show grants for $user");

#
# Test bug reported in SELECT INTO OUTFILE
#

safe_query("create table $opt_database.test3 (a int, b int)");
safe_query("grant SELECT on $opt_database.test3 to $user");
safe_query("grant FILE on *.* to $user");
safe_query("insert into $opt_database.test3 values (1,1)");
user_connect(0);
user_query("select * into outfile '$tmp_table' from $opt_database.test3");
safe_query("revoke SELECT on $opt_database.test3 from $user");
safe_query("grant SELECT(a) on $opt_database.test3 to $user");
user_query("select a from $opt_database.test3");
user_query("select * from $opt_database.test3",1);
user_query("select a,b from $opt_database.test3",1);
user_query("select b from $opt_database.test3",1);

safe_query("revoke SELECT(a) on $opt_database.test3 from $user");
safe_query("revoke FILE on *.* from $user");
safe_query("drop table $opt_database.test3");

#
# Test privileges needed for LOCK TABLES
#

safe_query("create table $opt_database.test3 (a int)");
user_connect(1);
safe_query("grant INSERT on $opt_database.test3 to $user");
user_connect(0);
user_query("select * into outfile '$tmp_table' from $opt_database.test3",1);
safe_query("grant SELECT on $opt_database.test3 to $user");
user_connect(0);
user_query("LOCK TABLES $opt_database.test3 READ",1);
safe_query("grant LOCK TABLES on *.* to $user");
safe_query("show grants for $user");
safe_query("select * from mysql.user where user='$opt_user'");
user_connect(0);
user_query("LOCK TABLES $opt_database.test3 READ");
user_query("UNLOCK TABLES");
safe_query("revoke SELECT,INSERT,UPDATE,DELETE on $opt_database.test3 from $user");
user_connect(0);
safe_query("revoke LOCK TABLES on *.* from $user");
user_connect(1);
safe_query("drop table $opt_database.test3");

#
# test new privileges in 4.0.2
#

safe_query("show grants for $user");
safe_query("grant all on *.* to $user WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3");
safe_query("show grants for $user");
safe_query("revoke LOCK TABLES on *.* from $user");
safe_query("flush privileges");
safe_query("show grants for $user");
safe_query("revoke ALL PRIVILEGES on *.* from $user");
safe_query("show grants for $user");

#
# Clean up things
#

unlink($tmp_table);
safe_query("drop database $opt_database");
safe_query("delete from user where user='$opt_user'");
safe_query("delete from db where user='$opt_user'");
safe_query("delete from tables_priv");
safe_query("delete from columns_priv");
safe_query("flush privileges");

print "end of test\n";
exit 0;

sub usage
{
    print <<EOF;
$0  Ver $version

This program tests that the GRANT commands works by creating a temporary
database ($opt_database) and user ($opt_user).

Options:

--database (Default $opt_database)
  In which database the test tables are created.

--force
  Don''t ask any question before starting this test.

--host='host name' (Default $opt_host)
  Host name where the database server is located.

--Information
--help
  Print this help

--password
  Password for root-user.

--server='server name'  (Default $opt_server)
  Run the test on the given SQL server.

--user  (Default $opt_user)
  A non-existing user on which we will test the GRANT commands.

--verbose
  Write all queries when we are execute them.

--root-user='user name' (Default $opt_root_user)
  User with privileges to modify the 'mysql' database.
EOF
  exit(0);
}


sub print_info
{
  my $tmp;
  print <<EOF;
This test will clear your table and column grant table and recreate the
$opt_database database !
All privileges for $user will be destroyed !

Don\'t run this test if you have done any GRANT commands that you want to keep!
EOF
 for (;;)
  {
    print "Start test (yes/no) ? ";
    $tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
    last if ($tmp =~ /^yes$/i);
    exit 1 if ($tmp =~ /^n/i);
    print "\n";
  }
}


sub user_connect
{
  my ($ignore_error,$password)=@_;
  $password="" if (!defined($password));

  print "Connecting $opt_user\n" if ($opt_verbose);
  $user_dbh->disconnect if (defined($user_dbh));

  $user_dbh=DBI->connect("DBI:mysql:$opt_database:$opt_host",$opt_user,
			 $password, { PrintError => 0});
  if (!$user_dbh)
  {
    if ($opt_verbose || !$ignore_error)
    {
      print "Error on connect: $DBI::errstr\n";
    }
    if (!$ignore_error)
    {
      die "The above should not have failed!";
    }
  }
  elsif ($ignore_error)
  {
    die "Connect succeeded when it shouldn't have !\n";
  }
}

sub safe_query
{
  my ($query,$ignore_error)=@_;
  if (do_query($dbh,$query, $ignore_error))
  {
    if (!defined($ignore_error))
    {
      die "The above should not have failed!";
    }
  }
  elsif (defined($ignore_error) && $ignore_error == 1)
  {
    die "Query '$query' succeeded when it shouldn't have !\n";
  }
}


sub user_query
{
  my ($query,$ignore_error)=@_;
  if (do_query($user_dbh,$query, $ignore_error))
  {
    if (!defined($ignore_error))
    {
      die "Query '$query' should not have failed!";
    }
  }
  elsif (defined($ignore_error) && $ignore_error == 1)
  {
    die "Query '$query' succeeded when it shouldn't have !\n";
  }
}


sub do_query
{
  my ($my_dbh, $query, $ignore_error)=@_;
  my ($sth, $row, $tab, $col, $found, $fatal_error);

  print "$query\n" if ($opt_debug || $opt_verbose);
  if (!($sth= $my_dbh->prepare($query)))
  {
    print "Error in prepare: $DBI::errstr\n";
    return 1;
  }
  if (!$sth->execute)
  {
    $fatal_error= ($DBI::errstr =~ /parse error/);
    if (!$ignore_error || ($opt_verbose && $ignore_error != 3) || $fatal_error)
    {
      print "Error in execute: $DBI::errstr\n";
    }
    die if ($fatal_error);
    $sth->finish;
    return 1;
  }
  $found=0;
  if (!$opt_silent)
  {
    while (($row=$sth->fetchrow_arrayref))
    {
      $found=1;
      $tab="";
      foreach $col (@$row)
      {
	print $tab;
	print defined($col) ? $col : "NULL";
	$tab="\t";
      }
      print "\n";
    }
    print "\n" if ($found);
  }
  $sth->finish;
  return 0;
}