Grant create any trigger vs grant create trigger

Question

In Oracle you can grant system privileges like

GRANT CREATE TRIGGER TO MY_USER;

But you can as well grant privileges this way

GRANT CREATE ANY TRIGGER TO MY_USER;

As system privileges are system-wide, where is the difference between the 2 statements above. Does the additional ANY-keyword grant anything else more than system? If I add a Grant ... ON SCHEMA ... it's no system privilege anymore, is it?

Assumption is that there are multiple schemas/objects in the database from different users one cannot access without these privileges.

EDIT:

SELECT *
FROM DBA_SYS_PRIVS
WHERE grantee = 'MY_USER';

returns

GRANTEE      PRIVILEGE                              
------------ -------------
MY_USER      CREATE ANY TRIGGER
MY_USER      CREATE TRIGGER

(I omitted the columns ADMIN_OPTION and COMMON)

And the result is the same when querying this with MY_USER, MY_USER2 or any other user. I see no connection to a schema here. And it is also possible to only have the CREATE ANY TRIGGER-privilege.

GRANT CREATE TRIGGER TO MY_USER; Allows MY_USER to create TRIGGERs in his schema. GRANT CREATE ANY TRIGGER TO MY_USER; Allows MY_USER to create TRIGGERs in other schemas also. — ArtBajji, Mar 13 '18 at 13:06

score 5 · Accepted Answer · answered Mar 13 '18 at 22:25

5

In most cases, the trigger owner is also the owner of the table (or view) on which the trigger is based. In those cases, the table owner, with CREATE TRIGGER can create create triggers on their own table.

CREATE ANY TRIGGER allows the user to create a trigger owned by any user on any table. It is a big security hole because they can create a trigger owned by a privileged user on a table that they own or can insert into. Because they can insert into that table, they can force the trigger to execute and the trigger executes with the privileges of the trigger owner. The effect is that a user with CREATE ANY TRIGGER privilege can create and execute code as a privileged user (similar to having CREATE ANY PROCEDURE plus EXECUTE ANY PROCEDURE).

Limit to as few as people as possible and audit appropriately.

answered Mar 13 '18 at 22:25

Gary Myers

34,963
3
49
74

But if MY_USER has no system privilege CREATE TRIGGER it can still create triggers on own tables. What's the use of the system privilege CREATE TRIGGER then? (`SELECT * FROM DBA_SYS_PRIVS` lists no grants for create trigger but other grants like create view) – Chrᴉz remembers Monica Mar 14 '18 at 14:51
3

Documentation says "To create a trigger in your schema on a table in your schema or on your schema (SCHEMA), you must have the CREATE TRIGGER system privilege." Bear in mind that could be via a role so check whether it is showing up in select * from session_privs https://docs.oracle.com/database/121/LNPLS/create_trigger.htm#LNPLS01374 – Gary Myers Mar 14 '18 at 22:04
That explains it. I tought grant by roles would simply somehow be added to the other views. Learned something new again. Thank you! – Chrᴉz remembers Monica Mar 15 '18 at 08:21

score 2 · Answer 2 · answered Mar 13 '18 at 13:11

The first statements grants the right to create triggers in the schema of MY_USER. The owner will always by MY_USER.

The second statements grants the right to create triggers in ANY schema. The owner of the trigger can then be any user.

The last option is usually not wanted because it gives user MY_USERS the possibility to corrupt the data model.

Grant create any trigger vs grant create trigger

2 Answers2

Linked