CREATE USER AND PRIVILEGES 

Purpose

Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user.

CREATE USER sidney 
    IDENTIFIED BY out_standing1 
    DEFAULT TABLESPACE example 
    QUOTA 10M ON example     
    PASSWORD EXPIRE;

 Privileges

System privileges

System privileges are privileges given to users to allow them to perform certain functions that deal with managing the database and the server . Most of the different types of permissions supported by the database vendors fall under the system privilege category. Let’s go through some examples of system privileges in Oracle and SQL Server.



EXAMPLE



GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO SIDNEY (WITH ADMIN OPTION);



TO CHECK PRIVILEGE



SELECT * FROM USER_SYS_PRIVS;



Object privileges

Object privileges are privileges given to users so that they can perform certain actions upon certain database objects – where database objects are things like tables, stored procedures, indexes, etc. Some examples of object privileges include granting a particular database user the right to DELETE and/or SELECT from a particular table. This is done using the GRANT clause.



GRANT SELECT,UPDATE,DELETE,INSERT ON SCOTT.DEPT TO SIDNEY (WITH GRANT OPTION);



TO CHECK PRIVILEGE



DESC USER_TAB_PRIVS_RECD.



REVOKE statement



Use the REVOKE statement to remove privileges from a specific user or role, or from all users, to perform actions on database objects. You can also use the REVOKE statement to revoke a role from a user, from PUBLIC, or from another role.




REVOKE CREATE TABLE, CREATE VIEW FROM SIDNEY.

REVOKE SELECT, UPDATE, DELETE,INSERT FROM SIDNEY.