Home » RDBMS Server » Security » Row level security
Row level security [message #378549] Tue, 30 December 2008 22:59 Go to next message
khalidmehz
Messages: 37
Registered: May 2008
Location: PAKISTAN
Member
This is the script:

GRANT EXECUTE ON DBMS_RLS TO RLS ;
GRANT EXECUTE ON DBMS_SESSION TO RLS ;

CREATE OR REPLACE CONTEXT USEA_CONTEXT USING SET_USEA_CONTEXT ;

CREATE OR REPLACE PACKAGE SET_USEA_CONTEXT
IS
	PROCEDURE SET_MANAGER ;
	PROCEDURE SET_ISLAMABAD ;
	PROCEDURE SET_KARACHI ;
	PROCEDURE SET_PESHAWAR ;
	PROCEDURE SET_LAHORE ;
END ;
/

CREATE OR REPLACE PACKAGE BODY SET_USEA_CONTEXT
AS
	PROCEDURE SET_MANAGER
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'MANAGER');
	END ;
	PROCEDURE SET_ISLAMABAD
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'ISLAMABAD') ;
	END ;
	PROCEDURE SET_KARACHI
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'KARACHI') ;
	END ;
	PROCEDURE SET_PESHAWAR
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'PESHAWAR') ;
	END ;
	PROCEDURE SET_LAHORE
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'LAHORE') ;
	END ;
END ;
/

CREATE OR REPLACE PACKAGE USEA_POLICY
AS
	FUNCTION USEA_PREDICATE(SCHEMA_NAME IN VARCHAR2, OBJECT_NAME IN VARCHAR2) RETURN VARCHAR2 ;
END ;
/

PACKAGE BODY USEA_POLICY
AS
	FUNCTION USEA_PREDICATE(SCHEMA_NAME IN VARCHAR2, OBJECT_NAME IN VARCHAR2) RETURN VARCHAR2
	IS
	   LV_PREDICATE VARCHAR2(1000) := '' ;
	BEGIN
		IF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'MANAGER' THEN
		  LV_PREDICATE := 'SALEPOINT IN (SELECT SALEPOINT FROM BILLING.USER_RIGHTS WHERE USERNAME LIKE '''||USER||'''' ;
/*		ELSIF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'ISLAMABAD' THEN
		  LV_PREDICATE := 'SALEPOINT LIKE ''%ISLAMABAD%''' ;
		ELSIF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'KARACHI' THEN
		 	LV_PREDICATE := 'SALEPOINT LIKE ''%KARACHI%''' ;
		ELSIF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'PESHAWAR' THEN
			LV_PREDICATE := 'SALEPOINT LIKE ''%PESHAWAR%''' ;
		ELSIF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'LAHORE' THEN
			LV_PREDICATE := 'SALEPOINT LIKE ''%LAHORE%''' ;*/
		ELSE
		LV_PREDICATE := '1=2' ;
		END IF ;
		RETURN LV_PREDICATE ;
	END ;
END ;
/

BEGIN
	DBMS_RLS.ADD_POLICY(
		OBJECT_SCHEMA 	=> 'BILLING',
		OBJECT_NAME 	=> 'BILLMASTER', -- TABLE NAME
		POLICY_NAME 	=> 'USEA_TEST_POLICY', 
		FUNCTION_SCHEMA => 'BILLING',
		POLICY_FUNCTION => 'USEA_POLICY.USEA_PREDICATE',
		STATEMENT_TYPES => 'SELECT, INSERT, UPDATE, DELETE',
		UPDATE_CHECK 	=> TRUE,
		ENABLE 		=> TRUE,
		STATIC_POLICY 	=> FALSE) ;
END ;
/

CREATE OR REPLACE TRIGGER USEA_LOGON_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
  SET_USEA_CONTEXT.SET_MANAGER ;
END ;
/


but i logon using rls user the following message displayed.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "BILLING.SET_USEA_CONTEXT", line 6
ORA-06512: at line 2


please help me its urgent...

[MERGED by LF]

[Updated on: Wed, 31 December 2008 03:13] by Moderator

Report message to a moderator

Re: Row Level Security [message #378601 is a reply to message #378549] Wed, 31 December 2008 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and show line numbers.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Which one is line 6?

Regards
Michel
Row Level Security on Multiple Tables [message #378634 is a reply to message #378549] Wed, 31 December 2008 03:08 Go to previous messageGo to next message
khalidmehz
Messages: 37
Registered: May 2008
Location: PAKISTAN
Member
Hi Experts!

I have implemented row level security on a single table. it runs successfully. but now i want to implement it on multiple tables. anybody can help me...

regards
Khalid Mehmood
Re: Row Level Security on Multiple Tables [message #378638 is a reply to message #378634] Wed, 31 December 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A feedback on the topic you create is welcome, please post what was wrong in your previous topic, I tend to no more try to help people if they don't feedback and post the solution they found.
If you want help, you must help others when you can and in first place posting how you solve your problem.

Regards
Michel
Re: Row level security [message #378655 is a reply to message #378549] Wed, 31 December 2008 04:16 Go to previous message
khalidmehz
Messages: 37
Registered: May 2008
Location: PAKISTAN
Member
I have found the solution there is spelling mistake that i have correct it....

I have use usea_test instead of usea_context in set_usea_context package...

[Updated on: Wed, 31 December 2008 04:36]

Report message to a moderator

Previous Topic: SELECT ANY TABLE DOES NOT SHOW DATA IN VIEW
Next Topic: can i do audit just on one table?
Goto Forum:
  


Current Time: Sat Dec 03 20:27:09 CST 2016

Total time taken to generate the page: 0.10090 seconds