https://pulumi.com logo
#getting-started
Title
# getting-started
i

incalculable-monkey-35668

06/19/2022, 3:59 AM
Hi all, I am thinking of installing pulumi-mysql. https://github.com/pulumi/pulumi-mysql I want to manage MySQL user permissions, but I don’t seem to be able to execute Grant statements at the column level.Is there a better way to use Pulumi and execute column level Grant statements?I would like to manage the following query using Pulumi.
Copy code
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
n

nice-lizard-13594

06/20/2022, 12:43 AM
You can create a Dynamic Provider that uses the
mysql
package under the hood. An example with a MySQL connection + custom SQL query can be found in the Automation API Examples repository - here.
i

incalculable-monkey-35668

06/20/2022, 1:07 AM
Thank you!!!🙏 That’s the information I need. I will confirm it.
b

billowy-army-68599

06/20/2022, 1:49 AM
@incalculable-monkey-35668 does the user you're connecting to the mysql database have
GRANT
permission? what error are you getting?
i

incalculable-monkey-35668

06/21/2022, 10:23 AM
@billowy-army-68599 Thanks for asking. I am running with root privileges. First run this pulumi program.
Copy code
package main

import (
	"<http://github.com/pulumi/pulumi-mysql/sdk/v3/go/mysql|github.com/pulumi/pulumi-mysql/sdk/v3/go/mysql>"
	"<http://github.com/pulumi/pulumi/sdk/v3/go/pulumi|github.com/pulumi/pulumi/sdk/v3/go/pulumi>"
)

func main() {
	pulumi.Run(getInitLocalTestingDb())
}

func getInitLocalTestingDb() func(ctx *pulumi.Context) error {
	return func(ctx *pulumi.Context) error {
		_, err := mysql.NewDatabase(ctx, "pulumi_testing", nil)
		if err != nil {
			return err
		}
		_, err = mysql.NewUser(ctx, "testUser", &mysql.UserArgs{
			Host:              pulumi.String("127.0.0.1"),
			PlaintextPassword: pulumi.String("test"),
			User:              pulumi.String("test"),
		})
		if err != nil {
			return err
		}
		return nil
	}
}
Next, execute SQL.
Copy code
create table tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);
In addition, this pulumi program is executed.
Copy code
package main

import (
	"<http://github.com/pulumi/pulumi-mysql/sdk/v3/go/mysql|github.com/pulumi/pulumi-mysql/sdk/v3/go/mysql>"
	"<http://github.com/pulumi/pulumi/sdk/v3/go/pulumi|github.com/pulumi/pulumi/sdk/v3/go/pulumi>"
)

func main() {
	pulumi.Run(getInitLocalTestingDb())
}

func getInitLocalTestingDb() func(ctx *pulumi.Context) error {
	return func(ctx *pulumi.Context) error {
		database, err := mysql.NewDatabase(ctx, "pulumi_testing", nil)
		if err != nil {
			return err
		}
		testUser, err := mysql.NewUser(ctx, "testUser", &mysql.UserArgs{
			Host:              pulumi.String("127.0.0.1"),
			PlaintextPassword: pulumi.String("test"),
			User:              pulumi.String("test"),
		})
		if err != nil {
			return err
		}
		_, err = mysql.NewGrant(ctx, "testGrant", &mysql.GrantArgs{
			Database: database.Name,
			Host:     testUser.Host,
			Privileges: pulumi.StringArray{
				pulumi.String("SELECT"),
			},
			User: testUser.User,
			Table: pulumi.String("tutorials_tbl"),
		})
		if err != nil {
			return err
		}
		return nil
	}
}
It succeeded. However, when I run the pulumi program with the following changes, it fails.
Copy code
Table: pulumi.String("tutorials_tbl.tutorial_id"),
result
Copy code
Diagnostics:
  mysql:index:Grant (testGrant):
    error: Error running SQL (GRANT SELECT ON `pulumi_testing-f0a806e`.`tutorials_tbl.tutorial_id` TO 'test'@'127.0.0.1' REQUIRE NONE): Error 1146: Table 'pulumi_testing-f0a806e.tutorials_tbl.tutorial_id' doesn't exist
 
  pulumi:pulumi:Stack (app-dev2):
    error: update failed
The reason for the failure is clear: the Grant statement is incorrect. If true, I would like to execute the following Grant statement.
Copy code
GRANT SELECT (tutorial_id) ON `pulumi_testing-f0a806e`.`tutorials_tbl` TO 'test'@'127.0.0.1' REQUIRE NONE;
b

billowy-army-68599

06/21/2022, 1:24 PM
the reason seems to me because the table
pulumi_testing-f0a806e.tutorials_tbl.tutorial_id
doesn't exist? How are you running the
create table
statement?
3 Views