1. Forum
  2. Usenet
  3. COMP.DATABASES.IBM-DB2

  • created Database in non-restrictive mode leading many privileges to PUB

    From Shashi Shekhar@21:1/5 to All on Wed May 31 01:36:54 2017
    Hello All,

    I have started working as DB2 LUW DBA ( AIX , Db2 10.5). Few months back I got to create a new database after all testing this is into PROD now.

    Recently I was just going through authorities and privileges on my database and see group "PUBLIC" has got many privileges like "SELECT", UPdate, write, EXECUTE, BIND, CREATEIN, IMPLICIT_SCHEMA.

    After doing some google , i get to know that I didn't use "RESTRICTIVE" while creating database . it may case many issue like users having "CONNECT" authority can create any objects on database .

    But since it's already there in PROD. I really need to know what all permissions group "PUBLIC" should have? and what all I can revoke from "PUBLIC"?


    Thanks in advance. [Have been getting always right solutions here. so came back again ]


    Regards
    Shashi

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jerry Stuckle@21:1/5 to Shashi Shekhar on Wed May 31 08:17:21 2017
    On 5/31/2017 4:36 AM, Shashi Shekhar wrote:
    Hello All,

    I have started working as DB2 LUW DBA ( AIX , Db2 10.5). Few months back I got to create a new database after all testing this is into PROD now.

    Recently I was just going through authorities and privileges on my database and see group "PUBLIC" has got many privileges like "SELECT", UPdate, write, EXECUTE, BIND, CREATEIN, IMPLICIT_SCHEMA.

    After doing some google , i get to know that I didn't use "RESTRICTIVE" while creating database . it may case many issue like users having "CONNECT" authority can create any objects on database .

    But since it's already there in PROD. I really need to know what all permissions group "PUBLIC" should have? and what all I can revoke from "PUBLIC"?


    Thanks in advance. [Have been getting always right solutions here. so came back again ]


    Regards
    Shashi


    Shashi,

    What PUBLIC needs is very dependent on your database and installation.
    In a (very) few cases, it might be OK for PUBLIC to have all of those privileges. However, that would be very seldom, IMHO. For instance,
    I've used it in classes so students can access the class database before
    we get into privileges, etc.

    For a production database I have always removed all privileges from
    PUBLIC. If they don't have a valid signon to the database, they have no business accessing the data.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    jstucklex@attglobal.net
    ==================

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Shashi Shekhar@21:1/5 to Jerry Stuckle on Wed May 31 09:31:35 2017
    On Wednesday, 31 May 2017 17:47:14 UTC+5:30, Jerry Stuckle wrote:
    On 5/31/2017 4:36 AM, Shashi Shekhar wrote:
    Hello All,

    I have started working as DB2 LUW DBA ( AIX , Db2 10.5). Few months back I got to create a new database after all testing this is into PROD now.

    Recently I was just going through authorities and privileges on my database and see group "PUBLIC" has got many privileges like "SELECT", UPdate, write, EXECUTE, BIND, CREATEIN, IMPLICIT_SCHEMA.

    After doing some google , i get to know that I didn't use "RESTRICTIVE" while creating database . it may case many issue like users having "CONNECT" authority can create any objects on database .

    But since it's already there in PROD. I really need to know what all permissions group "PUBLIC" should have? and what all I can revoke from "PUBLIC"?


    Thanks in advance. [Have been getting always right solutions here. so came back again ]


    Regards
    Shashi


    Shashi,

    What PUBLIC needs is very dependent on your database and installation.
    In a (very) few cases, it might be OK for PUBLIC to have all of those privileges. However, that would be very seldom, IMHO. For instance,
    I've used it in classes so students can access the class database before
    we get into privileges, etc.

    For a production database I have always removed all privileges from
    PUBLIC. If they don't have a valid signon to the database, they have no business accessing the data.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    jstucklex@attglobal.net
    ==================

    Thanks Jerry for response.

    We are planning to revoke all permissions . so do we need to revoke at all levels like -

    DB level -
    BINDADDAUTH
    CONNECTAUTH
    CREATETABAUTH
    IMPLSCHEMAAUTH

    Schema level -
    - CREATEIN on schema SQLJ
    - CREATEIN on schema NULLID

    Package level -
    - BIND on all packages created in the NULLID schema
    - EXECUTE on all packages created in the NULLID schema


    if I revoke BIND and EXECUTE from PUBLIC, and Developer ( has

    TBSPACEAUTH
    - revoking USE privilege on table space USERSPACE1.

    and some more AUTH table where PUBLIC gets default access.

    would it have any kind of impact any level? I am trying parallel to understand on a dummy database.


    Thanks again
    Shashi

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jerry Stuckle@21:1/5 to Shashi Shekhar on Wed May 31 13:00:28 2017
    On 5/31/2017 12:31 PM, Shashi Shekhar wrote:
    On Wednesday, 31 May 2017 17:47:14 UTC+5:30, Jerry Stuckle wrote:
    On 5/31/2017 4:36 AM, Shashi Shekhar wrote:
    Hello All,

    I have started working as DB2 LUW DBA ( AIX , Db2 10.5). Few months back I got to create a new database after all testing this is into PROD now.

    Recently I was just going through authorities and privileges on my database and see group "PUBLIC" has got many privileges like "SELECT", UPdate, write, EXECUTE, BIND, CREATEIN, IMPLICIT_SCHEMA.

    After doing some google , i get to know that I didn't use "RESTRICTIVE" while creating database . it may case many issue like users having "CONNECT" authority can create any objects on database .

    But since it's already there in PROD. I really need to know what all permissions group "PUBLIC" should have? and what all I can revoke from "PUBLIC"?


    Thanks in advance. [Have been getting always right solutions here. so came back again ]


    Regards
    Shashi


    Shashi,

    What PUBLIC needs is very dependent on your database and installation.
    In a (very) few cases, it might be OK for PUBLIC to have all of those
    privileges. However, that would be very seldom, IMHO. For instance,
    I've used it in classes so students can access the class database before
    we get into privileges, etc.

    For a production database I have always removed all privileges from
    PUBLIC. If they don't have a valid signon to the database, they have no
    business accessing the data.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    jstucklex@attglobal.net
    ==================

    Thanks Jerry for response.

    We are planning to revoke all permissions . so do we need to revoke at all levels like -

    DB level -
    BINDADDAUTH
    CONNECTAUTH
    CREATETABAUTH
    IMPLSCHEMAAUTH

    Schema level -
    - CREATEIN on schema SQLJ
    - CREATEIN on schema NULLID

    Package level -
    - BIND on all packages created in the NULLID schema
    - EXECUTE on all packages created in the NULLID schema


    if I revoke BIND and EXECUTE from PUBLIC, and Developer ( has

    TBSPACEAUTH
    - revoking USE privilege on table space USERSPACE1.

    and some more AUTH table where PUBLIC gets default access.

    would it have any kind of impact any level? I am trying parallel to understand on a dummy database.


    Thanks again
    Shashi


    If your authorized users have the appropriate access, revoking
    everything on PUBLIC shouldn't affect them at all. But you have to
    ensure they have the appropriate access or you will have problems.

    A dummy database is OK, but it won't show a problem if your authorized
    users don't have the needed permissions on the live databases.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    jstucklex@attglobal.net
    ==================

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)