User, Roles & Privileges

User Management

Creating users

CREATE USER omer IDENTIFIED BY sifreniz

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON users;

CREATE USER omer IDENTIFIED BY sifreniz

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON users;

Granting privileges

GRANT connect TO omer;

GRANT create table, create procedure TO omer;

GRANT connect TO omer;

GRANT create table, create procedure TO omer;

Locking and Unlocking User Accounts

alter user user_name account lock;

alter user user_name account unlock;

alter user user_name account lock;

alter user user_name account unlock;

Specifying passwords

alter user user_name identified by new_password;

ALTER USER user_name IDENTIFIED BY new_password ACCOUNT UNLOCK;

alter user user_name identified by new_password;

ALTER USER user_name IDENTIFIED BY new_password ACCOUNT UNLOCK;

Configuring an account lock after several failed login attempts

<span style="font-size: 0.9em; line-height: 1.3em;">ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS DEFAULT</span>

ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS 9

<span style="font-size: 0.9em; line-height: 1.3em;">ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS DEFAULT</span>

ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS 9

Configuring the DBMS to end a user session after being idle for a number of minutes.

ALTER PROFILE "DEFAULT" LIMIT IDLE_TIME 15 --(resource_limit = "False" by default, isn't active until the value is set to "True".)

1	ALTER PROFILE "DEFAULT" LIMIT IDLE_TIME 15 --(resource_limit = "False" by default, isn't active until the value is set to "True".)

Listing the users

set pages 999 lines 100

col username format a20

col status format a8

col tablespace format a20

col temp_ts format a20

select username

, account_status status

, created

, default_tablespace tablespace

, temporary_tablespace temp_ts

from dba_users

order by username

set pages 999 lines 100

col username format a20

col status format a8

col tablespace format a20

col temp_ts format a20

select username

, account_status status

, created

, default_tablespace tablespace

, temporary_tablespace temp_ts

from dba_users

order by username

Roles

select *

from dba_roles

where role like '%role'

select *

from dba_roles

where role like '%role'

Granting roles to users

select grantee

, granted_role

, admin_option

from dba_role_privs

where grantee like upper('%username')

/

select grantee

, granted_role

, admin_option

from dba_role_privs

where grantee like upper('%username')

Privileges granted to specific roles

select privilege

, admin_option

from role_sys_privs

where role like '%role'

select privilege

, admin_option

from role_sys_privs

where role like '%role'

The table rights given to specific roles

select owner || '.' || table_name "TABLE"

, column_name

, privilege

, grantable

from role_tab_privs

where role like '%role'

select owner || '.' || table_name "TABLE"

, column_name

, privilege

, grantable

from role_tab_privs

where role like '%role'

The roles and privileges assigned to a specific user

SET SERVEROUTPUT ON

SET VERIFY OFF

SELECT a.granted_role "Role",

a.admin_option "Adm"

FROM user_role_privs a;

SET SERVEROUTPUT ON

SET VERIFY OFF

SELECT a.granted_role "Role",

a.admin_option "Adm"

FROM user_role_privs a;

SELECT a.privilege "Privilege",

a.admin_option "Adm"

FROM user_sys_privs a;

SELECT a.privilege "Privilege",

a.admin_option "Adm"

FROM user_sys_privs a;

SET VERIFY ON