oracle oracle查询以查找存储过程的特权
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2156775/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle query to find priveleges on a stored procedure
提问by Omnipresent
What query can I run to simply see whether a user has privelege to execute a stored procedure.
我可以运行什么查询来简单地查看用户是否具有执行存储过程的特权。
lets say user is UserAand stored procedure name is my_stored_proc
假设用户是UserA，存储过程名称是my_stored_proc
I want to know whether UserAhas execute permission on my_stored_proc
我想知道是否UserA有执行权限my_stored_proc
UserA is not the owner of the storedproc. Some other owner grants him the permission.
UserA 不是存储过程的所有者。其他一些所有者授予他许可。
回答by dpbradley
To account for grants through a role:
要通过角色计算拨款：
select grantee, table_name, privilege
from dba_tab_privs
where
table_name = 'my_stored_proc'
and
owner = 'ownerOfObject'
and
(grantee = 'userA'
or
grantee in
(select granted_role
from dba_role_privs
where grantee = 'userA'
)
)
回答by FrustratedWithFormsDesigner
You could try
你可以试试
select ap.*
from All_Procedures ap
where ap.owner = 'UserA'
This only tells you if UserA is the owner. I suppose UserA could still have permission even if not the owner. Not sure how to check for that.
这只会告诉您 UserA 是否是所有者。我想即使不是所有者，UserA 仍然可以拥有权限。不知道如何检查。
EDIT:Other tables to check are
编辑：要检查的其他表是
USER_SYS_PRIVS USER_TAB_PRIVS USER_ROLE_PRIVS ROLE_SYS_PRIVS ROLE_TAB_PRIVS
I've rarely queried these so I'm not exactly sure how to find what you're looking for, but I would start with these.
我很少查询这些，所以我不确定如何找到您要查找的内容，但我会从这些开始。
回答by Omnipresent
Got it...
知道了...
SELECT * FROM DBA_TAB_PRIVS A WHERE GRANTEE = 'UserA' AND GRANTOR = 'someoneelse' and privilege = 'EXECUTE'
回答by stone1343
This is what worked for me, I wanted to find all SPs that I have access to:
这对我有用，我想找到我可以访问的所有 SP：
select * from USER_TAB_PRIVS where GRANTEE='______' and PRIVILEGE='EXECUTE'
select * from USER_TAB_PRIVS where GRANTEE='______' and PRIVILEGE='EXECUTE'
Columns in USER_TAB_PRIVS include GRANTEE, OWNER, GRANTOR, TABLE_NAME (in this case, the SP name) and PRIVILEGE, so in my opinion, this is perfect.
USER_TAB_PRIVS 中的列包括 GRANTEE、OWNER、GRANTOR、TABLE_NAME（在本例中为 SP 名称）和 PRIVILEGE，所以在我看来，这是完美的。
My understanding is that dpbradley and Omnipresent's answers won't work for a normal user because they can't access DBA_* tables.
我的理解是 dpbradley 和 Omnipresent 的答案对普通用户不起作用，因为他们无法访问 DBA_* 表。
