FAQ
I am a bit puzzled, maybe I am getting rusty.


I need to create a user with bare minimum permissions:



CREATE USER bb_stage

IDENTIFIED BY "password"

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE TEMP;

GRANT CONNECT TO bb_stage;

ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



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???

Search Discussions

  • Joel Patterson at Nov 8, 2011 at 3:52 pm
    Personally, I skip connect role and just grant create session; but that's besides the point. If you want minimum of permissions, why not say quota 0 on users.

    Are you sure you checked dba_role_privs where grantee = 'CONNECT' and dba_sys_privs? Because what you describe should not allow bb_stage to create tables without CREATE TABLE somewhere.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


    --
    http://www.freelists.org/webpage/oracle-l
  • Leo Drobnis at Nov 8, 2011 at 3:56 pm
    Yes, I did:

    select * from dba_sys_privs where grantee='BB_STAGE'

    no rows selected.

    GRANTEE GRANTED_ROLE
    ADMIN_OPTION
    ------------------------------ ------------------------------
    ------------
    DEFAULT_ROLE
    ------------
    BB_STAGE CONNECT NO

    YES


    1 row selected.

    select * from dba_sys_privs where grantee='CONNECT'


    GRANTEE PRIVILEGE
    ------------------------------ ----------------------------------------
    ADMIN_OPTION
    ------------
    CONNECT CREATE SESSION
    NO


    1 row selected.


    select * from dba_role_privs where grantee='CONNECT'

    no rows selected.

    Also, public has no privileges...

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Tuesday, November 08, 2011 10:51 AM
    To: Leo Drobnis; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Personally, I skip connect role and just grant create session; but
    that's besides the point. If you want minimum of permissions, why not
    say quota 0 on users.

    Are you sure you checked dba_role_privs where grantee = 'CONNECT' and
    dba_sys_privs? Because what you describe should not allow bb_stage to
    create tables without CREATE TABLE somewhere.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 8, 2011 at 4:04 pm
    Ok, PUBLIC is clean also of roles and system privileges.

    No changes made while bb_stage logged in right? New user, so had to log in after creation.

    Perhaps another will have something.

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:58 AM
    To: Patterson, Joel; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Yes, I did:

    select * from dba_sys_privs where grantee='BB_STAGE'

    no rows selected.

    GRANTEE GRANTED_ROLE
    ADMIN_OPTION
    ------------------------------ ------------------------------
    ------------
    DEFAULT_ROLE
    ------------
    BB_STAGE CONNECT NO

    YES


    1 row selected.

    select * from dba_sys_privs where grantee='CONNECT'


    GRANTEE PRIVILEGE
    ------------------------------ ----------------------------------------
    ADMIN_OPTION
    ------------
    CONNECT CREATE SESSION
    NO


    1 row selected.


    select * from dba_role_privs where grantee='CONNECT'

    no rows selected.

    Also, public has no privileges...

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Tuesday, November 08, 2011 10:51 AM
    To: Leo Drobnis; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Personally, I skip connect role and just grant create session; but
    that's besides the point. If you want minimum of permissions, why not
    say quota 0 on users.

    Are you sure you checked dba_role_privs where grantee = 'CONNECT' and
    dba_sys_privs? Because what you describe should not allow bb_stage to
    create tables without CREATE TABLE somewhere.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


    --
    http://www.freelists.org/webpage/oracle-l
  • Leo Drobnis at Nov 8, 2011 at 4:09 pm
    Dropped the user a few times and recreated it, so very clean.

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Tuesday, November 08, 2011 11:03 AM
    To: Leo Drobnis; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Ok, PUBLIC is clean also of roles and system privileges.

    No changes made while bb_stage logged in right? New user, so had to log
    in after creation.

    Perhaps another will have something.

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:58 AM
    To: Patterson, Joel; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Yes, I did:

    select * from dba_sys_privs where grantee='BB_STAGE'

    no rows selected.

    GRANTEE GRANTED_ROLE
    ADMIN_OPTION
    ------------------------------ ------------------------------
    ------------
    DEFAULT_ROLE
    ------------
    BB_STAGE CONNECT NO

    YES


    1 row selected.

    select * from dba_sys_privs where grantee='CONNECT'


    GRANTEE PRIVILEGE
    ------------------------------ ----------------------------------------
    ADMIN_OPTION
    ------------
    CONNECT CREATE SESSION
    NO


    1 row selected.


    select * from dba_role_privs where grantee='CONNECT'

    no rows selected.

    Also, public has no privileges...

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Tuesday, November 08, 2011 10:51 AM
    To: Leo Drobnis; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Personally, I skip connect role and just grant create session; but
    that's besides the point. If you want minimum of permissions, why not
    say quota 0 on users.

    Are you sure you checked dba_role_privs where grantee = 'CONNECT' and
    dba_sys_privs? Because what you describe should not allow bb_stage to
    create tables without CREATE TABLE somewhere.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Nov 8, 2011 at 4:10 pm
    Did you just upgrade to 11g?

    There are significant changes in the default permissions to improve
    security.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:58 AM
    To: Joel.Patterson@crowley.com; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Yes, I did:

    select * from dba_sys_privs where grantee='BB_STAGE'

    no rows selected.

    GRANTEE GRANTED_ROLE
    ADMIN_OPTION
    ------------------------------ ------------------------------
    ------------
    DEFAULT_ROLE
    ------------
    BB_STAGE CONNECT NO

    YES


    1 row selected.

    select * from dba_sys_privs where grantee='CONNECT'


    GRANTEE PRIVILEGE
    ------------------------------ ----------------------------------------
    ADMIN_OPTION
    ------------
    CONNECT CREATE SESSION
    NO


    1 row selected.


    select * from dba_role_privs where grantee='CONNECT'

    no rows selected.

    Also, public has no privileges...

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Tuesday, November 08, 2011 10:51 AM
    To: Leo Drobnis; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Personally, I skip connect role and just grant create session; but
    that's besides the point. If you want minimum of permissions, why not
    say quota 0 on users.

    Are you sure you checked dba_role_privs where grantee = 'CONNECT' and
    dba_sys_privs? Because what you describe should not allow bb_stage to
    create tables without CREATE TABLE somewhere.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


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


    --
    http://www.freelists.org/webpage/oracle-l
  • Leo Drobnis at Nov 8, 2011 at 4:12 pm
    No, original 11g install

    -----Original Message-----
    From: Mark W. Farnham
    Sent: Tuesday, November 08, 2011 11:08 AM
    To: Leo Drobnis; Joel.Patterson@crowley.com; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Did you just upgrade to 11g?

    There are significant changes in the default permissions to improve
    security.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org

    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:58 AM
    To: Joel.Patterson@crowley.com; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Yes, I did:

    select * from dba_sys_privs where grantee='BB_STAGE'

    no rows selected.

    GRANTEE GRANTED_ROLE
    ADMIN_OPTION
    ------------------------------ ------------------------------
    ------------
    DEFAULT_ROLE
    ------------
    BB_STAGE CONNECT NO

    YES


    1 row selected.

    select * from dba_sys_privs where grantee='CONNECT'


    GRANTEE PRIVILEGE
    ------------------------------ ----------------------------------------
    ADMIN_OPTION
    ------------
    CONNECT CREATE SESSION
    NO


    1 row selected.


    select * from dba_role_privs where grantee='CONNECT'

    no rows selected.

    Also, public has no privileges...

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Tuesday, November 08, 2011 10:51 AM
    To: Leo Drobnis; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Personally, I skip connect role and just grant create session; but
    that's besides the point. If you want minimum of permissions, why not
    say quota 0 on users.

    Are you sure you checked dba_role_privs where grantee = 'CONNECT' and
    dba_sys_privs? Because what you describe should not allow bb_stage to
    create tables without CREATE TABLE somewhere.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


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


    --
    http://www.freelists.org/webpage/oracle-l
  • Sheehan, Jeremy at Nov 8, 2011 at 4:13 pm
    Did you check DBA_ROLE_PRIVS for connect and BB_STAGE? Maybe something is granted there?

    Jeremy


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:58 AM
    To: Joel.Patterson@crowley.com; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Yes, I did:

    select * from dba_sys_privs where grantee='BB_STAGE'

    no rows selected.

    GRANTEE GRANTED_ROLE
    ADMIN_OPTION
    ------------------------------ ------------------------------
    ------------
    DEFAULT_ROLE
    ------------
    BB_STAGE CONNECT NO

    YES


    1 row selected.

    select * from dba_sys_privs where grantee='CONNECT'


    GRANTEE PRIVILEGE
    ------------------------------ ----------------------------------------
    ADMIN_OPTION
    ------------
    CONNECT CREATE SESSION
    NO


    1 row selected.


    select * from dba_role_privs where grantee='CONNECT'

    no rows selected.

    Also, public has no privileges...

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Tuesday, November 08, 2011 10:51 AM
    To: Leo Drobnis; oracle-l@freelists.org
    Subject: RE: Default user permissions

    Personally, I skip connect role and just grant create session; but
    that's besides the point. If you want minimum of permissions, why not
    say quota 0 on users.

    Are you sure you checked dba_role_privs where grantee = 'CONNECT' and
    dba_sys_privs? Because what you describe should not allow bb_stage to
    create tables without CREATE TABLE somewhere.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


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




    --
    http://www.freelists.org/webpage/oracle-l
  • Adric Norris at Nov 8, 2011 at 4:06 pm
    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 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
  • Leo Drobnis at Nov 8, 2011 at 4:12 pm
    Thanks. Ran it. It only has connect.


    ________________________________

    From: Adric Norris
    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 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???
  • Stephane Faroult at Nov 8, 2011 at 4:10 pm
    It comes from role CONNECT, and the reason is compatibility with Oracle
    5, when CONNECT was a privilege and not a role (roles and privileges
    were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.
    Oh, and RESOURCE is even worse ....

    HTH

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

    On 11/08/2011 04:44 PM, Leo Drobnis wrote:
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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



    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Richard at Nov 8, 2011 at 4:30 pm
    Stephen,

    Nope, that was dropped back in 9i. However the unlimited tablespace option is still attached to resource which I wish Oracle would drop.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo.Drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: Re: Default user permissions

    It comes from role CONNECT, and the reason is compatibility with Oracle 5, when CONNECT was a privilege and not a role (roles and privileges were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.
    Oh, and RESOURCE is even worse ....

    HTH

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

    On 11/08/2011 04:44 PM, Leo Drobnis wrote:
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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



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


    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 9, 2011 at 1:07 pm
    I create a developer role that combines what used to be in CONNECT and what is in RESOURCE without UNLIMITED TABLESPACE.

    I always grant CREATE SESSION and do not use CONNECT -- as is seems silly to have a role with one privilege -- at least in this case.

    So grant CREATE SESSION and the role and your pretty much have your generic start, and easier to administrate.

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Goulet, Richard
    Sent: Tuesday, November 08, 2011 11:29 AM
    To: sfaroult@roughsea.com; leo.drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: RE: Default user permissions

    Stephen,

    Nope, that was dropped back in 9i. However the unlimited tablespace option is still attached to resource which I wish Oracle would drop.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo.Drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: Re: Default user permissions

    It comes from role CONNECT, and the reason is compatibility with Oracle 5, when CONNECT was a privilege and not a role (roles and privileges were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.
    Oh, and RESOURCE is even worse ....

    HTH

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

    On 11/08/2011 04:44 PM, Leo Drobnis wrote:
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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



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


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


    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Richard at Nov 9, 2011 at 3:28 pm
    Been doing that for the last few years, ever since roles were introduced.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Wednesday, November 09, 2011 8:06 AM
    To: Goulet, Richard; sfaroult@roughsea.com; leo.drobnis@dealertrack.com
    Cc: oracle-l@freelists.org
    Subject: RE: Default user permissions

    I create a developer role that combines what used to be in CONNECT and what is in RESOURCE without UNLIMITED TABLESPACE.

    I always grant CREATE SESSION and do not use CONNECT -- as is seems silly to have a role with one privilege -- at least in this case.

    So grant CREATE SESSION and the role and your pretty much have your generic start, and easier to administrate.

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Goulet, Richard
    Sent: Tuesday, November 08, 2011 11:29 AM
    To: sfaroult@roughsea.com; leo.drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: RE: Default user permissions

    Stephen,

    Nope, that was dropped back in 9i. However the unlimited tablespace option is still attached to resource which I wish Oracle would drop.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo.Drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: Re: Default user permissions

    It comes from role CONNECT, and the reason is compatibility with Oracle 5, when CONNECT was a privilege and not a role (roles and privileges were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.
    Oh, and RESOURCE is even worse ....

    HTH

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

    On 11/08/2011 04:44 PM, Leo Drobnis wrote:
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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



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


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


    --
    http://www.freelists.org/webpage/oracle-l
  • Paul Baumgartel at Nov 9, 2011 at 4:36 pm
    Dick,

    "few years"? Roles were introduced in, what, Oracle7? ;-)

    Time flies when you're having fun!


    Paul Baumgartel
    UBS AG
    Group Finance Accounting Solutions
    400 Atlantic Street
    Stamford, CT 06904

    203.719.4368

    paul.baumgartel@ubs.com
    www.ubs.com


    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Goulet, Richard
    Sent: Wednesday, November 09, 2011 10:27 AM
    To: joel.patterson@crowley.com; sfaroult@roughsea.com;
    leo.drobnis@dealertrack.com
    Cc: oracle-l@freelists.org
    Subject: RE: Default user permissions

    Been doing that for the last few years, ever since roles were
    introduced.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Wednesday, November 09, 2011 8:06 AM
    To: Goulet, Richard; sfaroult@roughsea.com; leo.drobnis@dealertrack.com
    Cc: oracle-l@freelists.org
    Subject: RE: Default user permissions

    I create a developer role that combines what used to be in CONNECT and
    what is in RESOURCE without UNLIMITED TABLESPACE.

    I always grant CREATE SESSION and do not use CONNECT -- as is seems
    silly to have a role with one privilege -- at least in this case.

    So grant CREATE SESSION and the role and your pretty much have your
    generic start, and easier to administrate.

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Goulet, Richard
    Sent: Tuesday, November 08, 2011 11:29 AM
    To: sfaroult@roughsea.com; leo.drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: RE: Default user permissions

    Stephen,

    Nope, that was dropped back in 9i. However the unlimited
    tablespace option is still attached to resource which I wish Oracle
    would drop.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader


    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo.Drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: Re: Default user permissions

    It comes from role CONNECT, and the reason is compatibility with Oracle
    5, when CONNECT was a privilege and not a role (roles and privileges
    were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.
    Oh, and RESOURCE is even worse ....

    HTH

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

    On 11/08/2011 04:44 PM, Leo Drobnis wrote:
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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



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


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


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


    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 9, 2011 at 6:29 pm
    I'm glad to see (not surprisingly) that I am not alone.

    I believe I created this system (sounds bigger than it is) around 1995 (oracle 7), or as soon after as I realized that I needed better control over these 'default' recommendations, (especially the culprit RESOURCE). I believe just about every DBA did something similar, and for the same reasons.

    The other great third party vendor recommendations is to grant DBA, (or actually both at same time, thus giving the DBA an immediate 'ah oh, somebody doesn't know oracle moment'. Third parties want their product to seem easy to install, maintain and use, and appear to work out of the box: -- security and safety not a concern for them, selling the product is the goal. 'God' rights work pretty good for that, and last long enough to settle it.

    16 years later, nothing has changed (a generality).

    SQLServer has its analogous recommendations; parties request sa or dbo.

    Best Regards,

    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: Goulet, Richard
    Sent: Wednesday, November 09, 2011 10:27 AM
    To: Patterson, Joel; sfaroult@roughsea.com; leo.drobnis@dealertrack.com
    Cc: oracle-l@freelists.org
    Subject: RE: Default user permissions

    Been doing that for the last few years, ever since roles were introduced.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader

    -----Original Message-----
    From: Joel.Patterson@crowley.com
    Sent: Wednesday, November 09, 2011 8:06 AM
    To: Goulet, Richard; sfaroult@roughsea.com; leo.drobnis@dealertrack.com
    Cc: oracle-l@freelists.org
    Subject: RE: Default user permissions

    I create a developer role that combines what used to be in CONNECT and what is in RESOURCE without UNLIMITED TABLESPACE.

    I always grant CREATE SESSION and do not use CONNECT -- as is seems silly to have a role with one privilege -- at least in this case.

    So grant CREATE SESSION and the role and your pretty much have your generic start, and easier to administrate.

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Goulet, Richard
    Sent: Tuesday, November 08, 2011 11:29 AM
    To: sfaroult@roughsea.com; leo.drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: RE: Default user permissions

    Stephen,

    Nope, that was dropped back in 9i. However the unlimited tablespace option is still attached to resource which I wish Oracle would drop.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo.Drobnis@dealertrack.com
    Cc: ORACLE-L
    Subject: Re: Default user permissions

    It comes from role CONNECT, and the reason is compatibility with Oracle 5, when CONNECT was a privilege and not a role (roles and privileges were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.
    Oh, and RESOURCE is even worse ....

    HTH

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

    On 11/08/2011 04:44 PM, Leo Drobnis wrote:
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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



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


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


    --
    http://www.freelists.org/webpage/oracle-l
  • Norman Dunbar at Nov 10, 2011 at 8:39 am

    On 09/11/11 18:28, Joel.Patterson@crowley.com wrote:

    The other great third party vendor recommendations is to grant DBA, (or actually both at same time, thus giving the DBA an immediate 'ah oh, somebody doesn't know oracle moment'. Third parties want their product to seem easy to install, maintain and use, and appear to work out of the box: -- security and safety not a concern for them, selling the product is the goal. 'God' rights work pretty good for that, and last long enough to settle it.
    Tell me about it! I have supported applications that required DBA,
    CONNECT and RESOURCE - and yes, I have that "hmmm" moment when I read that.

    I usually send a "bug" report back to the vendor asking them to specify
    *exactly* what is needed and not what they used in their development.

    So far, I'be had one solitary vendor do the work and figure it all out.
    The rest state that if we don't assign those exact privs, they won't
    support the application. Which makes me thing, probably correctly, that
    they *really* don't have a clue about Oracle - especially when you point
    out the overlaps - they still insist on all three.

    That's when we get the security teams involved - nasty! ;-)


    Cheers,
    Norm.

    --
    Norman Dunbar
    Dunbar IT Consultants Ltd

    Registered address:
    Thorpe House
    61 Richardshaw Lane
    Pudsey
    West Yorkshire
    United Kingdom
    LS28 7EL

    Company Number: 05132767
    --
    http://www.freelists.org/webpage/oracle-l
  • Leo Drobnis at Nov 8, 2011 at 4:31 pm
    No difference.


    On the other hand, can a user drop tables in his own schema without the
    drop table privilege?



    ________________________________

    From: Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo Drobnis
    Cc: ORACLE-L
    Subject: Re: Default user permissions



    It comes from role CONNECT, and the reason is compatibility with Oracle
    5, when CONNECT was a privilege and not a role (roles and privileges
    were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.

    Oh, and RESOURCE is even worse ....

    HTH

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>



    On 11/08/2011 04:44 PM, Leo Drobnis wrote:

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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







    --
    http://www.freelists.org/webpage/oracle-l
  • Don Granaman at Nov 8, 2011 at 5:25 pm
    Yes. If he can create them, he can drop them. There is no simple declarative way to restrict a user's privilege on his own objects.


    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant . Intelligent . Security


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:33 AM
    To: Stephane Faroult
    Cc: ORACLE-L
    Subject: RE: Default user permissions

    No difference.


    On the other hand, can a user drop tables in his own schema without the
    drop table privilege?



    ________________________________

    From: Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo Drobnis
    Cc: ORACLE-L
    Subject: Re: Default user permissions



    It comes from role CONNECT, and the reason is compatibility with Oracle
    5, when CONNECT was a privilege and not a role (roles and privileges
    were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.

    Oh, and RESOURCE is even worse ....

    HTH

    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>



    On 11/08/2011 04:44 PM, Leo Drobnis wrote:

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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







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


    --
    http://www.freelists.org/webpage/oracle-l
  • Pete Finnigan at Nov 8, 2011 at 6:09 pm
    But Don, as you can see in my post the ability to drop is not connected
    to the ability to create. I created a table in my test schema as system
    but i could drop it as my user; its a subtle difference as there is no
    record that SYSTEM created it other than mining or audit if enabled but
    its still a difference.

    cheers

    Pete

    Don Granaman wrote:
    Yes. If he can create them, he can drop them. There is no simple declarative way to restrict a user's privilege on his own objects.


    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant . Intelligent . Security


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:33 AM
    To: Stephane Faroult
    Cc: ORACLE-L
    Subject: RE: Default user permissions

    No difference.


    On the other hand, can a user drop tables in his own schema without the
    drop table privilege?



    ________________________________

    From: Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo Drobnis
    Cc: ORACLE-L
    Subject: Re: Default user permissions



    It comes from role CONNECT, and the reason is compatibility with Oracle
    5, when CONNECT was a privilege and not a role (roles and privileges
    were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.

    Oh, and RESOURCE is even worse ....

    HTH
    --

    Pete Finnigan
    CEO and Founder
    PeteFinnigan.com Limited

    Specialists in database security.

    Makers of PFCLScan the database security auditing tool.
    Makers of PFCLObfuscate the tool to protect IPR in your PL/SQL

    If you need help to audit or secure an Oracle database, please ask for
    details of our training courses and consulting services

    Phone: +44 (0)1904 791188
    Fax : +44 (0)1904 791188
    Mob : +44 (0)7759 277220
    email: pete@petefinnigan.com
    site : http://www.petefinnigan.com

    Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
    Company No : 4664901
    VAT No. : 940668114

    Please note that this email communication is intended only for the
    addressee and may contain confidential or privileged information. The
    contents of this email may be circulated internally within your
    organisation only and may not be communicated to third parties without
    the prior written permission of PeteFinnigan.com Limited. This email is
    not intended nor should it be taken to create any legal relations,
    contractual or otherwise.

    --
    http://www.freelists.org/webpage/oracle-l
  • Leo Drobnis at Nov 8, 2011 at 6:12 pm
    Just looked up - there is no 'drop table' privilege at all.

    -----Original Message-----
    From: Pete Finnigan
    Sent: Tuesday, November 08, 2011 1:08 PM
    To: DonGranaman@solutionary.com
    Cc: Leo Drobnis; Stephane Faroult; ORACLE-L
    Subject: Re: Default user permissions

    But Don, as you can see in my post the ability to drop is not connected
    to the ability to create. I created a table in my test schema as system
    but i could drop it as my user; its a subtle difference as there is no
    record that SYSTEM created it other than mining or audit if enabled but
    its still a difference.

    cheers

    Pete

    Don Granaman wrote:
    Yes. If he can create them, he can drop them. There is no simple
    declarative way to restrict a user's privilege on his own objects.

    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax:
    402-361-3173 | Solutionary | Relevant . Intelligent . Security

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:33 AM
    To: Stephane Faroult
    Cc: ORACLE-L
    Subject: RE: Default user permissions

    No difference.


    On the other hand, can a user drop tables in his own schema without the
    drop table privilege?



    ________________________________

    From: Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo Drobnis
    Cc: ORACLE-L
    Subject: Re: Default user permissions



    It comes from role CONNECT, and the reason is compatibility with Oracle
    5, when CONNECT was a privilege and not a role (roles and privileges
    were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical
    possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.

    Oh, and RESOURCE is even worse ....

    HTH
    --

    Pete Finnigan
    CEO and Founder
    PeteFinnigan.com Limited

    Specialists in database security.

    Makers of PFCLScan the database security auditing tool.
    Makers of PFCLObfuscate the tool to protect IPR in your PL/SQL

    If you need help to audit or secure an Oracle database, please ask for
    details of our training courses and consulting services

    Phone: +44 (0)1904 791188
    Fax : +44 (0)1904 791188
    Mob : +44 (0)7759 277220
    email: pete@petefinnigan.com
    site : http://www.petefinnigan.com

    Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
    Company No : 4664901
    VAT No. : 940668114

    Please note that this email communication is intended only for the
    addressee and may contain confidential or privileged information. The
    contents of this email may be circulated internally within your
    organisation only and may not be communicated to third parties without
    the prior written permission of PeteFinnigan.com Limited. This email is
    not intended nor should it be taken to create any legal relations,
    contractual or otherwise.

    --
    http://www.freelists.org/webpage/oracle-l
  • Don Granaman at Nov 8, 2011 at 6:33 pm
    Correct. I was a bit sloppy and should have omitted the second sentence.


    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant . Intelligent . Security


    -----Original Message-----
    From: Pete Finnigan
    Sent: Tuesday, November 08, 2011 12:08 PM
    To: Don Granaman
    Cc: Leo.Drobnis@dealertrack.com; Stephane Faroult; ORACLE-L
    Subject: Re: Default user permissions

    But Don, as you can see in my post the ability to drop is not connected
    to the ability to create. I created a table in my test schema as system
    but i could drop it as my user; its a subtle difference as there is no
    record that SYSTEM created it other than mining or audit if enabled but
    its still a difference.

    cheers

    Pete

    Don Granaman wrote:
    Yes. If he can create them, he can drop them. There is no simple declarative way to restrict a user's privilege on his own objects.


    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant . Intelligent . Security


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 10:33 AM
    To: Stephane Faroult
    Cc: ORACLE-L
    Subject: RE: Default user permissions

    No difference.


    On the other hand, can a user drop tables in his own schema without the
    drop table privilege?



    ________________________________

    From: Stephane Faroult
    Sent: Tuesday, November 08, 2011 11:09 AM
    To: Leo Drobnis
    Cc: ORACLE-L
    Subject: Re: Default user permissions



    It comes from role CONNECT, and the reason is compatibility with Oracle
    5, when CONNECT was a privilege and not a role (roles and privileges
    were introduced with Oracle 6).
    Actually, it comes from the combination of CONNECT (which grants CREATE
    TABLE) with the unlimited quota (which gives the "physical possibility"
    of using the system privilege).
    Grant CREATE SESSION instead of CONNECT. No need for quotas.

    Oh, and RESOURCE is even worse ....

    HTH
    --

    Pete Finnigan
    CEO and Founder
    PeteFinnigan.com Limited

    Specialists in database security.

    Makers of PFCLScan the database security auditing tool.
    Makers of PFCLObfuscate the tool to protect IPR in your PL/SQL

    If you need help to audit or secure an Oracle database, please ask for
    details of our training courses and consulting services

    Phone: +44 (0)1904 791188
    Fax : +44 (0)1904 791188
    Mob : +44 (0)7759 277220
    email: pete@petefinnigan.com
    site : http://www.petefinnigan.com

    Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
    Company No : 4664901
    VAT No. : 940668114

    Please note that this email communication is intended only for the
    addressee and may contain confidential or privileged information. The
    contents of this email may be circulated internally within your
    organisation only and may not be communicated to third parties without
    the prior written permission of PeteFinnigan.com Limited. This email is
    not intended nor should it be taken to create any legal relations,
    contractual or otherwise.

    --
    http://www.freelists.org/webpage/oracle-l
  • Don Granaman at Nov 8, 2011 at 5:22 pm
    It is been the advice of Oracle Corp and the security community for many years to NOT use the connect and resource roles. In older versions of Oracle prior to 10g, the CONNECT role granted a LOT more than "create session". If you want to grant "create session", do so - and avoid using these roles altogether.

    RESOURCE is worse. Even in 10g, it grants unlimited tablespace.


    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant . Intelligent . Security


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 9:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


    --
    http://www.freelists.org/webpage/oracle-l
  • Paul Drake at Nov 8, 2011 at 5:30 pm
    Don,
    ... and what privilege was introduced with 11g in order to support access
    control lists for packages such as utl_tcp, utl_smtp?

    "its baaaack".

    connect.

    Brilliant.

    Paul

    On Tue, Nov 8, 2011 at 12:21 PM, Don Granaman
    wrote:
    It is been the advice of Oracle Corp and the security community for many
    years to NOT use the connect and resource roles. In older versions of
    Oracle prior to 10g, the CONNECT role granted a LOT more than "create
    session". If you want to grant "create session", do so - and avoid using
    these roles altogether.

    RESOURCE is worse. Even in 10g, it grants unlimited tablespace.


    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax:
    402-361-3173 | Solutionary | Relevant . Intelligent . Security


    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 9:44 AM
    To: ORACLE-L
    Subject: Default user permissions

    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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


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


    --
    http://www.freelists.org/webpage/oracle-l
  • Don Granaman at Nov 8, 2011 at 6:14 pm
    I was not (yet) aware of that one.
    Sometimes I swear that half of the people at Oracle Corp have absolutely no idea what anyone else is doing - or why. Another security-related example is that in Oracle 10.2.0.3 (at least), AUDIT_TRAIL=XML did not write LOGOFF_TIME (or the logoff action and time), LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK or SESSION_CPU when the session exited. When I noticed this, I filed a TAR/SR. The first response was "So what? This isn't security related." After I pointed out that knowing when a session was connected and when it was not connected is definitely security-related, they grudgingly created a bug report (5081050) and a patch - to capture the session logoff time only. As of 11.2.0.3 at least, you still cannot get the rest in XML, although they are all still available for AUDIT_TRAIL=OS or DB.

    PS: Resource is far worse and has caused a lot of consternation/confusion over the years. Create a user, grant resource to it, then "alter user ... quota 0 on SYSTEM", then have them create a table in the SYSTEM tablespace. (No problem. ) I wonder when someone who has no idea about this one will incorporate the resource role in some new feature...

    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant . Intelligent . Security

    From: Paul Drake
    Sent: Tuesday, November 08, 2011 11:30 AM
    To: Don Granaman
    Cc: Leo.Drobnis@dealertrack.com; ORACLE-L
    Subject: Re: Default user permissions

    Don,

    ... and what privilege was introduced with 11g in order to support access control lists for packages such as utl_tcp, utl_smtp?

    "its baaaack".

    connect.

    Brilliant.

    Paul
    On Tue, Nov 8, 2011 at 12:21 PM, Don Granaman wrote:
    It is been the advice of Oracle Corp and the security community for many years to NOT use the connect and resource roles. In older versions of Oracle prior to 10g, the CONNECT role granted a LOT more than "create session". If you want to grant "create session", do so - and avoid using these roles altogether.

    RESOURCE is worse. Even in 10g, it grants unlimited tablespace.


    Don Granaman | Phone: 402-361-3073<tel:402-361-3073> | Cell: 402-960-6955<tel:402-960-6955> | Fax: 402-361-3173<tel:402-361-3173> | Solutionary | Relevant . Intelligent . Security


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 9:44 AM
    To: ORACLE-L
    Subject: Default user permissions
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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

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



    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Richard at Nov 8, 2011 at 10:26 pm
    The resource role seems to be hardcoded with unlimited tablespace somewhere in the kernel. Try dropping resource role and recreating it without the unlimited tablespace system privilege. No dice.

    Richard Goulet
    Senior Oracle DBA/Na Team Leader


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Don Granaman
    Sent: Tuesday, November 08, 2011 1:14 PM
    To: Paul Drake
    Cc: Leo.Drobnis@dealertrack.com; ORACLE-L
    Subject: RE: Default user permissions

    I was not (yet) aware of that one.
    Sometimes I swear that half of the people at Oracle Corp have absolutely no idea what anyone else is doing - or why. Another security-related example is that in Oracle 10.2.0.3 (at least), AUDIT_TRAIL=XML did not write LOGOFF_TIME (or the logoff action and time), LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK or SESSION_CPU when the session exited. When I noticed this, I filed a TAR/SR. The first response was "So what? This isn't security related." After I pointed out that knowing when a session was connected and when it was not connected is definitely security-related, they grudgingly created a bug report (5081050) and a patch - to capture the session logoff time only. As of 11.2.0.3 at least, you still cannot get the rest in XML, although they are all still available for AUDIT_TRAIL=OS or DB.

    PS: Resource is far worse and has caused a lot of consternation/confusion over the years. Create a user, grant resource to it, then "alter user ... quota 0 on SYSTEM", then have them create a table in the SYSTEM tablespace. (No problem. ) I wonder when someone who has no idea about this one will incorporate the resource role in some new feature...

    Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant . Intelligent . Security

    From: Paul Drake
    Sent: Tuesday, November 08, 2011 11:30 AM
    To: Don Granaman
    Cc: Leo.Drobnis@dealertrack.com; ORACLE-L
    Subject: Re: Default user permissions

    Don,

    ... and what privilege was introduced with 11g in order to support access control lists for packages such as utl_tcp, utl_smtp?

    "its baaaack".

    connect.

    Brilliant.

    Paul
    On Tue, Nov 8, 2011 at 12:21 PM, Don Granaman wrote:
    It is been the advice of Oracle Corp and the security community for many years to NOT use the connect and resource roles. In older versions of Oracle prior to 10g, the CONNECT role granted a LOT more than "create session". If you want to grant "create session", do so - and avoid using these roles altogether.

    RESOURCE is worse. Even in 10g, it grants unlimited tablespace.


    Don Granaman | Phone: 402-361-3073<tel:402-361-3073> | Cell: 402-960-6955<tel:402-960-6955> | Fax: 402-361-3173<tel:402-361-3173> | Solutionary | Relevant . Intelligent . Security


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Leo Drobnis
    Sent: Tuesday, November 08, 2011 9:44 AM
    To: ORACLE-L
    Subject: Default user permissions
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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

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



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


    --
    http://www.freelists.org/webpage/oracle-l
  • Pete Finnigan at Nov 8, 2011 at 5:58 pm
    There are a number of "silent" privileges in Oracle. a user can issue
    "alter user identified by blah" for his own user or "noaudit select on
    tab". Similarly a user can drop his own tables without a drop table
    privilege; there are more. e.g.:

    SQL> create user test identified by test;
    create user test identified by test
    *
    ERROR at line 1:
    ORA-01920: user name 'TEST' conflicts with another user or role name


    SQL> create user testt identified by testt;

    User created.

    SQL> grant connect to testt;

    Grant succeeded.

    SQL> connect testt/testt@ora11gr2
    Connected.
    SQL> create table test (num varchar2(1));
    create table test (num varchar2(1))
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges


    SQL> connect system/xxxxxx@ora11gr2
    Connected.
    SQL> create table testt.test(num varchar2(1));

    Table created.

    SQL> connect testt/testt@ora11gr2
    Connected.
    SQL> drop table test;

    Table dropped.

    SQL>

    The user cannot create tables though. I test this separately:

    SQL> connect system/xxxxxx@ora11gr2
    Connected.
    SQL> alter user testt quota unlimited on users;

    User altered.

    SQL> connect testt/testt@ora11gr2
    Connected.
    SQL> create table test1 (num varchar2(1));
    create table test1 (num varchar2(1))
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges


    SQL>

    You can run hhttp://www.petefinnigan.com/find_all_privs.sql which will
    list all the privileges for a user and also
    http://www.petefinnigan.com/who_has_priv.sql and feed in CREATE TABLE or
    CREATE ANY TABLE to see who has these system privileges.

    Other than that can you show a sequence of creating the user and then
    creating a table?

    cheers

    Pete

    Leo Drobnis wrote:
    I am a bit puzzled, maybe I am getting rusty.


    I need to create a user with bare minimum permissions:



    CREATE USER bb_stage

    IDENTIFIED BY "password"

    DEFAULT TABLESPACE users

    TEMPORARY TABLESPACE TEMP;

    GRANT CONNECT TO bb_stage;

    ALTER USER bb_stage QUOTA UNLIMITED ON "USERS";



    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

    --

    Pete Finnigan
    CEO and Founder
    PeteFinnigan.com Limited

    Specialists in database security.

    Makers of PFCLScan the database security auditing tool.
    Makers of PFCLObfuscate the tool to protect IPR in your PL/SQL

    If you need help to audit or secure an Oracle database, please ask for
    details of our training courses and consulting services

    Phone: +44 (0)1904 791188
    Fax : +44 (0)1904 791188
    Mob : +44 (0)7759 277220
    email: pete@petefinnigan.com
    site : http://www.petefinnigan.com

    Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
    Company No : 4664901
    VAT No. : 940668114

    Please note that this email communication is intended only for the
    addressee and may contain confidential or privileged information. The
    contents of this email may be circulated internally within your
    organisation only and may not be communicated to third parties without
    the prior written permission of PeteFinnigan.com Limited. This email is
    not intended nor should it be taken to create any legal relations,
    contractual or otherwise.

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 8, '11 at 3:42p
activeNov 10, '11 at 8:39a
posts27
users12
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase