This discussion is archived
4 Replies Latest reply: Sep 27, 2013 12:40 AM by Ouazir Branched to a new discussion. RSS

probleme with database account management

Ouazir Newbie
Currently Being Moderated

Hi every body,

 

I have an  oracle ebs database realise 10 g ,I want to manage developers account  following as well as a defined strategy.

those is the steps that suggest :

- create a developer account  for each module and application in the Ebs application such as system account (INV,PO,GL,PER....).

- assigne the required privileges to each account to allow for each developers creates its own object for developement purpose (table, index, procedure, package, etc. ....)

 

is it possible to create those accounts according to predefined  account (INV,PO,PER,AR,OE,..............) the same privileges, grant's  and same roles.

 

Thank you for your contribution.

  • 1. Re: probleme with database account management
    nagulan.selvakumar Journeyer
    Currently Being Moderated

    Hi,

     

    To make clear, do you want to create users for example

    User A --> identical to system Account INV

    User B --> identical to system Account PO

    etc., etc.,?

  • 2. Re: probleme with database account management
    Ouazir Newbie
    Currently Being Moderated

    Hi nagulan.selvakumar

    Yes this is what i am talking about. those account will provide a limited access to different database objects and allows DBA to keep track of the tasks executed on the database side.

    thnx 

  • 3. Re: probleme with database account management
    nagulan.selvakumar Journeyer
    Currently Being Moderated

    Use the script below to clone the users.

     

    set lines 199 pages 999
    set verify off
    set feedback off
    set heading off
    undefine user
    accept userid prompt 'Enter user to clone: '
    accept newuser prompt 'Enter new username: '
    accept passwd prompt 'Enter new password: '
    select username
    , created
    from dba_users
    where lower(username) = lower('&newuser')
    /
    accept poo prompt 'Continue? (ctrl-c to exit)'
    spool uc.sql
    select 'create user ' || '&newuser' ||
    ' identified by ' || '&passwd' ||
    ' default tablespace ' || default_tablespace ||
    ' temporary tablespace ' || temporary_tablespace || ';' "user"
    from dba_users
    where username = '&userid'
    /
    select 'alter user &newuser quota '||
    decode(max_bytes, -1, 'unlimited'
    , ceil(max_bytes / 1024 / 1024) || 'M') ||
    ' on ' || tablespace_name || ';'
    from dba_ts_quotas
    where username = '&&userid'
    /
    select 'grant ' ||granted_role || ' to &newuser' ||
    decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
    from dba_role_privs
    where grantee = '&&userid'
    /
    select 'grant ' || privilege || ' to &newuser' ||
    decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
    from dba_sys_privs
    where grantee = '&&userid'
    /
    select 'grant '||PRIVILEGE||' on ' ||owner||'.'||table_name||' to &newuser;' from dba_tab_privs
    where grantee = '&&userid'
    /
    spool off
    undefine user
    set verify on
    set feedback on
    set heading on

     

    Thank you!!

  • 4. Re: probleme with database account management
    Ouazir Newbie
    Currently Being Moderated

    Thank you nagulan.selvakumar. it's a usefull script. 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points