Hi Tom,

I tried to connect to my instance. But I could not log as even SYS user.

SQL> connect sys/change_on_install as sysdba
ERROR:
ORA-01031: insufficient privileges

I tried with the SYSTEM and INTERNAL user accounts also. But still the same error. 

Then I recreate the password file. but still no help. Could you please tell me what's wrong, that I had done. 

Oracle8i SQL Reference says:
"You can revoke any role if you have the GRANT ANY ROLE system privilege."

Let's create a user U who will try to revoke some roles from user A:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
JServer Release 8.1.7.4.1 - Production

SQL> connect sys
Enter password:
Connected.

SQL> create user a identified by a;

User created.

SQL> grant dba, exp_full_database to a;

Grant succeeded.

SQL> create user u identified by u;

User created.

SQL> grant create session, grant any role to u;

Grant succeeded.

SQL> connect u/u
Connected.

SQL> revoke exp_full_database from a;

Revoke succeeded.

SQL> revoke dba from a;
revoke dba from a
*
ERROR at line 1:
ORA-01031: insufficient privileges

Why can Grant-Any-Role-User revoke EXP_FULL_DATABASE role but cannot revoke DBA role? Are there logical reasons for that? 

I have Oracle Database 10g running on Linux box.

We have main user for this schema, l
ets say XYZ.

We created more users on request.

But they can not retrieve data from view (abc).
Users can access to the base table on which view has been created. But they are not able to get values from abc_view but special user XYZ is fine.
User XYZ can retrieve data from view (abc).

TABLES

SQL> select count(*) from a_table;

COUNT(*)
----------
25412

SQL> select count(*) from b_table;

COUNT(*)
----------
32575

SQL> select count(*) from c_table;

COUNT(*)
----------
322

*****************************************
VIEW:

SQL> select count(*) from abc_view;

COUNT(*)
----------
0 


Dawar 
Los Angeles, CA USA

Tom,

Part 2 of above question.

Actually our views following condition as below:

select 
...
....
from
dept_users c
emp a
where c.user_name = user
... some other conditions


Now dept_user tables has all user name along with our internal department number.
I need to enter all users in this table along with dept no.
So they can be able to work with departments associat with users.

Last time I commented out below line from where condition

---where c.user_name = user

So every user list in the dept_users table can get values.
But after some time I noticed we are getting repeation values in out put.

SQL> select count(*) from 123_view;

  COUNT(*)
----------
   455685

But if removed commented out and used condition as below in the where clause

where c.user_name = user


SQL> select count(*) from 123_view;

  COUNT(*)
----------
19847


**********************
But again only few users can get values now.
I created new users but they are not getting any values from the views of this particular schema.

Dawar
LA, CA
USA