13 Replies Latest reply: Jan 12, 2013 6:13 AM by 905952 RSS

    Creating a user with SYSDBA privilege

    T.Boyd
      DB Version: 11g


      I was asked to create a non-SYS user with SYSDBA privileges. So i created this user (mndba) with all the important privileges plus the SYSDBA privilege.
      When i try to connect as this newly created user(mndba), it succeeds but when i query
      show user
      i realize that the connected user is actually SYS and not mndba !!!! why is this happening

      SQL> grant sysdba to mndba;
      
      Grant succeeded.
      
      SQL> conn mndba/mndba as sysdba
      Connected.
      SQL> show user
      USER is "SYS"                     ---------- (!!!!!!!!!!!!!!!!!)
      
      
      
      SQL> select * from v$pwfile_users ;
      
      USERNAME                       SYSDB SYSOP SYSAS
      ------------------------------ ----- ----- -----
      SYS                            TRUE  TRUE  FALSE
      mndba                          TRUE  FALSE FALSE
      
      SQL> exit;
      Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
      
      
      $ sqlplus mndba/mndba as sysdba
      
      SQL*Plus: Release 11.1.0.7.0 - Production on Mon Apr 12 07:26:29 2010
      
      Copyright (c) 1982, 2008, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
      
      SQL> show user
      USER is "SYS"              ---------- (!!!!!!!!!!!!!!!!!)
        • 1. Re: Creating a user with SYSDBA privilege
          NavneetU
          Hi,

          This is fine. When you grant SYSDBA priviledge to any user say ABC and connect with that user as SYS, it will be assumed that sys user is connected.

          In this case ABC user can connect with two ways...1. As sysdba 2. As normal ABC user.

          Regards
          • 2. Re: Creating a user with SYSDBA privilege
            P.Forstmann
            Actually SYSDBA is a privilege allowing to connect as SYS user.

            To create a "normal" user account, try:
            create user nondba identifed by nondba;
            grant create session to nondba;
            connect nondba/nondba
            • 3. Re: Creating a user with SYSDBA privilege
              Kamran Agayev A.
              Hi and welcome to the forum

              If you assigned a user SYSDBA privilege, the use will act as SYS user in the database. See the following blog post of mine
              http://kamranagayev.wordpress.com/2009/11/16/connecting_with_a_sysdba_privilege_you_act_like_sys_user/
              • 4. Re: Creating a user with SYSDBA privilege
                Azar
                v$pwfile_users - It show hOw many sysdba privilege users are avilable on your database.

                http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/dba.htm#1167

                If you assigned sysdba to users, the users can shutdown or startup database and doing all sysdba activities.
                • 5. Re: Creating a user with SYSDBA privilege
                  T.Boyd
                  Isn't this a bit dangerous? Lets say i connect as
                  sqlplus mndba/mndba as sysdba
                  and run a drop objects code like below, wouldn't this drop all the objects owned by SYS.
                  begin
                       for i in (select * from user_objects)
                       loop
                            begin
                            execute immediate 'drop ' || i.object_type || ' ' || i.owner || '.' || i.object_name;
                  
                            exception
                            when others then
                            dbms_output.put_line('Cannot drop table '|| 'i.owner' || '.' || i.object_name || CHR(10) || SQLERRM);
                            end;
                       end loop;
                      end;
                     / 
                  • 6. Re: Creating a user with SYSDBA privilege
                    NavneetU
                    Dangerous in what sense...yes user with sysdba can do anything.........if you think that its dangerous than why you should give this privilegde to any other user.

                    Regards
                    • 7. Re: Creating a user with SYSDBA privilege
                      T.Boyd
                      Thank you NKU, Kamran, Forstmann
                      • 8. Re: Creating a user with SYSDBA privilege
                        Girish Sharma
                        ...And thats why CMM Level 5 Companies gives sys password / SYSDBA privilege to only those Sr Oracle DBAs who have following :

                        (A) At least 5 year experience on at least one live production database. Some companies consider db size/users too.
                        (B) Have good track of previous employer.
                        (C) Have sense; what does it mean by having SYS password / sysdba privilege.
                        (D) Have worked well as junior oracle dba in previous company.
                        (E) Conditions apply more/less.

                        Regards
                        Girish Sharma
                        • 9. Re: Creating a user with SYSDBA privilege
                          Tim Boles
                          There are few reasons to truly connect to the system as the sys or system users. You can grant appropriate privileges outside of sysdba. Yes it is a "dangerous" practice to be using the sysdba account and for "normal" work, tuning and similar you should not be connecting with this account.

                          I have worked in shops where the only user that has sysdba privileges is the OS user Oracle and that password is generally "known" only to a select group of people whom do installs, updates and similar.

                          Regards
                          Tim Boles
                          • 10. Re: Creating a user with SYSDBA privilege
                            432630
                            Hi Guys,

                            There are a fwe comments worth making here.

                            1) Yes you are SYS when you connect AS SYSDBA.
                            2) no you cannot do everything as SYSDBA:

                            SQL> create user sysdba identified by sysdba;

                            User created.

                            SQL> grant create session to sysdba;

                            Grant succeeded.

                            SQL> grant sysdba to sysdba;

                            Grant succeeded.

                            SQL> connect sysdba/sysdba@ora11gpe as sysdba
                            Connected.
                            SQL> create view sysdba.mydata
                            2 as select * from v$datafile;
                            as select * from v$datafile
                            *
                            ERROR at line 2:
                            ORA-01031: insufficient privileges


                            SQL>

                            I connected and created a user, granted SYSDBA, the user is essentially SYS:

                            C:\>sqlplus sysdba/sysdba@ora11gpe as sysdba

                            SQL*Plus: Release 11.1.0.7.0 - Production on Mon Apr 12 14:09:20 2010

                            Copyright (c) 1982, 2008, Oracle. All rights reserved.


                            Connected to:
                            Personal Oracle Database 11g Release 11.1.0.7.0 - Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options

                            SQL> @check

                            USER USERNAME CURR SESS SCHEM
                            ---------- ---------- ---------- ---------- ----------
                            SYS SYS SYS SYS SYS

                            SQL>
                            SQL>
                            SQL> get check
                            1 col user for a10
                            2 col username for a10
                            3 col curr for a10
                            4 col sess for a10
                            5 col schem for a10
                            6 select user, username,
                            7 sys_context('USERENV','CURRENT_USER') curr,
                            8 sys_context('USERENV','SESSION_USER') sess,
                            9 sys_context('USERENV','CURRENT_SCHEMA') schem
                            10* from user_users
                            SQL>

                            But i was still not able to access some objects owned by SYS!
                            3) SYSDBA is very powerful but its not everything
                            4) SYSDBA is not needed for day to day activities. You should not increase the attack surface by creating more than one SYSDBA account. The user SYS in the database should retain SYSDBA and SYSOPER but no other accounts should be granted this privilege. Also at the OS level do not add any users to the OSDBA Unix (or other OS) group

                            hope this helps a bit, the main thing is to not increase the risk, in fact decrease the risk and create an OSOPER Unix group and add a user to it for stop/start/etc as a low level user allowing segregation of duties.

                            Kind regards

                            pete
                            • 11. Re: Creating a user with SYSDBA privilege
                              T.Boyd
                              Thank you Pete. Thanks everyone.
                              • 12. Re: Creating a user with SYSDBA privilege
                                Girish Sharma
                                Pete,

                                1.create user sysdba identified by sysdba;
                                2.grant create session to sysdba;
                                3.grant sysdba to sysdba;
                                SQL> show user;
                                USER is "SYS"
                                SQL> create view sysdba.mydata
                                  2  as select * from v$datafile;
                                as select * from v$datafile
                                                 *
                                ERROR at line 2:
                                ORA-01031: insufficient privileges
                                
                                
                                SQL> ed
                                Wrote file afiedt.buf
                                
                                  1  create view mydata
                                  2* as select * from v$datafile
                                SQL> /
                                
                                View created.
                                
                                SQL> select owner,object_name from all_objects where object_name='MYDATA';
                                
                                OWNER                          OBJECT_NAME
                                ------------------------------ ------------------------------
                                SYS                            MYDATA
                                
                                SQL> select owner,object_name from dba_objects where object_name='MYDATA';
                                
                                OWNER                          OBJECT_NAME
                                ------------------------------ --------------------------------------------------
                                SYS                            MYDATA
                                So, here issue is sysdba.mydata. Kindly correct me, where i am wrong.

                                Thanks and Best Regards
                                Girish Sharma
                                • 13. Re: Creating a user with SYSDBA privilege
                                  905952
                                  This post just helped me fix some user issues. Thanks