restricted session

[oddster]

anyone know why a user with the DBA role, and more specifically the 'Alter system' system privilege, is unable to issue:

SQL> alter system enable restricted session;
alter system enable restricted session
*
ERROR at line 1:
ORA-01031: insufficient privileges

Rgds,

[Raminder]

Restricted session is a special privilege given only to sysdba or internal user.
A user with restricted session privilege can restrict access to all other users of the database during the period of the session. That explains the sensitive nature (and hence special privilege required) for 'Alter system enable restricted session'.

[oddster]

Raminder,

I don't agree.

The restricted session privilege is part of the DBA role.
It allows users to connect to a database that has restricted session enabled.

The user I wish to use to 'alter system enable restricted session'; has the DBA role and restricted session privilege already but gets the above error.

Why?? I can run it as System no problem!

TIA

[julian]

If you create an user LION and grant DBA to LION, then LION is able to connect to the DB and issue successfully alter system enable restricted session;

[oddster]

Yes I know this Julian.

I want to know what has been taken away from my user to stop him performing this SQL.

[julian]

Originally posted by oddster
Yes I know this Julian.

I want to know what has been taken away from my user to stop him performing this SQL.

What do you get when you run the following:

select * from dba_role_privs where GRANTEE = 'LION'

[oddster]

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
LION DBA NO YES


that is all I granted him!

I have viewed roles and prvs through TOAD

As I said, my user HAS DBA ROLE and restricted session privilege, what does he not enough (i.e. taken away)!!

Many thanks for your efforts.

[jm]

try
grand "alter session " to xxx

[oddster]

The user has 'Alter session' privilege.

[oddster]

just to let you know,

I implicitly granted 'Alter system' to my user and it worked!

The wierd and wonderful world of Oracle eh?