~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'' at line 2

103

REVOKE SELECT on test_grant_db.* FROM joro

104

INDENTIFIED BY 'plug_dest_passwd';

105

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED BY 'plug_dest_passwd'' at line 2

106

REVOKE SELECT on test_grant_db.* FROM joro

107

INDENTIFIED BY PASSWORD 'plug_dest_passwd';

108

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED BY PASSWORD 'plug_dest_passwd'' at line 2

109

DROP DATABASE test_grant_db;

110

## GRANT PROXY tests

111

CREATE USER grant_plug IDENTIFIED WITH 'test_plugin_server'

112

AS 'grant_plug_dest';

113

CREATE USER grant_plug_dest IDENTIFIED BY 'grant_plug_dest_passwd';

114

CREATE USER grant_plug_dest2 IDENTIFIED BY 'grant_plug_dest_passwd2';

115

# ALL PRIVILEGES doesn't include PROXY

116

GRANT ALL PRIVILEGES ON *.* TO grant_plug;

117

ERROR 28000: Access denied for user 'grant_plug'@'localhost' (using password: YES)

118

GRANT ALL PRIVILEGES,PROXY ON grant_plug_dest TO grant_plug;

119

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROXY ON grant_plug_dest TO grant_plug' at line 1

120

this should fail : can't combine PROXY

121

GRANT ALL SELECT,PROXY ON grant_plug_dest TO grant_plug;

122

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT,PROXY ON grant_plug_dest TO grant_plug' at line 1

123

# this should fail : no such grant

124

REVOKE PROXY ON grant_plug_dest FROM grant_plug;

125

ERROR 42000: There is no such grant defined for user 'grant_plug' on host '%'

126

in grant_plug_dest_con

127

## testing what an ordinary user can grant

128

this should fail : no rights to grant all

129

GRANT PROXY ON ''@'' TO grant_plug;

130

ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost'

131

this should fail : not the same user

132

GRANT PROXY ON grant_plug TO grant_plug_dest;

133

ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost'

134

This is a valid grant

135

GRANT PROXY ON grant_plug_dest TO grant_plug;

136

REVOKE PROXY ON grant_plug_dest FROM grant_plug;

137

this should work : same user

138

GRANT PROXY ON grant_plug_dest TO grant_plug_dest2;

139

REVOKE PROXY ON grant_plug_dest FROM grant_plug_dest2;

140

this should fail : not the same user

141

GRANT PROXY ON grant_plug_dest@localhost TO grant_plug WITH GRANT OPTION;

142

ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost'

143

this should fail : not the same user

144

REVOKE PROXY ON grant_plug_dest@localhost FROM grant_plug;

145

ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost'

146

this should fail : can't create users

147

GRANT PROXY ON grant_plug_dest TO grant_plug@localhost;

148

ERROR 42000: You are not allowed to create a user with GRANT

149

in default connection

150

# test what root can grant

151

should work : root has PROXY to all users

152

GRANT PROXY ON ''@'' TO grant_plug;

153

REVOKE PROXY ON ''@'' FROM grant_plug;

154

should work : root has PROXY to all users

155

GRANT PROXY ON ''@'' TO proxy_admin IDENTIFIED BY 'test'

156

WITH GRANT OPTION;

157

need USAGE : PROXY doesn't contain it.

158

GRANT USAGE on *.* TO proxy_admin;

159

in proxy_admin_con;

160

should work : proxy_admin has proxy to ''@''

161

GRANT PROXY ON future_user TO grant_plug;

162

in default connection

163

SHOW GRANTS FOR grant_plug;

164

Grants for grant_plug@%

165

GRANT ALL PRIVILEGES ON *.* TO 'grant_plug'@'%'

166

GRANT PROXY ON 'future_user'@'%' TO 'grant_plug'@'%'

167

REVOKE PROXY ON future_user FROM grant_plug;

168

SHOW GRANTS FOR grant_plug;

169

Grants for grant_plug@%

170

GRANT ALL PRIVILEGES ON *.* TO 'grant_plug'@'%'

171

## testing drop user

172

CREATE USER test_drop@localhost;

173

GRANT PROXY ON future_user TO test_drop@localhost;

174

SHOW GRANTS FOR test_drop@localhost;

175

Grants for test_drop@localhost

176

GRANT USAGE ON *.* TO 'test_drop'@'localhost'

177

GRANT PROXY ON 'future_user'@'%' TO 'test_drop'@'localhost'

178

DROP USER test_drop@localhost;

179

SELECT * FROM mysql.proxies_priv WHERE Host = 'test_drop' AND User = 'localhost';

180

Host User Proxied_host Proxied_user With_grant Grantor Timestamp

181

DROP USER proxy_admin;

182

DROP USER grant_plug,grant_plug_dest,grant_plug_dest2;

183

## END GRANT PROXY tests

184

## cleanup

185

DROP USER plug;

186

DROP USER plug_dest;

187

## @@proxy_user tests

188

CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';

189

CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';

190

GRANT PROXY ON plug_dest TO plug;

191

SELECT USER(),CURRENT_USER(),@@LOCAL.proxy_user;

192

USER() CURRENT_USER() @@LOCAL.proxy_user

193

root@localhost root@localhost NULL

194

SELECT @@GLOBAL.proxy_user;

195

ERROR HY000: Variable 'proxy_user' is a SESSION variable

196

SELECT @@LOCAL.proxy_user;

197

@@LOCAL.proxy_user

198

NULL

199

SET GLOBAL proxy_user = 'test';

200

ERROR HY000: Variable 'proxy_user' is a read only variable

201

SET LOCAL proxy_user = 'test';

202

ERROR HY000: Variable 'proxy_user' is a read only variable

203

SELECT @@LOCAL.proxy_user;

204

@@LOCAL.proxy_user

205

NULL

206

# in connection plug_con

207

SELECT @@LOCAL.proxy_user;

208

@@LOCAL.proxy_user

209

'plug'@'%'

210

# in connection default

211

## cleanup

212

DROP USER plug;

213

DROP USER plug_dest;

214

## END @@proxy_user tests

215

## @@external_user tests

216

CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';

217

CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';

218

GRANT PROXY ON plug_dest TO plug;

219

SELECT USER(),CURRENT_USER(),@@LOCAL.external_user;

220

USER() CURRENT_USER() @@LOCAL.external_user

221

root@localhost root@localhost NULL

222

SELECT @@GLOBAL.external_user;

223

ERROR HY000: Variable 'external_user' is a SESSION variable

224

SELECT @@LOCAL.external_user;

225

@@LOCAL.external_user

226

NULL

227

SET GLOBAL external_user = 'test';

228

ERROR HY000: Variable 'external_user' is a read only variable

229

SET LOCAL external_user = 'test';

230

ERROR HY000: Variable 'external_user' is a read only variable

231

SELECT @@LOCAL.external_user;

232

@@LOCAL.external_user

233

NULL

234

# in connection plug_con

235

SELECT @@LOCAL.external_user;

236

@@LOCAL.external_user

237

'plug'@'%'

238

# in connection default

239

WL#5706 -- show the above got logged/rewritten correctly

240

SELECT argument FROM mysql.general_log WHERE argument LIKE CONCAT('%IDENTIFIED ','WITH %');

241

argument

242

CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'

243

GRANT INSERT ON TEST.* TO grant_user IDENTIFIED WITH 'test_plugin_server'

244

CREATE USER `�` IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'

245

GRANT ALL PRIVILEGES ON test_grant_db.* TO new_grant_user

246

IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'

247

GRANT ALL PRIVILEGES ON test_grant_db.* TO plug

248

IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'

249

GRANT ALL PRIVILEGES ON test_grant_db.* TO plug_dest

250

IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'

251

CREATE USER grant_plug IDENTIFIED WITH 'test_plugin_server'

252

AS 'grant_plug_dest'

253

CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'

254

CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'

255

## cleanup

256

DROP USER plug;

257

DROP USER plug_dest;

258

SET GLOBAL log_output= @old_log_output;

259

SET GLOBAL general_log= @old_general_log;

260

## END @@external_user tests

261

262

# Bug #56798 : Wrong credentials assigned when using a proxy user.

263

264

GRANT ALL PRIVILEGES ON *.* TO power_user;

265

GRANT USAGE ON anonymous_db.* TO ''@''

266

IDENTIFIED WITH 'test_plugin_server' AS 'power_user';

267

GRANT PROXY ON power_user TO ''@'';

268

CREATE DATABASE confidential_db;

269

SELECT user(),current_user(),@@proxy_user;

270

user() current_user() @@proxy_user

271

test_login_user@localhost power_user@% ''@''

272

DROP USER power_user;

273

DROP USER ''@'';

274

DROP DATABASE confidential_db;

275

# Test case #2 (crash with double grant proxy)

276

CREATE USER ''@'' IDENTIFIED WITH 'test_plugin_server' AS 'standard_user';

277

CREATE USER standard_user;

278

CREATE DATABASE shared;

279

GRANT ALL PRIVILEGES ON shared.* TO standard_user;

280

GRANT PROXY ON standard_user TO ''@'';

281

#should not crash

282

GRANT PROXY ON standard_user TO ''@'';

283

DROP USER ''@'';

284

DROP USER standard_user;

285

DROP DATABASE shared;

286

287

# Bug #57551 : Live upgrade fails between 5.1.52 -> 5.5.7-rc

288

289

CALL mtr.add_suppression("Missing system table mysql.proxies_priv.");

290

DROP TABLE mysql.proxies_priv;

291

# Must come back with mysql.proxies_priv absent.

292

SELECT * FROM mysql.proxies_priv;

293

ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist

294

CREATE USER u1@localhost;

295

GRANT ALL PRIVILEGES ON *.* TO u1@localhost;

296

REVOKE ALL PRIVILEGES ON *.* FROM u1@localhost;

297

GRANT ALL PRIVILEGES ON *.* TO u1@localhost;

298

CREATE USER u2@localhost;

299

GRANT ALL PRIVILEGES ON *.* TO u2@localhost;

300

# access denied because of no privileges to root

301

GRANT PROXY ON u2@localhost TO u1@localhost;

302

ERROR 28000: Access denied for user 'root'@'localhost'

303

# access denied because of no privileges to root

304

REVOKE PROXY ON u2@localhost FROM u1@localhost;

305

ERROR 28000: Access denied for user 'root'@'localhost'

306

# go try graning proxy on itself, so that it will need the table

307

GRANT PROXY ON u2@localhost TO u1@localhost;

308

ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist

309

REVOKE PROXY ON u2@localhost FROM u1@localhost;

310

ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist

311

# test if REVOKE works without the proxies_priv table

312

REVOKE ALL PRIVILEGES ON *.* FROM u1@localhost, u2@localhost;

313

# test if DROP USER work without the proxies_priv table

314

DROP USER u1@localhost,u2@localhost;

315

# test if FLUSH PRIVILEGES works without the proxies_priv table

316

FLUSH PRIVILEGES;

317

Warning: Using a password on the command line interface can be insecure.

318

Warning: Using a password on the command line interface can be insecure.

319

mysql.columns_priv OK

320

mysql.db OK

321

mysql.event OK

322

mysql.func OK

323

mysql.general_log OK

324

mysql.help_category OK

325

mysql.help_keyword OK

326

mysql.help_relation OK

327

mysql.help_topic OK

328

mysql.innodb_index_stats OK

329

mysql.innodb_table_stats OK

330

mysql.ndb_binlog_index OK

331

mysql.plugin OK

332

mysql.proc OK

333

mysql.procs_priv OK

334

mysql.servers OK

335

mysql.slave_master_info OK

336

mysql.slave_relay_log_info OK

337

mysql.slave_worker_info OK

338

mysql.slow_log OK

339

mysql.tables_priv OK

340

mysql.time_zone OK

341

mysql.time_zone_leap_second OK

342

mysql.time_zone_name OK

343

mysql.time_zone_transition OK

344

mysql.time_zone_transition_type OK

345

mysql.user OK

346

Warning: Using a password on the command line interface can be insecure.

347

Warning: Using a password on the command line interface can be insecure.

348

Warning: Using a password on the command line interface can be insecure.

349

mtr.global_suppressions OK

350

mtr.test_suppressions OK

351

SELECT Host,User,Proxied_host,Proxied_user,With_grant FROM mysql.proxies_priv;

352

Host localhost

353

User root

354

Proxied_host

355

Proxied_user

356

With_grant 1

357

FLUSH PRIVILEGES;

358

359

# Bug#58139 : default-auth option not recognized in MySQL standard

360

# command line clients

361

362

# Executing 'mysql'

363

364

365

# Executing 'mysqladmin'

366

mysqld is alive

367

# Executing 'mysqldump'

368

# Executing 'mysql_upgrade'

369

The --upgrade-system-tables option was used, databases won't be touched.

370

371

# Bug #59657: Move the client authentication_pam plugin into the

372

# server repository

373

374

CREATE USER uplain@localhost IDENTIFIED WITH 'cleartext_plugin_server'

375

AS 'cleartext_test';

376

## test plugin auth

377

ERROR 28000: Access denied for user 'uplain'@'localhost' (using password: YES)

378

select USER(),CURRENT_USER();

379

USER() CURRENT_USER()

380

uplain@localhost uplain@localhost

381

DROP USER uplain@localhost;

382

383

# Bug #59038 : mysql.user.authentication_string column

384

# causes configuration wizard to fail

385

INSERT INTO mysql.user(

386

Host,

387

User,

388

Password,

389

Select_priv,

390

Insert_priv,

391

Update_priv,

392

Delete_priv,

393

Create_priv,

394

Drop_priv,

395

Reload_priv,

396

Shutdown_priv,

397

Process_priv,

398

File_priv,

399

Grant_priv,

400

References_priv,

401

Index_priv,

402

Alter_priv,

403

Show_db_priv,

404

Super_priv,

405

Create_tmp_table_priv,

406

Lock_tables_priv,

407

Execute_priv,

408

Repl_slave_priv,

409

Repl_client_priv,

410

/*!50001

411

Create_view_priv,

412

Show_view_priv,

413

Create_routine_priv,

414

Alter_routine_priv,

415

Create_user_priv,

416

417

ssl_type,

418

ssl_cipher,

419

x509_issuer,

420

x509_subject,

421

max_questions,

422

max_updates,

423

max_connections)

424

VALUES (

425

'localhost',

426

'inserttest', '',

427

'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',

428

'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',

429

/*!50001 'Y', 'Y', 'Y', 'Y', 'Y', */'', '', '', '', '0', '0', '0');

430

FLUSH PRIVILEGES;

431

DROP USER inserttest@localhost;

432

SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE

433

COLUMN_NAME IN ('authentication_string', 'plugin') AND

434

TABLE_NAME='user' AND

435

TABLE_SCHEMA='mysql'

436

ORDER BY COLUMN_NAME;

437

IS_NULLABLE COLUMN_NAME

438

YES authentication_string

439

YES plugin

440

441

# Bug #11936829: diff. between mysql.user (authentication_string)

442

# in fresh and upgraded 5.5.11

443

444

SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

445

WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND

446

COLUMN_NAME IN ('plugin', 'authentication_string')

447

ORDER BY COLUMN_NAME;

448

IS_NULLABLE COLUMN_NAME

449

YES authentication_string

450

YES plugin

451

ALTER TABLE mysql.user MODIFY plugin char(64) DEFAULT '' NOT NULL;

452

ALTER TABLE mysql.user MODIFY authentication_string TEXT NOT NULL;

453

Run mysql_upgrade on a 5.5.10 external authentication column layout

454

Warning: Using a password on the command line interface can be insecure.

455

Warning: Using a password on the command line interface can be insecure.

456

mysql.columns_priv OK

457

mysql.db OK

458

mysql.event OK

459

mysql.func OK

460

mysql.general_log OK

461

mysql.help_category OK

462

mysql.help_keyword OK

463

mysql.help_relation OK

464

mysql.help_topic OK

465

mysql.innodb_index_stats OK

466

mysql.innodb_table_stats OK

467

mysql.ndb_binlog_index OK

468

mysql.plugin OK

469

mysql.proc OK

470

mysql.procs_priv OK

471

mysql.proxies_priv OK

472

mysql.servers OK

473

mysql.slave_master_info OK

474

mysql.slave_relay_log_info OK

475

mysql.slave_worker_info OK

476

mysql.slow_log OK

477

mysql.tables_priv OK

478

mysql.time_zone OK

479

mysql.time_zone_leap_second OK

480

mysql.time_zone_name OK

481

mysql.time_zone_transition OK

482

mysql.time_zone_transition_type OK

483

mysql.user OK

484

Warning: Using a password on the command line interface can be insecure.

485

Warning: Using a password on the command line interface can be insecure.

486

Warning: Using a password on the command line interface can be insecure.

487

mtr.global_suppressions OK

488

mtr.test_suppressions OK

489

SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

490

WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND

491

COLUMN_NAME IN ('plugin', 'authentication_string')

492

ORDER BY COLUMN_NAME;

493

IS_NULLABLE COLUMN_NAME

494

YES authentication_string

495

YES plugin

496

497

# Bug #12610784: SET PASSWORD INCORRECTLY KEEP AN OLD EMPTY PASSWORD

498

499

CREATE USER bug12610784@localhost;

500

SET PASSWORD FOR bug12610784@localhost = PASSWORD('secret');

501

ERROR 28000: Access denied for user 'bug12610784'@'localhost' (using password: NO)

502

DROP USER bug12610784@localhost;

503

504

# Bug # 11766641: 59792: BIN/MYSQL -UUNKNOWN -PUNKNOWN

505

# .-> USING PASSWORD: NO

506

# shoud contain "using password=yes"

507

Warning: Using a password on the command line interface can be insecure.

508

ERROR 1045 (28000): Access denied for user 'unknown'@'localhost' (using password: YES)

509

# shoud contain "using password=no"

510

ERROR 1045 (28000): Access denied for user 'unknown'@'localhost' (using password: NO)

511

512

# Bug #12818542: PAM: ADDING PASSWORD FOR AN ACCOUNT DISABLES PAM

513

# AUTHENTICATION SETTINGS

514

515

CREATE USER bug12818542@localhost

516

IDENTIFIED WITH 'test_plugin_server' AS 'bug12818542_dest';

517

CREATE USER bug12818542_dest@localhost

518

IDENTIFIED BY 'bug12818542_dest_passwd';

519

GRANT PROXY ON bug12818542_dest@localhost TO bug12818542@localhost;

520

SELECT USER(),CURRENT_USER();

521

USER() CURRENT_USER()

522

bug12818542@localhost bug12818542_dest@localhost

523

SET PASSWORD = PASSWORD('bruhaha');

524

Warnings:

525

Note 1699 SET PASSWORD has no significance for users authenticating via plugins

526

SELECT USER(),CURRENT_USER();

527

USER() CURRENT_USER()

528

bug12818542@localhost bug12818542_dest@localhost

529

DROP USER bug12818542@localhost;

530

DROP USER bug12818542_dest@localhost;

531

532

# Bug #12818542: PAM: ADDING PASSWORD FOR AN ACCOUNT DISABLES PAM

533

# AUTHENTICATION SETTINGS

534

535

CREATE USER bug12818542@localhost

536

IDENTIFIED WITH 'test_plugin_server' AS 'bug12818542_dest';

537

CREATE USER bug12818542_dest@localhost

538

IDENTIFIED BY 'bug12818542_dest_passwd';

539

GRANT PROXY ON bug12818542_dest@localhost TO bug12818542@localhost;

540

SELECT USER(),CURRENT_USER();

541

USER() CURRENT_USER()

542

bug12818542@localhost bug12818542_dest@localhost

543

SET PASSWORD = PASSWORD('bruhaha');

544

Warnings:

545

Note 1699 SET PASSWORD has no significance for users authenticating via plugins

546

SELECT USER(),CURRENT_USER();

547

USER() CURRENT_USER()

548

bug12818542@localhost bug12818542_dest@localhost

549

DROP USER bug12818542@localhost;

550

DROP USER bug12818542_dest@localhost;

551

End of 5.5 tests

Older »