Wednesday, July 11, 2012

DB Roles - it's worth repeating ...

The Oracle RDBMS Server has a GRANT command that permits assignment of privileges to a user or a role.

And it has a REVOKE command that removes privileges from a user or a role.  That command simply removes the privilege from the list currently available to the user or role.

However, the Oracle database does not have a 'DENY' command that could be used to store a REVOKE in a role, allowing the removal of a privilege based on the addition of a role.

A lot of [new] DBAs believe that revoking a privilege from a role will remove that privilege from a user who is granted that role. 

Test case (as DBA):

CREATE USER hans IDENTIFIED BY hans;
CREATE USER test IDENTIFIED BY test;
GRANT CREATE SESSION TO hans, test;

GRANT RESOURCE TO test;
CREATE TABLE test.testtab (x NUMBER);
INSERT INTO test.testtab VALUES (1);
COMMIT;

CREATE ROLE A;
GRANT SELECT, UPDATE ON test.testtab TO a;

CREATE ROLE B;
GRANT SELECT, UPDATE, DELETE ON test.testtab TO b;

GRANT a,b TO hans;

CONNECT hans/hans
SELECT X FROM test.testtab;
UPDATE test.testtab SET x=2;
COMMIT;

CONNECT {dba}
REVOKE UPDATE ON test.testtab FROM b;

CONNECT hans/hans
SELECT X FROM test.testtab;
UPDATE test.testtab SET x=3;
COMMIT;

If the revoke was stored in the role, then the update to set x to 3 would fail.

This question happens in nearly every DB Admin Workshop I've held or watched. 

2 comments:

Jeff Kemp said...

There's a good reason why this is not the case - if there was such a thing as a "DENY" for a role, it would lead to the strange fact that *revoking* a role (e.g. b, in this case) from a user could suddenly *increase* their privileges, rather than reduce, as one would expect.

Fuzzy GreyBeard said...

While I see your point, I'm not so sure that it is a 'good reason'. If implemented, it would simply be yet another tool, and a professional needs to learn how the tools work and what the side effects of their tools are.

My point, though, is that it is NOT implemented.

However, a lot of [new] DBAs believe that the revoke on a role either behaves as a deny or that it actually drills through to the users at revoke time. (The latter implying that they might believe associating a role to a user modifies the user's priv list at grant time.)