Is sysdba a privilege or a role ?
Ravi, November 13, 2002 - 2:17 pm UTC
Tom,
Is SYSDBA a privilege or a role ? How do I find which users have the priivege/role to connect as SYSDBA ?
Thanks in advance.
November 13, 2002 - 2:56 pm UTC
It is like a "magic" role.
sys@ORA920.LOCALHOST> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
OPS$TKYTE TRUE FALSE
and in unix -- anyone in the DBA group has it (at the OS level, if they use OS authentication)
execution plan
A reader, November 13, 2002 - 4:10 pm UTC
Hi, Tom,
What kind roles should i be granted in order to do:
set autotrace on explain;
I have some problem when i doing that right now, I think
i lack some role.
But when I conect / as sysdba, the same problem, what is
wrong?
Thanks
November 13, 2002 - 6:08 pm UTC
as for set autotrace on
chao_ping, November 13, 2002 - 6:09 pm UTC
run $ORACLE_HOME/sqlplus/admin/plustrce.sql as system.
run $ORACLE_HOME/rdbms/admin/utlxplan.sql as system.
Create public synonym plan_table for system.plan_table;
grant all on plan_table to public;
grant plustrace to public;
I think this will help.
And for sysdba user login, autotrace won't display anything.
and for sysdba
chao_ping, November 13, 2002 - 6:11 pm UTC
I think checking v$sesstat will give a better view of whether a user is login as sys or sysdba.
Oops, sorry
chao_ping, November 13, 2002 - 6:13 pm UTC
should be session_privs;
sorry.
November 13, 2002 - 7:31 pm UTC
that would be the answer to "how do i tell if I am logged in as sysdba"
if the question is "who CAN log in as sysdba...." it wouldn't work ;)
regarding magic 'sysdba '
reader, March 03, 2004 - 10:01 am UTC
Tom
I created a user abc & gave connect & resource grants.
I was astonished to see that with this user I can issue a command as
connect / as sysdba
I thought I am creating just a user...
Without admin privs.
using 9.0.2 oracle on NT
March 03, 2004 - 3:06 pm UTC
your OS account is the one with the privs -- not the database user.
You connected with "/" which says "please use the OS to authenticate me"