OraFAQ Forum: Server Administration » About dba_sys

Home » RDBMS Server » Server Administration » About dba_sys_privs

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

About dba_sys_privs [message #540256]

Sat, 21 January 2012 03:17

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

Report message to a moderator

Re: About dba_sys_privs [message #540258 is a reply to message #540256]

Sat, 21 January 2012 03:26

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

They stay in the DBA role.
Change AA by DBA in your query and you will see them.

Regards
Michel

Report message to a moderator

Re: About dba_sys_privs [message #540259 is a reply to message #540258]

Sat, 21 January 2012 03:39

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?

Report message to a moderator

Re: About dba_sys_privs [message #540274 is a reply to message #540259]

Sat, 21 January 2012 08:18

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

Report message to a moderator

Re: About dba_sys_privs [message #540282 is a reply to message #540259]

Sat, 21 January 2012 09:15

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Oct 27 18:25:31 CDT 2025