Restrict stored procedure to only perform SELECT operations

Question

Is is possible to restrict a stored proc to only SELECT from a database.

I want a stored proc which only selects data to execute correctly and a stored proc with UPDATE, CREATE, DELETE operations to return an error indicating insufficient permissions.

I am using Sybase 12.5

Answer 1

I think you're looking at the problem the wrong way. Essentially, once you give a user execute permission to a store procedure, they can execute that store procedure no matter what it does.

I think what you want to do is assign a "read-only" client role to your database and grant SELECT permissions as well as the execute permission on only the stored procedures that read data from the database. Add users to that role instead of granting them SELECT access on the database.

Answer 2

Separate writing and selecting actions to different procedures. And then allow selecting user to execute selecting procedures and writing user to execute selecting and writing procedures. This trick works pretty well with PostgreSQL.