Is it possible to stop Oracle DB grants for specific user?

Question

My company uses Oracle 12c database. We have several users defined in the database and have a few roles defined as well. My preference is for our development team to grant access to new tables/procedures via roles rather than granting directly to the users.

For instance lets say we have user USER1 and a role READ_ROLE granted to USER1. If a developer creates a new table TABLE1

• They should not be allowed to grant read access on TABLE1 directly to USER1
• Instead they should grant read access to READ_ROLE which indirectly gives the same access to USER1

Unfortunately we have team with 40+ developers and over time folks have been assigning the grants directly to users rather than roles. With 100k+ grants, a few hundred tables, and dozens of users it becomes hard to manage which is why I prefer to use roles. Also a bit hard to manually police it.

Is there a systematic way with oracle DB to stop new grants from being allowed for a user? Maybe something that causes an exception when someone tries to add new grants?

Answer 1

As pointed out by APC you should not permit developers to grant anything directly on production database. This might be OK when you have a small application with 3-4 developers only. In your scale this is really dangerous and you as a vendor cannot ensure any quality/security on your application.

Anyway, you can use a system trigger like this:

CREATE OR REPLACE TRIGGER grant_trigger
  BEFORE GRANT ON {your_schema}.SCHEMA

DECLARE

  user_list ora_name_list_t;
  number_of_grantees PLS_INTEGER;
  res INTEGER

BEGIN
  IF (ora_sysevent = 'GRANT') THEN
    number_of_grantees := ora_grantee(user_list);
    SELECT COUNT(*) 
    INTO res
    FROM DBA_USERS
    WHERE USERNAME MEMBER OF user_list;

    IF res > 0 THEN
       RAISE_APPLICATION_ERROR(-20010, 'It is not permitted to GRANT directly to USER. Use ROLE instead');
    END IF;
  END IF;
END;

You may add additional conditions, e.g.

DECLARE

  user_list ora_name_list_t;
  number_of_grantees PLS_INTEGER;
  res INTEGER

  privilege_list ora_name_list_t;
  number_of_privileges PLS_INTEGER;

BEGIN
  IF (ora_sysevent = 'GRANT') THEN
    number_of_grantees := ora_grantee(user_list);
    SELECT COUNT(*) 
    INTO res
    FROM DBA_USERS
    WHERE USERNAME MEMBER OF user_list;

    IF res > 0 THEN
       RAISE_APPLICATION_ERROR(-20010, 'It is not permitted to GRANT directly to USER. Use ROLE instead');
    END IF;
  END IF;

    number_of_privileges := ora_privilege_list(privilege_list);
    IF 'DELETE' MEMBER OF privilege_list THEN
       RAISE_APPLICATION_ERROR(-20010, 'You must not grant "DELETE"');
       -- Other privileges as SELECT, INSERT, UPDATE would be permitted.
    END IF;

    IF (ora_dict_obj_type = 'TABLE') THEN
       RAISE_APPLICATION_ERROR(-20010, 'You must not grant anything to a TABLE');
       -- Other objects like VIEW or PROCEDURE would be permitted.
    END IF;

END;

See SYSTEM TRIGGER for more details. Ensure your developers do not have ADMINISTER DATABASE TRIGGER or CREATE ANY TRIGGER privilege, otherwise they can just disable the trigger and thus bypass it.