User privs to backup db
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: User privs to backup db

  1. #1
    Join Date
    Nov 2000
    Posts
    205
    Hello there,

    I have our OS Systems Admin guys who are putting in automation for the online backup of our production db. They need a db username and password to do this. Since it is production I am reluctant to part with the system username and password.

    If I create a user for them, what privileges would I need to provide this user to ensure that they can perform the instructions (i.e put tablespaces in backup mode)?

    Thanks in advance,
    Nirasha

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Always have yourself an account on the production databases , with the DBA role assigned. It was not advisible by the oracle to use sys/system for every dba operations. The person(s) with the DBA role would be able to shutdown/startup/backup a database by taking the tablespace off line, recover the database and etc.

    Good luck
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    All privileges that user (whom ever taking backup or running scripts) need are
    SELECT ON SYS. DBA_TABLESPACES
    to select the list of the tablespaces to be backed up.

    I will advise you to setup scripts for 'em rather give DBA privs.




  4. #4
    Join Date
    Jan 2001
    Posts
    318
    basic question:
    I have a user a with dba privilege and user b with resource and connect privilege.
    What extra privileges does user a(dba) has over user b(resource and connect) in other words what things user a can do and user b cannot ?


    thanks
    Sonali
    Sonali

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You can query dba_sys_privs and dba_tab_privs to see the difference in the roles.
    select grantee, privilege
    from dba_sys_privs
    where grantee = 'DBA'
    /
    select grantee, privilege
    from dba_sys_privs
    where grantee = 'CONNECT'
    /
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jan 2001
    Posts
    318
    Thanks a lot
    Sonali

  7. #7
    Join Date
    Jan 2001
    Posts
    318
    I just want to clarify something:
    If I create a user with resource and connect privileges.
    As the user is a owner of all the objects I should be able to DML and execute stored procedures right ?

    I have to grant space to that user to create objects right ?

    thanks again
    Sonali

  8. #8
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    Yes. As the owner of objects, one can do any DML, DDL and write procedures. Once RESOURCE is granted, a user gets unlimited tablespace privilege. So no aditional space quotas need to be given to that user.

  9. #9
    Join Date
    Nov 2000
    Posts
    205
    Hello there,
    Back to my question... I see that I had to give alter tablespace (to start and end backup mode--- no RMAN), and alter database (to backup control file), and although it works it still does not make me feel comfortable with people having this access.

    Thanks for help so far..

    Please advise,
    Nirasha

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    [QUOTE][i]Originally posted by nirasha [/i]
    [B]although it works it still does not make me feel comfortable with people having this access.
    [/B][/QUOTE]
    So what's your alternatives?

    In a large majority of shops, the sysadmin can become any user he/she wants to anyway. Since database backups are a joint responsibility between the DBA group and the SYS Admin. group it would be best to design a solution with input from both groups. In addition, by involving both groups you will learn from each other. Once you come up with a plan, test, test, and re-test it to make sure you can recover. If you can recover from any failure scenario and your sysadmins use some funky software to back it up, who cares?

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width