Users or schemas:
====================
DEVDB - Lab1
TESTDB - Lab2
New database:
- 36+ users user will be created
- SYS - master account (DBA accounts)
- Any admin activity can be done + Maintenance activity
- SYSTEM - master account (DBA accounts)
- Any admin activity can be done
SYS user password will be stored in password file
Rest all user password stored inside database
all user can connect to database except SYS
sqlplus <user-name>/<password>
sys user can connect using OS authentication via sysdba or sysoper
sqlplus / as sysdba
or sys user can connect using passwordfile authentication via sysdba or sysoper
sqlplus sys/MAllik123##@DEVDB as sysdba
While creating DB we provide the password for sys and system
By querying dba_users
Ideal way to identify sys password is using TNS connection
sqlplus / as sysdba -> OS authentication
sqlplus sys/Mallik as sysdba -> OS authentication (wrong password)
sqlplus sys/Mallik123## as sysdba -> OS authentication (correct password)
sqlplus sys/Mallik123@DEVDB as sysdba -> password file authentication (wrong password)
sqlplus sys/Mallik123##@DEVDB as sysdba -> password file authentication (correct password)
Rest all users:
sqlplus <user-name>/<password>
sqlplus system/Mallik123
sqlplus mallik/mallik
sqlplus hr/hr
sqlplus system/Mallik123@DEVDB
sqlplus mallik/mallik@DEVDB
sqlplus hr/hr@DEVDB
When we create user account
- We give username and password
(create user user1 identified by user1;)
- assigned with default tablespace (USERS)
- default temp tablespace (TEMP)
- any object created by him placed under that USERS tablespace
any user create = called then as schema
Mallik user = Mallik schema
hr user = hr schema
john user = john schema
system - user
system - schema
collection of all the object created by a user is called schema
sys - user
sys - schema
collection of all the object created by a user is called schema
mallik - user
mallik - schema
collection of all the object created by a user is called schema
hr - user
hr - schema
collection of all the object created by a user is called schema
sqlplus hr/hr
sqlplus mallik/mallik
sqlplus user1/user1
sqlplus hr/hr@DEVDB
sqlplus mallik/mallik@DEVDB
sqlplus user1/user1@DEVDB
The moment we create any user inside database, That will be brand new user with 0 privileges
- As DBA we need to assign / provide required privileges
create user user1 identified by user1;
create user user2 identified by user2;
sqlplus user1/user1
sqlplus user2/user2
privileges:
===========
system : perform some action inside database
- connect to database -> grant create session to user1;
- create table -> grant create table to user1;
object : perform some action on existing object
HR user - DEPARTMENTS Table
- user1 wants to read HR.DEPARTMENTS -> grant select on HR.DEPARTMENTS to USER1;
Role:
++++++++++++++
Managing user privileges is always challenging for DBA
Role - group of privileges
In my organisation -> HR department
HR - Dept
EMPLOYEES
DEPARTMENTS
LOCATIONS
COUNTRIES
user1 - joined to HR department
He need select/read access on those table
grant select on HR.EMPLOYEES to user1;
grant select on HR.DEPARTMENTS to user1;
grant select on HR.LOCATIONS to user1;
grant select on HR.COUNTRIES to user1;
user1:
select * from HR.EMPLOYEES;
select * from HR.DEPARTMENTS;
select * from HR.LOCATIONS;
select * from HR.COUNTRIES;
user2- joined to HR department
He need select/read access on those table
grant select on HR.EMPLOYEES to user2;
grant select on HR.DEPARTMENTS to user2;
grant select on HR.LOCATIONS to user2;
grant select on HR.COUNTRIES to user2;
user2:
select * from HR.EMPLOYEES;
select * from HR.DEPARTMENTS;
select * from HR.LOCATIONS;
select * from HR.COUNTRIES;
user3 - joined to HR department
He need select/read access on those table
grant select on HR.EMPLOYEES to user3;
grant select on HR.DEPARTMENTS to user3;
grant select on HR.LOCATIONS to user3;
grant select on HR.COUNTRIES to user3;
user3:
select * from HR.EMPLOYEES;
select * from HR.DEPARTMENTS;
select * from HR.LOCATIONS;
select * from HR.COUNTRIES;
ROLE - group of privileges
=======
create roles HR_ROLE;
grant select on HR.EMPLOYEES to HR_ROLE;
grant select on HR.DEPARTMENTS to HR_ROLE;
grant select on HR.LOCATIONS to HR_ROLE;
grant select on HR.COUNTRIES to HR_ROLE;
grant HR_ROLE to user1;
grant HR_ROLE to user2;
grant HR_ROLE to user3;
Quota:
=======
When we create user account
- We give username and password
- create user user1 identified by user1;
- create user user2 identified by user2;
- assigned with default tablespace (USERS)
- default temp tablespace (TEMP)
- any object created by him placed under that USERS tablespace
user1/user2/user3
- assigned with default tablespace (USERS)
user1
T1/TEST1/TABLE1 - some random tables and inserting 1M records / 2M records
USERS - user.dbf - 32GB
Once this USERS tablespace - user.dbf datafile is full - 32GB
- then it will impact all user use1/user2/user3 or any user who has USERS as default tablespace
Quota is of restricting space usage under the tablespace for a user
any user created default gets unlimited tablespace
alter user user1 quota 1G on USERS;
alter user user1 quota 2G on USERS;
dba_ts_quotas
SELECT TABLESPACE_NAME,
BYTES / 1024 / 1024 "UTILIZIED_SPACE",
MAX_BYTES / 1024 / 1024 "QUOTA_ALLOCATED"
FROM dba_ts_quotas
WHERE username = 'USER1';
alter user USER2 quota unlimited on USERS;
SELECT TABLESPACE_NAME,
BYTES / 1024 / 1024 "UTILIZIED_SPACE",
MAX_BYTES / 1024 / 1024 "QUOTA_ALLOCATED"
FROM dba_ts_quotas
WHERE username = 'USER2';
Profiles:
+++++++++++++++++++++++
17 resource
restrict usage of SGA
restrict usage of PGA
restrict usage of CPU
Set some password policy
dba_profiles
-> DEFAULT profile
select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles order by PROFILE;
any user created will be assigned with this DEFAULT profile
select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';
CREATE PROFILE EBS_APPLN LIMIT
PASSWORD_LIFE_TIME 45
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX 5
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
CPU_PER_CALL 5000
PRIVATE_SGA 250K
LOGICAL_READS_PER_CALL 2000;
alter user USER1 profile EBS_APPLN;
Any users:
- system privileges
- object privileges
- role privileges
- quota
- profile
User creation:
=====================
Option1: - Best practice
create user user_1 identified by user_1
default tablespace USERS
TEMPRORY TABLESPACE temp
profile EBS_ppln
quota unlimited
roles ;
Option2:
create user user_1 identified by user_1;
Option3:
create user user_1 identified by user_1
profile EBS_APPLN;
Option4:
create user user_1 identified by user_1
profile EBS_APPLN
quota 1G on USERS;
Option5:
create user user_1 identified by user_1
profile EBS_APPLN
quota 1G on TEST1
default tablespace TEST1;
alter user user_1
profile / quota / tablespace / ROLE
What all privileges we can assign to user?
- system
- object
- role
Can I get what all privileges assigned for USER1?
Can you create user3 and assigned same privileges as of user1;
Then we DBA need to get all the privileges assigned for USER1
- system
- object
- role
- DBA_SYS_PRIVS
- DBA_TAB_PRIVS
- dba_role_privs
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');
SELECT * FROM DBA_SYS_PRIVS WHERE grantee IN ('USER2');
SELECT * FROM DBA_TAB_PRIVS WHERE grantee IN ('USER2');
SELECT * FROM dba_role_privs WHERE grantee IN ('USER2');
HR:
+++++++++
SELECT * FROM DBA_SYS_PRIVS WHERE grantee IN ('HR');
SELECT * FROM DBA_TAB_PRIVS WHERE grantee IN ('HR');
SELECT * FROM dba_role_privs WHERE grantee IN ('HR');
We can get all privileges of a role
+++++++++++
SELECT * FROM DBA_SYS_PRIVS WHERE grantee IN ('HR_ROLE');
SELECT * FROM DBA_TAB_PRIVS WHERE grantee IN ('HR_ROLE');
SELECT * FROM dba_role_privs WHERE grantee IN ('HR_ROLE');
No comments:
Post a Comment