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
midrange.com
