Direct privilege grant vs ALL PRIVILEGES vs roles vs PUBLIC

Question

1/ To my knowledge, direct privilege grant and ROLE grant and PUBLIC grant are independent, meaning all 3 can carry themselves the same privilege. Revoking from one does not interfere with that privilege still staying with the user. Meaning if we

GRANT SELECT ON T TO userA
GRANT SELECT ON T TO roleA; GRANT roleA to userA
GRANT SELECT on T TO PUBLIC

Revoking one or two of the 3 Grants leaves the userA with the SELECT privilege. How about ALL PRIVILEGES, does it overlap with any of these 3 zones ? If we have the 3 grants above and the following

GRANT ALL PRIVILEGES on T to userA;

and then we

REVOKE ALL PRIVILEGES on T to userA;

which one of the 3 grants will be additionally removed ? Does it behave the same for system privilege and object privileges ?

2/ There is a GRANT ANY PRIVILEGE and GRANT ALL PRIVILEGE*S*. Are they the same ?

Patrick Marchand · Answer 1 · 2016-04-26T02:30:03.560

As per the docs "Oracle Database provides the ALL PRIVILEGES shortcut for granting all the system privileges listed in Table 18-1, except the SELECT ANY DICTIONARY privilege." Your examples are not system privileges so there is no overlap. https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm

As for GRANT ANY PRIVILEGE, this grants the user the ability to in turn grant a system privilege to other users. For example:

dba@db> GRANT GRANT ANY PRIVILEGE to some_user;
grant succeeded

reconnect as some_user. This user can now pass on system grants to some_other_user.

some_user@db> GRANT COMMENT ANY TABLE to some_other_user;
grant succeeded

This seems to be like a privilege you want to use when you want to give a user partial dba privileges and should be used with care.

In contrast ALL PRIVILEGES is more like a macro that grants all of the individual system privileges at once so

GRANT ALL PRIVILEGES to some_user;

would be like running grant statements for all system privileges (and there are many):

GRANT ADVISOR TO some_user;
GRANT ADMINISTER SQL TUNING SET TO some_user;
GRANT ADMINISTER ANY SQL TUNING SET TO some_user;
GRANT CREATE ANY SQL PROFILE TO some_user;
GRANT DROP ANY SQL PROFILE TO some_user;
GRANT ALTER ANY SQL PROFILE TO some_user;
etc...

EDIT: Further in the link above, under the grant_object_privileges section there is also:

ALL [PRIVILEGES]

Specify ALL to grant all the privileges for the object that you have been granted with the GRANT OPTION. The user who owns the schema containing an object automatically has all privileges on the object with the GRANT OPTION. The keyword PRIVILEGES is provided for semantic clarity and is optional.

If you do something like

GRANT ALL PRIVILEGES on some_table TO some_user;

that user gets all of these table privileges (at least this is the list I get in 12c):

FLASHBACK
DEBUG
QUERY REWRITE
ON COMMIT REFRESH
READ
REFERENCES
UPDATE
SELECT
INSERT
INDEX
DELETE
ALTER

For sequences you get:

SELECT
ALTER

(And other object types will have their own list.)

So, it's just like ALL PRIVILEGES for system privileges in that it's a shortcut for granting all of the object privileges for the type of object specified; there isn't one "ALL PRIVILEGES" privilege that you get. For tables it's like typing in the grant statements for the 12 privileges listed above:

GRANT FLASHBACK on ...
GRANT DEBUG on ...
GRANT QUERY REWRITE on ...

Each one of these privileges can be individually revoked. So if you did:

REVOKE INSERT, UPDATE, DELETE on some_table FROM some_user;

you would still have the 9 other privileges from the above table privileges.

If you use "ALL PRIVILEGES" with REVOKE:

REVOKE ALL PRIVILEGES on some_table FROM some_user;

it will take away whatever table privileges some_user had remaining on some_table in the above list.

So, to make a long story longer, here's a SQL example to show the answer to your original question (the owner of table T is USERT):

SQL> create table t (a varchar2(1));

Table created.

SQL> grant select on t to userA;

Grant succeeded.

SQL> select grantor
  2       , grantee
  3       , table_schema
  4       , table_name
  5       , privilege
  6    from all_tab_privs
  7   where table_name = 'T'
  8   order by grantee;

GRANTOR    GRANTEE    TABLE_SCHEMA TABLE_NAME PRIVILEGE
---------- ---------- ------------ ---------- --------------------
USERT      USERA      USERT        T          SELECT

SQL>
SQL> grant select on t to roleA;

Grant succeeded.

SQL> select grantor
  2       , grantee
  3       , table_schema
  4       , table_name
  5       , privilege
  6    from all_tab_privs
  7   where table_name = 'T'
  8   order by grantee;

GRANTOR    GRANTEE    TABLE_SCHEMA TABLE_NAME PRIVILEGE
---------- ---------- ------------ ---------- --------------------
USERT      ROLEA      USERT        T          SELECT
USERT      USERA      USERT        T          SELECT

SQL> grant select on t to public;

Grant succeeded.

SQL> select grantor
  2       , grantee
  3       , table_schema
  4       , table_name
  5       , privilege
  6    from all_tab_privs
  7   where table_name = 'T'
  8   order by grantee;

GRANTOR    GRANTEE    TABLE_SCHEMA TABLE_NAME PRIVILEGE
---------- ---------- ------------ ---------- --------------------
USERT      PUBLIC     USERT        T          SELECT
USERT      ROLEA      USERT        T          SELECT
USERT      USERA      USERT        T          SELECT

SQL> grant all privileges on t to userA;

Grant succeeded.

SQL> select grantor
  2       , grantee
  3       , table_schema
  4       , table_name
  5       , privilege
  6    from all_tab_privs
  7   where table_name = 'T'
  8   order by grantee;

GRANTOR    GRANTEE    TABLE_SCHEMA TABLE_NAME PRIVILEGE
---------- ---------- ------------ ---------- --------------------
USERT      PUBLIC     USERT        T          SELECT
USERT      ROLEA      USERT        T          SELECT
USERT      USERA      USERT        T          INDEX
USERT      USERA      USERT        T          INSERT
USERT      USERA      USERT        T          ALTER
USERT      USERA      USERT        T          SELECT
USERT      USERA      USERT        T          FLASHBACK
USERT      USERA      USERT        T          DELETE
USERT      USERA      USERT        T          REFERENCES
USERT      USERA      USERT        T          READ
USERT      USERA      USERT        T          ON COMMIT REFRESH
USERT      USERA      USERT        T          QUERY REWRITE
USERT      USERA      USERT        T          DEBUG
USERT      USERA      USERT        T          UPDATE

14 rows selected.

SQL> REVOKE ALL PRIVILEGES on T from userA;

Revoke succeeded.

SQL> select grantor
  2       , grantee
  3       , table_schema
  4       , table_name
  5       , privilege
  6    from all_tab_privs
  7   where table_name = 'T'
  8   order by grantee;

GRANTOR    GRANTEE    TABLE_SCHEMA TABLE_NAME PRIVILEGE
---------- ---------- ------------ ---------- --------------------
USERT      PUBLIC     USERT        T          SELECT
USERT      ROLEA      USERT        T          SELECT

So the REVOKE ALL PRIVILEGES command removed all of the direct grants on table T from userA, but that user would still have SELECT privileges through the grant select to roleA (assuming userA has been granted the role) or the grant select to public.

1.I don't get your first point. There is at least overlapping between _ALL PRIVILEGES on object_ and direct privilege. https://docs.oracle.com/database/121/SQLRF/statements_9021.htm#SQLRF01609 the _Revoking an Object Privilege from a User: Example_ part. 2.GRANT ALL PRIVILEGES does work on objects. — Kenny, Apr 24 '16 at 18:52
1. Sorry, but I don't understand what you're trying to say here. Under Revoking an Object Privilege from a User: Example, it just says "You can grant DELETE, INSERT, READ, SELECT, and UPDATE privileges on the table orders to the user hr with the following statement..." These are not system privileges. System privileges are listed here: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm#BABEFFEE As for 2) What do you mean by "does work on objects"? Can you give an example? — Patrick Marchand, Apr 24 '16 at 19:25
1/You mentioned _Your examples are not system privileges so there is no overlap_. My example is not system privileges, that's right. And there is overlap as per my link: the user is given _ALL PRIVILEGES_ and a _DELETE_ is revoked, still leaving him with other privileges. That speaks for my point that _there is overlapping_. If they are independent, it would happen that the user has _DELETE_ right and _ALL PRIV_ right at the same time, and revoking individually DELETE leaves _ALL PRIV_ intact. — Kenny, Apr 25 '16 at 09:12
2/ _ ALL PRIVILEGES is more like a macro that grants all of the individual system privileges at once_ but there is _ALL PRIVILEGES_ for objects. _GRANT ALL PRIVILEGES ON T To A_ — Kenny, Apr 25 '16 at 09:13
Sorry for my incomplete answer from before. Please see the updated version and let me know if it's what you were looking for. — Patrick Marchand, Apr 26 '16 at 02:31

Direct privilege grant vs ALL PRIVILEGES vs roles vs PUBLIC

1 Answers1