Oracle users and Roles

1. User Management

1.1 create a user (Database verification)

Create user dave identified by pwd

Default tablespace users

Temporary tablespace temp

QUOTA 5 m ON users;

 

1.2 modify a user

Alter user dave quota 0 on system;

 

1.3 Delete a user

Drop user dave;

Drop user dave cascade;

 

1.4 Display User Information

SELECT * FROM DBA_USERS

SELECT * FROM DBA_TS_QUOTAS

 

Ii. System Permissions

System Permissions	Function
CREATE SESSION	Connect to database
CREATE TABLE	Create a table
CREATE TABLESPACE	Create a tablespace
CREATE VIEW	Create View
CREATE SEQUENCE	Establish Sequence
CREATE USER	Create a user

System permission refers to the right to execute a specific type of SQL command, which is used to control one or more database operations that a user can perform. (The new user does not have any permissions)

 

2.1 Grant System Permissions

Grant create session, create table to dave;

Grant create session to dave with admin option;

 

OPTION: admin option gives the user the permission to delegate system permissions.

 

2.2 Display System Permissions

 

View All system permissions:

Select * from system_privilege_map;

Display the user's system permissions:

Select * from dba_sys_privs;

Displays the system permissions of the current user:

Select * from user_sys_privs;

Displays the system permissions of the current session:

Select * from session_privs;

 

2.3 revoke system Permissions

Revoke create table from dave;

Revoke create session from dave;

1 2 3 4 Next Page