Using SYSBACKUP in 12c with a media manager layer
Posted by John Hallas on September 10, 2015
I think most large sites who have multiple support teams are aware of how the phrase “Segregation of Duties” is impacting the DBA world. The basic principle, that one user should not be able to, for instance, add a user, grant it privileges, let the user run scripts and then drop the user and remove all log files is a sound one and cannot be argued with.
With the release of 12c Oracle e added three new users to perform administrative tasks. Each user as a corresponding privilege with the same name as the user, which is a bit confusing.
SYSBACKUP – for RMAN backup and recovery work
SYSDG – to manage DataGuard operations
SYSKM – to manage activities involving ‘key management’ including wallets and Database Vault
I have no real experience of key management so cannot comment on that. I do fail to see which type of user would be allowed to manage a DG setup and yet not be allowed to perform other DBA work on the databases, however it probably does mean that any requirement to login as ‘sysdba’ is now reduced which can only be a good thing.
The SYSBACKUP user is a really good idea and has been a long-time coming
The privileges it has, along with select on many sys views are
STARTUP
SHUTDOWN
ALTER DATABASE
ALTER SYSTEM
ALTER SESSION
ALTER TABLESPACE
CREATE CONTROLFILE
CREATE ANY DIRECTORY
CREATE ANY TABLE
CREATE ANY CLUSTER
CREATE PFILE
CREATE RESTORE POINT (including GUARANTEED restore points)
CREATE SESSION
CREATE SPFILE
DROP DATABASE
DROP TABLESPACE
DROP RESTORE POINT (including GUARANTEED restore points)
FLASHBACK DATABASE
RESUMABLE
UNLIMITED TABLESPACE
SELECT ANY DICTIONARY
SELECT ANY TRANSACTION
One aspect I was keen to look at was if we could amend the connect string we use in our Media Manager Layer – Commvault from Simpana from having to connect using a ‘user/password as sysdba’ string
Unfortunately at the moment there is no way of changing the connect string to use the user SYSBACKUP. Simpana will be releasing Version 11 sometime later this year which will have be able to interact with the SYSBACKUP user, however I am unclear as to whether the requirement to connect as SYSDBA will be removed or not.
I am not aware of how other MMLs such as Networker, Netbackup or Data Protector have been updated to include the 12c changes and I am keen to find out.
Neil Chandler said
The problem with the sysbackup privileges is the same as the with the imp_full_database role. It had almost (but not quite) the same abilities as a full DBA role. The extra level of “security” that this provides is negligable at best, as they have alter system, alter database, drop tablespace, etc… There are very few sites large enough to warrant split in DBA duties that these roles provides. Again, Oracle providing features for its top 100 customer base with little use for the rest of the world. *grumpy_old_dba* 🙂
John Hallas said
Totally agree Neil.
Just found out that DGMGRL automatically uses this user and privelege
$dgmgrl sys/…
DGMGRL for Linux: Version 12.1.0.2.0 – 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected as SYSDG.