User Management SQL Commands

Feature Data Objects API

previous page next page
User Management API
User Management API
Data Store Management API
 
User Management SQL Commands
 
 
 

The following table maps methods to providers. 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 Oracle SQL Server Notes
AddUser(name, password) create user ‘ name ‘ identified by password create user name identified by password exec sp_addlogin @loginame = ‘name’, @passwd = ‘pwd’; exec sp_addsrvrolemember @loginname = ‘name’ @rolename = ‘dbcreator’ 1,2,3
ApplyWindowsUser(domain, name) Not supported Not supported EXEC sp_grantlogin @loginame = 'name'" domain / name ’  
AssignRoleOrPrivilege(name, roleOrPriv) grant roleOrPriv on *.* to name grant roleOrPriv to name Not supported 4,5
DropUser(name) drop user name drop user name exec sp_revokedbaccess @name_in_db = ‘name’; EXEC sp_droplogin @loginame = 'name’ 6
GetDatastores() FdoIListDatastores FdoIListDatastores FdoIListDatastores 7
GetDbUsers() select user as name from mysql.user select grantee as name from sys.dba_role_privs order by grantee use master and then for each user on server, drop table Temp_RoleMembers52; create table Temp_RoleMembers52 (DbRole varchar(50), MemberName varchar(128), MemberSID varchar(50)); insert into Temp_RoleMembers52 execute sp_helprolemember; drop table Temp_RoleMembers52 8
GetDbUsers(inDatastore) select distinct grantee as name from information_schema.schema_privilges where table_schema = inDatastore Not supported use inDatastore ; select name from sysusers where hasdbaccess=1 order by name asc; 9
GetRolesOrPrivileges() select distinct privilege_type from information_schema.user_privileges where grantee like “’root’%” order by privilege_type select role as role from sys.dba_roles order by role select name as role from sysusers where issqlrole=1 order by name 10,11, 12
GrantAccessToDatastore(user, datastore) grant all on datastore .* to user Not supported USE datastore; exec sp_grantdbaccess @loginame = ‘ user’; exec sp_addrolemember @rolename = ‘db_datareader’ @membername = ‘ user’ 13
GrantAccessToDatastore(user, datastore)   . exec sp_addrolemember @rolename = ‘db_datawriter’ @membername = ‘ user’; exec sp_addrolemember @rolename = ‘db_ddladmin’ @membername = ‘user’ 13
GrantAccessToDatastore(user, datastore, priv) grant priv on datastore .* to user Not supported USE datastore; exec sp_grantdbaccess @loginame = ‘user’; exec sp_addrolemember, @rolename = ‘priv’, @membername = ‘user’; use master 14
RevokeAccessToDatastore( user, datastore, priv) revoke priv on datastore .* from user Not supported use datastore; exec sp_droprolemember @rolename = ‘priv’, @membername = ‘user’; use master 15
RevokeRoleOrPrivilege(user, roleOrPriv) revoke roleOrPrivilege on *.* from user. revoke roleOrPriv from user Not supported 16
SupportsDatastoreGrants() True False True 17
SupportsWindowsAuthentication() False False True 18
Note
  1. 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.
  2. The new Oracle user is assigned the F_USER_ROLE role. The SQL statement used to obtain this information is select granted_role from sys.dba_role_privs where grantee = 'username';. A new Oracle user is assigned the 'unlimited tablespace,' 'execute any indextype,' and 'execute any procedure' privileges. The SQL statement used to obtain this information is select privilege from sys.dba_sys_privs where grantee = 'username'; A new Oracle user is assigned a long list of privileges as a result of being assigned the role of F_USER_ROLE. You can determine the contents of this list using the SQL statement select privilege from sys.role_sys_privs where role = 'F_USER_ROLE'; The list is drop user, alter user, analyze any, create user, alter session, drop any view, create session, drop any index, drop any table, grant any role, lock any table, alter any index, alter any table, create any view, create any index, create any table, create procedure, delete any table, drop any synonym, insert any table, select any table, update any table, drop any sequence, alter any sequence, create any synonym, create any sequence, grant any privilege, select any sequence, global query rewrite, and grant any object privilege. The F_USER_ROLE also has default table privileges. You can determine what these are by the SQL statement select table_name,privilege from sys.role_tab_privs where role = 'rolename';
  3. A new SQL Server user created using the stored procedure sp_login is given one system privilege db_creator. The user can login using SQL Server authentication. Call GrantAccessToDatastore(username, datastore) to grant the user default privileges for the data store.
  4. To confirm the results of this method call, call GetDBUsers().
  5. User privileges extend across all data stores defined in the Oracle instance.
  6. To drop a windows user (SQL Server only), format the argument as L”domain name\user name”
  7. 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. In Oracle, user names and data store names are classed as Oracle users. The Oracle SQL command SQL> select username from dba_users; shows data store and user names
  8. All users are listed. SQL Server reports the user privileges as well as the user name. If you are connected to MySQL and you created the user with AddUser, USAGE is shown as the privilege. If you are connected to SQL Server and you created the user with AddUser, db_creator is shown as the privilege.
  9. 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.
  10. 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.
  11. The list of supported roles for Oracle consists of AQ_ADMINISTRATIVE_ROLE, AQ_USER_ROLE, AUTHENTICATED_USER, CONNECT, CTXAPP, DBA, DELETE_CATALOG_ROLE, DMUSER_ROLE, DM_CATALOG_ROLE, EJBCLIENT, EXECUTE_CATALOG_ROLE, EXP_FULL_DATABASE, , F_SCHEMA_ROLE F_USER_ROLE , GATHER_SYSTEM_STATISTICS , GLOBAL_AQ_USER_ROLE, HS_ADMIN_ROLE, IMP_FULL_DATABASEJAVADEBUGPRIV, JAVAIDPRIV, JAVASYSPRIV, JAVAUSERPRIV, JAVA_ADMIN, JAVA_DEPLOY, LOGSTDBY_ADMINISTRATOR, MGMT_USER, OEM_MONITOR, RECOVERY_CATALOG_OWNER, RESOURCE, SCHEDULER_ADMIN, SELECT_CATALOG_ROLE, WKUSER, WM_ADMIN_ROLE, and XDBADMIN.
  12. The list of supported privileges for SQL Server consists of db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, db_securityadmin, and public.
  13. 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, create view, delete, drop, execute, index, insert, lock tables, references, select, show view, and update. For SQL Server the privileges are db_datareader, db_datawrtier, and db_ddladmin. Call GetDBUsers(<datastore>) to verify the result.
  14. 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.
  15. 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.
  16. This method takes two arguments: a user name and a role or privilege name. It revokes the specified role or privilege.
  17. If True, you can call the two GrantAccessToDatastore methods and the RevokeAccessToDatastore method.
  18. If True, you can call the ApplyWindowUser method.
Please send us your comment about this page
previous page start next page