1

I ran the following command per AWS Aurora's documentation to give my user permissions to write to S3: GRANT SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address'. Unfortunately, it revoked permissions to do anything, so I couldn't even run a basic select statement on any table.

So what would be the proper statement to append or add the additional permission to run select into s3 for a user?

simplycoding
  • 173
  • 6

2 Answers2

1

Suppose S3 is a table of database DB2, you would

grant insert on DB2.s3 to 'user'@'domain-or-ip-address'

But often you would

grant all on DB2.s3 to 'user'@'domain-or-ip-address'

There is no overwriting, grant modifies existing privileges.

Gerard H. Pille
  • 3,255
  • 1
  • 9
  • 13
  • This is not what the OP asked. "SELECT INTO S3" is a special permission for Aurora and Mysql tables hosted at Amazon's AWS, not the name of a table. – stephane Oct 04 '18 at 08:58
0

The problem is that your default aurora installation has a user 'user'@'%' with all grants and the password you set. If you manually do a GRANT ... TO 'user'@'ip', it's adding a new user, user@ip, that will be used when you connect from this ip (while the default user@% is used from all other ips). And this new user will have only SELECT INTO S3 permission. (and most probably, if you you didn't add IDENTIFIED BY xxx in your GRANT request, the new user won't even have a password set so you won't be able to connect from this ip).

To solve this, just run:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, LOAD FROM S3, SELECT INTO S3 ON *.* TO 'user'@'ip' IDENTIFIED BY PASSWORD 'xxxx' WITH GRANT OPTION;

(you can connect from another ip (so it defaults to the default profile), and run SHOW GRANTS to see what are the default grants and password for your default user)

stephane
  • 101
  • 2