restricted session

Printable View

10-12-2001, 07:11 AM
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,
10-12-2001, 07:48 AM
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'.
10-12-2001, 08:05 AM
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
10-12-2001, 08:06 AM
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;
10-12-2001, 08:10 AM
oddster

Yes I know this Julian.

I want to know what has been taken away from my user to stop him performing this SQL.
10-12-2001, 08:34 AM
julian

Quote:

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'
10-12-2001, 09:34 AM
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.
10-12-2001, 09:55 AM
jm

try
grand "alter session " to xxx
10-12-2001, 09:56 AM
oddster

The user has 'Alter session' privilege.
10-12-2001, 12:44 PM
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?
10-12-2001, 02:24 PM
julian

Yep, that was a strange thing :-)
10-12-2001, 03:01 PM
rsuri

I tried to duplicate your problem and the only way I could do this was by revoking the "alter system" privilege from the "dba" role. HAve you checked if this is not the case in your database (someone deliberately revoked the alter system from dba)

- Rajeev