RE: Default user permissions
- From: "Leo Drobnis" <Leo.Drobnis@xxxxxxxxxxxxxxx>
- To: "Adric Norris" <landstander668@xxxxxxxxx>
- Date: Tue, 8 Nov 2011 11:11:16 -0500
Thanks. Ran it. It only has connect.
________________________________
From: Adric Norris [mailto:landstander668@xxxxxxxxx]
Sent: Tuesday, November 08, 2011 11:04 AM
To: Leo Drobnis
Cc: ORACLE-L
Subject: Re: Default user permissions
Are you sure that PUBLIC doesn't have the necessary privileges granted
indirectly through a role (or series of same)? I typically use the
following query to ensure that I check all of the relevant roles,
regardless of how many levels deep the system privilege grant resides.
with
user_role_hierarchy as (
select t2.name username, t1.granted_role
from (select distinct sa.userid, u.name granted_role
from (select t.*, connect_by_root grantee# userid
from sys.sysauth$ t
connect by prior privilege# = grantee#
) sa,
sys.user$ u
where u.user# = sa.privilege#
and sa.userid in (select user# from sys.user$
where type# = 1 -- normal
users
or user# = 1 -- PUBLIC
)
) t1,
sys.user$ t2
where t1.userid = t2.user#
)
select * from user_role_hierarchy order by 1, 2;
This is effectively a hierarchical query against dba_role_privs, but
runs much faster... the original source was Tom Kyte.
On Tue, Nov 8, 2011 at 09:44, Leo Drobnis <Leo.Drobnis@xxxxxxxxxxxxxxx>
wrote:
Connect role only has create session.
Public has no privileges.
However the newly created user can create and drop tables.
I am trying to find where it's coming from.
Any idea???
--
//www.freelists.org/webpage/oracle-l
Other related posts:
