Users

1. SYS--> DBA Role + SYSDBA Role ( Startup / Maintenance activity ) --> Super Master User
2. SYSTEM--> DBA Role --> Master users
3. 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

1. sqlplus / as sysdba or sqlplus sys/password as sysdba
2. 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

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