Advice on tablespace and beginning of studies

[Rafael_Santos]

Hello gentlemen,
I'vê been studying for hollow and I was interested in deepening some knowledge.
During the study walk I was interested in creating a specific user for studies.
I created a student user, Tablespace Users and Temporary Tablespace Temp.
Then I created Roles for that user who would be select for all tables in the HR and OE schemas and I have gritted CREATE TABLE, view and trigger for the student.
When I performed the tests I saw that the student user could not create tables because Tablespace User does not have this permission. "Error Ora-01950: There are no privileges in Tablespace Users".

My questions:
For all grants of granted to a User should also be made to a tablespace?
The best way to create a student user, would you create a database or a tablespace?
What am I doing is interesting for the Career of DBA?

Hugs and grateful for aid granted.

[stcoutinho]

Hi Raphael!

It would be legal for you to assign two special permissions so that a user created by you (eg xpto) can work with tablespaces (eg TBS_XPTO):

Execute These commands:



You must repeat these two commands for all the tablespaces where you want your "XPTO" user to create tables or indexes.

The first command (default) indicates that any table or index created by you is stored on the "TBS_XPTO" tablespace. Objects will be created automatically there when you stop mentioning the tablespace name on the CREATE TABLE or CREATE INDEX commands.

The second command (quota) indicates that you will be allowed to use the space of the tablespaces mentioned (eg TBS_XPTO) for the creation of tables or indices.

I always prefer to restrict user permissions so they only work with the tablespaces planned by me. I do not really like to give genial permissions like "DBA", "Unlimited tablespace", etc. At first it seems simpler for the DBA, but keep in mind that permissions beyond recommended can "zonear" the administration of your database in the future.
Ah, I do not know if you took this care when creating your "XPTO" user, but I would recommend another command, which would need to run a single time:






1]]

The above command will allow your XPTO user to use Tablespace Temp for ordinations and other operations that can not be performed in memory. It will also prevent physical ordering from being held on Tablespace System.

A big hug and good studies.

Sergio Coutinho

[stcoutinho]

Raphael,

I had forgotten your questions -Rsss - Come on:
A) For all grants to a USER should also be made to a tablespace ?
A: Then apply the commands that I explained to you in the previous post. If you need your user to create tables / indices in 4 tablespaces - for example - then repeat the two commands for each of them.

B) The best way to create a student user would be to create a database or a tablespace?

A: You do not need to create a new database or tablespace to work with a student user. Let's say you have an XE base already created. You can create the Xpto user and ask for it to use the Tablespace Users and Temp of this XE base.
But for the purposes of studies as a DBA, to learn how to use Bank's resources better, I recommend following this script when creating a new user:
- Create a bank profile;
- Create a bank roller;
- Create a data tablespace and another for indices;
- Create the new user;
- Assign permissions to this user (tablespaces) and to the role;
- Assign the user to the user;
- Assign the profile to the user;

c) What am I doing is interesting for the Career of DBA?
A: If your goal is to become a DBA I would say your posture is simply excellent for a beginner!
If you have a base for your personal use, do not be afraid to test all commands related to user administration and security. Do not manage the bank by web pages, because someday you can be required to sit in front of a UNIX terminal and will only have an access password to solve problems.

feel free to post other questions in the forum. I'm sure colleagues will be happy to help a person interested in learning!

A big hug and good study!

Sergio Coutinho

[Rafael_Santos]

Coutinho, I understood almost everything.

You told me to create two tablespaces one for data and another for indexes, but if I hold the command that said to me:

the first (Default) indicates that any table or index created by you are stored on the "TBS_XPTO" tablespace.

How can I designate for that user when it creates an index, the index Go to Tablespace XPto2 while the table and data stand on Tablespace XPto1.

How do I do this, I do not quite understand, can you explain?

Hugs and thank you Coutinho

[stcoutinho]

Hi Rafa,

I mentioned data tablespace and more than one issue of organization. A tablespace can store tables or indexes, and Oracle will not know how to identify where a table or index should be created.

You will always need to complement your Create commands with the Tablespace parameter.

Ex:

Select all

CREATE TABLE <TAB_XPTO> .... TABLESPACE <TS_XPTO_DAT>; 
CREATE INDEX  <IDX_XPTO> ....  TABLESPACE <TS_XPTO_IDX>;

If you forget or intentionally do not complete these Create commands with the "tablespace <..>" then Oracle It will automatically create the object (table / index) on the tablespace that you indicated with the

Select all

ALTER USER DEFAULT TABLESPACE <..>

command

Hugs,

Sergio Coutinho

[Rafael_Santos]

I did Coutinho, thank you.
Just one more question, can you grant permission to a tablespace?

I did not understand an event that occurred in my bank.
I created a tablespace
I changed the user to have as default tablespace the tablespace created
I counted permission to the user to create tables

later when I tried to create A table on the tablespace, an error appears that does not have permission to create in that tablespace.

I searched the command that told me quota

Select all

ALTER USER <XPTO> QUOTA UNLIMITED ON <TBS_XPTO>;

and when I performed this command my user managed to create a table.

Now see if my reasoning is correct:

Once I released undetermined space for this user, he can use space, since before he did not possess space set to use. That is every user who is to create something needs to have a limited space or that is unlimited. Is my reasoning correct?

Thank you very much Coutinho

[stcoutinho]

Rafa,

With the unlimited quota command, the user-created objects may occupy the entire space of the tablespace. Remember that a table / index if it contains data, occupies space on the tablespace.

You do not need to use the Unlimited parameter. One can mention a maximum space that your objects will occupy on this tablespace. I prefer however to use Unlimited.

Then this command (quota), in addition to allowing the creation of objects from a given user on a tablespace, also establishes a maximum limit consumed by these objects inside the tablespace.

Hugs,

Sergio Coutinho

[Rafael_Santos]

Thank you Coutinho