| Unable To Find the Privilege [message #690436] |
Fri, 05 December 2025 10:48  |
wtolentino
Messages: 429 Registered: March 2005
|
Senior Member |
|
|
Recently, we observed that some accounts have been modifying a table that it does not own. This USER01 was able to alter a table it does not own as seen on the unified_audit_trail:
DBUSERNAME: USER01
ACTION_NAME: ALTER TABLE
OBJECT_SCHMA: APPS
OBJECT_NAME: POSTING_DQ
SQL_TEXT:
ALTER TABLE APPS.POSTING_DQ MODIFY REC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 999999999999 CACHE 1000 NOCYCLE)

SQL> select * from dba_sys_privs where grantee = 'USER01';
no rows selected
SQL>
SQL> select 'USER01' grantee, drp.granted_role , dsp.privilege
2 from dba_role_privs drp join dba_sys_privs dsp on (dsp.grantee = drp.granted_role)
3 where drp.grantee = 'USER01';
GRANTEE GRANTED_ROLE PRIVILEGE
--------------- ------------------------- -------------------------
USER01 DEV_ROLE_00 CREATE DATABASE LINK
USER01 NYS_CONNECT_ONLY CREATE SESSION
USER01 NYS_CONNECT_ONLY ALTER SESSION
SQL>
SQL> select grantee, 'APPS' owner, 'POSTING_DQ' table_name, 'APPS' grantor, privilege, grantable, type
2 from dba_tab_privs where table_name = 'POSTING_DQ';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA TYPE
--------------------- ---------- --------------- ---------- ---------- --- ----------
DELETE_ROLE_00 APPS POSTING_DQ APPS DELETE NO TABLE
INSERT_ROLE_00 APPS POSTING_DQ APPS INSERT NO TABLE
READ_ROLE_00 APPS POSTING_DQ APPS SELECT NO TABLE
UPDATE_ROLE_00 APPS POSTING_DQ APPS UPDATE NO TABLE
SQL>
Please advise how to find the privilege that allows USER01 to modify a table that it does not own. Also, note that the user name, table name, and roles are not the actual names. I have replaced them for security purposes.
Thank you.
[Updated on: Fri, 05 December 2025 10:58] Report message to a moderator
|
|
|
|
| Re: Unable To Find the Privilege [message #690437 is a reply to message #690436] |
Fri, 05 December 2025 12:24   |
John Watson
Messages: 8996 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
|
Your queries are obscuring so much that I can't understand what they are actually showing. However, you have Enterprise Edition which means that you have access to Privilege Analysis. Are you familiar with this? You use dbms_privilege_capture to monitor what you are interested in, and then generate reports on what privileges were used by whom to do what. Including the path, or roles, by which the session gained the privilege. I may have sone demos of using it somewhere.
|
|
|
|
|
|
|
|
|
|
| Re: Unable To Find the Privilege [message #690454 is a reply to message #690436] |
Fri, 16 January 2026 00:26  |
 |
ZeeshanAwan
Messages: 4 Registered: November 2017
|
Junior Member |
|
|
Could you please check that query was successful?
someone can run any query but we have to see it was successful or return error of privilege? for example if someone have create session privilege then user can run grant DBA to <username> but it will not work. So please check that query was successful or not?
[Updated on: Fri, 16 January 2026 00:44] Report message to a moderator
|
|
|
|