删除数据库用户
drop user 删除的用户名 cascade
查询数据库用户select * from dba_users order by created desc;
package com.ceshi.duijie.controller;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class User {
public static void main(String[] args) {
String driverClassName="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@ip:1521:实例名";//
String userName="用户";//dpmiddle
String password="密码";
String tableSpace="表空间";//DPMIDDLE
String user="xxx";//创建用户
String deptName="xxxx单位";//单位名称y
String pwd=User.getRandomString(6);//密码
System.out.println();
System.out.println("使用的数据库信息：");
System.out.println("url:"+url);
System.out.println("用户名/密码："+userName+"/"+password);
System.out.println("表空间:"+tableSpace);
System.out.println();
System.out.print("确认请输入(y)：");
Scanner scanner = new Scanner(System.in);
String str=scanner.next();
if(!"Y".equals(str.toUpperCase())){
System.exit(1);
}
System.out.println();
scanner = new Scanner(System.in);
System.out.print("请输入您需要创建的用户名：");
user=scanner.next();
pwd=user;
System.out.print("请输入您创建的用户所属单位：");
scanner = new Scanner(System.in);
deptName=scanner.next();
System.out.println("************创建中，请稍等....*********************");
///新增用户需要赋予的权限
String [] sqlArr={"create user "+user+" identified by "+pwd+" default tablespace "+tableSpace+" temporary tablespace temp",//创建用户
//赋权限
"grant connect to "+user,
"grant create synonym to "+user,
//赋表操作权限
"grant select,update on 表名1 to "+user,//
"grant select,insert,update on 表名12 to "+user//
};
boolean isAdd=false;//是否添加此用户
/******************第一步：创建用户及表操作权限************************************/
Connection conn=null;
PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
Statement stmt = null;
ResultSet result = null;// 创建一个结果集对象
try {
Class.forName(driverClassName);
conn = DriverManager.getConnection(url, userName, password);// 获取连接
conn.setAutoCommit(false);//禁止自动提交
stmt = conn.createStatement();
String sql="select * from dba_users where default_tablespace='"+tableSpace+"' and username='"+user.toUpperCase()+"' ";
//System.out.println(sql);
result = stmt.executeQuery(sql);// 执行查询，注意括号中不需要再加参数
isAdd=result.next();
if(isAdd){
System.out.println("用户"+user+"已存在，请勿重复添加");
System.out.println(sql);
}else{
///循环执行用户权限
for(String sqlS : sqlArr){
System.out.println(sqlS);
stmt.executeUpdate(sqlS);
}
///插入用户信息到 用户记录表
String addUserSql="insert into userinfo (ACCOUNT,PASSWORD,DEPTNAME,CREATEDATE) "
+"values ('"+user+"','"+pwd+"','"+deptName+"',sysdate)";
System.out.println(addUserSql);
stmt.executeUpdate(addUserSql);
}
conn.commit(); //当两个操作成功后手动提交
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e){
e.printStackTrace();
} finally{
try{
if (result != null)
result.close();
if (pre != null)
pre.close();
if (conn != null)
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
System.out.println("用户创建成功，创建同义词中...");
System.out.println();
/****第二步，用创建的用户登录，创建同义词*********************/
if(!isAdd){
///创建同义词
String [] sySqlArr={
"create synonym 表名1 for "+userName+".表名1",
"create synonym 表名2 for "+userName+".表名2"//解除挂起信息表
};
try {
Class.forName(driverClassName);
conn = DriverManager.getConnection(url, user, pwd);// 获取连接
conn.setAutoCommit(false);//禁止自动提交
stmt = conn.createStatement();
///循环执行用户权限
for(String sqlS : sySqlArr){
stmt.executeUpdate(sqlS);
}
System.out.println();
System.out.println("恭喜，用户添加成功！");
System.out.println("用户名:"+user);
System.out.println("密码:"+pwd);
System.out.println();
System.out.println("删除用户信息语句:");
System.out.println("drop user "+user+" cascade;");
System.out.println("delete from userinfo where account='"+user+"';");
conn.commit(); //当两个操作成功后手动提交
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e){
e.printStackTrace();
} finally{
try{
if (result != null)
result.close();
if (pre != null)
pre.close();
if (conn != null)
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
/**
* 取随机字符串
* @param length
* @return
*/
public static String getRandomString(int length) {
//随机字符串的随机字符库
String KeyString = "abcdefghijklmnopqrstuvwxyz0123456789";
StringBuffer sb = new StringBuffer();
int len = KeyString.length();
for (int i = 0; i < length; i++) {
sb.append(KeyString.charAt((int) Math.round(Math.random() * (len - 1))));
}
return sb.toString();
}
}