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

    probleme with database account management

    Ouazir

      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

          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

            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

              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

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