Home » SQL & PL/SQL » SQL & PL/SQL » Unexpected Grants To Public
Unexpected Grants To Public [message #685447] Wed, 12 January 2022 01:20 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7892
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 #685450 is a reply to message #685447] Wed, 12 January 2022 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68172
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is there a way to find the procdure or package which causes the grant?

No, it may be a command from a client tool or a program.

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:
SQL> create or replace trigger trg_grant
  2  before grant on database
  3  disable
  4  declare
  5    name_list ora_name_list_t;
  6    n         binary_integer;
  7  begin
  8    n := ora_grantee (name_list);
  9    for i in 1..n loop
 10      if name_list(i) = 'PUBLIC' then
 11        raise_application_error(-20000, 'GRANT to PUBLIC is not allowed');
 12      end if;
 13    end loop;
 14  end;
 15  /

Trigger created.

SQL> alter trigger trg_grant enable;

Trigger altered.

SQL> grant select on t to scott;

Grant succeeded.

SQL>  grant select on t to public;
 grant select on t to public
                 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: GRANT to PUBLIC is not allowed
ORA-06512: at line 8

Re: Unexpected Grants To Public [message #685451 is a reply to message #685449] Wed, 12 January 2022 03:21 Go to previous message
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.
Previous Topic: Oracle generate from-to DATE ranges and group by
Next Topic: PL/SQL escaping variable with a single quote
Goto Forum:
  


Current Time: Thu May 26 23:00:13 CDT 2022