How to host and separate multiple projects on one Oracle Database

oracleschemausers

From Difference between database vs user vs schema

I'm trying to understand the concepts of the schema, user and database.

My current understanding is you cannot create a schema separately.
You can create a user and a schema will be automatically created.

Then my question is, what if you want to create 3 users and only use one schema by granting privileges to only one schema? Will there be 2 empty, unused schemata?

For example, in a project called "Killer Web", I have 3 application developers and I am the DBA.

So I created 3 users for each developer, dev1, dev2 and dev3.
And I granted on these three devs, access and other privileges to the actual schema on which the 3 devs will actually work.
Would that create 3 schemata, empty and unused?

If I wanted to create a schema called "KillerWeb", would I have had to create a "user" called "KillerWeb"?

How do I host multiple projects on one Oracle database and separate them?

In MySQL, I would just create a database for each project. (Not sure if this is the correct practice though.)

Best Answer

What if you want to create 3 users and only use one schema by granting privileges to only one schema? Will there be 2 empty, unused schemata?

[...] Would that create 3 schemata, empty and unused?

Essentially yes. You'll use the users to log in to the database, but some of them won't have any objects, i.e. they'll have empty schema. (This is not uncommon at all.)

_{But none of this "creates empty schemas" in a technical sense. A schema is just a concept in Oracle, it doesn't have a concrete existence. You have users, and they own objects. Users and objects exist in the database in a very concrete sense, "schema" does not. It is just a term used to describe a user and its objects.}

If I wanted to create a schema called "KillerWeb", would I have had to create a "user" called "KillerWeb"?

Yes, you need to create that user, even if no-one/nothing ever uses it to log in. (This not uncommon either. And you should actually prevent logins if no-one should.)

How do I host multiple projects on one Oracle database and separate them?

One option is creating a user for each application. By default, they won't be able to access each other's objects (unless you grant them the appropriate privileges).

Another option is Oracle's multitenant (additional cost, >= 12g only) option, a.k.a. pluggable databases, which gives you something in between one database with separate users and multiple, independent databases.

Or you could just give each app its own database.

How you decide between those depends on a lot of factors, including your budget, whether there is any shared data (and how you want/must share it), security considerations, backup/restore considerations, whether you suspect you'll want to split them up later, how you plan to scale, etc.

Related Solutions

MySQL user permission on stored procedure

As the error message itself says that user do not have the execute permissions.

mysql> SELECT hello('world');
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.hello'

You need to grant the Execute Permission to that user.For that you need to login as root user and grant the permission as

 grant execute on db.* to user@localhost;

For your other queries :

Yes It is possible that your username is an empty string but it is not safe to create the users like this.
For creating and granting privileges in brief have a look at This Link.
Yes you can grant all the privileges on all entities within a database. for this you can execute a command like

GRANT ALL ON DB_NAME.* TO 'USER'@'HOST' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.

Best Answer

Related Solutions

MySQL user permission on stored procedure

Sql-server – How to connect multiple users/schemas in Oracle 11g

Related Question