CREATE ANY CONTEXT privilege [message #666082] |
Thu, 12 October 2017 08:02 |
John Watson
Messages: 8962 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 #666093 is a reply to message #666082] |
Fri, 13 October 2017 02:44 |
John Watson
Messages: 8962 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 |
|
Michel Cadot
Messages: 68729 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #666144 is a reply to message #666139] |
Tue, 17 October 2017 08:57 |
|
Michel Cadot
Messages: 68729 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|