RE: Single schema access

Based on
http://search400.techtarget.com/tip/Display-all-objects-private-authorit
ies-from-a-given-library

I created a pdm user option that looks like this: DSPOBJAUT OBJ(&N)
OBJTYPE(*LIB) OUTPUT(*OUTFILE) OUTFILE(KEVIN/LIBAUTH) OUTMBR(*FIRST
*ADD)
WRKLIBPDM LIB(*ALL)
Repeat my user option on the entire list, and the query the libauth
file.




Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
rob@xxxxxxxxx
Sent: Monday, January 18, 2016 1:50 PM
To: Midrange Systems Technical Discussion
Subject: 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
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.