12 Replies Latest reply: Jan 24, 2014 1:24 PM by jgarry RSS

    User accounts

    KMWS

      SQL> connect UserName1/Password1;

      Connected.

      SQL> show user

      USER is "UserName1"--------->I am connected as (UserName1) here.

      SQL> connect UserName1/Password1 as sysdba;

      Connected.

      SQL> show user

      USER is "SYS"-------------->Which account am I connected as here?What happened to UserName1 here?

      SQL> connect UserName1/Password1;

      Connected.

      SQL> show user

      USER is "UserName1"---------->I am connected as (UserName1) here.

      SQL> connect / as sysdba;

      Connected.

      SQL> show user;

      USER is "SYS"---------->Which account am I connected as here?What happened to UserName1 here?

      SQL>

        • 1. Re: User accounts
          Baris Yildirim

          Hi,

           

          When you use "/ as sysdba", you are authenticated by OS.

           

          connect UserName1/Password1 as sysdba --> Bold part isn't considered.


          Regards

          • 2. Re: User accounts
            Baris Yildirim

            if you want, try to connect by a non-existence user. You will connect successfully by sys.

             

            connect sdsds/dfdfd as sysdba

            • 3. Re: User accounts
              KMWS

              Then isnt security a concern here ? If I have access to a workstation I can login as fsadfsadf/fasdfs as sysdba;

              My user will be SYS.

              • 4. Re: User accounts
                Baris Yildirim

                Hi,

                you can only connect (as sysdba) on db server not workstation.

                 

                Regards

                • 5. Re: User accounts
                  KMWS

                  Then itsn't that still a concern if someone has access to a server then would it be not a concern that he can login as SYS without even having an account ?

                  • 6. Re: User accounts
                    rp0428
                    Then itsn't that still a concern if someone has access to a server then would it be not a concern that he can login as SYS without even having an account ?

                    Sure it is a concern - that is why you need to use a proper authentication method for DBAs.

                     

                    See the DBA guide for the details - including the answer to your original question

                    SQL> show user;

                    USER is "SYS"---------->Which account am I connected as here?What happened to UserName1 here?

                    SQL>

                    http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba006.htm

                     

                    Connecting with Administrative Privileges: Example

                    This example illustrates that a user is assigned another schema (SYS) when connecting with the SYSDBA system privilege. Assume that the sample user oe has been granted the SYSDBA system privilege and has issued the following statements:

                    CONNECT oe  CREATE TABLE admin_test(name VARCHAR2(20));  

                    Later, user oe issues these statements:

                    CONNECT oe AS SYSDBA  SELECT * FROM admin_test;  

                    User oe now receives the following error:

                    ORA-00942: table or view does not exist  

                    Having connected as SYSDBA, user oe now references the SYS schema, but the table was created in the oe schema.

                    That doc discusses the various authentication methods that can be used:

                    In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA or SYSOPER privilege:

                    •   Operating system (OS) authentication
                    •   A password file
                    •   Strong authentication with a network-based authentication service, such as Oracle Internet Directory

                    These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. (They can also be used when the database is available.)

                    • 7. Re: User accounts
                      jgarry

                      That security issue would be a concern if you allow remote access for sys users, or use an OS that isn't very well secured.  Don't do those things.

                      • 8. Re: User accounts
                        KMWS

                        Assume I have the server on my workstation and I have logged into my workstation and for left my workstation unlocked.

                        using the SYSDBA privilege anyone can login to the database.How can I make this situation more secure even I leave my workstation unlocked and make sure no one can enter into the DB?

                        • 9. Re: User accounts
                          Brian Bontrager

                          Lock your workstation.  Set a screen saver to automatically lock the workstation after a very short time if you are prone to forget the most basic of security steps.  At some point personal responsibility comes into play.

                          • 10. Re: User accounts
                            Brian Bontrager

                            Ironically, I was reminded of this at a conference a few years back.  We were doing a security exercise where multiple teams each secure a system and then we switched workstations and tried to see what we could get into of the other teams.  Since it was a generic lab setting, I neglected to lock the workstation, so the other team stepped right up and had a very easy head start, while I kicked myself at walking up to their station and faced my first obstacle... the locked workstation login screen.

                            • 11. Re: User accounts
                              jgarry

                              I've come to the habit of just locking whenever I get up from my workstation.  It's not that I don't trust my cow-orkers - they can sysadmin anyways - I'm more worried about someone sitting down because it's closer than their desk, and working on something different than they thought.

                               

                              Actually, maybe I shouldn't trust my cow-orkers.  (Aw dang, I thought I could insert an image)

                              • 12. Re: User accounts
                                rp0428
                                Assume I have the server on my workstation and I have logged into my workstation and for left my workstation unlocked.

                                using the SYSDBA privilege anyone can login to the database.How can I make this situation more secure even I leave my workstation unlocked and make sure no one can enter into the DB?

                                Instead - let's 'assume' that you actually read that doc that I quoted.

                                 

                                Then let's 'assume' that you actually implemented authentication the way that doc describes.

                                 

                                Now not just 'anyone' can login to the database.

                                 

                                AMAZING!