confilicts between permissions on a store procedure and the objects it referers to_在指定的異動協調員中無法編輯列新異動-优快云博客

本文链接：https://blog.youkuaiyun.com/chenpan1988/article/details/10123479

本文探讨了用户在没有直接访问表T的权限时，能否执行引用了该表的存储过程p1的问题。结论指出，如果存储过程p1和表T的所有者相同，则用户可以执行p1；反之，若所有者不同且用户未被授予适当权限，则无法执行p1。文章还详细解释了在创建和执行存储过程时，系统如何进行权限检查。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.