Users

SYS--> DBA Role + SYSDBA Role ( Startup / Maintenance activity ) --> Super Master User
SYSTEM--> DBA Role --> Master users
30 to 35 users will be created in default. desc dba_users; Mostly all will be locked and expired.

Any users created , DBA will assign System Level Privileges & Object Level Privileges.
what are all the System Level Privileges ( activities performed at DB side ) ? CREATE SESSION , CREATE TABLE.
When a user tries to read the data of other user , then object level privilege comes into picture.
Object Level Privileges --> enables users to access and change data's in the object.

These System level privileges comes along with ADMIN option.
These Object level privileges comes along with GRANT option.

grant CREATE SESSION to user1 with ADMIN OPTION; --> which very risky. Because this user can give access to any user.

grant select on USER2.T2 to user1 with GRANT option; --> which very risky. Because this user can give select access to any user.

If we want to revoke the admin option (System level privileges) for the user which you have given then it will revoke only for that user , it won't revoke for other users. If the user as given access to other friends . So Manually you need to check in the audit and then manually you need to remove the access.
If we want to revoke the Grant option (Object level privileges) , then it will revoke for that user and also for other user which is granted.

Roles

Create a role and assign the privileges to the role. Why ? If new users comes in and DBA can't provide privilege for each users who is coming.

create roles ROLE1;
grant select on HR.Employees to ROLE1;
grant select on HR.Regions to ROLE1;
grant select on HR.Locations to ROLE1;
grant ROLE1 to user1;
grant ROLE1 to user2; --> these are new users joining
grant ROLE1 to user3; --> these are new users joining

one more on this ROLE

grant select on HR.JOBS to user1;
.
.
grant select on HR.JOBS to user100;

rather than the above one , we can provide to roles because roles are already assigned to user1,user2,user3

grant select on HR.JOBS to ROLE1;

Profiles & Quotas

Quotas --> the space usage

profiles --> we can further more restrictions like the CPU usage a user can use & how much logical reads & password complexity.

desc dba_profiles;

20 to 30 resources will be listed down in each profile.

any user you create "DEFAULT" profile will be associated with the user.

desc dba_users;

what are the resources ? Each resources you can specify.

create custom profile for users

Like for DEVELOPERS, DBA's , app users .

Grant the profile to user1.

Alter user user1 profile dummy;
select username, acccount_status, profile from dba_users where username = 'user1';

You can increase the complexity for creating the user slowly after getting the knowledge like below :

Even you can modify using alter user too ,

alter user user1 profile dummy;
alter user user1 quota 2g on users;
alter user user1 default tablespace test1;

Notes

sqlplus / as sysdba or sqlplus sys/password as sysdba
sqlplus system/password ( no need of any role )

Above both the users are used to perform DB and maintenance activities .

DBSNMP user mostly used for OEM.
"---------------------" this is 100 characters , just restrict to 40 letters/characters like " col PROFILE for a40; --> for is format.

Command

show user
set pages 1000 lines 1000
col username for a20 --> for is format
/ --> last command will be executed
grant CREATE SESSION to user1;
grant CREATE TABLE to user1;
create table T1 (SLNO number(10))
insert into T1 value (1);
alter user user1 quota unlimited on USERS; & then commit ;--> UNLIMITED space is allocated.
grant select on USER2.T2 to user1;
grant insert on USER2.T2 to user1;
grant delete on USER2.T2 to user1;

select * FROM DBA_SYS_PRIVS where grantee in ('USER1');
select * FROM DBA_TAB_PRIVS where grantee in ('USER1');
select * FROM DBA_ROLE_PRIVS where grantee in ('USER1');

sqlplus sys/password@service_name as sysdba; --> remote authentication
sqlplus / as sysdba; --> OS authentication

grant sysdba to user1;
grant dba to user1; --> dba is role

DBA is a role --> system level , object level , roles.

Issues

Insufficient privilege
user lacks CREATE SESSION privilege-
Above two errors are related to missing " System Level Privileges " for the users.
no insert privilege on tablespace --> assign some quota so that we can assign some values to it.
Quota exceeds limit.
Account is locked & timed

Questions

What are all the privileges user can have ? System level , Object level & roles.
List all the privileges owned by user1 ?
List all the privileges owned by user1 & grant some privilege to user2?
Create user5 & assign all privilege of user1 ?
duplicate user1 as user5 with all privileges ?
what is composite limit in the profile ?

Users in Database