User management and Profile Managment on Oracle 12C

To create tablespace

sql> create tablespace raneshtab datafile '/u01/app/oracle/oradata/prod21/raneshtab.dbf' size 10m;

To create User

sql> create user ranesh identified by ranesh default tablespace raneshtab temporary tablespace temp;

To grant permission to user

sql>grant create session,create table to ranesh;

To revoke any permission from user

sql>revoke create table from ranesh;

To change Password of user

sql> alter user ranesh identified by ranesh1

To allocate quota on tablespace

sql> alter user ranesh quota 10m on raneshtab;

To change default tablespace or temporary tablespace

sql>alter user ranesh default tablespace test;

sql> alter user ranesh default temporary tablespace temp;

To check default permanent &temporary tablespace for a user

sql> select default tablespace temporary tablespace from dba users where username="ranesh";

To lock or unlock a user

sql> alter user ranesh account lock;

sql> alter user ranesh account uinlock;

To change default permanent tablespace

sql> alter database default tablespace mydata;

To change default temporary tablespace

sql> alter database default temporary tablespace temp;

To check system privileges for a user

sql>select privilege from dba_sys _privs where grantee='ranesh';

To check roles assigned to a user;

sql>select granted role from dba _role _privs where grantee='ranesh';

To drop a user

sql> drop user ranesh;

or

sql> drop user ranesh cascade;

Profile Managment

sql> create profile my_profile limit failed_login_attempts 3,password_lock_time 1/24/60,session_per_user 1,idle_time 5;

To assign a profile to user

sql> alter user ranesh profile my_profile;

To alter a profile value

sql>alter profile my_profile limit session_per_user 3;

To check and resource_limit value

sql> show parameter resource_limit

sql>alter system set resource_limit=true scope=both;

To kill a session

sql>select sid serial# from v$session where username='ranesh';