8

We have an IBM System i running IBM i OS v6r1. On this system, I have created some database views. What I want to do is give a particular user group access to ONLY these views and nothing else within the library in which the views reside. Is this possible? I had a user group that had read only permissions to all tables and views in the library in which my views are located, and access works when the user is under this usergroup. I tried copying the user group, and then assigning permissions to only include the views I have created, and access is denied. Does a user or usergroup also have to have permissions on the table from which the view originates in order to access the view?

3
  • Consider posting this on dba.stackexchange.com
    – WarrenT
    Nov 7, 2013 at 2:41
  • @WarrenT believe it or not this is actually as much system administration question as it is a DB question for iSeries/midrange -- the DB2 database is tightly integrated into OS/400 and from the question it sounds like it's OS/400 permissions on the library that are tripping things up. (Unfortunately it's been so long since I've logged into a midrange box I'm not 100% sure how to resolve the problem!)
    – voretaq7
    Nov 8, 2013 at 0:02
  • @voretaq7 Yes. The database is not only woven into the essence of the operating system, it is designed into the machine itself (the machine interface, MI). It's been over a decade since I was QSECOFR, so I would have to look up the answer. But since no one here has answered yet, and it is fair game for both sites, I was thinking there might be someone over at dba who might know it off the top of their head.
    – WarrenT
    Nov 8, 2013 at 2:28

1 Answer 1

4
+250

Yes. It is not enough to grant authority to use a view. The user must also have authority to the underlying table as well.

One way to allow access to a restricted user is adopted authority. For the sake of example, let's assume the following:

User RESTRICTED has no access to any tables. All libraries are either AUT(*EXCLUDE) -or- user RESTRICTED has *EXCLUDE authority to all libraries. USER PERMITTED can access (or owns) the tables and libraries. Library DATA has the tables. Library PROGRAMS has the programs and stored procedures.

Have PERMITTED create an RPG stored procedure in library PROGRAMS. Make sure the program has USRPRF(*OWNER) specified. This means that when the program runs it will use the authority of the owner (PERMITTED) rather than the authority of the profile executing it (RESTRICTED).

Grant user PERMITTED *USE authority to the RPG program object -or- GRANT via SQL. Also, grant user RESTRICTED *USE access to library PROGRAMS. This will allow RESTRICTED to execute the stored procedure / program.

Note that user RESTRICTED has no rights to access the tables in DATA and you want to leave it that way.

Now have user RESTRICTED use SQL to CALL the stored procedure. She has authority to execute the program because she has *USE rights to library PROGRAMS as well as the compiled program object. The stored procedure runs under the authority of the owner, PERMITTED and since PERMITTED has full access to library DATA and the tables therein, the stored procedure can access / update those tables. Any raw SQL that RESTRICTED attempts to use will fail due to insufficient authority - the only thing that she can do is call the stored procedures created for her.

If your programs are stored in the same library as your data you will need to give RESTRICTED *USE authority to the library and specifically restrict her (via *EXCLUDE) over all the tables in that library. It may be easier to create a RESTRICTED library that only holds the stored procedures and authorise her to that library. This makes ongoing maintenance easier because you won't have to try to remember to keep restricting her from newly created tables in DATA.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .