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

/

 

b)Assigning to a profile

 

ALTER PROFILE my_profile LIMIT

PASSWORD_VERIFY_FUNCTION my_varification_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 jward

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 JOHN 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)

 

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

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *