Access to SYSIBM tables

What kind of authority does a user need to be able to run queries against the SYSIBM tables on the iSeries? Our programmers need me to set up a user for them - but I want to give the least amount of authority as possible - while still being able to query the tables in SYSIBM.

I appreciate the help
LVL 1
Matthew RoessnerSenior Systems ProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Matthew,

Your programmers (with DB2 access) should be able to query the SYSIBM tables without any special permissions.  They cannot write directly to these tables, but read access should be the default.

Kent
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
Found the following command which solved my issue:

RUNSQL SQL('GRANT SELECT ON TABLE SYSIBM.SYSDUMMY1 TO USERNAME')
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for hfcsp's comment #a41151864

for the following reason:

While awaiting a response - continued to search the internet for a solution and found the command that I needed to grant access to the table.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Gary PattersonVP Technology / Senior Consultant Commented:
It just depends on the permissions assigned to the SYSIBM files on your system.  You can use the DSPOBJAUT command to view the authorities for any native object in the system.  By default, *PUBLIC has Object Operational and Data Read authority to the tables and views in SYSIBM, which is adequate for SELECT operations.  

If you have users that can't access these objects, it means that someone has changed the default authorities, probably to *PUBLIC *EXCLUDE.  In a secure environment, you'll generally see authorities assigned via group profiles and/or authorization lists, rather than assigning private authorities to individual objects (which can be very difficult to maintain, and can cause user profiles to grow very large, since private object authority is stored in the user profile object.

Suggest you take a look at how these objects are currently secured (DSPOBJAUT), and consult the person who set up IBM i system security before you make any changes.  The particular table you mention just contains one dummy row, so it isn't really security sensitive, but it is still a good idea to stay consistent with the overall security plan.  If you don't, someone may come behind you at some point and undo your changes (especially if a tool is used to enforce object security policies) if they aren't compliant with security policies.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent OlsenDBACommented:
Hi Matthew,

SYSIBM.SYSDUMMY1 isn't a real table.  It's a catch-all name that adds capability to DB2 that also exists in Oracle and SQL Server (and others).

I'm absolutely shocked that the default behavior of DB2 restricts access to that name.  It seems like more of a bug in DB2 than a feature.  What version of DB2 and O/S are you running?

Kent
Kent OlsenDBACommented:
Thanks for a great explanation, Gary.  

That scenario never occurred to me.  :)


Kent
Gary PattersonVP Technology / Senior Consultant Commented:
I encourage clients to secure SYSIBM (and other database metadata), since it provides so much information about the database, and by default can be read by anyone with a profile and a way to execute a query or otherwise access the tables/views.

*PUBLIC=*READ offers an attacker who may have obtained only the most restricted end user credentials the opportunity to gather a lot of information about the database.  An attacker can query the list of schemas, for example, and obtain clues about third-party products installed on the system - some of which may have known vulnerabilities or may create privileged IDs with well-known default passwords.  Table names may give clues to the location of sensitive information.  Stored procedure names may provide clues to ways to access sensitive data or elevated operations.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.