created Database in non-restrictive mode leading many privileges to PUBLIC

[Shashi Shekhar]

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

[Jerry Stuckle]

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
jstu...@attglobal.net
==================

[Shashi Shekhar]

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

[Jerry Stuckle]

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.