Re: Need DBA advices on creating a new user, application level or DBA level?

From: Jimmy <anonymous_at_anonymous.com>
Date: 2000/08/04
Message-ID: <398BA244.EAFFBB3E_at_anonymous.com>#1/1


[Quoted] Hello all,

    Thanks for the suggestions.

    My company's policy now is, after agree with my company's DBA, he creates a procedure to create a new user.

    However, this procedure is owned by PROJECTA_OWNER. DBA granted "CREATE USER", "CREATE SESSION with ADMIN OPTION" to the PROJECTA_OWNER such that PROJECTA_OWNER can create a user and then grant create session to him. THe reason why this procedure is owned by PROJECTA_OWNER is we want to ensure all the objects created by PROJECTA_OWNER can be export once and then import to other database (since the database not only for one project, it has more than one project).

    Client application (only PROJECTA_OWNER logon) can use this procedure to create a new user.

[Quoted]     I have a question: is it possible to keep track who create a new user in Oracle (and the newly created user name and when)? i.e. any audit table in Oracle which is used to store the information about who and when to create a new user? Or need to write a PL/SQL procedures to do this?

Thanks

Ewan Parker wrote:

> On 03-Aug-00 05:37:01, Jimmy said about Need DBA advices on creating a new user,
> application level or DBA level?:
> > Hello all,
 

> > Recently, my company wants to develop an application. One of the
> > functions of the application is it can create a new user. This function
> > can only be done by a project owner, such as PROJECTA_OWNER.
> > PROJECTA_OWNER is not a DBA, he is just a project owner with some system
> > privileges (such as create user).
 

> > However, my company's DBA strongly disagree this function. He said
> > that creating a new user should be done by DBA, not on application
> > level. This is because using a client application to create a user may
> > bypass his vision, as a result, he don't know why such a user exist
> > after the application is running (since anyone who knows the
> > PROJECTA_OWNER password can create a new user). He think that it is more
> > difficult to manage the user accounts in the future.
 

> > Now, I have some questions:
 

> > 1) What do u think the above scenario? Should user creation done by
> > DBA, or done on application level? ANy other disadvantages if done on
> > application level?
>
> Ideally, the user should be created by the DBA, but this easily causes an
> unnecessary bottleneck. A stored procedure (written or checked by the DBA) can
> be created in a new, different schema that creates the user and logs exactly
> what/when/who, etc. This new schema would only have "create user", and maybe
> "alter user" privileges permanently. The DBA would be the only person able to
> change the procedure and the log tables. Only he can grant execute on the
> procedure to others, so the DBA has complete control. He may introduce auditing
> to see if the newly added users are actually in use.
>
> > 2) I know that if done on application level, PROJECTA_OWNER need to
> > alter some user parameters (e.g. default and temporary tablespace etc).
> > However, such parameters may need to be hardcoded. This is not a good
> > practice since we need to recompile the program if the tablespace name
> > is changed to another name. Is there other ways to handle such
> > situation? (I think using a PL/SQL procedure to create a new user, and
> > the application is calling this procedure. This procedure is written by
> > DBA. In this way, DBA can change this procedure without affect the
> > application. Am I right)
>
> Just create a table to hold this information. You can then use the same
> version of the program in each environment. You are on the right track here.
> As long as the DBA soley has control over the procedure and logs, he can be sure
> that everything is in order, or at least a trail can lead back to the source of
> the new user. It is a step back from full control, but it will save him time in
> the long run.
>
> HTH,
> Ewan.
>
> --
> PGP footprint: 3A 82 19 D7 7A 61 D3 DC 4D F3 87 B2 27 99 BE 77
Received on Fri Aug 04 2000 - 00:00:00 CEST

Original text of this message