Home » RDBMS Server » Server Administration » Unable To Find the Privilege (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
Unable To Find the Privilege [message #690436] Fri, 05 December 2025 10:48 Go to next message
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)

/forum/fa/14871/0/


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 Go to previous messageGo to next message
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 #690438 is a reply to message #690437] Fri, 05 December 2025 13:08 Go to previous messageGo to next message
wtolentino
Messages: 429
Registered: March 2005
Senior Member
First time heard about the dbms_privilege_capture. If you have info and examples could you please share and I appreciate it.
Re: Unable To Find the Privilege [message #690439 is a reply to message #690438] Fri, 05 December 2025 13:25 Go to previous messageGo to next message
John Watson
Messages: 8996
Registered: January 2010
Location: Global Village
Senior Member
Quote:
First time heard about the dbms_privilege_capture
You need to read your New Features manuals Smile

I recorded a lesson on it a few years ago,
https://skillbuilders.com/course/how-to-use-privilege-analysis-secure-oracle-database/lessons/oracle-security-hardening-the-database- with-privilege-analysis/
though it may be a bit too long for the 21st Century Schizoid Man's attention span. Enjoy....
Re: Unable To Find the Privilege [message #690440 is a reply to message #690436] Fri, 05 December 2025 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Maybe he does it using a privileged procedure he has been granted EXECUTE privilege.

Re: Unable To Find the Privilege [message #690454 is a reply to message #690436] Fri, 16 January 2026 00:26 Go to previous message
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

Previous Topic: Unable to connect Forms 6i and Oracle 12c 12.2.0.1.0
Next Topic: Faster apply of an RU
Goto Forum:
  


Current Time: Sun Jan 18 01:31:07 CST 2026