Revoke ANY Privileges Oracle

Question

Hi I have this question.

Is posibble GRANT ANY privileges excluding some tables of the same schema.

For Example:

 EXECUTE IMMEDIATE 
     'CREATE USER  USREJECUTA_SUI_ABAS
      IDENTIFIED BY VALUES ''test''
      DEFAULT TABLESPACE USERS
      TEMPORARY TABLESPACE TEMP
      PROFILE DEFAULT
      ACCOUNT UNLOCK';
     -- 2 Roles for USREJECUTA_SUI 
   EXECUTE IMMEDIATE 'GRANT CONNECT TO USREJECUTA_SUI_ABAS';
   EXECUTE IMMEDIATE 'GRANT RESOURCE TO USREJECUTA_SUI_ABAS';
   EXECUTE IMMEDIATE 'ALTER USER USREJECUTA_SUI_ABAS DEFAULT ROLE ALL';
     -- 1 System Privileges for USREJECUTA_SUI 
   EXECUTE IMMEDIATE 'GRANT SELECT ANY TABLE TO USREJECUTA_SUI_ABAS';          
   EXECUTE IMMEDIATE 'GRANT UPDATE ANY TABLE TO USREJECUTA_SUI_ABAS';  
   EXECUTE IMMEDIATE 'GRANT INSERT ANY TABLE TO USREJECUTA_SUI_ABAS';  
   EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO USREJECUTA_SUI_ABAS';

Now revoke the permissions from some tables

REVOKE INSERT,UPDATE ON VELITTDA.TAPROVEEDORESXPAIS      FROM USREJECUTA_SUI';

but the system launch this error:

ORA-01927: cannot REVOKE privileges you did not grant.

Do you kow some wat to do this? I will apreciate a lot your help.

score 1 · Answer 1 · answered Sep 18 '14 at 01:02

1

No, you can't. If you give someone SELECT ANY TABLE, you've given them the ability to query any table in the database. You can't revoke object-level privileges when you haven't granted object-level privileges.

The proper approach is almost always to create a role that has object-level privileges on the actual set of objects that the user needs access to. Grant the role to the user (and any other users that need a similar set of privileges). The various ANY roles are really only appropriate for folks like DBAs.

answered Sep 18 '14 at 01:02

Justin Cave

227,342
24
367
384

Hi Justin I´m reading about the use of Roles, but what If new table s are added to the schema, Do you Know some way to GRANT the privileges for this new objects in automatic? – OAlavez Sep 18 '14 at 15:20
@OAlavez - When new tables are added, that should be going through change control. That change control should include the grants those tables will need (otherwise how will you know whether the new table is one of the tables that you'd like to exclude). If you really, really want to, you can create a trigger that grants privileges but I would strongly advise against it (http://stackoverflow.com/questions/9262539/how-do-i-create-a-oracle-trigger-that-grants-permissions/9263285#9263285) – Justin Cave Sep 18 '14 at 18:08
Thanks by the advice, I'll do this giving the GRANTS for each table in the change control, aprecciate your help. – OAlavez Sep 18 '14 at 18:33

score 0 · Answer 2 · answered Sep 18 '14 at 01:17

Granting the SELECT ANY TABLE (or any other ANY TABLE) privilege is generally the wrong thing to do and is almost as bad as granting DBA to arbitrary schema users.

If you are trying to avoid issuing a lot of grant statements, then use the simple trick of generating your DDL from the data dictionary.

set head off
set pagesize 0
spool grant_foo.sql

select 'GRANT SELECT ON '||table_name||' TO FOO_ROLE;'
from all_tables where owner = 'FOO'
order by table_name
;

spool off

Then edit grant_foo.sql as needed before executing.

Revoke ANY Privileges Oracle

2 Answers2