Home » RDBMS Server » Security » CREATE ANY CONTEXT privilege (DB any release)
CREATE ANY CONTEXT privilege [message #666082] Thu, 12 October 2017 08:02 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Contexts aren't schema objects, so there is no privilege CREATE CONTEXT, only CREATE ANY CONTEXT. Usually the ANY privileges need to be granted with care, but would there be any risk in granting CREATE ANY CONTEXT to a large group of users? The packages controlling the contexts would be created by each user in their own schemas, so no problem there.

Thank you for any insight.
Re: CREATE ANY CONTEXT privilege [message #666090 is a reply to message #666082] Thu, 12 October 2017 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

One can kidnap the context of another one.

Re: CREATE ANY CONTEXT privilege [message #666091 is a reply to message #666090] Thu, 12 October 2017 15:43 Go to previous messageGo to next message
Alien
Messages: 289
Registered: June 1999
Senior Member
Hi,

maybe Michel is talking about the same, but my fear would be that users would overwrite already existing contexts. Contexts are global entities, so you should control them centrally. Any user with CREATE ANY CONTEXT can destroy the application using the context.

Regards,

Arian
Re: CREATE ANY CONTEXT privilege [message #666092 is a reply to message #666091] Fri, 13 October 2017 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, this is what I was thinking, one can replace another one context package to its own and, more than destroy the application, depending what the context is made for, gain access to some data it should not see or modify.

Re: CREATE ANY CONTEXT privilege [message #666093 is a reply to message #666082] Fri, 13 October 2017 02:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. Is this the problem:
orclx> conn system/oracle
Connected.
orclx> drop user jw;
drop user jw
          *
ERROR at line 1:
ORA-01918: user 'JW' does not exist


orclx> grant create session,create any context to jw identified by jw;

Grant succeeded.

orclx> create context system_context using system_package;

Context created.

orclx> conn jw/jw
Connected.
orclx> create or replace context system_context using jw_package;

Context created.

orclx>
In that example, jw will have broken system's application. So I guess I'll have to either create the contexts myself (which I really don't want to do) or perhaps just audit CREATE ANY CONTEXT.

Thanks again.
Re: CREATE ANY CONTEXT privilege [message #666095 is a reply to message #666093] Fri, 13 October 2017 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Depending on your actual needs, the best way, I think, is to create a "container account" (an account that contains objects but is locked and with an invalid password to prevent from any connection). This account will be granted CREATE ANY CONTEXT and contain a procedure (say CREATE_CONTEXT) taking a package as a parameter and, maybe if the context name is not standardized, a context name and will create a context verifying the passed context does not exist or is associated to a package owned by the caller, and maybe other verification you need to do to make it safe. Then grant EXECUTE on this procedure to the application accounts that require it.

[Updated on: Fri, 13 October 2017 10:00]

Report message to a moderator

Re: CREATE ANY CONTEXT privilege [message #666098 is a reply to message #666095] Fri, 13 October 2017 16:04 Go to previous messageGo to next message
Alien
Messages: 289
Registered: June 1999
Senior Member
I agree with Michel on his last post. Even though I think it is a very forced solution.
Can you explain why the users need to create multiple contexts? In general I would expect one, maybe two contexts to be created for an application. So I can't understand the need to create multiple contexts by multiple users.

Maybe if you can explain your business needs, we can think of a better solution.

Regards,

Arian
Re: CREATE ANY CONTEXT privilege [message #666107 is a reply to message #666098] Mon, 16 October 2017 02:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
It's a database used for hosting applications built and managed by several clients. We provide the server, the DB licence, all the admin (you get ME as your DBA - that must be worth paying for Smile ) and you get a schema and an APEX workspace. In an ideal world one would use Enterprise Edition and the Multitenant option to isolate the clients, but this machine is licenced for Standard Edition only.
It isn't a problem - having to control any creation of contexts is a bit of a pain hassle, but it won't happen very often.
Re: CREATE ANY CONTEXT privilege [message #666139 is a reply to message #666107] Tue, 17 October 2017 06:52 Go to previous messageGo to next message
Alien
Messages: 289
Registered: June 1999
Senior Member
Ah, I see.
Michel's solution should help you then.
I'm wondering if you can use a DDL trigger (pre create) to let the transaction fail if certain conditions are met (Context already exists). But I don't have the chance to test this now.

Regards,

Arian
Re: CREATE ANY CONTEXT privilege [message #666144 is a reply to message #666139] Tue, 17 October 2017 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace trigger trg_pre_ddl before ddl on database
  2  declare
  3    dummy pls_integer;
  4  begin
  5    if ora_sysevent = 'CREATE' and ora_dict_obj_type = 'CONTEXT' then
  6      begin
  7        select 1 into dummy from dba_context where namespace = ora_dict_obj_name;
  8        raise_application_error (-20000, 'Context already exist, you can''t overwrite it');
  9      exception when no_data_found then null;
 10      end;
 11    end if;
 12  end;
 13  /

Trigger created.

SQL> drop context test_ctx;

Context dropped.

SQL> create or replace context test_ctx using pkg;

Context created.

SQL> /
create or replace context test_ctx using pkg
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Context already exist, you can't overwrite it
ORA-06512: at line 7

[Updated on: Tue, 17 October 2017 09:01]

Report message to a moderator

Re: CREATE ANY CONTEXT privilege [message #666160 is a reply to message #666144] Tue, 17 October 2017 15:04 Go to previous message
Alien
Messages: 289
Registered: June 1999
Senior Member
Thanks Michel,

that's exactly what I had in mind.

Regards,

Arian
Previous Topic: PDB SYSOPER/SYSDBA privileges
Next Topic: Service-Level ACLs for TCP Protocol
Goto Forum:
  


Current Time: Thu Mar 28 04:26:12 CDT 2024