Keeping SYSDBA connection open in C++

Question

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:

CONNECT sys/<syspassword>@<datasource> AS SYSDBA
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?

what happens if you change commands to something innocuous like Select 1 from dual; ? — kevinskio, Apr 17 '15 at 14:49
The way that I am executing SQL commands has worked perfectly dozens of times before. However, now that I have to be connected as SYSDBA, problems are arising. A select statement works fine because you don't need SYSDBA permissions. Thanks. — Kyle Williamson, Apr 17 '15 at 14:53

score 1 · Answer 1 · answered Apr 20 '15 at 19:48

1

Pure ODBC Connections do not, I believe, support the "AS SYSDBA" option for Oracle connection strings.

The solution is to have them connect to a user that has the specific privs you require (CREATE ANY USER in your example), and the "as sysdba" problem takes care of itself.

answered Apr 20 '15 at 19:48

Michael Broughton

4,045
14
12

Although this is not the answer I was looking for (look at my solution), I don't want to waste the reputation. You did give one possible solution, thanks. – Kyle Williamson Apr 24 '15 at 15:29

score 1 · Answer 2 · answered Apr 21 '15 at 01:20

you can user a pseudo user which has the dba privileges granted to it and connect to that user from your script. Since the new user will also have the admin privilege you will be able to create other users or any other task that you wish to accomplish.

create user test identified by password ;
grant dba to test with admin option;

Now connect to "test" using your script and perform your operations.

score 1 · Accepted Answer · answered Apr 21 '15 at 13:33

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);

Keeping SYSDBA connection open in C++

3 Answers3