Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my software, at runtime, I need to check if the connected user has the privileges on some tables, across 2 different schemas. From what I found doing research, I came across these 2 views ALL_TAB_PRIVS_RECD ALL_TAB_PRIVS Should I use one of these views, or is there another better way to do it? Thanks

share|improve this question

2 Answers 2

up vote 3 down vote accepted

I finally came with this query that I use only if the connected user is different than the schema I want to check privileges on:

SELECT PRIVILEGE
FROM ALL_TAB_PRIVS_RECD
WHERE PRIVILEGE = 'SELECT'
AND TABLE_NAME = 'GN_VERSION'
AND OWNER = 'USER_A'
UNION ALL
SELECT PRIVILEGE
FROM SESSION_PRIVS
WHERE PRIVILEGE = 'SELECT ANY TABLE';

This checks the SELECT privilege on the table GN_VERSION on the USER_A schema

share|improve this answer

ALL_TAB_PRIVS is a superset of ALL_TAB_PRIVS_RECD. You probably want ALL_TAB_PRIVS as it includes tables that the current user owns.

share|improve this answer
    
After some tests, ALL_TAB_PRIVS also returns the privileges the current user granted to other users. Even if the documentation says so, it does not include tables that the user own, that's why I prefer ALL_TAB_PRIVS_RECD –  Goldorak84 Aug 8 '11 at 15:21
    
Neither view includes tables that the user owns unless there is an explicit grant - both show grants not permissions. You may like to do a union with user_tables? –  Jack Douglas Aug 8 '11 at 15:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.