Re: password iin dba_users - Oracle 11
- From: Jared Still <jkstill@xxxxxxxxx>
- To: ian.cary@xxxxxxxxxxxxxx
- Date: Tue, 20 Jan 2009 12:47:17 -0800
On Mon, Jan 19, 2009 at 3:52 AM, Ian Cary <ian.cary@xxxxxxxxxxxxxx> wrote:
> Oracle have removed the column from the dba_users view but it it is still
> present in SYS.USER$.
>
This is really rather interesting.
I ran into something similar while updating my default password checker.
It didn't work on 11g, simply due to the fact that the password hash is no
longer
available in DBA_USERS.
It is however available in sys.user$ as Ian pointed out.
As referenced in Pete's blog, the old style password hash is still there,
presumably for non-11g clients, though I haven't made any attempt to
verify that.
What I found interesting is that a user that has been granted the DBA_ROLE
automatically has SELECT privileges on sys.user$.
I can find no privilege granted to the DBA role that explicitly grants
SELECT
on sys.user$, whether directly, or indirectly through a role.
There are some users that do have that, CTXSYS, XDB and WKSYS among them.
As a by product of looking into this, a couple of useful queries developed:
System privilege map for a user:
This shows how a user may have been granted a privilege:
with privmap as (
select grantee#,privilege#,level padsize, rownum rownumber
from sys.sysauth$
connect by grantee#=prior privilege#
and privilege#>0
start with (grantee#=:v_grantee or grantee#=1) and privilege#>0
)
select
lpad(' ',2*(padsize-1)) || u.name grantee
, p.grantee#
, s.privilege
, s.name privname
from
privmap p
, system_privilege_map s
, sys.user$ u
where 0-s.privilege = p.privilege#
and u.user# = p.grantee#
order by p.rownumber
/
Sample output:
GRANTEE GRANTEE# PRIVILEGE PRIVNAME
---------------------------------------- ---------- ----------
------------------------------
TRACEME 151 -4 AUDIT SYSTEM
DBA 4 -6 ALTER SESSION
SELECT_CATALOG_ROLE 6 -20 CREATE USER
DBA 4 -7 RESTRICTED
SESSION
EXECUTE_CATALOG_ROLE 7 -20 CREATE USER
EXP_FULL_DATABASE 9 -6 ALTER SESSION
SELECT_CATALOG_ROLE 6 -20 CREATE USER
EXP_FULL_DATABASE 9 -7 RESTRICTED
SESSION
EXECUTE_CATALOG_ROLE 7 -20 CREATE USER
DBA 4 -10 CREATE
TABLESPACE
IMP_FULL_DATABASE 10 -6 ALTER SESSION
SELECT_CATALOG_ROLE 6 -20 CREATE USER
IMP_FULL_DATABASE 10 -7 RESTRICTED
SESSION
EXECUTE_CATALOG_ROLE 7 -20 CREATE USER
DBA 4 -13 DROP
TABLESPACE
DBA 4 -22 ALTER USER
DBA 4 -61 CREATE ANY
CLUSTER
17 rows selected.
Something similar can be done with sys.objauth$, though I haven't gotten
that one to work yet.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
