MANAGING SECURITY
Managing Profiles
1.Creating profile and Granting it to user
CONN sys/password AS SYSDBA
CREATE PROFILE my_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3 — Account locked after 3 failed logins.
PASSWORD_LOCK_TIME 5 — Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
PASSWORD_LIFE_TIME 30 — Password expires after 90 days.
PASSWORD_GRACE_TIME 3 — Grace period for password expiration.
PASSWORD_REUSE_TIME 120 — Number of days until a specific password can be reused. UNLIMITED means never.
PASSWORD_REUSE_MAX 10 — The number of changes required before a password can be reused. UNLIMITED means never.
SESSIONS_PER_USER 6
CONNECT_TIME 1440
IDLE_TIME 120
/
ALTER USER scott PROFILE my_profile;
2.Password verification function
a)Creating procedure
CREATE OR REPLACE FUNCTION my_verification_function (
username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2)
RETURN BOOLEAN AS
BEGIN
IF LENGTH(password) < 8 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END my_verification_function;
/
b)Assigning to a profile
ALTER PROFILE my_profile LIMIT
PASSWORD_VERIFY_FUNCTION my_verification_function;
3.Dropping profile
DROP PROFILE my_profile CASCADE
4.Altering Profile
ALTER PROFILE my_profile LIMIT
CONNECT_TIME 1440
5.Quering Profile information
1.DBA_PROFILES
2.USER_PASSWORD_LIMITS
3.RESOURCE_COST
Managing Users
1.Creating user
Sql>CREATE USER ramkumar
IDENTIFIED BY AZ7BC2
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
PROFILE clerk
ACCOUNT UNLOCK;
2.Modifying User or Changing password for user
Sql>ALTER USER RAM IDENTIFIED BY DEB DEFAULT TABLESPACE DATA;
3.Locking or Unlocking or Expiring password of User
Sql>ALTER USER ACCOUNT LOCK/UNLOCK;
Sql>ALTER USER DEB PASSWORD EXPIRE.
4.Dropping User
Sql>DROP USER DEB CASCADE;
5.Quering User Information.
1.USER_USERS.
2.DBA_USERS.
3.DATABASE_PROPERTIES.
4.DBA_TS_QUOTAS
6.Authenticate User from OS
a)set OS_AUTHENT_PREFIX=” ” or “OPS$”
b)set REMOTE_OS_AUTHENT=TRUE
c)CREATE USER OPS$DEB IDENTIFIED EXTERNALLY;
d)modify SQLNET.AUTHENTICATION_SERVICES= (OS) in sqlnet.ora file kept in $ORACLE_HOME/network/admin directory.
e)sqlplus / (This is to connect sqlplus to OS authenticated user id)
- Authenticate User from Passwordfile
a)set REMOTE_LOGIN_PASSWORD = EXCLUSIVE in init.ora file.
b)Rename sqlnet.ora to sqlnet.ora_bak
c)Create password file
-go to cd $ORACLE_HOME/database
-orapwd file=PWD(sid).ora password=sys123
d)Now connect sys/sys123 as sysdba
MANAGING PRIVILEGES
a)Object Privileges
SQL>GRANT SELECT,UPDATE ON CUSTOMER TO JAMES;
SQL>GRANT SELECT,UPDATE ON CUSTOMER TO JAMES WITH GRANT OPTION;
SQL>GRANT INSERT(CUSTOMER_ID) ON CUSTOMER TO JAMES;
SQL>GRANT ALL ON CUSTOMER TO JAMES;
SQL>GRANT INSERT,UPDATE,SELECT ON CUSTOMER TO JULIE,SCOTT;
SQL>REVOKE UPDATE ON CUSTOMER FROM JAMES;
SQL>REVOKE ALL ON CUSTOMER FROM JAMES;
SQL>REVOKE REFERENCES ON CUSTOMER FROM JAMES CASCADE CONSTRAINTS;
b)System Privileges
SQL>GRANT CREATE ANY TABLE TO JOHN;
SQL>GRANT CREATE ANY TABLE TO JOHN WITH ADMIN OPTION.
c)Using Dictionary Views to Display Privileges
1.user_sys_privs:- Shows all system privileges associated with the user.
2.session_privs:-Shows all privileges available in this session.
MANAGING ROLES
a)creating role
sql>create role rpt_writer.
b)modifying role
sql>alter role rpt_writer identified by deb.
c)granting privileges to roles.
Sql>grant select on emp to rpt_writer.
d)granting roles to users
sql>grant rpt_writer to turner.
e)defining user default roles.
Sql>alter user ford default role none.
f)enabling current role
sql>set role rpt_writer identified by deb;
g)Revoking and dropping roles.
Sql>revoke rpt_writer from turner.
Sql>drop role rpt_writer.
h)Some pre-defined roles.
1.connect.
2.resource
3.dba
i)Dictionary views for roles
1.user_role_privs:=Identified the roles granted to you.
2.role_role_privs.:=Identifies the roles granted to other roles in the database.
3.role_tab_privs.:=Identifies object privileges granted to roles.
4.role_sys_privs.:=Identifies system privileges granted to roles.
4.role_sys_privs.:=Identifies system privileges granted to roles.
5.session_roles.:=Identifies roles available to current session.
DATABASE AUDITING
1.Setting audit_trail parameter.
a)NONE
b)DB:-Enable auditing,writing to SYS.AUD$ table.
c)OS:-Enable auditing writing the os audit trail.
2.Login audits.
Sql>audit session
Sql>audit session whenever successful;(Attepting success)
Sql> audit session whenever not successful;
Sql>noaudit session(Disabling auditing)
3.Action audits.
Sql>audit role(All the system level command can be audited)
Sql>noaudit role(Disabling auditing)
4.Object audit(Auditing by session or by access)
sql>audit insert on thumpner.employee;
sql>audit all on thumpner.time_cards.
Sql>audit delete on thumpner.dept by session.
Sql>audit update table by deb(all update action by deb will be monitored)
5.Protecting the audit trail
Sql>audit all on sys.aud$ by access.
6.Views and tables associated with audit
a)sys.aud$
b)dba_audit_session.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
