This content has been marked as final. Show 23 replies
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.
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
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
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.
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?
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.
I should not check the box DBA role?
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.
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.
$ 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;
Hand out the privileges needed to do the work, but no more than what is needed, its a good guideline/rule/policy to follow.
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..
mhh.. i am kinda confused...
in my windows-register-editor:
But in my environment variables:
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?