The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
###########################################
# checking default user privileges
###########################################
#
# Note: this script is broken in MySQL 5.7.6 and later
# DEPRECATED: replaced by user_privileges.sb.pl

shell:
command  = make_sandbox $TEST_VERSION -- --no_confirm --sandbox_directory=msb_XXXX
expected = sandbox server started
msg      = sandbox creation

#+-----------+-------------+-------------------------------------------+
#| host      | user        | password                                  |
#+-----------+-------------+-------------------------------------------+
#| localhost | root        | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | 
#| 127.%     | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | 
#| localhost | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | 
#| localhost | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | 
#| 127.%     | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | 
#| 127.%     | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | 
#| localhost | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | 
#| 127.%     | rsandbox    | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD | 
#+-----------+-------------+-------------------------------------------+

sql:
path     = $SANDBOX_HOME/msb_XXXX/
query    = select count(*) from mysql.user
expected = 8
msg      = number of users

sql:
path     = $SANDBOX_HOME/msb_XXXX/
query    = select count(*) from mysql.user where host = '127.%'
expected = 4
msg      = number of users with host '127.%'

sql:
path     = $SANDBOX_HOME/msb_XXXX/
query    = select count(*) from mysql.user where user = 'msandbox'
expected = 2
msg      = number of 'msandbox' users

sql:
path     = $SANDBOX_HOME/msb_XXXX/
query    = select count(*) from mysql.user where password = password('msandbox')
expected = 7
msg      = number of 'msandbox' passwords

sql:
path     = $SANDBOX_HOME/msb_XXXX/
query    = select user from mysql.user where password = password('rsandbox')
expected = rsandbox
msg      = user with 'rsandbox' password

shell:
command  = $SANDBOX_HOME/msb_XXXX/use -u msandbox_rw -e 'create table test.t1( i int)'
expected = ok
msg      = table creation allowed

shell:
command  = $SANDBOX_HOME/msb_XXXX/use -u msandbox_rw -e 'insert into test.t1 values (2011)'
expected = ok
msg      = table insertion allowed

shell:
command  = $SANDBOX_HOME/msb_XXXX/use -u msandbox_ro -e 'create table test.t2(i int)' 2>&1 || echo 1
expected = denied
msg      = table creation denied

shell:
command  = $SANDBOX_HOME/msb_XXXX/use -u msandbox_ro -e 'select * from test.t1'
expected = 2011
msg      = table selection allowed

shell:
command  = $SANDBOX_HOME/msb_XXXX/use -u msandbox_rw -e 'drop table test.t1'
expected = ok
msg      = table drop allowed


shell:
command  = $SANDBOX_HOME/msb_XXXX/use -h 127.0.0.1 -u rsandbox -prsandbox -e 'show grants for rsandbox@"127.%"'
expected = REPLICATION SLAVE
msg      = replication slave user

###########################################
# checking user privileges in replication
###########################################

shell:
command     = make_replication_sandbox --replication_directory=rsandbox_XXXX $TEST_VERSION 
expected    = replication directory installed
msg         = replication started

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select sleep(1)
expected = 0
msg      = pause, waiting for replication

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/master/
query    = select count(*) from mysql.user
expected = 8
msg      = number of users (master)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/master/
query    = select count(*) from mysql.user where host = '127.%'
expected = 4
msg      = number of users with host '127.%' (master)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/master/
query    = select count(*) from mysql.user where user = 'msandbox'
expected = 2
msg      = number of 'msandbox' users (master)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/master/
query    = select count(*) from mysql.user where password = password('msandbox')
expected = 7
msg      = number of 'msandbox' passwords (master)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/master/
query    = select user from mysql.user where password = password('rsandbox')
expected = rsandbox
msg      = user with 'rsandbox' password (master)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/master/use -u msandbox_rw -e 'create table test.t1( i int)'
expected = ok
msg      = table creation allowed (master)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/master/use -u msandbox_rw -e 'insert into test.t1 values (2011)'
expected = ok
msg      = table insertion allowed (master)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/master/use -u msandbox_ro -e 'create table test.t2(i int)' 2>&1 || echo 1
expected = denied
msg      = table creation denied (master)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/master/use -u msandbox_ro -e 'select * from test.t1'
expected = 2011
msg      = table selection allowed (master)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/master/use -u msandbox_rw -e 'drop table test.t1'
expected = ok
msg      = table drop allowed (master)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/master/use -h 127.0.0.1 -u rsandbox -prsandbox -e 'show grants for rsandbox@"127.%"'
expected = REPLICATION SLAVE
msg      = replication slave user (master)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select count(*) from mysql.user
expected = 8
msg      = number of users (node1)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select count(*) from mysql.user where host = '127.%'
expected = 4
msg      = number of users with host '127.%' (node1)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select count(*) from mysql.user where user = 'msandbox'
expected = 2
msg      = number of 'msandbox' users (node1)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select count(*) from mysql.user where password = password('msandbox')
expected = 7
msg      = number of 'msandbox' passwords (node1)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select user from mysql.user where password = password('rsandbox')
expected = rsandbox
msg      = user with 'rsandbox' password (node1)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/node1/use -u msandbox_rw -e 'create table test.t11( i int)'
expected = ok
msg      = table creation allowed (node1)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/node1/use -u msandbox_rw -e 'insert into test.t11 values (2011)'
expected = ok
msg      = table insertion allowed (node1)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/node1/use -u msandbox_ro -e 'create table test.t2(i int)' 2>&1 || echo 1
expected = denied
msg      = table creation denied (node1)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/node1/use -u msandbox_ro -e 'select * from test.t11'
expected = 2011
msg      = table selection allowed (node1)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/node1/use -u msandbox_rw -e 'drop table test.t11'
expected = ok
msg      = table drop allowed (node1)

shell:
command  = $SANDBOX_HOME/rsandbox_XXXX/node1/use -h 127.0.0.1 -u rsandbox -prsandbox -e 'show grants for rsandbox@"127.%"'
expected = REPLICATION SLAVE
msg      = replication slave user (node1)

shell:
command  = sbtool -o delete -s $SANDBOX_HOME/msb_XXXX
expected = ok
msg      = single sandbox removed


shell:
command  = sbtool -o delete -s $SANDBOX_HOME/rsandbox_XXXX
expected = ok
msg      = replication sandbox removed

#################################################
# checking remote access ('%' instead of '127.%')
#################################################

shell:
command  = make_sandbox $TEST_VERSION -- --no_confirm --sandbox_directory=msb_XXXX --remote_access=%
expected = sandbox server started
msg      = sandbox creation

sql:
path     = $SANDBOX_HOME/msb_XXXX/
query    = select count(*) from mysql.user
expected = 8
msg      = number of users

sql:
path     = $SANDBOX_HOME/msb_XXXX/
query    = select count(*) from mysql.user where host = '127.%'
expected = 0
msg      = number of users with host '127.%'

sql:
path     = $SANDBOX_HOME/msb_XXXX/
query    = select count(*) from mysql.user where host = '%'
expected = 4
msg      = number of users with host '%'

shell:
command     = make_replication_sandbox --replication_directory=rsandbox_XXXX --remote_access=% $TEST_VERSION 
expected    = replication directory installed
msg         = replication started

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select count(*) from mysql.user
expected = 8
msg      = number of users (node1)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select count(*) from mysql.user where host = '127.%'
expected = 0
msg      = number of users with host '127.%' (node1)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/node1/
query    = select count(*) from mysql.user where host = '%'
expected = 4
msg      = number of users with host '%' (node1)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/master/
query    = select count(*) from mysql.user
expected = 8
msg      = number of users (master)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/master/
query    = select count(*) from mysql.user where host = '127.%'
expected = 0
msg      = number of users with host '127.%' (master)

sql:
path     = $SANDBOX_HOME/rsandbox_XXXX/master/
query    = select count(*) from mysql.user where host = '%'
expected = 4
msg      = number of users with host '%' (master)

shell:
command  = $SANDBOX_HOME/msb_XXXX/stop
expected = OK
msg      = stopped 

shell:
command     = $SANDBOX_HOME/rsandbox_XXXX/stop_all
expected    = OK
msg         = replication stopped