Home » RDBMS Server » Security » SYS/SYSDBA is special
SYS/SYSDBA is special [message #486117] Mon, 13 December 2010 14:26 Go to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot

- 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
  • my example was created on 10g XE

Something like this: the first step is easy - creating a user:
SQL> connect sys as sysdba
Enter password:
Connected.

SQL> create user my_sys identified by ms;

User created.

OK, now I suppose that we should grant certain privileges, such as DBA:
SQL> grant dba to my_sys;

Grant succeeded.

SQL>

Is DBA one (and only)? It contains many privileges:
SQL> select * from role_sys_privs where role = 'DBA';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            CREATE SESSION                           YES
DBA                            ALTER SESSION                            YES
DBA                            DROP TABLESPACE                          YES
DBA                            BECOME USER                              YES
DBA                            DROP ROLLBACK SEGMENT                    YES
...
DBA                            CREATE ANY SQL PROFILE                   YES
DBA                            READ ANY FILE GROUP                      YES
DBA                            CREATE EXTERNAL JOB                      YES

160 rows selected.

Or should we not grant DBA at all, but
a) first grant privileges we think we need
b) grant other privileges later, as required

If that's the case, what is the minimum set of privileges? CREATE SESSION; what next?

So, once again - could you, please, do that for us who don't know what Michel's words exactly mean?
Re: SYS/SYSDBA is special [message #486121 is a reply to message #486117] Mon, 13 December 2010 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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).

And of course, DDL are logged.

Regards
Michel

[Updated on: Mon, 13 December 2010 15:12]

Report message to a moderator

Re: SYS/SYSDBA is special [message #486138 is a reply to message #486121] Mon, 13 December 2010 21:09 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2659418700346202574
Re: SYS/SYSDBA is special [message #486139 is a reply to message #486138] Mon, 13 December 2010 21:11 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@ Michel Good Plan I need to opt it !
Re: SYS/SYSDBA is special [message #486153 is a reply to message #486139] Tue, 14 December 2010 00:40 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
[Didn't read AskTom yet]

Michel

DBA role should not be granted (as well as CONNECT or RESOURCE ones)

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.
Re: SYS/SYSDBA is special [message #486162 is a reply to message #486153] Tue, 14 December 2010 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So why do these roles exist? Backward compatibility?

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".

Regards
Michel
Re: SYS/SYSDBA is special [message #486326 is a reply to message #486162] Tue, 14 December 2010 19:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Actually, you can connect as SYS, without SYSDBA. Set the instance parameter O7_DICTIONARY_ACCESSIBILITY to TRUE, and away you go. But you can't do some SYSDBA type things:
SQL> conn sys/oracle@orcl
Connected.
SQL> sho user
USER is "SYS"
SQL> shu abort
ORA-01031: insufficient privileges
SQL>

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.
Re: SYS/SYSDBA is special [message #486341 is a reply to message #486326] Wed, 15 December 2010 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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.
Re: SYS/SYSDBA is special [message #486349 is a reply to message #486341] Wed, 15 December 2010 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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).

Regards
Michel
Re: SYS/SYSDBA is special [message #677680 is a reply to message #486349] Fri, 04 October 2019 10:59 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks to Oracle, starting with 19c, O7_DICTIONARY_ACCESSIBILITY is desupported.

Previous Topic: TDE multiple master encryption keys within single schema.
Next Topic: Oracle 19c Security O7 dictionary parameter de supported
Goto Forum:
  


Current Time: Thu Mar 28 07:26:20 CDT 2024