SQL Commands

The following table maps a set of. The Operation column contains the name of methods in class FdoUserManager.

If the method returns a boolean, the table cell contains the boolean value returned by the provider. If the method performs an oiperation and the provider supports the operation, the table cell contains the SQL statements executed by the provider.

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()	FdoIListDatastores	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

Note

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. Log in by executing C:\ >mysql -u newuser -p. Then execute mysql> show grants for current_user;. 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. Call FdoUserManager::GrantAccessToDatastore() with user name and data store arguments to give the user default privileges.
To confirm the results of this method call, call GetDBUsers().
You can get the same information from the connection properties dictionary for the DataStore property. The MySQL SQL command mysql> show databases; lists the database (data store) names.
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. If connected to MySQL and you used the two-argument version of GrantAccessToDatastore, the privileges are listed in the note for that method.
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 overloaded method takes two arguments: a user name and a data store name. It grants a set of default privileges to the user when connected to that data store. For MySQL 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. Call GetDBUsers(<datastore>) to verify the result.
This overloaded method takes three arguments: a user name, a data store name, and a privilege name. It 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>). The SQL Server provider does allow you to grant any of the supported privileges with the exception of the publicprivilege. Call GetDBUsers(<datastore>) to verify the result.
This method takes three arguments: a user name, a data store name, and a privilege name. It 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. In this case the SQL Server provider also drops the database user from the data store; the user’s login status is not changed.
This method takes two arguments: a user name and a role or privilege name. It revokes the specified role or privilege.