List all users who have been assigned a particular role
-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'
List all roles given to a user
-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';
List all privileges given to a user
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
List which tables a certain role gives SELECT access to?
-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';
List all tables a user can SELECT from?
--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';
List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.
-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;
You want local Windows users to have the same permissions as the database owner of a specific database? How about:
- Manually create logins for each Windows account:
-- repeat this whole block for each user account
USE [master];
GO
CREATE LOGIN [Domain\Account] FROM WINDOWS;
GO
-- repeat just this portion for each database that account should belong to
USE [your_database];
GO
CREATE USER [Domain\Account] FROM LOGIN [Domain\Account];
GO
- Add the users to the dbo role of each database to which you've added a user account above, using dynamic SQL (uncomment the
EXEC
line when you think it looks right):
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + N'
EXEC sp_addrolemember N''db_owner'', N''' + name + ''';'
FROM sys.database_principals
WHERE principal_id BETWEEN 5 AND 16383
AND [type] IN ('U', 'S');
PRINT @sql;
--EXEC sp_executesql @sql;
Best Answer
The table_privileges view is only preserved for compatibility with ancient versions of Oracle.
You should instead be using the user_tab_privs view, all_tab_privs view, or dba_tab_privs view (documentation links for each when you click).