Users and security
Profile
Profiles are used to control database resource usage. DEFAULT profile is created at creation time of database. To enforce resource limit,
RESOURCE_LIMIT=TRUE should be set in initialization file.
CREATE PROFILE OFFICE_USER LIMIT
SESSIONS_PER_USER 6
CONNECT_TIME 1440
IDLE_TIME 120
FAILED_LOGIN_ATTEMTS 3
PASSWORD_LOCK_TIME UNLIMITED
ALTER USER clerk PROFILE office_user
RESOURCE_LIMIT=TRUE should be set in initialization file.
CREATE PROFILE OFFICE_USER LIMIT
SESSIONS_PER_USER 6
CONNECT_TIME 1440
IDLE_TIME 120
FAILED_LOGIN_ATTEMTS 3
PASSWORD_LOCK_TIME UNLIMITED
ALTER USER clerk PROFILE office_user
Users
CREATE USER MKM
IDENTIFIED BY MKM
DEFAULT TABLESPACE SUPERMARKET_DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON SUPERMARKET_DATA
PROFILE DEFAULT
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE,
EXECUTE_CATALOG_ROLE TO MKM
DROP USER mkm CASCADE
IDENTIFIED BY MKM
DEFAULT TABLESPACE SUPERMARKET_DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON SUPERMARKET_DATA
PROFILE DEFAULT
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE,
EXECUTE_CATALOG_ROLE TO MKM
DROP USER mkm CASCADE
Privilege
Privileges control what users can or can’t do in database.
Object privilege – provides permission to access schema objects. Granted for specific objects.
GRANT SELECT, UPDATE ON product, price TO clerk (WITH GRANT OPTION)
System privilege – provide right to perform structural change in database level.
GRANT CREATE ANY TABLE TO john (WITH ADMIN OPTION) REVOKE CREATE ANY TABLE FROM john
For object privileges, both grantor and grantee information is stored in data dictionary; where as for system privilege, only grantee information is stored.
Object privilege – provides permission to access schema objects. Granted for specific objects.
GRANT SELECT, UPDATE ON product, price TO clerk (WITH GRANT OPTION)
System privilege – provide right to perform structural change in database level.
GRANT CREATE ANY TABLE TO john (WITH ADMIN OPTION) REVOKE CREATE ANY TABLE FROM john
For object privileges, both grantor and grantee information is stored in data dictionary; where as for system privilege, only grantee information is stored.
Roles
A role is named set of privileges.
CREATE ROLE CLERK
GRANT SELECT, INSERT, UPDATE ON TRANSACTION TO CLERK
ALTER USER john DEFAULT ROLE CLERK (NONE)
CREATE ROLE CLERK
GRANT SELECT, INSERT, UPDATE ON TRANSACTION TO CLERK
ALTER USER john DEFAULT ROLE CLERK (NONE)
User related data dictionary views
DBA_USERS
DBA_TS_QUOTA – space assigned to users
V$SESSION – users currently connected to database
DBA_TAB_PRIVS
DBA_COL_PRIVS
DBA_SYS_PRIVS
SESSION_PRIVS
DBA_ROLES
DBA_ROLES_PRIVS
ROLE_ROLE_PRIVS
DBA_TS_QUOTA – space assigned to users
V$SESSION – users currently connected to database
DBA_TAB_PRIVS
DBA_COL_PRIVS
DBA_SYS_PRIVS
SESSION_PRIVS
DBA_ROLES
DBA_ROLES_PRIVS
ROLE_ROLE_PRIVS
No comments:
Post a Comment