Application Programming Interfaces - Learning MySQL and MariaDB - page 405

Creating API User Accounts
Assuming that theprograms that we'll write may be executed by the public, let's create a
user account specifically for them (creating users was covered in Chapter13 ) . We'll call
this user accountpublic_apiand give itonly the SELECT privilege for the rookery and
birdwatchers databases. Execute the following on your server:
CREATE USER 'public_api' @ 'localhost'
IDENTIFIED BY 'pwd_123' ;
GRANT SELECT
ON rookery .*
TO 'public_api' @ 'localhost' ;
GRANT SELECT
ON birdwatchers .*
TO 'public_api' @ 'localhost' ;
This creates thepublic_api@localhostuser account with the passwordpwd_123. You can
give it a more secure and different password. It has access just to our two databases from
the localhost. It can only execute SELECT statements and can't change or delete data or do
anything else. We'll use this user account for the API programs that we'll create, which re-
trieve data through a public web page.
For some of the API programs we will write, we'll need another administrative user ac-
count,admin_members. It will be designated for administering information on members of
our site. Create that user account by executing the following SQL statements:
CREATE USER 'admin_members' @ 'localhost'
IDENTIFIED BY 'doc_killdeer_123' ;
GRANT SELECT , UPDATE , DELETE
ON birdwatchers .*
TO 'admin_members' @ 'localhost' ;
This administrative user account can select, update, and delete data only on the bird-
watchers database. It mostly needs access to the humans table, but may sometimes
need access to the other tables in the database. It won't use the rookery database, so
we're not giving it access to that database.