Here are the examples of Oracle SQL queries to check user permissions.
Check Current User Permissions in Oracle
If you want to check the user permissions for the currently logged in user, run the following SQL queries:
select * from USER_ROLE_PRIVS where USERNAME=USER; select * from USER_TAB_PRIVS where Grantee = USER; select * from USER_SYS_PRIVS where USERNAME = USER;
To check the permissions for other users, you can run the following Oracle SQL queries:
To check the roles granted to a user:
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USERNAME';
Permissions already have:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USERNAME';
System privileges granted:
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USERNAME';
SatyaGutti
=================(SQL SCRIPT START)==========================
SET ECHO off
REM NAME: TFSPRVVW.SQL
REM USAGE:"Hameed Pathan/tfsprvvw"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM Should be run as SYS
CREATE OR REPLACE VIEW DBA_USER_PRIVS (USERNAME, ROLENAME, PRIVILEGE) AS
SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME), SUBSTR(U2.NAME,1,20),
SUBSTR(SPM.NAME,1,27)
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1,
SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE SA1.GRANTEE# = U1.USER#
AND SA1.PRIVILEGE# = U2.USER#
AND U2.USER# = SA2.GRANTEE#
AND SA2.PRIVILEGE# = SPM.PRIVILEGE
UNION
SELECT U.NAME, NULL, SUBSTR(SPM.NAME,1,27)
FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U
WHERE SA.GRANTEE#=U.USER#
AND SA.PRIVILEGE#=SPM.PRIVILEGE
/
==================(SQL SCRIPT END)=======================
2) Run the command to get all the privileges of the specified user
SQL>select * from DBA_USER_PRIVS where username='<USER NAME>';