Oracle Database SYS or SYSDBA - Oracle Database SYS or SYSDBA What / Why Special - Oracle DBA Tips!
Never ever use SYS (or SYSDBA) but still you can use for maintenance purpose (startup, shutdown, backup, restore/recovery)
SYS/SYSDBA is special
SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "I did that with SYS/SYSDBA" and you'll see the immediate answer from Oracle Support)
SYS/SYSDBA does not act like any other user
When you use SYS/SYSDBA Oracle deactivates some code path and activates others
Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.
NEVER EVER use SYS/SYSDBA for anything that can be done by another user. Use SYS/SYSDBA ONLY for something that can't be done by someone else.
It means exactly what it is said. SYS is NOT an Oracle user/schema, it is NOT for use.
SYS does not follow consistent read.
SYS objects can't go in recycle bin when you drop them.
SYS is out of the scope of relational database.
SYS/SYSDBA is the way to manage the database itself not its content (this is why SYS can't connect without SYSOPER/SYSDBA option).
So it is not only because you MUST create your own accounts, it is also and above all because it does not act as a normal account.
On the other point: "DBA" privilege. Of course DBA role should not be granted (as well as CONNECT or RESOURCE ones). Create your own roles depending on your organization.
When you worked with customer and still you can create 3 "DBA" roles named DBA_LEVEL1, DBA_LEVEL2 and DBA_LEVEL3 (plus another one named DBA_PERF dedicated to performances goal) with increasing privileges. A DBA connects with DBA_LEVEL1, when he needs more privileges (and is allowed to) he "upgrades" to next level using a procedure protecting the roles (cannot simply use SET ROLE). He has to give a parameter that mentions why he needs to upgrade and obviously this is recorded. When he no more needs the previous added privileges he MUST downgrades to a lower level.
The first level allows the DBA to do 95% of his common tasks. NO level has SELECT ANY, DML ANY, EXECUTE ANY privileges.
The goal is the least privileges almost always, powerful privileges the less possible time.
And also the details of privileges will depend on your organization standards, but I can give you the privileges granted to DBA_LEVEL1 as an example:
PLUSTRACE
SELECT_CATALOG_ROLE
ADMINISTER SQL MANAGEMENT OBJECT
ADMINISTER SQL TUNING SET
ADVISOR
ALTER DATABASE
ALTER ROLLBACK SEGMENT
ALTER SESSION
ALTER SYSTEM
ALTER TABLESPACE
ALTER USER
CREATE SESSION
MANAGE TABLESPACE
RESTRICTED SESSION
RESUMABLE
SELECT ANY DICTIONARY
EXECUTE on SYS.DBMS_MONITOR
EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY
DBA_PERF is special, it is given to DBA that are dedicated to optimize application performances and audit database performances, for this task it contains SELECT ANY and EXECUTE ANY but the DBA that have this role does not have the other DBA_LEVEL one (used to manage the database structures, objects and users) and of course, DDL are logged.
Post a Comment: