Re: Identifying super users

<tunity5_at_yahoo.com> wrote in message
news:32bcd267.0301131002.713015ff_at_posting.google.com...
> Is there a way to determine whether the current user is a super user
> (with admin privileges) from within a stored procedure?
>
> Thanks!

By Super User, I presume you mean an Oracle User who is able to perform any one of the 5 privileged actions (create, startup, shutdown, backup and recover)?

For anyone to perform any of those actions, they must have been granted the SYSDBA privilege, so it would be relatively easy to check v$pwdfile_users to see who is a member.

However, for that to be meaningful, you must be using a password file to authenticate privileged users. You could instead choose to use O/S authentication, in which case it's membership of the relevant O/S group that confers 'super user' status on a person (and I've no idea how to code a procedure that would query group memberships for ORA_DBA group (Windows) or dba group (Unix)), if it were actually possible in the first place.

What's more, the check of v$pwdfile_users is only relevant if you're using an exclusive password file (ie, remote_login_passwordfile in the init.ora is set to EXCLUSIVE). If it is instead set to SHARED, then SYS is, and can be, the only privileged user. You then typically let the relevant people know what SYS's password is... and there really isn't a stored procedure which can determine what you happen to have mentioned to assorted members of the DBA team over a nice cappuccino and curry!

Regards
HJR Received on Mon Jan 13 2003 - 20:38:10 CST