1 2 Previous Next 23 Replies Latest reply: Apr 28, 2012 6:52 AM by 933068 Go to original post RSS
      • 15. Re: Oracle XE can not build more than one Database -> meaning?
        Yes, that's pretty much it.

        The DBA role is a powerful one. You don't want just anyone have that role- a DBA user can create users, grant privileges, grant privileges on any user object, drop users, create, update and delete table rows, and create/drop any user tables, procedures, etc. And create/drop database tablespaces too. And change parameters in the in the instance.

        The SYSTEM user has the dba role in any database, "out of the box". There are a few items SYSTEM can not do, two that come to mind are shutting down and starting up the instance.

        Outside of SYS and SYSTEM, its not good practice to let other database users have those privileges. In most cases the RESOURCE role has most any privilege needed to manage a schema- create tables, procedures, indexes, etc. Just the 'create view' privilege is not granted to the resource role.
        • 16. Re: Oracle XE can not build more than one Database -> meaning?
          Assuming that "SYSTEM" creates "admin-user" with the checking box DBA-Role:

          1. The privilege domain of "admin-user" is still empty?
          2. Can "admin-super" directly CREATE USER, or does "admin-super" have to get the CREATE USER privilege first?
          • 17. Re: Oracle XE can not build more than one Database -> meaning?

            I got this from documentation:
            Multiple instances can run concurrently on the same computer, each accessing its own database. For example, a computer can host two distinct databases: prod1 and prod2. One database instance manages prod1, while a separate instance manages prod2.

            So, it seems to be possible to have 2 databases(assuming we have a regular Oracle Database version)?

            So, in one computer, following scenarios are possible?
            database1 <------------ instance1
            database2 <------------ instance2

            database1 <------------ instance2
            database2 <------------ instance1
            • 18. Re: Oracle XE can not build more than one Database -> meaning?
              I also get this from the documentation:

              Single-instance does not mean that only one Oracle instance can reside on a single host computer. In fact, multiple Oracle instances (and their associated databases) can run on a single host computer. A single-instance database is a database that is accessed by only one Oracle instance
              • 19. Re: Oracle XE can not build more than one Database -> meaning?
                Yep, pretty much.

                If user1 has the DBA role, probably quite a lot more responsibility than he/she would want.

                For creating tables, the resource role is a better fit- it has the create table, index, procedure, function, etc. roles that any schema owner could need. Except for the create view privilege, but that is just one tiny little extra step.
                • 20. Re: Oracle XE can not build more than one Database -> meaning?
                  Hallo clcarter,

                  oh thanks, this is a good point. So, I want to create a user in a database, I should not check the box DBA role?? Only connect and resouce?
                  Lets say, the name is "user0".

                  "user0" should later be able to create other users to insert the hours he/she works in a week.

                  So, "user0" is like the administrator, who is each end of month printing the hours an empoloyee works.

                  user0 -> admin, should be able to create users, since only the users know their hours.
                  user1 -> just inserting the hours he/she has done
                  user2 -> just inserting the hours he/she has done
                  user3 -> just inserting the hours he/she has done

                  So, if "user0" wants to create "user1", should "user0" also grant "uiser1" with connect and resource?

                  • 21. Re: Oracle XE can not build more than one Database -> meaning?
                    I should not check the box DBA role?
                    Yes, its your database and you really don't want just anyone with do-nearly-anything-possible rights messing about with your database, unless they know and understand what they are doing.

                    And part of the database administrator responsibilities are making backups, and sometimes performing restores. Limit the grants of the dba role privilege unless you like doing the latter one frequently. Restoring means downtime, and downtime must be minimized.

                    So in your example, user0 could be a resource user, making the tables, and can grant other users the rights to do updates to user0 tables, and let user0 have the create user system privilege. Also you may let user0 create a role for the other users, that way all user0 has to do is grant that role to the other users.

                    But user0 would not need the dba role just to do that, just grant the create user and create role privileges.
                    $ sqlplus /nolog
                    grant create user to user0;
                    grant create session to user0 with admin option;
                    grant create role to user0;
                    connect user0
                     ... password ...
                    create role user_users;
                    grant select, insert, update on <table> to user_users;
                    grant user_users to <userN>;
                    create user user4 identified by user4pass password expire;
                    grant create session, user_users to user4;
                    The user0 should already have the create session privilege, but the ' with admin' bit will let user0 grant that privilege to other users. Letting user0 have the ability to create users and roles as well as grant the create session privilege to the users user0 creates is a better solution than letting him/her have the DBA role.

                    Hand out the privileges needed to do the work, but no more than what is needed, its a good guideline/rule/policy to follow.
                    • 22. Re: Oracle XE can not build more than one Database -> meaning?
                      Thank you for the concrete explanations.

                      I met the theme "environment variable":
                      Assuming I have an Enterprise Version, as long as I know, we have to set the environment variables for ORACLE_BASE and ORACLE_HOME.
                      I am suggested to set the environment variable for the first database with this path:
                      In fact, this will automatically set default from GUI if we dont set it before.

                      If lets say I want to create a second database, will a second path exist? Like:

                      In the XE version, this is totally different:
                      the path is C:\oraclexe\app\oracle\product\10.2.0\server\bin

                      This is confusing..
                      • 23. Re: Oracle XE can not build more than one Database -> meaning?
                        mhh.. i am kinda confused...

                        in my windows-register-editor:

                        ORACLE_BASE: C:\oraclexe\
                        ORACLE_HOME: C:\oraclexe\app\oracle\product\10.2.0\server

                        But in my environment variables:
                        PATH: C:\oraclexe\app\oracle\product\10.2.0\server\bin
                        this is the only regarding oracle.

                        What is the idea, why we set environment variable in PATH? If I am not mistake, in order to access oracle from MS-DOS?

                        1 2 Previous Next