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)?
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.
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.
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 ?
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'
/
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 ?
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.
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.
[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?
Reply With Quote
Bookmarks