CODEWITHSUNDEEP
databaseoracle-databaseprivileges
user2671057
user2671057

Reputation: 1533

Prevent privileges for user

Is there a way to prevent (revoke) privileges like:

create table, create package, create function and so on,

from the user in it's own schema, but allow him to do those actions for another user.

exmaple:

-- connected as DP1

-- will raise an exception
create table dp1.tst (id number);


-- no exception
create table dp2.tst (id number);

Thank you.

Upvotes: 0

Views: 97

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

In general this is not possible.

You can grant privilege like GRANT CREATE TABLE TO DP1 which permits user DP1 to create tables in his own schema.

Or you can grant privilege like GRANT CREATE ANY TABLE TO DP1 which permits user DP1 to create tables in any schema - which includes his own schema, of course.

One solution could be a procedure like this:

create or replace procedure DP2.create_table(ddl in varchar2) as

begin
   if regexp_like(ddl, '^CREATE TABLE DP2.', 'i') then
      execute immediate ddl;
   end if; 
end;
/

grant execute on DP2.create_table to DP1;

However, I consider this as an ugly workaround. The execute immediate ddl creates potential security flaws and the procedure is error prone.

Another approach could be a Database Trigger:

GRANT CREATE ANY TABLE TO DP1;

CREATE OR REPLACE TRIGGER CREATE_TABLE_CHECK
   BEFORE CREATE ON DATABASE 
BEGIN
   IF ora_login_user = 'DP1' THEN
       IF NOT (ora_dict_obj_owner = 'DP2' AND ora_dict_obj_type = 'TABLE') THEN
          RAISE_APPLICATION_ERROR(-20001, 'You are permitted only to create TABLES in schema "DP2"');
       END IF;
   END IF;
END;

But you should better review your design requirements.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

You can revoke those privileges from DP1, but then allowing them to manage objects under DP2 is messy, as @Wernfried explained. You could also use the CREATE ANY privileges with a DDL trigger that errors for DDL run against the DP1 schema, but that's even messier, and ANY privileges should really be avoided for general use as they are so powerful.

You could set up proxy authentication instead:

alter user DP2 grant connect through DP1;

then instead of DP1 connecting as say:

sqlplus dp1/dp1passwd@db

then can connect as:

sqlplus dp1[dp2]/dp1passwd@db

They do not need to know the account password for DP2 - they still authenticate using their own account password. The pass-through proxy mechanism means that they are now in a session as DP2, to all intents and purposes. Any DDL will be against DP2's schema, and and the audit trail can show that the actions were performed via a proxy and who the real user (i.e. DP1) was. They will have the roles and privileges of DP2, so they will be able to manage objects in that schema, even if they have no create privileges on their own account.

They won't even be able to see anything in their own DP1 schema (unless they've granted privileges to DP2); but as they don't own anything in this scenario, that's a bit of a moot point.

This isn't just available from SQL*Plus of course, you can use it over OCI, JDBC etc.

Read more.

Upvotes: 0

Related Questions