We help IT Professionals succeed at work.

How does GRANT SELECT ACCESS TO PUBLIC really work????

itsonlyme4
itsonlyme4 used Ask the Experts™
on
We are running DB2 LUW v9.7 on Windows.   I am struggling to understand how the "PUBLIC" group access works.    We Grant Select access to PUBLIC on most of our tables but how does that grant a Domain User access?    Is there a specific AD (active directory) group that the user has to be a member of????
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent OlsenData Warehouse / Database Architect

Commented:
Howdy, me4...

DB2, like all major DBMS, maintains access lists that allow access to objects.  

GRANT SELECT to PUBLIC on schema1.table2 allows anyone connected to the database to the see the contents of table2.  DB2 maintains the access lists (ACLs) internally and don't require validation back through AD.
Database Administrator / Software Engineer
Commented:
Hi,

PUBLIC group as Kent mentions means that every object granted to PUBLIC is available for the action granted to any user that can access the host.
It is a recommended practice to create a database with the RESTRICTIVE keyword where the PUBLIC granting is disabled.
Otherwise connect is granted to the PUBLIC group.
https://www.ibm.com/support/pages/revoke-connect-privileges-public-and-allow-only-users-specified-role-connect-databases

If the RESTRICTIVE option is present it causes the restrict_access database configuration parameter to be set to YES and limited privileges and no authorities are automatically granted to PUBLIC. If the RESTRICTIVE option is not present then the restrict_access database configuration parameter is set to NO and privileges are automatically granted to PUBLIC. For information about privileges, see: "Default privileges granted on creating a database".
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001941.html

You can create ROLES in Db2 and assign some privileges to that role and then add users and/or groups to that role.
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.sec.doc/doc/c0051427.html

If you have a database that is already created and it was created without the restrictive keyword, then I strongly recommend that you revoke all PUBLIC grants otherwise risking unauthorized access to your data and in worst case a databreach.

Regards,
    Tomas Helgi