SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to allow update data only with macro

Reply
Contributor
Posts: 32

How to allow update data only with macro

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

Super User
Super User
Posts: 9,416

Re: How to allow update data only with macro

Posted in reply to DmitryErshov

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.

Respected Advisor
Posts: 4,673

Re: How to allow update data only with macro

Posted in reply to DmitryErshov

@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.

 

Contributor
Posts: 32

Re: How to allow update data only with macro

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

Ask a Question
Discussion stats
  • 3 replies
  • 184 views
  • 2 likes
  • 3 in conversation