- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- 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)
- 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's been said so many times that, I believe, most people who visit this forum know that they should create their own user that will do tasks people usually do using SYS account.

But, I also think that nobody explained how exactly should that be done. So, if it is not a problem, would you, Michel (everyone else's invited too), mind to spend a few moments and explain it. You know, in details.

note that the rest of my message is NOT a walkthrough - I'm just trying to describe what I'd like to see from you who know what to do

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).
And so on.
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.
In the previous customer I worked I created 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.

I cannot go really deeper because this was done for a customer and is protected by a contract (and also the details of privileges will depend on your organization) 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).

So why do these roles exist? Backward compatibility? If that's so, but - as it turns out, these roles aren't supposed to be used as it is not a good practice - why doesn't Oracle simply "remove" them? OK, that would make life complicated for some (many?) people for some period of time (until they create their own roles), but that would, actually, force everyone to act in a responsible manner.

Basically, what you are saying is that the whole non-sys/sysdba concept requires a good plan. There's no universal solution that applies to everyone; all situations are unique, so are the plans.

That also means that what I was hoping - having an exact example, with bunch of SQL*Plus copy/pastes - is not going to happen. I'm not a DBA so I won't cry too much, but I thought that some not so experienced DBAs would benefit from it.

Thank you, anyway; I believe that what you said is a guideline one should consider when administering his own database.

Yes. Don't you see that CONNECT role is now granted only CREATE SESSION? And many new privileges are no more embedded into DBA role.

Quote:

why doesn't Oracle simply "remove" them?

(Bad) Softwares use to use them. Did you see how many of them just require DBA role for their owner schema? Did you try to ask the editor "what are the actual privileges needed for your product?"? Each time I did it I have the following answer: "don't know, just grant DBA".

The above is really just for completeness: proof that it isn't SYS who is special (he is just another schema), but SYSDBA.
As for why the roles exist, if I remember correctly, in a long de-supported release there were three roles: DBA was for the administrators, RESOURCE was for developers, and CONNECT was for users.

Right; when we used Oracle 7.1, every department had their own database so I was kind of "administering" ours (that's most probably a too strong word; these were really some basic tasks), but I remember that - when we created new users - we granted CONNECT & RESOURCE roles and that was it ... no need to grant anything else in most cases.

These were pre-V7 only privileges and grossly converted to roles in V7 and up.

SYS, even without SYSDBA, remains special concerning read consistency and other stuff.
And, as this is Security forum, NEVER EVER set O7_DICTIONARY_ACCESSIBILITY to TRUE. I wonder why it is not a hidden parameter (or even removed).