| SYS/SYSDBA is special [message #486117] |
Mon, 13 December 2010 14:26  |
 |
Littlefoot
Messages: 21808
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
