OraFAQ Forum: Server Administration » create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10

Home » RDBMS Server » Server Administration » create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 (Oracle Apex, Ver:3.2.1.00.10, Windows)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #559923]

Sat, 07 July 2012 13:16

Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore

Junior Member

The requirement is as follows:

Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.

Could you please let me know you the steps in detail.

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #559935 is a reply to message #559923]

Sat, 07 July 2012 17:14

Littlefoot
Messages: 16958
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't think that it is an Apex problem. First you need to find out whether it is possible in the first place (i.e. connect to one database and create user in another one). I don't know whether you can do that or not; all users I have ever created or dropped belonged to "my" database, not a remote one. However, I'd put my bet on NO, you can't do that - I believe that you need to be connected to a database in which you want to create a user and - obviously - have required privileges.

Therefore, I'll move this question into the Server Administration forum. Let's hope that someone will be able to assist. Once you know the steps (if they exist), it shouldn't be a problem to implement it in Apex.

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #559936 is a reply to message #559935]

Sat, 07 July 2012 18:33

BlackSwan
Messages: 20092
Registered: January 2009

Senior Member

>Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database
within this other Oracle DB, there needs to be a procedure that is owned by a suitably privileged owner
which can issue EXECUTE IMMEDIATE CREATE USER statement.

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #559939 is a reply to message #559935]

Sun, 08 July 2012 00:24

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

However, I'd put my bet on NO, you can't do that - I believe that you need to be connected to a database in which you want to create a user and - obviously - have required privileges.

It is possible to do it but it is definitively not wise to do it. To give high privileges through a database link is never a good decision.
THe best way is indeed to directly connect to the remote database.

Regards
Michel

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560106 is a reply to message #559939]

Tue, 10 July 2012 10:24

Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore

Junior Member

Hi Michel,

As far as my knowledge goes, DB links can be used to access existing remote objects only and not for DDLs remotely.
I didn't understand your point "To give high privileges through a database link is never a good decision." Could you please elaborate.

Thanks and Regards,
Sangeetha.

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560107 is a reply to message #559935]

Tue, 10 July 2012 10:36

Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore

Junior Member

As of now I am following these steps.
1. Create procedure in the remote database with create/drop user statement.
2. Create procedure in the local DB which calls the above procedure through DB link.
3. Call the local procedure in Apex application.

But, the problem is - this process is not dynamic, i.e., if there are more databases added - then I need to manually do the above steps. I am thinking of making these steps dynamic (just with botton click in Apex application).

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560108 is a reply to message #560107]

Tue, 10 July 2012 10:46

gazzag
Messages: 40
Registered: November 2010
Location: UK

Member

It sounds like you're treating Oracle databases like SQL Server ones. How often do you add a database?

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560109 is a reply to message #560108]

Tue, 10 July 2012 10:56

Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore

Junior Member

Not very often. But, whenever added can't we make the above steps dynamic?
I see lots of dependencies (on the local admin who has created Apex application) in the above steps as we need to interact with multiple databases in this manner. So, I need to create the remote procedure in all databases manually.

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560110 is a reply to message #560109]

Tue, 10 July 2012 11:03

BlackSwan
Messages: 20092
Registered: January 2009

Senior Member

>So, I need to create the remote procedure in all databases manually.
I suspect that you'll waste more time trying to automate than doing it manually.
For discussion sake let us assume you proceed to automate.
1) how to automagically know or determine when new DB has come into being?
2) how to automagically know or determine upon which system this new DB resides?
3) how to automagically obtain correct username & password to access remote OS?
4) how to automagically know or determine new instance SID?
5) how to automagically obtain suitably privileged schema name & password to create needed procedure?

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560118 is a reply to message #560106]

Tue, 10 July 2012 12:23

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sangeethahg wrote on Tue, 10 July 2012 17:24

Hi Michel,

As far as my knowledge goes, DB links can be used to access existing remote objects only and not for DDLs remotely

Your knowledge is not up to date.

Quote:

I didn't understand your point "To give high privileges through a database link is never a good decision." Could you please elaborate.

To be able to create a user into a remote database means you have DBA privilege on remote database means the local DBA can't manage what you do through your database link and if your database is hacked the remote is then too.

Regards
Michel

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560298 is a reply to message #560110]

Wed, 11 July 2012 22:19

Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore

Junior Member

> For discussion sake let us assume you proceed to automate.
> 1) how to automagically know or determine when new DB has come into being?
-- The user will be able to enter the new DB name through the Apex application. Which can be stored in a table and can be used to create DB links through a local procedure.
> 2) how to automagically know or determine upon which system this new DB resides?
-- I think, this is not required.
> 3) how to automagically obtain correct username & password to access remote OS?
-- I don't think, we need OS information for this. Do we? If so what for?
> 4) how to automagically know or determine new instance SID?
-- In our case the DB name (entered by the user) itself is the SID.
> 5) how to automagically obtain suitably privileged schema name & password to create needed procedure?
-- Let us consider that I have sys access to the remote DB.

With the above given information, is there any way I can automate the process?

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560300 is a reply to message #560298]

Wed, 11 July 2012 22:32

BlackSwan
Messages: 20092
Registered: January 2009

Senior Member

>With the above given information, is there any way I can automate the process?
I doubt you could do it.
I know I could do it it, but I would be wise enough to not waste my time attempting to do so.

Take careful notes when you successfully manually complete the necessary tasks for existing DBs.
Then just write some wrapper code around all the SQL that you manually issued.

Report message to a moderator

Re: create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10 [message #560768 is a reply to message #560300]

Mon, 16 July 2012 22:26

Sangeethahg
Messages: 8
Registered: June 2012
Location: Bangalore

Junior Member

Thank you all for all your valuable inputs on this topic.

Report message to a moderator

Previous Topic:	“ORA-01102 Cannot mount database in Exclusive mode”
Next Topic:	enable row movement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 03:37:30 CDT 2013

Total time taken to generate the page: 0.40207 seconds