GRANT Table Permissions for a limited amount of time

Question

I have a plsql job that runs as a specific user, and I need to perform a grant to give this user access to specific tables until the job completes. The job never takes more than 30 minutes.

My question is, in oracle is there anyway to grant a specific user access to a table for a limited amount of time, or should I just create another function to revoke privileges after the job is complete? I did a few quick searches and was unable to find anything on this. Does anyone know if this is possible?

It would be awesome if I could do grant all for 30 minutes.

Answer 1

There is a method in plsql called grantAccess(). It allows for you to give the number of seconds that a user is granted access for.

So, if you wanted to give them access for, say, a year, you would write the following:

rightSideUser.grantAccess(31536000) 

Answer 2

The comment from @ksa is the correct answer for this. You should create a new user with exactly the permissions required, and define this procedure on that schema. You can then GRANT EXECUTE on this procedure to the role or users that need to run it. Although it is the default, you can also explicitly specify AUTHID DEFINER. In essence this means the procedure runs as the definer no matter who calls it. It saves you from any timing/scheduling issues and the permissions are clear to your successors. If you don't like having to specify the schema on every call, you can define a synonym.