Summary
How you find the objects grants for a specific user with the following query:
SELECT tpm.NAME PRIVILEGE,
DECODE (MOD (oa.OPTION$, 2), 1, 'YES', 'NO') grantable,
ue.NAME Grantee, ur.NAME grantor, u.NAME owner,
DECODE (o.TYPE#,
0, 'NEXT OBJECT',
1, 'INDEX',
2, 'TABLE',
3, 'CLUSTER',
4, 'VIEW',
5, 'SYNONYM',
6, 'SEQUENCE',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
19, 'TABLE PARTITION',
20, 'INDEX PARTITION',
21, 'LOB',
22, 'LIBRARY',
23, 'DIRECTORY',
24, 'QUEUE',
28, 'JAVA SOURCE',
29, 'JAVA CLASS',
30, 'JAVA RESOURCE',
32, 'INDEXTYPE',
33, 'OPERATOR',
34, 'TABLE SUBPARTITION',
35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION',
41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT',
46, 'RULE SET',
47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION',
52, 'LOCATION',
55, 'XML SCHEMA',
56, 'JAVA DATA',
57, 'SECURITY PROFILE',
59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED'
) object_type,
o.NAME object_name, '' column_name
FROM SYS.objauth$ oa, SYS.obj$ o, SYS.USER$ u, SYS.USER$ ur, SYS.USER$ ue, table_privilege_map tpm
WHERE oa.obj# = o.obj#
AND oa.grantor# = ur.USER#
AND oa.Grantee# = ue.USER#
AND oa.col# IS NULL
AND oa.PRIVILEGE# = tpm.PRIVILEGE
AND u.USER# = o.owner#
AND o.TYPE# IN (2, 4, 6, 9, 7, 8, 42, 23, 22, 13, 33, 32)
AND ue.NAME = 'NISA_DL'
UNION ALL -- column level grants
SELECT tpm.NAME PRIVILEGE,
DECODE (MOD (oa.OPTION$, 2), 1, 'YES', 'NO') grantable,
ue.NAME Grantee, ur.NAME grantor, u.NAME owner,
DECODE (o.TYPE#,
0, 'NEXT OBJECT',
1, 'INDEX',
2, 'TABLE',
3, 'CLUSTER',
4, 'VIEW',
5, 'SYNONYM',
6, 'SEQUENCE',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
19, 'TABLE PARTITION',
20, 'INDEX PARTITION',
21, 'LOB',
22, 'LIBRARY',
23, 'DIRECTORY',
24, 'QUEUE',
28, 'JAVA SOURCE',
29, 'JAVA CLASS',
30, 'JAVA RESOURCE',
32, 'INDEXTYPE',
33, 'OPERATOR',
34, 'TABLE SUBPARTITION',
35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION',
41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT',
46, 'RULE SET',
47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION',
52, 'LOCATION',
55, 'XML SCHEMA',
56, 'JAVA DATA',
57, 'SECURITY PROFILE',
59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED'
) object_type,
o.NAME object_name, c.NAME column_name
FROM SYS.objauth$ oa, SYS.obj$ o, SYS.USER$ u, SYS.USER$ ur, SYS.USER$ ue, SYS.col$ c, table_privilege_map tpm
WHERE oa.obj# = o.obj#
AND oa.grantor# = ur.USER#
AND oa.Grantee# = ue.USER#
AND oa.obj# = c.obj#
AND oa.col# = c.col#
AND BITAND (c.property, 32) = 0 /* not hidden column */
AND oa.col# IS NOT NULL
AND oa.PRIVILEGE# = tpm.PRIVILEGE
AND u.USER# = o.owner#
AND o.TYPE# IN (2, 4)
AND ue.NAME = 'NISA_DL';
PRIVILEGE GRA GRANTEE GRANTOR OWNER OBJECT_TYPE OBJECT_NAME
---------------------------------------- --- ------------------------------ ------------------------
SELECT NO NISA_DL BORAIG BORAIG TABLE PCM
SELECT NO NISA_DL PLDM PLDM TABLE FER
SELECT NO NISA_DL PLDM PLDM TABLE PCM
SELECT NO NISA_DL BATH BATH TABLE FER
SELECT NO NISA_DL BATH BATH TABLE PCM
SELECT NO NISA_DL GIAN GIAN TABLE FER
SELECT NO NISA_DL GIAN GIAN TABLE PCM
SELECT NO NISA_DL DYAT DYAT TABLE FER
SELECT NO NISA_DL DYAT DYAT TABLE PCM
SELECT NO NISA_DL PRS PRS TABLE FER
SELECT NO NISA_DL ANMAK ANMAK TABLE PCM
SELECT NO NISA_DL LRS LRS TABLE FER
SELECT NO NISA_DL TRP TRP TABLE FER
SELECT NO NISA_DL TRP TRP TABLE PCM
SELECT NO NISA_DL NISIA NISIA TABLE PCM
SELECT NO NISA_DL BORAIG BORAIG TABLE FER
SELECT NO NISA_DL BER BER TABLE FER
SELECT NO NISA_DL BER BER TABLE PCM
SELECT NO NISA_DL GLOB GLOB TABLE TEK_KENTRO
SELECT NO NISA_DL ATHINA ATHINA TABLE FER
SELECT NO NISA_DL ATHINA ATHINA TABLE PCM
SELECT NO NISA_DL ANAT ANAT TABLE FER
SELECT NO NISA_DL ANAT ANAT TABLE PCM
SELECT NO NISA_DL PATRA PATRA TABLE FER
SELECT NO NISA_DL PATRA PATRA TABLE PCM
SELECT NO NISA_DL PRS PRS TABLE PCM
SELECT NO NISA_DL ANMAK ANMAK TABLE FER
SELECT NO NISA_DL GATH GATH TABLE FER
SELECT NO NISA_DL GATH GATH TABLE PCM
SELECT NO NISA_DL LAM LAM TABLE FER
SELECT NO NISA_DL LAM LAM TABLE PCM
SELECT NO NISA_DL HR HR TABLE FER
SELECT NO NISA_DL HR HR TABLE PCM
SELECT NO NISA_DL NISIA NISIA TABLE FER
SELECT NO NISA_DL ANTHES ANTHES TABLE FER
SELECT NO NISA_DL ANTHES ANTHES TABLE PCM
SELECT NO NISA_DL LRS LRS TABLE PCM
SELECT NO NISA_DL KZN KZN TABLE FER
SELECT NO NISA_DL KZN KZN TABLE PCM
WRITE NO NISA_DL SYS SYS DIRECTORY DMPDIR
READ NO NISA_DL SYS SYS DIRECTORY DMPDIR
SELECT NO NISA_DL GLOB GLOB VIEW ROOTALL
