User Management
Creating users
1 2 3 4 5 6 7 |
CREATE USER omer IDENTIFIED BY sifreniz DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; |
Granting privileges
1 2 3 |
GRANT connect TO omer; GRANT create table, create procedure TO omer; |
Locking and Unlocking User Accounts
1 2 3 |
alter user user_name account lock; alter user user_name account unlock; |
Specifying passwords
1 2 3 |
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
1 2 3 |
<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.
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
1 2 3 4 5 |
select * from dba_roles where role like '%role' |
Granting roles to users
1 2 3 4 5 6 7 8 9 10 11 |
select grantee , granted_role , admin_option from dba_role_privs where grantee like upper('%username') / |
Privileges granted to specific roles
1 2 3 4 5 6 7 |
select privilege , admin_option from role_sys_privs where role like '%role' |
The table rights given to specific roles
1 2 3 4 5 6 7 8 9 10 11 |
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
1 2 3 4 5 6 7 8 9 |
SET SERVEROUTPUT ON SET VERIFY OFF SELECT a.granted_role "Role", a.admin_option "Adm" FROM user_role_privs a; |
1 2 3 4 5 |
SELECT a.privilege "Privilege", a.admin_option "Adm" FROM user_sys_privs a; |
SET VERIFY ON
Installing Oracle 10G...
12 March 2019