Creating Profile and priviliges

Creating Profile

SQL> CREATE PROFILE profile_name LIMIT
[parameter_1] [parameter_2] [parameter_3] [parameter_n] ;

For Example:
SQL> CREATE PROFILE prfl LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 30
PASSWORD_CERIFY_FUNCTION verify_func
CPU_PER_SESSION 1000
IDEL_TIME 60 ;
Profile Parameters
Password Settings:
PARAMETERS DESCRIPTION
FAILED_LOGIN_ATTEMPTS Number of failed login attempts before lockout of the
account.
PASSWORD_LOCK_TIME Number of days the account is locked after the specified
number of failed login attempts.
PASSWORD_LIFE_TIME Lifetime of the password in days after which the
password expires.
PASSWORD_GRACE_TIME Grace period in days for changing the password after the
first successful login after the password has expired.
PASSWORD_REUSE_TIME Number of days before a password can be reused.
PASSWORD_REUSE_MAX Maximum number of changes required before a
password can be reused.
PASSWORD_VERIFY_FUNCTION PL / SQL function that perform a password complexity
check before a password is assigned.
Resource Limit:
PARAMETERS DESCRIPTION
CPU_PER_SESSION Total CPU time measured in hundredths of second.
SESSIONS_PER_USER Number of concurrent sessions allowed fro each
username.
CONNECT_TIME Elapsed connect time measured in minutes.

IDLE_TIME Periods of inactive time measured in minutes.
LOGICAL_READS_PER_SESSION Number of data blocks ( physical and logical reads)
PRIVATE_SGA Private space in the SGA measured in bytes ( for Shared
server only )
CPU_PER_CALL CPU time per call in hundredths of seconds.
LOGICAL_READS_PER_CALL Number of data blocks that can be read per call.
Enabling Resource Limits
Enable or disable the enforcement of resource limit by setting the initialization parameters
RESOURCE_LIMIT = TRUE , or by using the ALTER SYSTEM command…
SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE ;
Altering Profile
Use ALTER PROFILE command to change password limits.
SQL> ALTER PROFILE profile_name LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60 ;
Dropping Profile
SQL> DROP PROFILE profile_name ;
SQL> DROP PROFILE profile_name CASCADE ;
CASCADE revokes the profile from the users to whom it was assigned.
Changing Users Quota
SQL> ALTER USER abc QUOTA 10M ON data ;
SQL> ALTER USER abc QUOTA 0M ON data ;
After a quota 0 is assigned, the objects owned by the user remain in the revoked tablespace, but they
cannot be allocated a new space.

Dropping User
SQL> DROP USER user_name ;
SQL> DROP USER user_name CASCADE ;
The CASCADE option drop all objects in the schema before dropping the user. This must be
specified if the schema contains any objects. Users who are currently connected to Oracle server
cannot be dropped.
Granting Privileges
“System Privileges”
SQL> GRANT CREATE SESSION TO abc ;
SQL> GRANT CREATE SESSION TO abc WITH ADMIN OPTION ;
WITH ADMIN OPTION enables the grantee to further grant the privileges or role to other users.
“Object Privileges”
SQL> GRANT SELECT ON emp TO hr ;
SQL> GRANT SELECT ON emp TO hr WITH GRANT OPTION ;
WITH GRANT OPTION enables the grantee to grant object privileges or role to other users or
role.
Revoking Privileges
“System Privileges”
SQL> REVOKE CREATE TABLE FROM abc ;
“Object Privileges”
SQL> REVOKE SELECT ON emp FROM hr ;
Grantors can revoke object privileges from only those users to whom they have granted privileges.