Re: Single schema access

Is there a way to list out the authority for *public for all schemas?
...

I think this will work:

// Count the number of schema that are *PUBLIC *USE
SELECT count(*)
FROM QSYS2.SYSSCHEMAAUTH
WHERE GRANTEE='PUBLIC'
AND PRIVILEGE_TYPE='USAGE'

and the answer is 511. This leaves about 100 they do not have access to.

// count of all schemas
select count(*)
from qsys2.sysschemas
617

// Count *PUBLIC *EXCLUDE schemas
SELECT COUNT(*)
FROM QSYS2.SYSSCHEMAS
WHERE SYSTEM_SCHEMA_NAME NOT IN(
SELECT SYSTEM_SCHEMA_NAME
FROM QSYS2.SYSSCHEMAAUTH
WHERE GRANTEE='PUBLIC')
= 106

// List of *PUBLIC *EXCLUDE schemas:
SELECT SYSTEM_SCHEMA_NAME
FROM QSYS2.SYSSCHEMAS
WHERE SYSTEM_SCHEMA_NAME NOT IN(
SELECT SYSTEM_SCHEMA_NAME
FROM QSYS2.SYSSCHEMAAUTH
WHERE GRANTEE='PUBLIC')


What I am not seeing is whether or not the user can modify, or just read,
objects within that schema.
Is that the difference between PRIVILEGE_TYPE 'USAGE vs 'CREATEIN'?

*USE authority to a library tells authority checking to use the authority
of the objects within that schema. So they can insert, update, delete
data and tables to tables they have permission to.
CREATEIN (*CHANGE) allows them to create new items within that schema in
addition to USAGE (*USE).


Rob Berendt