Re: Row or object in *FILE in use

One more question. Is there a way that all new files automatically get
assigned to the default authorization list for the library?

-----Original Message-----
From: Matt Olson <Matt.Olson@xxxxxxxx>
Sent: Thursday, November 8, 2018 2:51 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Row or object in *FILE in use

Nice! I think we will go with the GRTOBJAUT on an entire library when we
can get the system into a restricted state.

-----Original Message-----
From: Rob Berendt <rob@xxxxxxxxx>
Sent: Thursday, November 8, 2018 2:38 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Row or object in *FILE in use

1) Initially assigning an authorization list to an object still requires
exclusive access to an object. They've been around for decades and one
should really know how security works on IBM i before creating your own
tables, etc. Some might push for a DBA. And one who knows IBM i.
2) Some people may actually run more than one application on a single
lpar of IBM i. Actually it's really common. Payroll by package XYZ and
ERP by package ABC. Having one authorization list flagged as the default
for everything would not make sense.
3) As an addendum to item 2 you can assign a default authorization list
to new tables created in a schema. See: CHGLIB LIB(MYLIB) CRTAUT(MYAUTL)
4) To mass blast all objects in a schema it's easy to do a two step
process.
4a) Assign an authorization list to all objects: GRTOBJAUT
OBJ(MYSCHEMA/*ALL) OBJTYPE(*ALL) AUTL(MYAUTL)
4b) Assign *PUBLIC to that authorization list: GRTOBJAUT
OBJ(MYSCHEMA/*ALL) OBJTYPE(*ALL) USER(*PUBLIC) AUT(*AUTL)
4(cont) While I wish it could be done with one statement, this works.
5) One may also use a separate authorization list for your program
libraries versus that for your data schemas. DATACLERK can edit data,
should they be allowed to change programs?
6) Of course, one may want to consider "application only" access for your
data schemas. But, even in that scenario adding Query experts with *use
authority to the authorization list may be desired.
7) One could join OBJECT_STATISTICS to AUTHORIZATION_LIST_INFO to find
objects in a schema not secured by a particular authorization list.
http://ibm.biz/DB2foriServices But I'm rather fond of the mass blasting
as shown in item 4.


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





From: "Matt Olson" <Matt.Olson@xxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 11/08/2018 02:21 PM
Subject: RE: Row or object in *FILE in use
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I guess you don't in looking at this more. It's strange that if this is
the preferred way to assign permissions why it didn't assign a default
authorization list to every file in the system. Right now it defaults to
assigning *PUBLIC CHANGE control to files (which we want to stop
perpetuating).

I'm still looking for the SQL way to grant permissions like you do in
other RDBMS to authorization lists, but so far have not found it.

Hopefully you can grant an authorization list to all files in the system
while they are in use, or I am back to the beginning of shutting everything
down just to assign permissions.

I have never run into this kind of limitation in other RDBMS's before...

I was hoping to run this query:

SELECT 'GRANT SELECT ON ' || TRIM(SYSTEM_TABLE_SCHEMA) || '.' ||
TABLE_NAME || ' TO DBREADONLY;' FROM qsys2.SYSTABLES WHERE
SYSTEM_TABLE_SCHEMA IN ('mylibrary1','mylibrary2') AND TABLE_TYPE IN ('T',
'P', 'L' )

While this would have fixed my problem, it runs into the *FILE in use
limitation.

So now I am trying to find how I can grant an authorization list to all
files in the system that are not already assigned to an authorization list
so then I can assign the DBREADONLY group *USE only permissions to any file
in that authorization list as I think this would also satisfy the end goal.

-----Original Message-----
From: Charles Wilt <charles.wilt@xxxxxxxxx>
Sent: Thursday, November 8, 2018 1:09 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Row or object in *FILE in use

Why would you need more than one?


On Thu, Nov 8, 2018 at 12:04 PM Matt Olson <Matt.Olson@xxxxxxxx> wrote:

Seems like you can't assign more than one authorization list to a file.
How do you get around that?

-----Original Message-----
From: Rob Berendt <rob@xxxxxxxxx>
Sent: Thursday, November 8, 2018 12:39 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Row or object in *FILE in use

Matt,

This is why many people use authorization lists instead.
Once you've assigned the authorization list to an object (which
initially cannot be in use) it becomes easy to add/change/delete
members of the authorization list while the file is in 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





From: "Matt Olson" <Matt.Olson@xxxxxxxx>
To: "Midrange Systems Technical Discussion"

<midrange-l@xxxxxxxxxxxx>

Date: 11/08/2018 01:32 PM
Subject: Row or object in *FILE in use
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Why is it that when you want to grant permissions you might get: Row
or object in *FILE in use

Here is the statement: GRANT SELECT ON library.filename TO group_name

Is it normal to not be able to grant permissions while a system is in

use.

We have to enter restricted state to assign permissions?

--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com