CODEWITHSUNDEEP
databaseoracle-databaseoracle12csql-grant
George
George

Reputation: 1283

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

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

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?

Upvotes: 0

Views: 782

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

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.

Upvotes: 4

Related Questions