Search code examples
c++sqloracleoracle11gsysdba

Keeping SYSDBA connection open in C++

Problem: I am trying to connect as "SYSDBA" and then create a user in my C++ application. The connection to SYSDBA seems to close before running the create command.

I am trying to run the following two commands:

  1. CONNECT sys/<syspassword>@<datasource> AS SYSDBA
  2. CREATE USER "<username>" PROFILE "DEFAULT" IDENTIFIED BY "<userpassword>" ACCOUNT UNLOCK

If I highlight and run the two commands in Oracle SQL Developer, everything works fine and the user is created. The connection is closed automatically and I receive a message like:

Connected

user "KYLE" created.

Connection created by CONNECT script command disconnected

When I run the commands from my C++ application, it seems that the connection is closed before command #2 is ran.

This is code I use to call the commands:

strcpy(szProcName,"CONNECT sys/");
strcat(szProcName,Sys_Password);
strcat(szProcName,"@");
strcat(szProcName,info.szServerName);
strcat(szProcName," AS SYSDBA");
rc=SQLPrepare(sqlc.g_hstmt,(SQLCHAR*)szProcName,(SQLINTEGER)strlen(szProcName));
rc = SQLExecute(sqlc.g_hstmt);
                
strcpy(szProcName,"CREATE USER \"");
strcat(szProcName,userName);
strcat(szProcName,"\" PROFILE \"DEFAULT\" IDENTIFIED BY \"");
strcat(szProcName,Password);
strcat(szProcName,"\" ACCOUNT UNLOCK;");
rc=SQLPrepare(sqlc.g_hstmt,(SQLCHAR*)szProcName,(SQLINTEGER)strlen(szProcName));
rc = SQLExecute(sqlc.g_hstmt);

When the commands are ran in C++, I get the SQL error:

SQL Error: ORA-01031: "insufficient privileges"

Also, if I try to run one command at a time in Oracle SQL Developer, I get the same message. This leads me to think that my connection is being closed early. Is there a way to keep my connection open long enough to run the CREATE USER command?

Solution

  • After many hours of research, I have found a way to solve my original problem:

    I am trying to connect as "SYSDBA" and then create a user in my C++ application. The connection to SYSDBA seems to close before running the create command.

    Instead of running the two commands separately, I use a pipe to execute the sqlplus commands in the command line.

    • @echo CREATE USER "<username>" PROFILE "DEFAULT" IDENTIFIED BY "<userpassword>" ACCOUNT UNLOCK; | CONNECT sys/<syspassword>@<datasource> AS SYSDBA

    The command is called with C++ code in the following way:

    strcpy(szProcName,"@echo CREATE USER \"");
strcat(szProcName,userName);
strcat(szProcName,"\" PROFILE \"DEFAULT\" IDENTIFIED BY \"");
strcat(szProcName,Password);
strcat(szProcName,"\" ACCOUNT UNLOCK; | sqlplus sys/");
strcat(szProcName,Sys_Password);
strcat(szProcName,"@");
strcat(szProcName,info.szServerName);
strcat(szProcName," AS SYSDBA");

system(szProcName);