Already a member?
Sign in
Welcome! Wikis are websites that everyone can build together. It's easy!
Location: Database
Discussion: Oracle Roles and Privileges
Watch
 TonyR_NZ |
Oracle Roles and Privileges
May 5 2008, 10:42 PM EDT I have created a database in Oracle 9i that has numerous application packages, tables and views that reside on separate schema. The development staff require access to multiple schema objects on the db but I am loathed to assign the 'ALL' to the DDL privileges as I would like to restrict access to a specific schema without limiting the developers to their own schema. Does anyone have any detail on a good model for this level of security? Regards 3 out of 3 found this valuable. Do you? |
|
monowar94ru monowar94ru |
RE: Oracle Roles and Privileges
May 6 2008, 12:01 AM EDT First Create a role based o the privileges (Ex: select, delete or update) on the owner or schema.object. EX: Step1: Create role role1; Step 2: assign privileges as many as you want for that role: grant select on system.<object_name> to role1; grant select on owner.<object_name> to role1; grant select on owner.<object_name> to role1; Step3: now you can grant that role to the specified user Grant role1 to <user> Thanks Monowar Mukul 1 out of 1 found this valuable. Do you? |
|
TonyR_NZ |
RE: Oracle Roles and Privileges
May 6 2008, 6:50 PM EDT Many thanks for posting the reply. However, I was thinking more along the lines or the DDL and not the data manipulation. While it would be easy to grant 'CREATE ALL TABLE' etc. I would like to restrict a specific schema from the across the board privilege that this applies but allowing the Developers to CREATE tables and Procedures in schema other than their own. I have attempted to tckle this problem with role based privileges but this doesn'r seem to work. Any other thoughts on this? Regards Tony Do you find this valuable? |
|
monowar94ru monowar94ru |
RE: Oracle Roles and Privileges
May 6 2008, 8:05 PM EDT Did you give : Did you grant create session, create table privileges to that schema.objects for that developer user (under that role) ? You need to allow those privileges also. Thanks Monowar 1 out of 1 found this valuable. Do you? |
|
TonyR_NZ |
RE: Oracle Roles and Privileges
May 6 2008, 10:57 PM EDT Hi There, I have tried this but still hit problem with insufficient privileges. I created role under schema s1. and allocated create session and create table. I then granted the role to schema s2. Should this be enough? Regards Tony Do you find this valuable? |
|
monowar94ru monowar94ru |
RE: Oracle Roles and Privileges
May 7 2008, 2:41 AM EDT Before everythings check privileges for the developer: Select * from dba_role_privs where grantee=’developer_user_id’ If not then provide grant connect, create session for that developer. That should work. Thanks Monowar Do you find this valuable? |
|
TonyR_NZ |
RE: Oracle Roles and Privileges
May 8 2008, 7:34 PM EDT Thanks for the reply. Unfortunately, I had already granted these privileges. Still not working I would like. Regards Tony Do you find this valuable? |
|
monowar94ru monowar94ru |
RE: Oracle Roles and Privileges
May 8 2008, 10:11 PM EDT How but the schema s1----> is that schema got sysdba privileges like sys or system schema. If not then create role under system schema. Then grant that system role to other schema s2. This is my best advice. Thanks Do you find this valuable? |
|
TonyR_NZ |
RE: Oracle Roles and Privileges
May 11 2008, 10:12 PM EDT Hi monowar, The following is more along the lines of the user model I am trying to achieve but keep getting the insifficient privileges for the recipient schema: DBA-Schema (Me) App_schema_1 App_schema_2 App_schema_3 Usr_schema_1 Usr_schema_2 Usr_schema_3 The Usr_schema_(1,2,3) need access to App_schema_(2,3) but not App_scheam_1 for the following: create session, connect, create table, create procedure etc. but without applying system privileges such as: create all table and sysdba. Any thoughts on this model or can you suggest another? Many thanks Tony Do you find this valuable? |
|
Joords Joords |
RE: Oracle Roles and Privileges
Jun 12 2008, 9:55 AM EDT Hi Maybe an complete other way is to use "Use a (secure) application role " with "Fine-Grained Access Control" with this it is possibloe to setup a correct structure how users can have access to one group but not to the other. Do you find this valuable? |
 normanjd |
RE: Oracle Roles and Privileges
Jun 16 2008, 9:40 AM EDT Hmmm, Try granting all the privileges directly to one of the users and see if it works... Sometimes granting through roles does not work depending what you are doing... If direct grants do work, copy them to a role, then start removing the dioect grants one by one until you identify which ones you can and cannot grant via roles... 2 out of 2 found this valuable. Do you? |
|
sum1else sum1else |
RE: Oracle Roles and Privileges
Aug 2 2008, 12:38 AM EDT would it be due to some setup in sqlnet.ora about authentication ?? check it up. 1 out of 1 found this valuable. Do you? |