Apex - Unlimited Tablespace

[milhorini]

I am using APEX, but the doubt also serves when I use SQL Developer or SQL Plus.

When using Apex, I create a desktop. When I have two or more users created in Apex residents in the same desktop, the tables that one creates the other can see and use, and vice versa. When I put users in different desktops, one can only see the other tables if there are awards such as Grant Select, for example.

I read in several forums that a user can read all the database tables when creates a user and establishes Grant Resources to it, which implicitly guarantees Unlimited Tablespace . That is, a user can read all the tables of a database because of Unlimited Tablespace . I took a test at Apex and this was not confirmed. In SQL Plus, I created two users. Inside one of them, I created several tables. In the other, only one. In the first, I gave Grant Connect, Resources and in the second, I gave Egant , as Grant Creator Table, Create View ... , not assigning Unlimited Tablespace . In Apex, I created two distinct workspaces based on these two created users. Then I created two users in Apex.

Conclusion: When the two users are in the same work area, one can access the other tables. When they are created in different desktops, they can not access the tables of the other, even if Grant Unlimited Tablespace .

After all, why does Unlimited Tablespace serves ? How can I see the concessions that are assigned to the user when I give a Grant Resource, Connect ?

[stcoutinho]

Hello Milhorini,

We are talking about two distinct products. One of them would be Oracle Apex and another would be Oracle Database. Both have different administration / security concepts.

The permission of "Unlimited tablespace" has nothing to do with access permissions to the bank tables.

When you create a database user, I can for example "limit" the space occupied in tablespaces by your objects (tables, indexes).

For example, I created in the database an Xptouser user and desire that the table and index objects created by it occupy a maximum of 500 m on Tablespace Users. In this case, I could run as DBA the command

Select all

ALTER USER XPTOUSER QUOTA 500M ON USERS; 

but let's say you do not want your Xptouser user to undergo space restrictions on Tablespace Users. In this case you can run as DBA the command

Select all

ALTER USER XPTOUSER QUOTA UNLIMITED ON USERS;

Let's say that you do not want your Xptouser bank user to undergo any space restriction on any bank tablespaces. This way you can run as DBA the

Select all

GRANT UNLIMITED TABLESPACE TO XPTOUSERS;

command


"Workspace" and "User" Apex features are Oracle Apex product features and not from the database.

When you create a workspace, you so need to point to a database schema (a schema is a bank user who has objects created in it - tables, synonyms, views, etc. ).

So, let's say you created in Apex:

- Workspace System, pointing to the Xptouser bank schema (this is a bank user);
- Two users (Joao / Antonio) for this workspace system (these are Apex users);

In theory, any bank object created with the Xptouser (Bank) user will be visible to JOAO (APEX) and ANTONIO (APEX) users and there is no need to create any additional permission Bank.

Now, let's say you created in APEX:

- Workspace System2, pointing to the same XPTOUSER bank schema (this is a bank user);
- Two users (ZE / luis) for this workspace system2 (these are APEX users);

Users Apex Joao, Antonio, Ze and Luis will be able to access the same tables as they belong to the same Xptouser schema;

Let's assume a last activity. Let's say you created:

- Workspace System3, pointing to the WXYZUser bank schema (this is a bank user);
- Two users (ANA / MARIA) for this workspace system3 (these are APEX users);

By the fact that system3 points to a different schema (WXYZUser), Ana and Mary will not be able to access Schema Xptouser tables, while Joao, Antonio, Ze and Luis will not be able to access the Schema tables WXYZUser.

In this case, how would it be possible to access all tables? I would have to enter the bank like Xptouser and give the access permissions to his bank objects for the WXYZUser bank user. And you should repeat the same process in WXYZUser by assigning access permissions to his tables for Xptouser. Other than that, it should create private synonyms in Xptouser and Wxyzuser, so that one could consult the tables of each other without the need to inform the schema
ex:

Select all

SELECT * FROM XPTOUSER.TABELA_A

Sorry, I wrote a lot and even then it may not be very clear to you. It is necessary to understand that we are talking about two different universes (Apex and Bank). When we talk about users and apex permissions, it does not mean that we are exactly talking about database.

Maybe this article below (from Oracle) allows you to understand a little about database permissions:
http://www.oracle.com/technetwork/pt/ar ... 6-ptb.html
Other than that, it would be interesting to give a read on the Oracle Apex manuals, to check how you can organize the workspaces.

Hugs and all good,

Sergio Coutinho

[milhorini]

Sergio, you were very clear and helped me a lot.

Only one thing was not clear, not even after reading the link sent. When you talk about tablespace , are you referring to schema (user)?

[milhorini]

Sérgio, you were very clear and helped me a lot.

Only one thing was not clear, not even after reading the link sent. When you talk about tablespace , are you referring to schema (user)?

I can not edit, so I go Correct my question above.

Tablespace Is it like a "package" (Package Java) or a location where your tables will be placed after created? If I do not specify one by default, will you use Users?

[stcoutinho]

Hi Milorini,

A database is composed of Datafiles, which are Oracle files in the operating system.

Open a bank session like System and run this command to see the datafiles of your bank:

Select all

SELECT * FROM DBA_DATA_FILES

Now, a tablespace would be the set of one or more Datafiles.

is on the tablespaces that you store the tables and indices, for example.

Open a bank session like System and run this command to see the Datafiles of your bank:

Select all

SELECT * FROM DBA_TABLESPACES

When you create a user in the Database, you can assign to it a Temporary Tablespace (usually Name Temp, where Oracle can make records of records) and a Default tablespace, where the user can create their tables and indexes. This information can provide at the time of user creation. Example:

Select all

CREATE USER XPTOUSER IDENTIFIED BY Q1234R 
TEMPORARY TABLESPACE TEMP 
DEFAULT TABLESPACE USERS;

You can also create the user without these parameters, but both the temporary area and the default tablespace for this user will be the Tablespace System (O which is a bad practice). The situation I gave you now would happen if you did:

Select all

CREATE USER XPTOUSER IDENTIFIED BY Q1234R;

In the example above, you could change the properties of this user by assigning the temporary Tablespace and default. Example:

Select all

 ALTER USER XPTOUSER TEMPORARY TABLESPACE TEMP; 
ALTER USER XPTOUSER DEFAULT TABLESPACE USERS;

All commands above do not suffice for you to be allowed to create objects in the bank. You need to for example give CREATE SESSION permissions, Alter Session, Quota Unlimited On, Resource for Xptouser user to create and manage tables. It is at this point that the link article enters, which I replaced for you.

Maybe I have not explained to you in a didactic way and at the end of this conversation the amount of doubts that I have proven to be greater than before the applications. It would be nice to check if you can get an introductory book in Portuguese on Oracle, where these concepts of Tablespace, User, Roles, Grants are explained in more detail. You do not have to go deeper into understanding these themes .. only enough to be able to interact the Apex with the database.

Apex itself also has its concepts of Users, Workspaces, which are only ways to allow a better organization of your projects in Apex. If you created a workspace in Apex pointing to a database user, open a bank session with this user and refer to this APEX data dictionary information:

Select all

 
SELECT * FROM APEX_WORKSPACES 
SELECT * FROM APEX_WORKSPACE_SCHEMAS 
SELECT * FROM APEX_WORKSPACE_APEX_USERS 
SELECT * FROM APEX_WORKSPACE_DEVELOPERS 
SELECT * FROM APEX_WORKSPACE_GROUPS 
SELECT * FROM APEX_WORKSPACE_GROUP_USERS 

While - for the database - you have another data dictionary:

Select all

 
SELECT * FROM DBA_DATA_FILES 
SELECT * FROM DBA_TABLESPACE 
SELECT * FROM DBA_USERS 

Apex, if you are moreovered, it consists of a series of tables metadata where your projects are stored. When running Apex, these metadata are read, interpreted by APEX and transformed into the screens, reports and graphics you see in your applications.

feel free to continue asking, if you think you need some more guidance.

Hugs,

Sergio Coutinho

[milhorini]

I understood everything you said, Sérgio!

Before his explanation, I had already held many of the commands you passed. Previously, I had created users in SQL Plus without passing tablespace . It had everything stayed in System . I saved my whole bank and did it again. I still learned a lot at Apex.

With regard to concessions, I am manually applying one by one. Sometimes I apply Connect and resource , revoking the unlimited tablespace .

Thank you!

Wilson

[milhorini]

Sergio ...

One thing that is intriguing me is the following:

I created two different users in two tablespaces also distinct in SQL Plus . In APEX, I created the work areas based on these tablespaces . I created two Apex users.

One of the users has the DBA Granted . The other, no. With this, the first user can access the second tables, but the second can not see the tables from the first.

In SQL Developer, I created two connections based on the users I created in the bank. The problem is that the bank user who is not DBA is able to access the other user's tables.

While in Apex behavior is desired, in SQL Developer is not. Why is this happening? [/ u]

I loosen as a DBA in Apex to show you the privileges I gave to the Accounts in its creation:

Workspace Schemes

Schemes: Wilson
tablespaces: Wilson, System
Benefits: Create Any Directory, Create Materialized View, Creature Procedure, Create Sequence, Create Session, Create Synonym, Create Table , CREATE TABLESPACE, CREATE TRAGGER, CREATE USER, CREATE VIEW, DROP USER
Database assignment privileges: Connect, DBA, Resource
Workspace Schemes

Schemes: Milhearini
tablespaces: Milhearini
Benefits: Create Any Directory, Create Materialized View, Creature, Create Sequence, Create Session, Create Synonym, Creator Table, Creator Tablespace, Creator Trigger, Create User, Create View, Drop User
Database assignment privileges: Connect

You can see that the first user is in two tablespaces , even though I have established it only one. System is because of it being dba ?
PS: I underlined one of the question to get easier to see.

Wilson

[stcoutinho]

Hi Milhorini,

I ask you to take a test in SQLPlus.

I understand that you created two bank users. Just to simplify, I'll call them from User_a and User_B.

I will also simplify in the theme tables: User_a bank user has the table_a table and the user_b user has the User_B table.

In this way, proceed with these tests (which will be done exclusively in the bank):

1) Open a session in SQL * Plus as User_A;
2) Perform

Select all

SELECT COUNT(*) FROM USER_B.TABELA_B;

3) Open a session in SQL * Plus as User_B;
4) Perform

Select all

SELECT COUNT(*) FROM USER_A.TABELA_A;

If only one of them is DBA either no, then one of these select count (*) would give some errors.

If this error does not happen in SQL * Plus, then you need to review the assigned permissions to each of the users.

If this error occurs in SQL * Plus, but in SQL Developer No, then I recommend that you review the information of the connections you registered in it.

Publish the results of these tests on this topic. Depending on the case, I can then publish some queries here for you to review the permissions of the two users.
Hugs,

Sergio Coutinho


Connect as the DBA user and make Select Count (*) on a table

[milhorini]

I can not create tables in the account that is not DBA (Milorini account).
When soon as System and attribute to the "Milhorini" account Resources , the creation of tables is accepted. When I relse only Unlimited Tablespace of "Milhearini", it gives the error that tablespace There are no privileges.

I tried to see the privileges given to "Milhearini" through

Select all

select privilege from dba_sys_privs where grantee = 'RESOURCE'

and gives the message of "table or view not found." The same occurs when I put 'Connect'. With the "Wilson" account these instructions work.

I loosened as admin in Apex and I saw that Milhorini has the privilege Connect and resource .

I did not understand these mistakes. Do you know why they are taking place?

[milhorini]

I can create tables again. I read Oracle's documentation and 11G R2 has a peculiarity. When I create a tablespace , I give you a size. In the case of Tablespace Milhearini, I gave you Size 25M Autoextend on Next 25M EXTENT MANAGEMENT Local; . Then I assign the Resource privilege to "Milhearini". Then I revived this privilege.

According to R2 documentation, when you assign the Resource privilege, Unlimited Tablespace is given as well. If you later remove Resource , the size of the original tablespace (25m, in my case) is lost. You have to assign again through Alter User User Quota Quota on Tablespace;


For this reason I was not able to create tables. I know what caused the presence of creating tables, but I do not know why.

The commands that generated the Table or View messages not found whether to the "Milorini" account is not DBA. Thus, dba_sys_privs was not found.

If I have spoken something wrong, please correct me!

Wilson

[stcoutinho]

Hello Milhorini,

I would recommend you keep the resource grant for the schemas where you will work with Apex. Do not worry about the permission of Unlimited Tablespace.

The important thing is that your bank users can create tables. Remember that - for end users of your Apex application - it does not matter how Workspace will be organized.


I would recommend (for now) to adopt this philosophy: Create a Workspace pointing to a single database schema. I do not know if it's worth it to be creating a complex series of workspaces and schemas at this time.

Keep your environment simple enough, and allow you to develop applications. I believe this would be the most important at the moment, if your focus is to develop applications.

You can create multiple Apex applications in the same workspace, all accessing the tables from the same schema.

The Apex application of a Workspace will not conflict with other applications of the same workspace.

If you want to differentiate the tables from one schema that serve different APEX systems, you can adopt a nomenclature type for the tables.

Let's say you have a backup system and another performance system. All tables would be in the same schema, but you could adopt the pattern "bak_ <name_tabela>" for the backup system and "per_ <name_tabela>" for the performance system.

Over time, you will probably advance both Apex and Database. This will require you a certain time to assimile all these concepts. Usually these bank concepts are obtained in database management courses. Or, through a little self-study, specific questions in this forum, etc.

Then instead of breaking your head now, you would recommend that you focus on the development of Apex applications, leaving aside - for a while - these workspace organization issues, Schemas and Grants. Keep your environments simple at this time. Later, you can focus on this theme of organization.

I'm sorry if I do not go into detail about database administration now. This subject would be a bit complex to be cleared (in a nutshell) in this Topic of Apex. It involves some basic concepts for which a little research is needed, reading books, etc.

If you have any relevant Apex issues you need explanations, please update the post.

Hugs and all the best. I'm hoping you can develop your first applications in Apex. As personal experience, I can tell you that I sorted a little at first, but with experience, I have noted that the development time for new applications has reduced considerably.

Sergio Coutinho

[milhorini]

Sergio ...

I have an account that has the following privileges: Connect, Resource and DBA. So, this account is a DBA, right? With this account, should I not be able to access the tables of the other users who are in other areas of work? Well, I can not access.

Hugs!

Wilson

[milhorini]

Apex is not allowing the DBA account to access the other account tables - which is not DBA. However, in SQL Developer and SQL Plus access is being allowed. Do you know why?

Wilson

[stcoutinho]

Hi Milhorini,

honestly would not know, because if a bank user has the permission of DBA, then he would be able to read any table, as long as it informs the Owner of the table
Ex:

Select all

SELECT COUNT(*) FROM USER_B.TABELA_B

If you want to see the table without informing the Owner, it would be necessary to create a private synonym.

As explained earlier, try to create a simple development environment. Avoid creating a complex environment of many schemas or workspaces. Or you focus on the development / study of APEX or you focus on the database administration study (DBA).

maybe other forists can have a different view of this problem.

Hugs,

Sergio Coutinho

[stcoutinho]

Hello Milorini,

I would like to suggest two books well interesting database administration:

Oracle 10g
Author: Ramalho, Jose Antonio Alves
Publisher: Thomson Pioneer http://www.livrariacultura.com.br

Oracle Database 10g Express Edition - Interactive
Basic guide for guidance and development
Author: Manzano, Jose Augusto ng
Publisher: Erica

The Ramalho has been editing Oracle books since version 8i. His books I could learn enough about database management. I think it would be useful for you to advance the theme of bank administration and even PL / SQL programming, if this is your desire.

Hugs,

Sergio Coutinho