最近做一个程序需要在程序中创建 Oracle 数据库的用户。核心代码如下:
//创建用户
private void CreateUser()
{
string IPString = "192.168.0.1"; //数据库IP地址
string SIDString = "orcl"; //当前数据库服务的SID
string SystemUser = "system"; //系统管理员账户
string SystemPassword = "system"; //系统管理员密码
string UserName = "newuser"; //要创建的用户名称
string UserPassword = "newpassword"; //要创建的用户密码
string UserTableSpace = "testTableSpace"; //创建用户所在的表空间
string connString = string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={1})));user id = {2};password = {3}", IPString, SIDString, SystemUser, SystemPassword);
OracleConnection oracleConn = CreateOracleConn(connString);
//开启事务
OracleTransaction oracleTransaction = oracleConn.BeginTransaction();
try
{
string createUserStr = string.Format("CREATE USER {0} PROFILE DEFAULT IDENTIFIED BY {1} DEFAULT TABLESPACE {2} QUOTA UNLIMITED ON {3} ACCOUNT UNLOCK", UserName, UserPassword, UserTableSpace, UserTableSpace);
ExecuteSql(oracleConn, createUserStr, oracleTransaction); //创建用户
string createUserGrantStr = string.Format("GRANT CONNECT,CREATE TABLE,CREATE VIEW,DBA,CREATE SEQUENCE, CREATE PROCEDURE,CREATE TRIGGER,CREATE ANY INDEX, GLOBAL QUERY REWRITE TO {0}", UserName);
ExecuteSql(oracleConn, createUserGrantStr, oracleTransaction); //分配权限
string alter1 = "ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE";
ExecuteSql(oracleConn, alter1, oracleTransaction);
string alter2 = "ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY=TRUSTED";
ExecuteSql(oracleConn, alter2, oracleTransaction);
//成功提交
oracleTransaction.Commit();
MessageBox.Show("创建成功!", "温馨提示");
}
catch (Exception ex)
{
//错误回滚
oracleTransaction.Rollback();
MessageBox.Show(ex.Message);
}
}
//创建OracleConnection,连接Oracle数据库
private OracleConnection CreateOracleConn(string oracleConnectionString)
{
OracleConnection oracleConn = new OracleConnection();
oracleConn.ConnectionString = oracleConnectionString;
oracleConn.Open();
return oracleConn;
}
//执行sql语句
public void ExecuteSql(OracleConnection oracleConn, string CommandText, OracleTransaction oracleTransaction)
{
OracleCommand oracleCommand = new OracleCommand(CommandText, oracleConn);
oracleCommand.Transaction = oracleTransaction;
oracleCommand.ExecuteNonQuery();
}
注意事项:
1、只有使用管理员账户才有权利创建新用户。
2、新创建的用户只有分配权限后才能正常使用。