BookmarkSubscribeRSS Feed
DmitryErshov
Obsidian | Level 7

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Patrick
Opal | Level 21

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

 

DmitryErshov
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 790 views
  • 2 likes
  • 3 in conversation