IBM System i Permissions on Database views

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?

Solution 1:

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.