Search code examples
sqloracle-databaseoracle12coracle19c

Creating users under specific tables in oracle

I am supposed to create two users for the Customer Service department and one user for the Inventory department.

Below is the code for one of the users I was creating for customer service department but I don't know how to place the user under the Customer Service department. The Customer Service Department is a table that is already in the database

CREATE USER Patricia
IDENTIFIED BY Patricia001
DEFAULT TABLESPACE extra_tbs
DEFAULT TEMPORARY TABLESPACE Temp
QUOTA UNLIMITED ON extra_tbs
PROFILE Secretary 
PASSWORD EXPIRE
GRANT CREATE SESSION
ACCOUNT UNLOCK
LIMIT 
FAILED_LOGIN_ATTEMPTS 3 
PASSWORD_LOCK_TIME 1
PASSWORD_LIFE_TIME 60
IDLE_TIME 10;
Solution

  • There are several things wrong here:

    First, as mentioned above, users are not created in tables. Privileges on tables, like SELECT, INSERT, UPDATE, and DELETE are granted to users.

    Second, your syntax for CREATE USER is incorrect on several counts:

    a) Of the various "options" you have included, the actual CREATE USER command should only include the following:

    CREATE USER Patricia
IDENTIFIED BY Patricia001
DEFAULT TABLESPACE extra_tbs
DEFAULT TEMPORARY TABLESPACE Temp
QUOTA UNLIMITED ON extra_tbs
PROFILE Secretary 
PASSWORD EXPIRE
ACCOUNT UNLOCK;

    b) Granting a privilege like CREATE SESSION is a separate command:

    GRANT CREATE SESSION TO PATRICIA;

    c) And the LIMIT clause is part of a profile, not a user. You have already referenced the SECRETARY profile, so presumably these settings are already covered there.

    See CREATE USER documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-USER.html#GUID-F0246961-558F-480B-AC0F-14B50134621C