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
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.