Home » RDBMS Server » Security » SYS/SYSDBA is special
| SYS/SYSDBA is special [message #486117] |
Mon, 13 December 2010 14:26  |
 |
Littlefoot
Messages: 21823
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  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #486153 is a reply to message #486139] |
Tue, 14 December 2010 00:40 |
|
Littlefoot
Messages: 21823
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 #486326 is a reply to message #486162] |
Tue, 14 December 2010 19:18 |
John Watson
Messages: 8960
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.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 11:14:23 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
