Privileges for Oracle roles does not work

Discussion created by gnevan on Nov 3, 2013
Latest reply on Dec 23, 2013 by crafty762

Like • Show 0 Likes0
Comment • 9

Hello!

I have an issue: there are oracle users and roles, created for managing access to the data.
I gave the privileges on the ArcSDE feature datasets to the oracle roles, but the users in that roles still do not have access to the data.

[ATTACH=CONFIG]28836[/ATTACH]

The configuration is: ArcGIS Desktop 10.1 -> ArcSDE 9.3.1 -> Oracle 9i

Any ideas?

Ivan

Attachments

dbms_priv.png8.0 KB

Outcomes

9 Replies

vangelo-esristaff Nov 3, 2013 4:12 PM
It's been most of decade since I last used Oracle 9i, but I've never had any difficulty
using roles to manage access in Oracle. How exactly are you going about assigning
permissions? What geometry storage are you using?

You should know that Oracle retired 9iR2 long ago, and ArcGIS 9.3.1 rolls off into
Retired status soon, so you'll be working without a net soon.

- V
Like • Show 0 Likes0
Actions
gnevan Nov 4, 2013 6:26 PM
Vince,

There has been created oracle roles for different data access (for example, read-only access). SELECT ANY TABLE system privilege has been granted to the roles. The roles has been granted to different oracle users.
Then I used ArcCatalog to apply a privileges on feature datasets to that roles (for example, SELECT only).
The users in the roles do not have any access to the data unless I give the privileges to the user directly.
Maybe it is neccesary to give more base privileges to the roles, not only to the data itself?

Yes, that is the old software configuration and it is planned to move to contemporary releases in the near future.

Ivan
Like • Show 0 Likes0
Actions
vangelo-esristaff Nov 4, 2013 8:26 PM
SELECT ANY TABLE access is a dangerous trap. Don't go anywhere near it.

The proper way to grant access has three tiers:
- Tables
- Roles
- Users
You should explicitly grant role access to the tables (feature classes) using Desktop,
and explicitly grant user access to roles using SQL.

- V
Like • Show 0 Likes0
Actions
gnevan Nov 4, 2013 9:02 PM
Vince,

Ok, SELECT ANY TABLE is a bad idea, but it was implemented after a fault to give access using roles.

You should explicitly grant role access to the tables (feature classes) using Desktop,
and explicitly grant user access to roles using SQL.

It is all done exactly as you say. The role access to tables is done using ArcCatalog, user access to roles in made using SQL.

Ivan
Like • Show 0 Likes0
Actions
vangelo-esristaff Nov 5, 2013 3:03 AM
You'll need to give an example of a set of permission maps that doesn't work
as expected before we can help you.

- V
Like • Show 0 Likes0
Actions
gnevan Nov 5, 2013 8:33 PM
Vince,

Here are the scripts for the role GEOPH_SURF and a user ABRAMOVA_MI.
------------------------

DROP ROLE GEOPH_SURF;

CREATE ROLE GEOPH_SURF NOT IDENTIFIED;

GRANT DELETE, INSERT, SELECT, UPDATE ON GEOPHYSICS.MINELEASE_T TO GEOPH_SURF;
GRANT DELETE, INSERT, SELECT, UPDATE ON GEOPHYSICS.S165_IDX$ TO GEOPH_SURF;

GRANT GEOPH_SURF TO SYS WITH ADMIN OPTION;
GRANT GEOPH_SURF TO ABRAMOVA_MI;

----------------------------------

DROP USER ABRAMOVA_MI CASCADE;

CREATE USER ABRAMOVA_MI
IDENTIFIED BY VALUES '*'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 3 Roles for ABRAMOVA_MI
GRANT CONNECT TO ABRAMOVA_MI;
GRANT GEOPH_SURF TO ABRAMOVA_MI;
GRANT RESOURCE TO ABRAMOVA_MI;
ALTER USER ABRAMOVA_MI DEFAULT ROLE CONNECT, RESOURCE;
-- 1 System Privilege for ABRAMOVA_MI
GRANT UNLIMITED TABLESPACE TO ABRAMOVA_MI;

--------------------------------------

The privileges for a feature dataset with a single feature class in a Desktop are:
[ATTACH=CONFIG]28887[/ATTACH]

In this case the user does not see the feature class, unless I give explicit rights:
[ATTACH=CONFIG]28888[/ATTACH]

Ivan
Attachments
- test_privs2.png8.4 KB
- test_privs.png8.0 KB
Like • Show 0 Likes0
Actions
crafty762 Dec 13, 2013 7:15 PM
I'm not convinced that all of the permissions have been assigned correctly; mainly with the F, S, D, and A tables that correspond to your base table. I see from you original screenshot that the object class in question is versioned as a State ID is referenced in the error. This is why it's critical to use the ArcGIS Desktop tools when granting and revoking permissions to roles for given object classes... because doing so in Oracle can cause issues if you don't do it for all of the right tables involved.

You will need to find out which F, S, D, and A tables correspond to your feature class's base table and grant the same permissions to those as you did with the base table. Once you commit your permission SQL statements from within Oracle, close and re-open ArcGIS Desktop and try connecting again with a user from the role that was just given permissions.

EDIT: I've just re-read a few things in this thread. You said that the user can see the data if permissions are given to the user directly rather than just to the role. In that case, please check to see if the role in question for that user account is set to be a DEFAULT role in Oracle.
1 person found this helpful
Like • Show 1 Like1
Actions
gnevan Dec 22, 2013 11:51 PM

EDIT: I've just re-read a few things in this thread. You said that the user can see the data if permissions are given to the user directly rather than just to the role. In that case, please check to see if the role in question for that user account is set to be a DEFAULT role in Oracle.

Thank you very much, William. You were exactly right about the default roles.

Ivan
Like • Show 0 Likes0
Actions
crafty762 Dec 23, 2013 5:54 AM
Thank you very much, William. You were exactly right about the default roles.

Ivan

I'm glad it worked out for you. Please mark the correct answer using the green check so others with the same challenges can identify the solution. Have a great day!
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

Privileges for Oracle roles does not work

Attachments

Outcomes

Attachments

Related Content

Recommended Content