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 ;
GRANT dba TO project1;
But why??? You grantDBA
privilege to that user, and expect to hide data from other users. Do not useDBA
, grant proper privileges likeCREATE TABLE
,CREATE VIEW
, etc.