User Management
User Management
Data Store Management API
MySQL
The following table maps a set of user-related operations expressed as pseudo-API calls to the SQL commands that perform them.
| Operation | MySQL | Notes |
|---|---|---|
| AddUser(name, password) | create user name identified by ‘password’ | 1 |
| AssignRoleOrPrivilege(user, roleOrPriv) | grant roleOrPriv on *.* to user | 2 |
| DropUser(name) | drop user name | |
| GetDatastores() | show databases | 3 |
| GetDbUsers() | select user as name from mysql.user | 4 |
| GetDbUsers(inDatastore) | select distinct grantee as name from information_schema.schema_privilges where table_schema = ‘inDatastore’ | 5 |
| GetRolesOrPrivileges() | select distinct privilege_type from information_schema.user_privileges where grantee like “’root’%” order by privilege_type | 6 |
| GrantAccessToDatastore(user, datastore) | grant all on datastore .* to user | 7 |
| GrantAccessToDatastore(user, datastore, priv) | grant priv on datastore .* to user | 8 |
| RevokeAccessToDatastore( user, datastore, priv) | revoke priv on datastore .* from user | 9 |
| RevokeRoleOrPrivilege(user, roleOrPriv) | revoke roleOrPriv on *.* from user | 10 |
| ShowAccessToDatastore(user, datastore) | select privilege_type from information_schema.schema_privileges where table_schema = ‘datastore’ and grantee = “‘user@’%’” order by privilege_type | |
| ShowPrivilges(user) | select * from mysql.user where user = ‘ user ’ | 11 |
Note
- You must have the global CREATE USER privilege or the INSERT privilege for the mysql database. A new MySQL user is created with no privileges. Assume that you have created a new user called newuser on the MySQL instance on your machine. Execute mysql> show grants for newuser;. The result string is "GRANT USAGE ON *.* TO 'newuser'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'". This means that newuser has no privileges and can only log in locally. To give the user default privileges in a datastore execute the SQL command that maps to the pseudo-API callGrantAccessToDatastore(user,datastore).
- To confirm the results of this command, execute the SQL command that maps to the pseudo-API callShowPrivileges(user).
- You can get the same information from the FdoIListDatastores command.
- If you are connected to MySQL and you created the user with AddUser, USAGE is shown as the privilege.
- All users in a particular data store are listed along with the privileges that they have in the data store.
- The list of supported privileges for MySQL consists of ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, and UPDATE.
- This command grants a set of default privileges to the user when connected to that data store. The privileges are alter, alter routine, create, create routine, create temporary tables, crew view, delete, drop, execute, index, insert, lock tables, references, select, show view, and update. To verify the result execute the SQL command that maps to the pseudo-API call ShowAccessToDatastore(user, datastore).
- This command grants the specified privilege to that user for the named data store. The MySQL provider does not allow you to grant any privilege other than those assigned by a call to GrantAccessToDatastore(user, datastore). To verify the result execute the SQL command that maps to the pseudo-API call ShowAccessToDatastore(user, datastore).
- This command revokes the specified privilege from that user for the named data store. If the role is set to ‘*’, the provider revokes all privileges in the data store from the user. To verify the result execute the SQL command that maps to the pseudo-API call ShowAccessToDatastore(user, datastore).
- This command revokes the specified role or privilege. To verify the result execute the SQL command that maps to the pseudo-API call ShowPrivileges(user).
- This SQL command returns a list of boolean values indicating whether the user has the privilege.