Home » SQL & PL/SQL » SQL & PL/SQL » Role based schema access (Oracle 10g)
Role based schema access [message #309391] Thu, 27 March 2008 05:27 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Please see the requirement below. How to achieve this with a simple script ?

Read Only - this only concerns data

=====================================
So, when we create a role that is supposed to be read only, we should negate read access from certain tables / columns in schema/view/MV.
A read access schema should not be able to execute executables (SP's + all other executables) but it is important to be able to read their scripts - there is no exception to this, this is the only rule when we create a read only access schema (it is fundamental to its nature that it can only 'Read').
- allow ability to read data , disallow ability to read data (from the 'revoke' file)
- DO NOT allow ability to change 'scripts'
- DO NOT allow ability to execute any executables
- DO NOT allow ability to create any executables or any object
- DO NOT allow ability to drop anything

Read and Write - this only concerns data

So, when we create a role that can amend data, we need to be able to negate wrtie access selectively, and be careful because there are executables and other objects as well.
General rule is similar to the read only access schema
- allow ability to change 'data', disallow ability to change 'data (from the 'revoke' file)
- DO NOT allow ability to change executables
- DO NOT allow ability to execute any executables
- DO NOT allow ability to create any executables or any object
- DO NOT allow ability to drop anything
Re: Role based schema access [message #309394 is a reply to message #309391] Thu, 27 March 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just give the necessary privilege to your role.
What is the problem?

Regards
Michel
Re: Role based schema access [message #309414 is a reply to message #309394] Thu, 27 March 2008 06:33 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
I'm particularly not clear about the following...
1. How to grant/revoke Read access to executables ? i.e, the user should be able to read the code
2. How to grant/revoke write access to executables ?
i.e, the user should be able to modify the code
3. How to grant/revoke Read/write access to trigger code ?


Re: Role based schema access [message #309426 is a reply to message #309414] Thu, 27 March 2008 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Grant/revoke
1. If read mean execute then execute privilege
2. If write means create/recplace then create procedure privilege
3. Idem for trigger

Regards
Michel
Re: Role based schema access [message #309446 is a reply to message #309426] Thu, 27 March 2008 08:30 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks Michel
Re: Role based schema access [message #310702 is a reply to message #309446] Wed, 02 April 2008 02:27 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
May I know how to disallow the ability to create/drop any object ?
Re: Role based schema access [message #310709 is a reply to message #310702] Wed, 02 April 2008 02:40 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't give the privilege to do it.

Regards
Michel
Previous Topic: Want sql output in xls format
Next Topic: How to Fetch The closing balance in every account number in the cursor block.
Goto Forum:
  


Current Time: Fri Dec 09 21:24:38 CST 2016

Total time taken to generate the page: 0.10996 seconds