Question
If user 'A' has the permission to execute stored procedure 'p1', while doesn't have access permission to table 'T' which is refered to in 'p1', then whether user 'A' can execute 'p1'?
Answer
The following section "Reference" is quoted form Sybooks Online. According to that, we can deduce the following conclusion.
-
If the tables 'p1' is refering to share the same owner with 'p1', then user 'A' can execute 'p1'.
-
If the table 'T' (which is referred to by 'p1') and 'p1' have different owners and user 'A' was not granted appropiate permissions on table 'T', then user 'A' can't execute 'p1'.
Reference
Permissions
create procedure permission defaults to the Database Owner, who can transfer it to other users.
Permission to use a procedure must be granted explicitly with the grant command and may be revoked with the revoke command.
Permissions on objects at procedure creation When you create a procedure, Adaptive Server makes no permission checks on objects, such as tables and views, that are referenced by the procedure. Therefore, you can create a procedure successfully even though you do not have access to its objects. All permission checks occur when a user executes the procedure.
Permissions on objects at procedure execution When the procedure is executed, permission checks on objects depend upon whether the procedure and all referenced objects are owned by the same user.
-
If the procedure’s objects are owned by different users, the invoker must have been granted direct access to the objects. For example, if the procedure performs a select from a table that the user cannot access, the procedure execution fails.
-
If a procedure and its objects are owned by the same user, however, special rules apply. The invoker automatically has “implicit permission” to access the procedure’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables and views, you can give them restricted access with a stored procedure. In this way, a stored procedure can be a security mechanism. For example, invokers of the procedure might be able to access only certain rows and columns of your table.