wood burning stoves 2.0*
The moose likes JDBC and the fly likes Creating user in Oracle through JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Creating user in Oracle through JDBC" Watch "Creating user in Oracle through JDBC" New topic
Author

Creating user in Oracle through JDBC

Neha k Agrawal
Greenhorn

Joined: Mar 25, 2008
Posts: 25
Hello Friends,

I m trying to create a user in oracle through jdbc. When I m creating user through SQL plus I m able to do it successfully but when I m doing it through java code there is some problem. I can see that user�s entry in dba_users & dba_role_privs tables.But when I am trying to drop that user I get message �User doesn�t exist�.

Also when I am trying to logged in through the newly created user (one that I have created through code)in SQL Plus , I get error �ORA-01017: invalid username/password ; logon denied�.If user doesn�t exist then how can I see it�s entry in dba_users & dba_role_privs?? One more thing �. I can drop that user through code but not using SQL Plus.

This is the code�.In this code I have logged in using system-manager & I m trying to create user test88 with password test88.I am granting 3 roles to this user Connect , Resource & DBA same as Scott �Tiger. Please help.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class OracleDB1 {

public static final String isSUCCESS = "SUCCESS";

public static final String isORACLE = "ORACLE";

public static final String isFail = "ERROR";

public String dbDriver;

public String dbURL;

public String dbUserName;

public String dbName;

public String dbPassword;

public String dbType;

private Connection con;

private Statement stmt;


/**
*
* @param s Username
* @param s1 Indentified by
* @param s2 tablespace
* @param s3
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/

public String createUserOracle(String s, String s1, String s2, String s3)
throws SQLException, ClassNotFoundException {
System.out.println("Creating user method ");
String s4 = "";
if (!s2.equals("")) {
if (!s3.equals("")) {
s4 = new String("CREATE USER \"" + s + "\" IDENTIFIED BY \""
+ s1 + "\" DEFAULT TABLESPACE " + s2 + " QUOTA " + s3
+ " ON " + s2);

}
else {
s4 = new String("CREATE USER \"" + s + "\" IDENTIFIED BY \""
+ s1 + "\" DEFAULT TABLESPACE " + s2
+ " TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON " + s2);
}
}
else {
s4 = new String("CREATE USER \"" + s + "\" IDENTIFIED BY \"" + s1
+ "\"");
}
try {
con = this.init();
System.out.println("The new user statemeent " + s4);
stmt = con.createStatement();
System.out.println("The new user statemeent " + s4);
stmt.execute(s4);


System.out.println("the info success "+stmt.SUCCESS_NO_INFO);

stmt.close();
} finally {
if (null != con) {
con.close();
}
}
return "SUCCESS";
}

public String grantRightsOracle(String s)
throws SQLException, ClassNotFoundException
{

String s1 = new String("GRANT connect , resource , dba to \"" + s + "\"");

try
{
con=this.init();
stmt = con.createStatement();
System.out.println("granting "+s+"with the connect priveleges");
stmt.execute(s1);
stmt.close();
}
finally
{
if(null != con)
{
this.close(con);
}
}
return "SUCCESS";
}

public Connection init() throws SQLException, ClassNotFoundException {
System.out.println("initialising");
Class.forName("oracle.jdbc.driver.OracleDriver");
dbURL = "jdbcracle:thin:@localhost:1521:priya";
con = DriverManager.getConnection(dbURL, "system", "manager");
return con;
}




public void close(Connection connection) throws SQLException {
if (!connection.isClosed()) {
connection.close();
}
}






public String disableUserOracle(String s) throws SQLException,
ClassNotFoundException {
String s1 = "REVOKE CONNECT, RESOURCE, SELECT ANY TABLE FROM \"" + s
+ "\"";
try {
con = this.init();
stmt = con.createStatement();
stmt.execute(s1);
stmt.close();
} finally {
if (null != con) {
this.close(con);
}
}
return "SUCCESS";
}


public String deleteUserOracle(String s) throws SQLException,
ClassNotFoundException {
String s1 = new String("DROP USER \"" + s + "\" CASCADE");
try {
con = this.init();
stmt = con.createStatement();
stmt.execute(s1);
stmt.close();
} finally {
if (null != con) {
this.close(con);
}
}
return "SUCCESS";
}








public static void main(String args[]) {
String s = "";

OracleDB1 oracleoperations = new OracleDB1();
try {


System.out.println("creating user");
s = oracleoperations.createUserOracle("test88", "test88", "", "");
System.out.println("creating user is " + s);
s=oracleoperations.grantRightsOracle("test88");
System.out.println("granting rights" + s);

//s=oracleoperations.deleteUserOracle("test88");
//System.out.println("deleting user" + s );

} catch (Exception exception) {
exception.printStackTrace();
}
}
}
bonka
Greenhorn

Joined: Jun 24, 2008
Posts: 1
-- privilege to create user
grant create user to xxxxx;

-- privilege to grant other user to CONNECT and RESOURCE
grant grant any role to xxxxx with admin option;
grant GRANT ANY PRIVILEGE to xxxxx with admin option;

---List all privilege
select * from user_sys_privs
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

bonka, please check your private messages.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Creating user in Oracle through JDBC