CODEWITHSUNDEEP
oracle-databaseoracle11gprivileges
Vivek
Vivek

Reputation: 4642

User access issue in Oracle 11G

In my oracle DB, i have a user named test this user has DML_ROLE in the DB. And, i have provided insert/update/delete/select access to DML_ROLE on a table named hdr_detail.

But, when user test execute an update query on hdr_detail table its getting error message as Returned error: ORA-01031: insufficient privileges. It works fine when i provide the access directly to the user.

I'm confused why this error shows up only when i provide the access through role.

Table structure:

COLUMN NAME          DATA TYPE
PERIOD               NUMBER
HDR_ID               VARCHAR2(50)

Query i use to update:

update test_sch.hdr_detail set period=201108 where hdr_id = 'check';

Statement i use to grant:

grant insert,select,update,delete on test_sch.hdr_detail to dml_role;

select * from dba_role_privs where grantee like 'TEST' returns the following result

GRANTEE GRANTED_ROLE  ADMIN_OPTION  DEFAULT_ROLE
TEST    DML_ROLE      NO            NO

select * from dba_tab_privs where table_name like 'HDR_DETAIL' returns the following result

GRANTEE    OWNER       TABLE_NAME   GRANTOR     PRIVILEGE   GRANTABLE    HIERARCHY
DML_ROLE   TEST_SCH    HDR_DETAIL   TEST_SCH    DELETE      NO           NO
DML_ROLE   TEST_SCH    HDR_DETAIL   TEST_SCH    INSERT      NO           NO
DML_ROLE   TEST_SCH    HDR_DETAIL   TEST_SCH    SELECT      NO           NO
DML_ROLE   TEST_SCH    HDR_DETAIL   TEST_SCH    UPDATE      NO           NO

Please help me in resolving this issue. Reply in comment if any more information is needed about this issue.

Upvotes: 2

Views: 1009

Answers (3)

Ollie
Ollie

Reputation: 17538

Try setting the role as the users default role: 

ALTER USER test DEFAULT ROLE dml_role;

Upvotes: 2

Florin Ghita
Florin Ghita

Reputation: 17643

That seems impossible. Are you sure that your user connect to correct DB, schema, and query the right table? I'm stunned.

Pls try

select * from test_sch.hdr_detail

wiht test user.

Upvotes: 0

Ollie
Ollie

Reputation: 17538

It could be an issue with how you are accessing the databse object HDR_DETAIL.

From Don burleson (http://www.dba-oracle.com/concepts/roles_security.htm):

Oracle roles have some limitations. In particular object privileges are granted through Oracle roles can not be used when writing PL/SQL code. When writing PL/SQL code, you must have direct grants to the objects in the database that your code is accessing.

If your user is issuing the UPDATE through an application or PL/SQL block then it will not use the role-based permissions. If this is the case you will have to grant the permissions directly.

Upvotes: 0

Related Questions