0

I've done some research about it but I found some answers that are not clearly saying if it is possible or not.

Right now, I have a user admin. Everyone in my team is using it to create other users. One user = one project. When we create the users, we grant all privileges to him.

Now, we would like to be a bit more secure (I know, it's absolutely not secured right now).

I thought, we could have a user, let's name him "User_Creator", to have the sole purpose of creating other users (projects). The new user, let's name him "Project1", can do whatever he wants in his own schema (create/drop/alter/insert/delete). When User_Creator, creates another project (Project2), I would like that Project1 can not see data from Project2 and Project2 from Project1.

Is that even possible ? I understand that the grant/revoke is done on the object-level right ? Do I need to create a trigger to revoke access to other users immediately after the creation of a new object?

Edit1: I've created my user "User_Creator" like this:

CREATE USER user_creation IDENTIFIED BY abcdef; 
GRANT RESOURCE TO user_creation WITH ADMIN OPTION; 
GRANT dba TO user_creation with admin option;
CREATE ROLE user_creation_role; 
GRANT CREATE SESSION TO user_creation_role with admin option; 
GRANT ALTER USER TO user_creation_role;
GRANT CREATE USER TO user_creation_role WITH ADMIN OPTION;
GRANT DROP USER TO user_creation_role;
GRANT CREATE TABLE TO user_creation_role with admin option; 
GRANT user_creation_role TO user_creation ;

And a project is created like this:

CREATE USER project1 IDENTIFIED BY abc;
GRANT CREATE SESSION TO project1;
GRANT dba TO project1;

Edit2:

The answer is to not grant DBA and only grant the creation. That way, user will not share data. Thank you for your help guys. Sorry if it was a bit of a rookie question.

I create users like this now:

CREATE USER user_creator IDENTIFIED BY abcdef; 
GRANT RESOURCE TO user_creator WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO user_creator with admin option;
CREATE ROLE user_creation_role; 
GRANT CREATE SESSION TO user_creation_role with admin option; 
GRANT CREATE USER TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE TABLE TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE VIEW TO user_creation_role WITH ADMIN OPTION;
CREATE USER project1 IDENTIFIED BY abc;
GRANT CREATE SESSION TO project1 ;
GRANT CREATE TABLE TO project1 ;
GRANT UNLIMITED TABLESPACE TO project1 ;
GRANT CREATE VIEW TO project1 ;
GRANT CREATE PROCEDURE TO project1 ;
  • What you're describing is how Oracle works by default - objects created by schema1 are only accessible by that schema. (Ignoring super users.) – Mat Aug 4 '16 at 11:44
  • GRANT dba TO project1; But why??? You grant DBA privilege to that user, and expect to hide data from other users. Do not use DBA, grant proper privileges like CREATE TABLE, CREATE VIEW, etc. – Balazs Papp Aug 4 '16 at 13:07
  • 1
    I understand now, so I granted only the creation and I had to grant the space on the tablespace also. And now, indeed, projects can't see data from other projects. Thanks so much for your help – Aymeric Aug 4 '16 at 13:13
  • You should leave off the admin options. This allows users to grant permissions to other users. You don't want this. – Andrew Brennan Aug 4 '16 at 14:49
1

This is the default behaviour in Oracle. Users do not have access to other user's objects unless explicitly granted.

The DBA role includes the "GRANT ANY OBJECT" privilege, allowing the user to see all database objects: https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba005.htm https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm

You should revoke the DBA role from every app user and stop granting it to new users.

  • Thanks for your answer, I just edited (Edit1) my post to show you how I create the users. – Aymeric Aug 4 '16 at 12:37
  • OK I'll edit my answer in response – Andrew Brennan Aug 4 '16 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.