Profiles,roles & Grants

Profiles in Oracle • •

Profiles are a means to limit resources a user can use. For Oracle to use profiles you must enter this in the initSID.ora, recompile the spfile and restart the instance

resource_limit = TRUE • ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; • Password Control Attributes in a Profile: Attribute FAILED_LOGIN_ATTEMPTS PASSWORD_LIFE_TIME PASSWORD_REUSE_TIME

PASSWORD_REUSE_MAX

PASSWORD_LOCK_TIME PASSWORD_GRACE_TIME

PASSWORD_VERIFY_FUNCTION

Description Specifies the number of failed attempts to log in to the user account before the account is locked. Limits the number of days the same password can be used for authentication. The password expires if it is not changed within this period, and further connections are rejected. Specifies the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED. Specifies the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED. Specifies the number of days an account will be locked after the specified number of consecutive failed login attempts. Specifies the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires. Allows a PL/SQL password complexity verification script to be passed as an argument to the CREATE PROFILE statement. Oracle provides a default script, but you can create your own routine or use third-party software instead. Function is the name of the password complexity verification routine. NULL indicates that no password verification is performed.

Roles: A role is a set or group of privileges that can be granted to users or another role Creating a Role: To create a role, you must have CREATE ROLE system privileges. The syntax for creating a role is: CREATE ROLE role_name [ NOT IDENTIFIED | IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ; The role_name phrase is the name of the new role that you are creating. This is how you will refer to the grouping of privileges. The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role. The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled. The BY password phrase means that a user must supply a password to enable the role.

The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package. The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service. The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role. Data Dictionary Objects Related to Roles:

dba_roles dba_role_privs role_role_privs role_sys_privs role_tab_privs

session_roles user_application_roles user_role_privs v$pwfile_users

System Privileges Related To Roles: alter any role

                     create role                      drop any role                      grant any role Predefined Roles: connect, resource, dba. Creating Roles Create Role

Create Password Protected Role

CREATE ROLE ; CREATE ROLE read_only; CREATE ROLE  IDENTIFIED BY  <password>; CREATE ROLE dba IDENTIFIED BY "S0^Sorry";

Assigning Privileges And Roles To Roles

GRANT <privilege_name> TO ;

Assign Privilege To A Role

GRANT create session TO read_only

Roles Granted To A User

SELECT *  FROM user_role_privs;

Privileges Granted To A Role

SELECT * FROM role_sys_privs;

System Privileges

SELECT DISTINCT privilege  FROM dba_sys_privs;

Roles Granted To Schemas

SELECT grantee, granted_role FROM dba_role_privs;

Tables And Columns That Can Be Modified by a User

SELECT * FROM all_updatable_columns;

Privileges A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges: system privileges and object privileges. A privilege can be assigned to a user or a privilege The set of privileges is fixed, that is, there is no SQL statement like create privilege xyz... System privileges: select name from system_privilege_map • • • • • •

create session (A user cannot login without this privilege. If he tries, he gets an ORA-01045). Create table Create view Create procedure sysdba sysoper

Executing this statement, we find privileges like create session, drop user, alter database, see system privileges. Object privileges Privileges can be assigned to the following types of database objects: • • • • • • • • • •

Tables select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all Views select, insert, update, delete, under, references, flashback, debug Sequence alter, select Packeges, Procedures, Functions (Java classes, sources...) execute, debug Materialized Views delete, flashback, insert, select, update Directories read, write Libraries execute User defined types execute, debug, under Operators execute Indextypes execute

Object privileges can be displayed using all_tab_privs_made or user_tab_privs_made. Oracle DML statements: Select, Update, Insert, Merge & Delete.cle

Statements Oracle DML Statements

DML Statementsracle DML