Home » SQL & PL/SQL » SQL & PL/SQL » create or replace context abc Using package_name (oracle 10g,windows XP)
| create or replace context abc Using package_name [message #606507] |
Sat, 25 January 2014 01:12  |
 |
faizan_amini
Messages: 11
Registered: January 2014
Location: India
|
Junior Member |
|
|
I have created one context in oracle data base through "create or replace context abc Using package_name" command and given them privileges and after that its working fine but when i am login after starting the server that time we have to give privileges again because if i not give privileges again so at the time when i m firing packge.procedure('entity_code', 'AD');.
then time i am getting error like "INSUFFICIENT PRIVILEGES"
Please help me out..
|
|
|
|
|
|
| Re: create or replace context abc Using package_name [message #606658 is a reply to message #606509] |
Tue, 28 January 2014 02:06  |
|
faizan_amini
Messages: 11
Registered: January 2014
Location: India
|
Junior Member |
|
|
I have created a SYS_CONTEXT like namespace(ABC) in schema(SCOTT) using package PACK1..
NAMESPACE SCHEMA PACKAGE TYPE
ABC SCOTT LHS_UTILITY ACCESSED LOCALLY
if i am creating another SYS_CONTEXT with same namespace(ABC) with different schema(XYZ) useing
same PACK1 like ..
NAMESPACE SCHEMA PACKAGE TYPE
ABC XYZ LHS_UTILITY ACCESSED LOCALLY
Then my previous schema SCOTT overwrite with new XYZ..due to this i am getting error while using SYS_CONTEXT ABC
in SCOTT schema because SCOTT is already overwrite with XYZ.
NAMESPACE is unique for all schema ?
[Updated on: Tue, 28 January 2014 02:49] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: create or replace context abc Using package_name [message #606692 is a reply to message #606667] |
Tue, 28 January 2014 04:01 |
|
faizan_amini
Messages: 11
Registered: January 2014
Location: India
|
Junior Member |
|
|
1 - first i created one context
********************************
create or replace context LHS_VAR Using package_abc
2- Then fire below command
****************************
select * from dba_context;-- below is output
-------------------------------------------------------
NAMESPACE # SCHEMA # PACKAGE # TYPE
-------------------------------------------------------
LHS_VAR # SCOTT # package_abc # ACCESSED LOCALLY
-------------------------------------------------------
3- grant to scott
********************
Grant Create ANY CONTEXT TO scott;
GRANT Execute On DBMS_SESSION To scott;
GRANT CREATE TRIGGER TO scott;
3- execute set context command througe procedure(working fine in SCOTT)
************************************************************
begin ---in scott schema it is wokring fine
package_abc.set_var_data('from_date', '01-apr-2013');
package_abc.set_var_data('to_date', '22-jan-2014');
end;
4- creation of context in ABC schema (working fine in ABC but getting error in SCOTT
*********************************************************************************
When i am creating same context LHS_VAR in another schema like ABC
with "create or replace context LHS_VAR Using package_abc" command and grant them
all above previlages then in ABC it is working fine but in SCOTT i am getting error
like
Error
ORA-01031 : insufficient privileges
ORA-06512 : at "SYS.DBMS_SESSION"
ORA-06512 : at "SCOTT.PACKAGE_ABC"
view program sources of error stack?
-----------------------------------------
[Updated on: Tue, 28 January 2014 04:02] Report message to a moderator |
|
|
|
|
|
|
|
| Re: create or replace context abc Using package_name [message #606707 is a reply to message #606703] |
Tue, 28 January 2014 05:14 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1/ Don't do this with SYS.
- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
(no flashback query for it, no read only transactions, no triggers, no consistency...)
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.
NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.
2/ Did you do everything with the same user (SYS)? It is not clear from your post. When you change connection, show us.
3/ We have not LHS_UTILITY so we can't reproduce what you have.
4/ What is the purpose of "GRANT CREATE TRIGGER TO SARLERP;" and the relation with the question?
...
|
|
|
|
| Re: create or replace context abc Using package_name [message #606722 is a reply to message #606707] |
Tue, 28 January 2014 06:33 |
|
faizan_amini
Messages: 11
Registered: January 2014
Location: India
|
Junior Member |
|
|
1- What ever you explained is absolutely correct.it will be in my mind.
2- No i am using SYSDBA when i am firing only GRANT COMMAND.
3- LHS_UTILITY is package name of my DATA BASE.
4- Please ignore :GRANT CREATE TRIGGER TO SARLERP;" command
I just want to know that can we create TWO CONTEXT with same name for two different schema like below
connect to SCOTT user
create or replace context CONTEXT_ABC using PACKAGE_XYZ
connect to ABC user
create or replace context CONTEXT_ABC using PACKAGE_XYZ
[Updated on: Tue, 28 January 2014 06:38] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: create or replace context abc Using package_name [message #606963 is a reply to message #606736] |
Fri, 31 January 2014 07:31 |
|
faizan_amini
Messages: 11
Registered: January 2014
Location: India
|
Junior Member |
|
|
Getting error while setting sys_context value for second user(schema).Now It is working for only one user(Schema).
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as scott
SQL>
SQL> CREATE OR REPLACE CONTEXT App_Ctx using My_pkg
2 ACCESSED GLOBALLY;
Context created
SQL>
SQL> CREATE OR REPLACE PACKAGE my_pkg IS
2
3 PROCEDURE set_session_id(p_session_id NUMBER);
4 PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
5 PROCEDURE close_session(p_session_id NUMBER);
6
7 END;
8 /
Package created
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY my_pkg IS
2
3 g_session_id NUMBER;
4
5 PROCEDURE set_session_id(p_session_id NUMBER) IS
6 BEGIN
7 g_session_id := p_session_id;
8 dbms_session.set_identifier(p_session_id);
9 end set_session_id;
10 --===============================================
11 PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
12 BEGIN
13 dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id);
14 END set_ctx;
15 --===============================================
16 PROCEDURE close_session(p_session_id number ) IS
17 BEGIN
18 dbms_session.set_identifier(p_session_id);
19 dbms_session.clear_identifier;
20 END close_session;
21 --===============================================
22 END;
23 /
Package body created
SQL>
SQL> begin
2 my_pkg.set_ctx('Var1', 'Val1');
3 my_pkg.set_ctx('Var2', 'Val2');
4 end;
5 /
PL/SQL procedure successfully completed
SQL>
SQL> SELECT sys_context('app_ctx', 'var1') var1,
2 sys_context('app_ctx', 'var2') var2
3 FROM DUAL;
VAR1 VAR2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
Val1 Val2
SQL> connect
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as ABC_USER
SQL>
SQL> CREATE OR REPLACE PACKAGE my_pkg IS
2
3 PROCEDURE set_session_id(p_session_id NUMBER);
4 PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
5 PROCEDURE close_session(p_session_id NUMBER);
6
7 END;
8 /
Package created
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY my_pkg IS
2
3 g_session_id NUMBER;
4
5 PROCEDURE set_session_id(p_session_id NUMBER) IS
6 BEGIN
7 g_session_id := p_session_id;
8 dbms_session.set_identifier(p_session_id);
9 end set_session_id;
10 --===============================================
11 PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
12 BEGIN
13 dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id);
14 END set_ctx;
15 --===============================================
16 PROCEDURE close_session(p_session_id number ) IS
17 BEGIN
18 dbms_session.set_identifier(p_session_id);
19 dbms_session.clear_identifier;
20 END close_session;
21 --===============================================
22 END;
23 /
Package body created
SQL>
SQL> begin
2 my_pkg.set_ctx('Var1', 'Val1');
3 my_pkg.set_ctx('Var2', 'Val2');
4 end;
5 /
begin
my_pkg.set_ctx('Var1', 'Val1');
my_pkg.set_ctx('Var2', 'Val2');
end;
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "ABC_USER.MY_PKG", line 13
ORA-06512: at line 3
SQL>
|
|
|
|
| Re: create or replace context abc Using package_name [message #607054 is a reply to message #606963] |
Sat, 01 February 2014 15:44 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
A wild guess (Sorry I don't have environment)
1. Change package name
2. In ABC_USER, is below query giving result:
SQL>
SQL> SELECT sys_context('app_ctx', 'var1') var1,
2 sys_context('app_ctx', 'var2') var2
3 FROM DUAL;
3. As its accessed globally, and created by Scott, so only Scott can set, but other can only access.
Manu
|
|
|
|
| Re: create or replace context abc Using package_name [message #607056 is a reply to message #607054] |
Sat, 01 February 2014 16:51 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
manubatham20 wrote on Sat, 01 February 2014 16:44As its accessed globally, and created by Scott, so only Scott can set, but other can only access.
Not true. Any user with EXECUTE permission on the package will be able to set values. I have no idea why OP is trying to create exactly same package in second schema (ABC) and set context via that second package. That's why "insufficient privileges" is raised. All OP needs is granting execute on scott.my_pkg to abc. Also the way OP created set_ctx procedure will prevent sharing context across the instance. If user ABC sets context sets Var1 and/or Var2 it will reset Var1 and/or Var2 for user scott to null. To share values across database regardless what user set it set_ctx procedure should be:
dbms_session.set_context('App_Ctx',p_name,p_value);
SY.
|
|
|
|
| Re: create or replace context abc Using package_name [message #607137 is a reply to message #607056] |
Mon, 03 February 2014 04:05 |
|
faizan_amini
Messages: 11
Registered: January 2014
Location: India
|
Junior Member |
|
|
I have dropped my_pkg in ABC_USER as Suggested and created on procedure for execute set_context in ABC_USER
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as ABC_USER
SQL>
SQL> CREATE OR REPLACE PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
2 BEGIN
3 dbms_session.set_context('App_Ctx',p_name,p_value);
4 END set_ctx;
5 /
Procedure created
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as SYS
and granted to ABC_USER through sys user
grant execute on scott.my_pkg to ABC_USER
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as ABC_USER
SQL>
SQL> begin
2 set_ctx('XYZ', 'D');
3 end;
4 /
begin
set_ctx('XYZ', 'D');
end;
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "ABC_USER.SET_CTX", line 3
ORA-06512: at line 3
still i am getting insufficient privileges error.
[Updated on: Mon, 03 February 2014 04:07] Report message to a moderator |
|
|
|
| Re: create or replace context abc Using package_name [message #607145 is a reply to message #607137] |
Mon, 03 February 2014 05:25 |
|
faizan_amini
Messages: 11
Registered: January 2014
Location: India
|
Junior Member |
|
|
Please ignore my previous message now i am able to set sys_context for user ABC_USER using SCOTT package as suggested by you
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as ABC_USER
SQL> begin
2 scott.my_pkg.set_ctx('ABC', 'xyz');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> select sys_context('App_Ctx','ABC') FROM DUAL;
SYS_CONTEXT('APP_CTX','ABC')
--------------------------------------------------------------------------------
xyz
but as per you replay If user ABC sets context sets Var1 and/or Var2 it will reset Var1 and/or Var2 for user scott to null
and it is also happening in my side but i want to sets context session wise so is there any way to do the same? i want to set separate value for ABC user and SCOTT user.
[Updated on: Mon, 03 February 2014 05:28] Report message to a moderator |
|
|
|
| Re: create or replace context abc Using package_name [message #607174 is a reply to message #607145] |
Mon, 03 February 2014 07:50 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
faizan_amini wrote on Mon, 03 February 2014 06:25i want to set separate value for ABC user and SCOTT user.
Use USER as client_id:
SCOTT@pdborcl12 > DROP CONTEXT app_ctx
2 /
Context dropped.
SCOTT@pdborcl12 > CREATE OR REPLACE
2 CONTEXT app_ctx using set_app_ctx
3 ACCESSED GLOBALLY
4 /
Context created.
SCOTT@pdborcl12 > CREATE OR REPLACE
2 PROCEDURE set_app_ctx(
3 p_name VARCHAR2,
4 p_value VARCHAR2
5 )
6 IS
7 BEGIN
8 dbms_session.set_identifier(user);
9 dbms_session.set_context('app_ctx',p_name,p_value,null,user);
10 END;
11 /
Procedure created.
SCOTT@pdborcl12 > GRANT EXECUTE ON set_app_ctx TO u1
2 /
Grant succeeded.
SCOTT@pdborcl12 > begin
2 set_app_ctx('Var1', 'SCOTT');
3 set_app_ctx('Var2', 'TIGER');
4 end;
5 /
PL/SQL procedure successfully completed.
SCOTT@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
SCOTT TIGER
SCOTT@pdborcl12 > connect u1@pdborcl12
Enter password: **
Connected.
U1@pdborcl12 > show user
USER is "U1"
U1@pdborcl12 > begin
2 scott.set_app_ctx('Var1', 'U1');
3 scott.set_app_ctx('Var2', 'LION');
4 end;
5 /
PL/SQL procedure successfully completed.
U1@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
U1 LION
U1@pdborcl12 > connect scott@pdborcl12
Enter password: *****
Connected.
SCOTT@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
SCOTT@pdborcl12 > begin
2 dbms_session.set_identifier(user); -- you MUST set identifier
3 end;
4 /
PL/SQL procedure successfully completed.
SCOTT@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
SCOTT TIGER
SCOTT@pdborcl12 > connect u1@pdborcl12
Enter password: **
Connected.
U1@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
U1@pdborcl12 > begin
2 dbms_session.set_identifier(user); -- you MUST set identifier
3 end;
4 /
PL/SQL procedure successfully completed.
U1@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
U1 LION
U1@pdborcl12 > -- but you still are able to see SCOTT's context attributes
U1@pdborcl12 > begin
2 dbms_session.set_identifier('SCOTT');
3 end;
4 /
PL/SQL procedure successfully completed.
U1@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
SCOTT TIGER
U1@pdborcl12 >
And if you don't want users to see each other context attributes:
SCOTT@pdborcl12 > DROP CONTEXT app_ctx
2 /
Context dropped.
SCOTT@pdborcl12 > CREATE OR REPLACE
2 CONTEXT app_ctx using set_app_ctx
3 ACCESSED GLOBALLY
4 /
Context created.
SCOTT@pdborcl12 > CREATE OR REPLACE
2 PROCEDURE set_app_ctx(
3 p_name VARCHAR2,
4 p_value VARCHAR2
5 )
6 IS
7 BEGIN
8 dbms_session.set_identifier(user);
9 dbms_session.set_context('app_ctx',p_name,p_value,user,user);
10 END;
11 /
Procedure created.
SCOTT@pdborcl12 > GRANT EXECUTE ON set_app_ctx TO u1
2 /
Grant succeeded.
SCOTT@pdborcl12 > begin
2 set_app_ctx('Var1', 'SCOTT');
3 set_app_ctx('Var2', 'TIGER');
4 end;
5 /
PL/SQL procedure successfully completed.
SCOTT@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
SCOTT TIGER
SCOTT@pdborcl12 > connect u1@pdborcl12
Enter password: **
Connected.
U1@pdborcl12 > show user
USER is "U1"
U1@pdborcl12 > begin
2 scott.set_app_ctx('Var1', 'U1');
3 scott.set_app_ctx('Var2', 'LION');
4 end;
5 /
PL/SQL procedure successfully completed.
U1@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
U1 LION
U1@pdborcl12 > connect scott@pdborcl12
Enter password: *****
Connected.
SCOTT@pdborcl12 > show user
USER is "SCOTT"
SCOTT@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
SCOTT@pdborcl12 > exec dbms_session.set_identifier(user);
PL/SQL procedure successfully completed.
SCOTT@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
SCOTT TIGER
SCOTT@pdborcl12 > connect u1@pdborcl12
Enter password: **
Connected.
U1@pdborcl12 > show user
USER is "U1"
U1@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
U1@pdborcl12 > exec dbms_session.set_identifier(user);
PL/SQL procedure successfully completed.
U1@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
U1 LION
U1@pdborcl12 > exec dbms_session.set_identifier('SCOTT');
PL/SQL procedure successfully completed.
U1@pdborcl12 > SELECT sys_context('app_ctx','var1') var1,
2 sys_context('app_ctx','var2') var2
3 FROM DUAL
4 /
VAR1 VAR2
---------- ----------
U1@pdborcl12 >
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 22:01:19 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
Thanks once again.