DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: SYSDBA privilege

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    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



  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    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



    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    connect as Internal

    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
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  4. #4
    Join Date
    Dec 2000
    Posts
    255

    SYSDBA privilege

    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

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Apr 2001
    Posts
    151

    who has sysdba privs

    Hi all,

    How can I check who has sysdba privs?

    thanks,
    Elin@trend

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SELECT * FROM v$pwfile_users WHERE sysdba='TRUE';
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Apr 2001
    Posts
    151
    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,

    Elin@trend

  9. #9
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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
    Rajeev Suri

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width