What resources or services are needed to run the %File:FileSet query?

Question

Question

Laura Cavanaugh · Feb 22, 2018

I was running the %File:FileSet class query, with my development user, but I am unable to run this query for an application user. Does anyone know what resource or service is needed to run this query? Assume the user has access to a certain directory on the file system needed for the query.

On second though, having tried almost all the available resources and services, perhaps the user doesn't have access to the directory. How to tell when the error is this:

set ok=st.%PrepareClassQuery("%File","FileSet")

d $system.Status.DisplayError(ok)
 
ERROR #5540: SQLCODE: -99 Message: User Laura_Test_DEV is not privileged for the operation

Thanks,

Laura

Discussion (13)1

Log in or sign up to continue

John Murray · Feb 23, 2018

I think your user Laura_Test_DEV needs to be granted a SQL permission to perform an EXECUTE on the relevant SQL procedure in whatever namespace you need:

1 0

score 0 · Answer 1 · 2018-02-22T17:05:59-05:00

SQLCODE -99 Privilege violation

You have 3 different sets of access rights in this scenario

your development user
the application user
the user your Caché / ENS server installation is running on

Check if also your server has the required access rights at OS level.
under *IX it's quite likely that you don't run as root.
(though this happens also on other OS)

score 0 · Answer 2 · 2018-02-23T08:50:33-05:00

A long shot: if auditing is turned on, and the %System/%Security/Protect event is enabled, does anything get logged when the user encounters the failure?

Failing that, can you debug through the %PrepareClassQuery method of %SQL.Statement in the context where it's failing for you? Not so easy when the call works for your user when run in Terminal.

score 0 · Answer 3 · 2018-02-23T09:54:00-05:00

I reproduced this error, and when I managed to catch it, I tried to work with Auditing, but with no luck.

But Func way does not check any privilege and just works.

score 0 · Answer 4 · 2018-02-23T10:12:23-05:00

Are you sure that you granted your test user the EXECUTE privilege on the procedure in the actual namespace where it will be executed?

I created a new user "laura" who only has the %Developer role. From the USER namespace in Terminal she can't prepare the class query:

USER>w $username laura USER>w $roles %Developer USER> USER>s st=##class(%SQL.Statement).%New() USER>s ok=st.%PrepareClassQuery("%File","FileSet") USER>d $system.Status.DisplayError(ok) ERROR #5540: SQLCODE: -99 Message: User laura is not privileged for the operation USER>

Then in Portal I granted her EXECUTE on the procedure, per my previous screenshot, being careful to do this in the USER namespace. Now the test results are as follows:

USER>k USER>s st=##class(%SQL.Statement).%New() USER>s ok=st.%PrepareClassQuery("%File","FileSet") USER>d $system.Status.DisplayError(ok) USER>

And here are a couple of lines where I confirm that the user "laura" has the necessary privilege in the USER namespace but not in the %SYS namespace:

USER>w $system.SQL.CheckPriv("laura","9,%Library.File_FileSet","e","USER") 1 USER>w $system.SQL.CheckPriv("laura","9,%Library.File_FileSet","e","%SYS") 0 USER>

score 1 · Answer 5 · 2018-02-23T10:14:27-05:00

Dmitry Maslennikov · Feb 23, 2018

Yep, with the right namespace, it works well.

1 0

score 0 · Answer 6 · 2018-02-23T11:25:59-05:00

I am not sure. I'll check now.

Edited to: I wish I could paste an image, but this may be the problem. I granted my laura_test_dev user

1. direct permission to execute

2. the %Library.File_FileSet SQL Procedure,

3. in the PMG namespace.

Logged out of the terminal, logged back in as that user, and got this:

Instance: ENSEMBLEDEV

Username: laura_test_DEV
Password: *********
PMG>w $system.SQL.CheckPriv("laura_test_dev","9,%Library.File_FileSet","e","PMG")
0

I agree; this seems like it should work, and for some reason that I haven't yet figured out, it is not working. At this point, the folder structure and who created the folders has no bearing. We're on version 2016.2.2.

MISSED A CRITICAL STEP. I then ran the query from the terminal, after having given that user the direct permission, and the query worked. Now I'm confused as to why I got a 0 on the priv check, but the query worked. But, it worked. So maybe I won't mess with it.

score 0 · Answer 7 · 2018-02-23T11:47:09-05:00

The priv check is case-sensitive on the username:

USER>w $system.SQL.CheckPriv("laura_test_dev","9,%Library.File_FileSet","e","USER")
0
USER>w $system.SQL.CheckPriv("laura_test_DEV","9,%Library.File_FileSet","e","USER")
1
USER>

Anyway, I'm glad it's now working for you.

BTW, to paste an image into a DC post I save it to a file, then use the following button in the DC editor to upload the file:

And then I sometimes (as above) use the Image button on the toolbar to add a 2-pixel border to the image, then the Increase Indent to move it across.

score 0 · Answer 8 · 2018-02-23T11:54:48-05:00

Also, a similar kind of check using a method of %SYSTEM.Security is not case-sensitive with respect to the username:

USER>w $system.Security.CheckUserPermission("laura_test_DEV","%Development")
USE
USER>w $system.Security.CheckUserPermission("laura_test_dev","%Development")
USE
USER>w $system.Security.CheckUserPermission("laura_test_devX","%Development")

USER>

score 0 · Answer 9 · 2018-02-23T12:07:25-05:00

Ha! That was it. Thanks. Wish we had entered all our usernames in one case, but alas.

Thanks for the help - I appreciate all the answers. Now, to go through our security and update all the appropriate roles....

score 0 · Answer 10 · 2018-02-23T11:08:33-05:00

We do have auditing turned on, for all kinds of events. I was looking at that, and didn't see much that I could use. Scratch that -- we happen to have %System/%DirectMode/DirectMode events turned on, and I could only see the fact that user Laura_test_DEV ran that command line in the terminal, but the Details page doesn't show failure or any more info.

Didn't think to debug the %PRepare method. I can probably see what line of code is actually failing. The other "low-level" (as I think of it) call to FileSetFunc works at the terminal prompt, but I'm not sure if it's working from the application. I can only hope it has something to do with the test folders.

Thanks for the help. I'll update with my final solution.

score 1 · Answer 11 · 2018-02-22T17:45:04-05:00

try this call

  set rs=##class(%File).FileSetFunc("c:\")
  if rs.%SQLCODE {
    write rs.%SQLCODE," - ",rs.%Message
  } else {
    do rs.%Display()
  }

score 0 · Answer 12 · 2018-02-23T08:58:40-05:00

From the terminal prompt, logged in as my application user, the ##class(%File).FileSetFunc(directory) appears to be working. Oddly, giving the user execute permissions like John suggested didn't work. I wish it had because my code is all set up for the class query.

I had seen the FileSestFunc in other posts... I don't know why this works differently. Can I use a FileMask, however?

set rs=##class(%File).FileSetFunc("c:\","*.pdf;*.csv")

Oh, and we're on Windows, and the instance is running as a domain user with lots of privileges, but if the application user copies and deletes a file, who is actually doing it? The instance's user, or the application user?

Too early for me.

Also, I did try the call with a FileMask, and it does work. Is it the same as the FileSet query? I can't find it in the code... must be a lower-level call to that query or something.