Transcript Database Security

DATABASE SECURITY
Nitin Singh/AAO RTI ALLAHABAD
1
OBJECTIVES
•
•
•
•
•
•
•
What is Database security
Creating user
Modifying user
Dropping user
Creating profile
Previliges and roles
Granting of roles to user
Nitin Singh/AAO RTI ALLAHABAD
2
Database Security
(a) Manage Users
(b) Manage Resource
(c) ROLEs & PRIVILEGEs
Nitin Singh/AAO RTI ALLAHABAD
3
CREATE
USER
CREATE USER test IDENTIFIED BY test
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
QUOTA 1M ON USERS
QUOTA 2M ON TEMP;
Nitin Singh/AAO RTI ALLAHABAD
4
Considerations
• Specify DEFAULT & TEMP
• Otherwise SYSTEM tablespace will be
used, which is not recommended
• Quota must be provided to allow user to
store objects in specified tablespace
• Profile to restrict usage of system
resources
Nitin Singh/AAO RTI ALLAHABAD
5
MODIFY USER
• GRANT CONNECT TO TEST;
• ALTER USER TEST DEFAULT ROLE
ALL;
• GRANT DBA TO TEST;
With DBA ROLE user gets an
UNLIMITED QUOTA on every
tablespace
Nitin Singh/AAO RTI ALLAHABAD
6
Security Issues
• A USER is A SCHEMA
• A USER has FULL access on the
OBJECTS in her SCHEMA
• Password is stored in Data Dictionary
• A USER can always CHANGE his
Password
Nitin Singh/AAO RTI ALLAHABAD
7
Dropping A User
• User’s Schema is removed from Data
Dictionary
• Cannot Drop a Active User
• Use Cascade to Drop all Schema Objects
Nitin Singh/AAO RTI ALLAHABAD
8
Resource Limit & Profiles
• To optimize the usage of System Resources
• Profile is a set of Resource Limits assigned
to each user
• Resource Control Level
(a) Session Level
(b) Call Level
• Resources : CPU time, Logical Reads,
Others (Idle Time, No of concurrent
sessions per user, Elapsed time per session
)
Nitin Singh/AAO RTI ALLAHABAD
9
CREATE Profile
CREATE PROFILE MYPROFILE LIMIT
CPU_PER_SESSION 10
CPU_PER_CALL 25 CONNECT_TIME
5
IDLE_TIME 1
SESSIONS_PER_USER 2;
Nitin Singh/AAO RTI ALLAHABAD
10
Oracle Profiles
• Profiles enable you to set limits on:
•
•
•
•
•
•
•
sessions_per_user
CPU_per_session
CPU_per_call
logical_reads_per_session
logical_reads_per_call
idle_time
connect_time
Nitin Singh/AAO RTI ALLAHABAD
11
Oracle Profiles
• Most popular:
• sessions_per_user
• idle_time
• Sessions_Per_user
– prevent one runaway user from consuming
all Oracle processes
– Example syntax:
alter profile default sessions_per_user 4;
• Idle_Time
– useful when memory is in shortage
Nitin Singh/AAO RTI ALLAHABAD
12
PRIVILEGES & ROLES
• Privilege is right to execute a particular
type SQL or access another user’s
object
e.g, the right to
CREATE TABLE
• SYSTEM Privileges (ALTER SESSION)
• OBJECT Privileges (INSERT ON
SCOTT.EMP)
• GRANT privileges to USER or a ROLE
Nitin Singh/AAO RTI ALLAHABAD
13
CREATE ROLE
CREATE ROLE test NOT IDENTIFIED;
GRANT “CONNECT” TO “TEST”;
GRANT ALTER ANY TABLE TO “TEST”;
GRANT INSERT ON SCOTT.EMP TO TEST;
Assigning
(a) ROLE to a ROLE
(b) OBJECT Privilege to a ROLE
(c) SYSTEM Privilege to a ROLE
Nitin Singh/AAO RTI ALLAHABAD
14
DEFAULT ROLES
•
•
•
•
•
CONNECT
DBA (All System Privileges with Admin)
RESOURCE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
Query DBA_TAB_PRIVS,
DBA_ROLE_PRIVS,SESSION_PRIVS
Nitin Singh/AAO RTI ALLAHABAD
15
Listing User Info
•
•
•
•
•
•
•
ALL_, DBA_, USER_ + USERS
USER_TS_QUOTAS
USER_RESOURCE_LIMITS
DBA_PROFILES
RESOURCE_COST
V$SESSION V$SESSTAT
V$STATNAME
Nitin Singh/AAO RTI ALLAHABAD
16
ROLEs Granted to User
• SELECT * FROM
SYS.DBA_ROLE_PRIVS
WHERE GRANTEE = ‘SCOTT’;
(Roles Granted to SCOTT)
• SELECT * FROM
SYS.DBA_TS_QUOTAS;
(Each User’s TS quotas)
Nitin Singh/AAO RTI ALLAHABAD
17