What privilege or privs does a user need in order to read %SYS_Task.History

Question

Question

Laura Cavanaugh · Jan 17, 2017

I have a user with %All, and a user with %Admin_Task. The %All user can use SQL to view the %SYS_Task.HIstory table. The user with %Admin_Task does not have the privileges to see the same table.

What priv (or privs) is (or are) needed to view the %SYS_Task.History table? I just want to start with 'Select * from %SYS_Task.History'.

I've tried %Admin_Manage and %Admin_Operate. I must be close.

Thanks,

Laura

Discussion (3)0

Log in or sign up to continue

John Murray · Jan 17, 2017

%Admin_Task is a Resource, as are %Admin_Manage and %Admin_Operate.

In contrast, %All is a Role.

Access to SQL tables is controlled either at the Role level or at the individual User level.

If your user has permissions on the %Admin_Task resource because they hold a role, then it may be appropriate to grant the necessary SQL permissions to that role. By doing this, anyone else holding the role will also be able to access the table.

To grant the SQL permissions, edit the role (or user) definition. Go to the "SQL Tables" tab. Set the namespace dropdown to "%SYS" and check the box to include system items:

In my example above the %Operator role has no permissions on SQL tables in %SYS.

Use the Add Tables button to add a row that gives this role permission to perform a SELECT on the %SYS_Task.History table .

1 0

score 1 · Answer 1 · 2017-01-17T12:15:23-05:00

Eduard Lebedyuk · Jan 17, 2017

%DB_CACHESYS may also be required, because data for this table is stored in CACHESYS database.

1 0

score 0 · Answer 2 · 2017-01-17T13:20:51-05:00

So, I created a new Role, called TaskAdmin. To this role, I added 3 privs:

%Admin_Task

%DB_CACHESYS

%Development (so user could login to terminal)

I included the only tables I could find in %SYS that are relevant:

%SYS_Task.Config

%SYS_Task.History

I added my own tables and views that use these tables.

To this role, I added a new user called task_admin, to test a query from the terminal. i.e. I created a new user, task_admin, and added this single role.

This test user, task_admin, can run the SQL shell in the terminal and select * from %SYS_Task.History. (%SYS.Task is a different story!). So, success.

In order for a real user to see my table, which is a mix of %SYS.Task, %SYS_Task.Histoy, and my own tables, and my own view based on those same tables, I had to add my own tables and views to the role TaskAdmin as well; but it did work finally work.

I also remembered seeing somewhere that embedded SQL does not check SQL privs, which is maybe why I could see all this table data in the <tablePane> without adding tables. (?) I admit that the user looking at my table data did have %Admin_Task and %DB_CACHESYS privs already (from some role). But the user could not call a query/view that queried %SYS_Task.History directly until I added my own tables.

Yes, I really l mean that about the <tablePane>. My user had the privs, but not the tables, and yet could see the data in a <tablePane>. Is this included in embedded SQL?

So, basically I think I got it, and the real user needed my tables/view added to a role; the test user needed the %SYS tables added since he (it) was querying just the %SYS tables.

Thank you for your help!

Laura