topic Re: How to allow update data only with macro in SAS Data Management

How to allow update data only with macro

DmitryErshov — Mon, 18 Dec 2017 16:10:57 GMT

Hello!

I have a dataset MYDS (it is Oracle table) and macro %update_myds which can update it according to some algorithm. I don’t want user can manually update MYDS (ex. with proc sql or data step). Is there some way to make such restriction?

Regards,

Dmitry

Re: How to allow update data only with macro

RW9 — Mon, 18 Dec 2017 16:17:55 GMT

One way would be to make that table read only in Oracle, then have a special account for write access, and grant it only to the process which runs that macro (i.e. create a user called write_to_db, and use that account to run the code with. It doesn't stop them running other code however. Maybe stored process might help, if you have that (you haven't specified what you have).

Another method maybe that the user writes their data to an area, and a general utility program is run once something appears in that area which uploads the data and then removes the file.

Can't think of much more than that, its one of the problems with trying to drive one application with another. Far better to have your users write out data to given area, then the database (i.e. no involvement of SAS or user) picks up that data and runs with it.

Re: How to allow update data only with macro

Patrick — Wed, 20 Dec 2017 10:54:57 GMT

@DmitryErshov

I believe that boils down to the question how you can have a user initiate a defined process which does something the user can't do with his own credentials.

From the SAS side I can only think of:

- secure compiled macro which connects to Oracle with different credentials (requires user and pw to be compiled into the macro which is ugly).

- Stored Process with pooled workspace server (so sp process executes with different credentials)

- batch process: user can only trigger the batch which then runs under a batch user

As this is Oracle and you actually want to control what can be done on the Oracle side: I believe you could also implement an Oracle Stored Process which does the update. Load the data from SAS to a staging table and then call the Oracle stored process from SAS and only this Ora process got actually the grant to update the table.

Re: How to allow update data only with macro

DmitryErshov — Wed, 20 Dec 2017 11:46:48 GMT

Hello!

Thanks for your replies! I like idea with Stored Process (proc stp wrapped within macro). But how can I make STP execute from the other user?

Thanks,

Dmitry