10 Replies Latest reply: Apr 14, 2014 10:08 AM by 846411 RSS

    Query regarding user privilege as sysdba

    manojswarnakar

      Hi All,

       

      I am using a test database (oracle 11g) on windiws 7 64 bit. I created a user 'scott' and gave 'sysdba' privilege to user. Now when i connect with user as sysdba, i am not able to query any dba or V$ views. Please assist

        • 1. Re: Query regarding user privilege as sysdba
          Mike Kutz

          4 digit database version?

          Which version of Oracle? (i'm assuming XE since you are in the XE forum)

          what commands did you use to create the user?

          what commands did you use to login as that user?

          what commands did you use to prove you don't have access to dba or v$ views?

           

          MK

           

          PS - this question may be more applicable to the Database Security - General forum.

          • 2. Re: Query regarding user privilege as sysdba
            Suntrupth

            What error do you encounter?

             

            Regards,

            Suntrupth

            • 3. Re: Query regarding user privilege as sysdba
              sb92075

              manojswarnakar wrote:

               

              Hi All,

               

              I am using a test database (oracle 11g) on windiws 7 64 bit. I created a user 'scott' and gave 'sysdba' privilege to user. Now when i connect with user as sysdba, i am not able to query any dba or V$ views. Please assist

               

              when all else fails Read The Fine Manual

               

              Operations Authorized by Administrative Privileges

              The following table lists the operations that are authorized by each administrative privilege:

              Administrative PrivilegeOperations Authorized
              SYSDBA
              • Perform STARTUP and SHUTDOWN operations
              • ALTER DATABASE: open, mount, back up, or change character set
              • CREATE DATABASE
              • DROP DATABASE
              • CREATE SPFILE
              • ALTER DATABASE ARCHIVELOG
              • ALTER DATABASE RECOVER
              • Includes the RESTRICTED SESSION privilege

              This administrative privilege allows most operations, including the ability to view user data. It is the most powerful administrative privilege.

              • 4. Re: Query regarding user privilege as sysdba
                846411

                Hi,

                 

                When you connect using scott you will have to login as sysdba to use the sys schema.

                 

                # sqlplus scott

                SQL> Show user

                SCOTT

                 

                #sqlplus scott as sysdba

                SQL> show user

                SYS

                 

                So if you login using "as sysdba" you are connected to scott user using SYS schema.

                 

                Try to login as sysdba and you will be able to access all datadictionary views.

                 

                Regards,

                Atif Iqbal

                • 5. Re: Query regarding user privilege as sysdba
                  EdStevens

                  Atif Iqbal wrote:

                   

                  Hi,

                   

                  When you connect using scott you will have to login as sysdba to use the sys schema.

                  Not so.  User only needs to have proper normal privileges.

                   

                  SQL> conn / as sysdba

                  Connected.

                  SQL> select * from v$pwfile_users;

                   

                  USERNAME                       SYSDB SYSOP SYSAS

                  ------------------------------ ----- ----- -----

                  SYS                            TRUE  TRUE  FALSE

                   

                  SQL> conn scott/tiger

                  Connected.

                  SQL> show user

                  USER is "SCOTT"

                  SQL> select count(*) from sys.utl_recomp_sorted;

                   

                    COUNT(*)

                  ----------

                           0

                   

                  SQL>

                   

                   

                   

                  Above, SCOTT is shown to NOT have SYSDBA privs, but clearly has "access to SYS schema"

                   

                   

                  # sqlplus scott

                  SQL> Show user

                  SCOTT

                   

                  #sqlplus scott as sysdba

                  SQL> show user

                  SYS

                   

                  So if you login using "as sysdba" you are connected to scott user using SYS schema.

                  No, he is NOT connect as SCOTT. He is connected as SYS.  Use of the 'as sysdba' option actually overrides whatever username was supplied.

                  In the below example, how can we be "connected to fubar user using the SYS schema", when there clearly is no user FUBAR at all?

                   

                  SQL> conn / as sysdba

                  Connected.

                  SQL> select username from dba_users where username='FUBAR';

                   

                  no rows selected

                   

                  SQL> conn fubar/fubar as sysdba

                  Connected.

                  SQL> show user

                  USER is "SYS"

                  SQL>

                   

                   

                  Try to login as sysdba and you will be able to access all datadictionary views.

                   

                  Regards,

                  Atif Iqbal

                  SYSDBA is not necessary to see dictionary views.  There are several other privileges and/or standard roles that allow it.  I leave the details of discovery as an exercise for the student.

                  • 6. Re: Query regarding user privilege as sysdba
                    Alvaro
                    In the below example, how can we be "connected to fubar user using the SYS schema", when there clearly is no user FUBAR at all?

                     

                    Because you're using OS authentication, which means the username is ignored. To connect as sysdba with a named user, you have to do so remotely, via password file.

                     

                    When you do connect remotely via sysdba, you're logging in as SYS. SYS is always linked with SYSDBA. You can use your named user as sysdba, which provides you a way of logging in as SYS remotely. The password file is exactly for this, to authenticate you when there is no data dictionary available, to bring up the database for instance.

                    • 7. Re: Query regarding user privilege as sysdba
                      EdStevens

                      Alvaro wrote:

                       

                      In the below example, how can we be "connected to fubar user using the SYS schema", when there clearly is no user FUBAR at all?

                       

                      Because you're using OS authentication, which means the username is ignored. To connect as sysdba with a named user, you have to do so remotely, via password file.

                       

                      Exactly my point.  My question of "how can we .....?" was a rhetorical question directed at Atif Iqbal with the intent of making it clear that his assertion that "So if you login using "as sysdba" you are connected to scott user using SYS schema." was incorrect.

                       

                       

                       

                       

                      When you do connect remotely via sysdba, you're logging in as SYS. SYS is always linked with SYSDBA. You can use your named user as sysdba, which provides you a way of logging in as SYS remotely. The password file is exactly for this, to authenticate you when there is no data dictionary available, to bring up the database for instance.

                      While I had not covered remote login as sysdba (many consider that a security risk), your statement is correct and refutes the statement "So if you login using "as sysdba" you are connected to scott user using SYS schema."

                      The following demo is for the benefit of Atif Aqbal:

                       

                      First, we grant SYSDBA to SCOTT

                      SQL> grant sysdba to scott;

                       

                      Grant succeeded.

                       

                      SQL> select * from v$pwfile_users;

                       

                      USERNAME                       SYSDB SYSOP SYSAS

                      ------------------------------ ----- ----- -----

                      SYS                            TRUE  TRUE  FALSE

                      SCOTT                          TRUE  FALSE FALSE

                      SQL> show parameter remote_login_passwordfile

                       

                      NAME                                 TYPE        VALUE

                      ------------------------------------ ----------- ------------------------------

                      remote_login_passwordfile            string      EXCLUSIVE

                      SQL>

                       

                      Then we do a remote connect as sysdba, using SCOTT's credentials, then check to see who he is actually connected as:

                       

                      C:\Users\estevens\Documents\sql>sqlplus scott/tiger@mydb as sysdba

                       

                      SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 14 06:44:01 2014

                       

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

                       

                       

                      Connected to:

                      Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

                       

                      SQL> show user

                      USER is "SYS"

                      SQL> select username from v$session where username is not null;

                       

                      USERNAME

                      ------------------------------

                      SYS

                      SYS

                       

                      SQL>

                       

                      In the last query above, if the use had actually connected as SCOTT (but "using" the SYS schema) we would see SCOTT listed as the username of a session.

                      So, even when the user HAS been specifically granted SYSDBA, and those credentials are used to verify a logon as sysdba, the user is still connected as user SYS.  He is not connected as himself and simply "using" the SYS schema. 

                      • 8. Re: Query regarding user privilege as sysdba
                        846411

                        Hi,

                         

                        User and schema both are different I hope you agree with this.

                        Not so.  User only needs to have proper normal privileges.

                         

                        If you want to login to scott user as SYS schema then you will have to specify "as sysdba" while login.

                         

                        No, he is NOT connect as SCOTT. He is connected as SYS.  Use of the 'as sysdba' option actually overrides whatever username was supplied.

                        In the below example, how can we be "connected to fubar user using the SYS schema", when there clearly is no user FUBAR at all?

                         

                        If you connect to database by directly login in to OS  then this will use OS authentication.

                        Even if you specify incorrect password for that user it will allow you to login as SYS schema as its using OS authentication.

                        But if you have a password file configured and if you grant scott sysdba then user is added to password file and user has access to connect using SYS schema.

                         

                        So he can directly connect from any client as a SYSDBA and use SYS schema. But he is still connected as Scott user.

                        • 9. Re: Query regarding user privilege as sysdba
                          EdStevens

                          Perhaps it is a language barrier.

                          Connecting "as sysdba" NEVER connects "as" any user but SYS.

                          The method used to authenticate the user may depend on the user's name and password, as in the demonstrated case of remote login.  But they are still connected as SYS, which I also demonstrated.  Authentication and actual connection are two different concepts. 

                          "Access to any schema" means simply the ability to access objects in the schema.  If I can query any table owned by SYS, if I can execute any procedure owned by SYS, then I can "access" the SYS schema, regardless of who I am connected as.

                          • 10. Re: Query regarding user privilege as sysdba
                            846411

                            Authentication and actual connection are two different concepts.

                             

                            Okay I totally agree with your statement. Will clears my doubt regarding the login as sysdba. So here it is using scott authentication to connect as SYS user.

                             

                            Thanks for clarification. You are a star.