Re: Default user permissions

From: Adric Norris <landstander668_at_gmail.com>
Date: Tue, 8 Nov 2011 10:03:55 -0600
Message-ID: <CAJueESrZaf39u9suHqy6CGdnwbX5DjKC_VY6M2Jc4UBs+yw+zQ_at_mail.gmail.com>

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_at_dealertrack.com>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???
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 08 2011 - 10:03:55 CST