Users in Database
Published: 2024-07-17 06:41:49

## Users 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k1h0ixoawwuvk8bxqufu.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3lf7oujn9yd3wvpuveb1.png)

- 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mzefeyzyruryi70qshel.png)

- 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h06cqhxnovcqduv16ssd.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2qatkiizvqs6ljnje8ho.png)

- 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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m085t69a2tk5b14lbcss.png)

- 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pcfaad2pe7j6uhy8bsly.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o845yl59haa75qht7ba8.png)

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

desc dba_users;

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/po0ad82pf1wpn721i243.png)

- what are the resources ? Each resources you can specify.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h4hfyjljc5ruc2b8yo7h.png)

#### create custom profile for users

- Like for DEVELOPERS, DBA's , app users .

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wu67df5fdv95plcxss51.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n3tt7ti4t5xa6ekt6c87.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u9rdnsgig311m7rj0itk.png)

- Grant the profile to user1.

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/idhm3k3d5frex58pcxwy.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xzknd8sgljllwrybsxzx.png)

- 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;

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hkpcfmazmeqdsawsi4gh.png)

# 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');

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8m8u1ydzfsm87w0koj3y.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5liq8ih8damg30nsi8tf.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m1mteznp7o42hisrbzp7.png)

# 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 ?