SYSDBA privilege

[amolik]

hi all

I have a small query. It is said that SYSDBA and SYSOPER
are respectively DBA level Administrative and Operational
Privileges. I am working on Oracle 8i on NT. Now if I say
grant sysdba to ; (User which I have created ) It
shows me grant succeded. However If connect as the user
and say 'shutdown' or 'create tablespace' it still gives me
error as 'Insufficient Privileges'. What can be the reason
for this. How to incorporate these privileges to the user

Amol

[rohitsn]

Hi,
In order to startup and shutdown by a normal user having sysdba and sysoper roles.go into servermanager and
connect as following

svrmgr>connect scott as sysdba
svrmgr>shutdown immediate

In case of any help needed please be free to ask me at rohitsn@altavista.com

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i

[padmam]

Dear Amolik, 5th May 2001 18:57 hrs chennai

Try to understand with the practicall solution at the bottom.

SVRMGR> connect internal
Connected.
SVRMGR> grant sysdba to scott;
Statement processed.
SVRMGR> connect scott/tiger as sysdba;
Connected.
SVRMGR> create tablespace mani
2> datafile 'd:\mani1' size 100m;
Statement processed.
SVRMGR> select user from dual;
USER
------------------------------
SYS
1 row selected.

I am able to create a TS.I think you are trying to directly connect as scott/tiger and create TS that wont work out.You have to connect as Sysdba.

Cheers

Padmam

[amolik]

Hi all

First of all Thanks a lot
I tried what you have suggested and it did worked out. Actually I was trying it without saying AS SYSDBA and also I was on SQL> prompt. Oracle allows this only in SVRMGR.
Thanks for all

Amol

[sambavan]

The way you would do this under sqlplus is that log in as some user under sqlplus and when the sqlplus prompt comes, issue

SQL> connect sys/passwd as sysdba


Sam

[elintrend]

Hi all,

How can I check who has sysdba privs?

thanks,

[jmodic]

SELECT * FROM v$pwfile_users WHERE sysdba='TRUE';

[elintrend]

SELECT * FROM v$pwfile_users WHERE sysdba='TRUE'

and it return error as below

ERROR:
ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], []

also
connect / as sysdba
grand sysdba to user username
error show beloww

ORA-01994: GRANT failed: cannot add users to public password file

but password file has been created long time ago by previous DBA.

Thanks,

[rsuri]

About that --
SELECT * FROM v$pwfile_users WHERE sysdba='TRUE'
I think this is a know bug with some of the Oracle version.

Don't ask me why but if you play a little with the select statement it does let you see the result. For example try following --


select username, sysdba, sysoper from v$pwfile_users
group by username, sysdba, sysoper;

Other variations of this SELECT may work as well.

- Rajeev

[jmodic]

Originally posted by elintrend
SELECT * FROM v$pwfile_users WHERE sysdba='TRUE'

and it return error as below

ERROR:
ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], []

This is a known bug in 8.1.5, fixed in 8.1.6. For a workaround, add an ORDER BY clause and it will work:

SELECT * FROM v$pwfile_users WHERE sysdba='TRUE'
ORDER BY 1;

also
connect / as sysdba
grand sysdba to user username
error show beloww

ORA-01994: GRANT failed: cannot add users to public password file

In Error Messages manual you'll find the explanation:

ORA-01994 GRANT failed: cannot add users to public password file

Cause: A grant failed because a user could not be added to the password file.
This is because the value of the REMOTE_LOGIN_PASSWORDFILE
initialization parameter is set to PUBLIC.

Action: To add a user to the password file, shutdown the database, change the
REMOTE_LOGIN_PASSWORDFILE initialization parameter to PRIVATE, and
restart the database.

HTH,