Home » RDBMS Server » Server Administration » About dba_sys_privs
About dba_sys_privs [message #540256] Sat, 21 January 2012 03:17 Go to next message
justlearner
Messages: 9
Registered: January 2012
Junior Member
i grant the dba role to aa, but i can only get the privilege "UNLIMITED TABLESPACE" from dba_sys_privs view,where are the privileges of the dba role?

SQL> CREATE USER aa identified by 1;
SQL> grant dba to aa;
SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'AA';
GRANTEE PRIVILEGE ADMIN_OPTION
------- ------------------------ ------------
AA UNLIMITED TABLESPACE NO
Re: About dba_sys_privs [message #540258 is a reply to message #540256] Sat, 21 January 2012 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They stay in the DBA role.
Change AA by DBA in your query and you will see them.

Regards
Michel
Re: About dba_sys_privs [message #540259 is a reply to message #540258] Sat, 21 January 2012 03:39 Go to previous messageGo to next message
justlearner
Messages: 9
Registered: January 2012
Junior Member
Thanks a lot.but i have another question.
SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'SYS';
GRANTEE PRIVILEGE ADMIN_OPTION
-------- -------------------------------- ------------
SYS UPDATE ANY CUBE BUILD PROCESS NO
SYS CREATE MINING MODEL NO
SYS DROP ANY ASSEMBLY NO
ADMIN_OPTION is no,but why can sys grant the privilege to other users or roles?
Re: About dba_sys_privs [message #540274 is a reply to message #540259] Sat, 21 January 2012 08:18 Go to previous messageGo to next message
GirishSharma
Messages: 22
Registered: March 2010
Junior Member
Oracle and/or any other technology is "rocket science" if you don't consult docs.
ADMIN_OPTION is Indicates whether the grant was with the ADMIN option (YES) or not (NO).

Just test as sys user, grant any sys privilege to user with ADMIN_OPTION and now see dba_sys_privs. It will now show you Yes.

http://docs.oracle.com/cd/B12037_01/server.101/b10755/statviews_2357.htm

Regards
Girish Sharma
Re: About dba_sys_privs [message #540282 is a reply to message #540259] Sat, 21 January 2012 09:15 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- 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.


Regards
Michel
Previous Topic: oracle tracing at stop and start
Next Topic: How to find when the datafile was resized?
Goto Forum:
  


Current Time: Fri Mar 29 00:19:56 CDT 2024