Unexpected Grants To Public [message #685447] |
Wed, 12 January 2022 01:20  |
Gogetter
Messages: 39 Registered: December 2009 Location: Cologne Germany
|
Member |
|
|
Hi all,
for audit reasons we are not allowed to grant rights to Public. Now for a few days there is always a right that is automatically granted.
SELECT * FROM dba_tab_privs WHERE grantee = 'PUBLIC' AND owner = 'XXXXXXXXXX'
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INH
---------- ---------- ------------------------------ ---------- ---------------------------------------- --- --- --- ------------------------ ---
PUBLIC XXXXXXXXXX ST00001VQR0xGSIqbgUwYKF6yqCA= XXXXXXXXXX EXECUTE NO NO NO TYPE NO
Is there a way to find the procdure or package which causes the grant?
If I revoke the right, it will be granted again in the following days with another "Table" Name
thanks for your help
regards
Rudi
|
|
|
Re: Unexpected Grants To Public [message #685449 is a reply to message #685447] |
Wed, 12 January 2022 02:23   |
Frank
Messages: 7897 Registered: March 2000
|
Senior Member |
|
|
I would start by querying dba_objects to see what object type those 'tables' are. That might shed some light on where these grants come from.
Also, it may help to know if your hidden username is an oracle internal account (MDSYS, XDB, etc) or an application schema.
|
|
|
|
Re: Unexpected Grants To Public [message #685451 is a reply to message #685449] |
Wed, 12 January 2022 03:21  |
Gogetter
Messages: 39 Registered: December 2009 Location: Cologne Germany
|
Member |
|
|
Thanks for your answers.
The current DB Version is Version 18.12.0.0.0.
Quote:
I would start by querying dba_objects to see what object type those 'tables' are. That might shed some light on where these grants come from.
Also, it may help to know if your hidden username is an oracle internal account (MDSYS, XDB, etc) or an application schema.
It's an application schema. It's the name of a client so i had to mask it. The object type is "TYPE".
Quote: Your solution is to 1) activate the audit on GRANT (depends on your version you didn't post) and 2) create a DDL trigger on GRANT to forbid the action:
I thinks it's an internal process, which creates a temporary TYPE and grants it to public. This sometime happens by using plsql tables in stored procedures or packages - but I do not know the circumstances.
I'm going to try your solution out, that looks like a workable solution. Since we will be switching to an ADB in the coming weeks, there could be problems there. However, we could already successfully implement a logon trigger in the ADB.
|
|
|