USER CREATION AND DELETION.
SQL> create user tom identified by jerry
2 default tablespace users
3 temporary tablespace temp
4 quota 20m on users;
User created.
Above command creates a user tom with password jerry.
The tablespace in which tom will store his data is "USERS".
The tablespace used for storing temporary segments will be "TEMP".
And amount of space which tom can use on "USERS" tablespace is 20M.
SQL> drop user tom cascade;
SQL> alter user scott identified by tiger account unlock;
User altered.
The above command unlocks the "SCOTT" user with password "TIGER".
SQL> select username, account_status, default_tablespace,
temporary_tablespace, profile from dba_users
where username = 'TOM'
Code:
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
-------- -------------- ------------------ -------------------- ---------
TOM OPEN USERS TEMP DEFAULT
The above query shows the account information related to "TOM".
ALTERING TABLEPSACE QUOTA
SQL> select * from dba_ts_quotas where username = 'TOM';
Code:
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
--------------- -------- ----- --------- ------ ---------- ---
USERS TOM 0 20971520 0 2560 NO
SQL> alter user tom quota 30m on users;
User altered.
SQL> select * from dba_ts_quotas where username = 'TOM';
T
Code:
ABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
--------------- -------- ----- --------- ------ ---------- ---
USERS TOM 0 31457280 0 3840 NO
GRANTING AND REVOKING PRIVILEGES.
SQL> grant create table to tom;
Grant succeeded.
SQL> grant create session to tom;
Grant succeeded.
SQL> grant create any table, create tablespace to tom;
Grant succeeded.
SQL> revoke create any table from tom;
Revoke succeeded.
SQL> revoke create tablespace from tom;
Revoke succeeded.
SQL> grant select, insert, update, delete on scott.emp to tom;
Grant succeeded.
SQL> revoke update,delete on scott.emp from tom;
Revoke succeeded.
ROLES
SQL> grant create any table,
2 alter any table,
3 drop any table,
4 select any table,
5 update any table,
6 delete any table
7 to manager;
SQL> grant create any index,
2 alter any index,
3 drop any index
4 to manager;
Grant succeeded.
SQL> grant alter session,
2 restricted session
3 to manager;
Grant succeeded.
SQL> grant create tablespace,
2 alter tablespace,
3 drop tablespace,
4 unlimited tablespace
5 to manager;
Grant succeeded.
SQL>create role manager;
SQL> grant select, insert, update, delete on scott.dept to manager;
Grant succeeded.
SQL> grant manager to tom;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee = 'TOM';
Code:
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TOM CREATE TABLE NO
TOM CREATE SESSION NO
SQL> select * from dba_sys_privs where grantee = 'MANAGER'
Code:
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
MANAGER DELETE ANY TABLE NO
MANAGER CREATE ANY TABLE NO
MANAGER DROP TABLESPACE NO
MANAGER ALTER TABLESPACE NO
MANAGER ALTER ANY INDEX NO
MANAGER DROP ANY TABLE NO
MANAGER DROP ANY INDEX NO
MANAGER UPDATE ANY TABLE NO
MANAGER ALTER SESSION NO
MANAGER SELECT ANY TABLE NO
MANAGER RESTRICTED SESSION NO
MANAGER CREATE ANY INDEX NO
MANAGER ALTER ANY TABLE NO
MANAGER UNLIMITED TABLESPACE NO
MANAGER CREATE TABLESPACE NO
15 rows selected.
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
2 where grantee = 'TOM';
Code:
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
-------- ------ ------------ --------- ---------
TOM SCOTT EMP SCOTT SELECT
TOM SCOTT EMP SCOTT INSERT
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
where grantee = 'MANAGER'
Code:
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
-------- ------ ------------ --------- ---------
MANAGER SCOTT DEPT SCOTT UPDATE
MANAGER SCOTT DEPT SCOTT SELECT
MANAGER SCOTT DEPT SCOTT INSERT
MANAGER SCOTT DEPT SCOTT DELETE
SQL> select * from dba_roles where role = 'MANAGER';
Code:
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
MANAGER NO NONE
SQL> select * from dba_role_privs where grantee = 'TOM';
Code:
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TOM MANAGER NO YES
SQL> select * from role_sys_privs where role = 'MANAGER';
Code:
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
MANAGER DROP TABLESPACE NO
MANAGER CREATE ANY TABLE NO
MANAGER DELETE ANY TABLE NO
MANAGER ALTER TABLESPACE NO
MANAGER DROP ANY TABLE NO
MANAGER ALTER ANY INDEX NO
MANAGER UPDATE ANY TABLE NO
MANAGER DROP ANY INDEX NO
MANAGER ALTER SESSION NO
MANAGER RESTRICTED SESSION NO
MANAGER SELECT ANY TABLE NO
MANAGER CREATE TABLESPACE NO
MANAGER UNLIMITED TABLESPACE NO
MANAGER ALTER ANY TABLE NO
MANAGER CREATE ANY INDEX NO
15 rows selected.
SQL> select * from role_tab_privs where role = 'MANAGER';
Code:
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
-------- ------- ------------ ------------- ----------- ---
MANAGER SCOTT DEPT DELETE NO
MANAGER SCOTT DEPT UPDATE NO
MANAGER SCOTT DEPT SELECT NO
MANAGER SCOTT DEPT INSERT NO
SQL> revoke manager from tom;
Revoke succeeded.
PROFILES
SQL> create profile developer limit
failed_login_attempts 3
password_lock_time unlimited
password_life_time 30
password_reuse_time 30
password_grace_time 5
idle_time 30;
Profile created.
SQL> alter user tom profile developer;
User altered.
SQL> select * from dba_profiles where profile = 'DEVELOPER';
Code:
PROFILE RESOURCE_NAME RESOURCE LIMIT
----------- -------------- -------- -----
DEVELOPER COMPOSITE_LIMIT KERNEL DEFAULT
DEVELOPER SESSIONS_PER_USER KERNEL DEFAULT
DEVELOPER CPU_PER_SESSION KERNEL DEFAULT
DEVELOPER CPU_PER_CALL KERNEL DEFAULT
DEVELOPER LOGICAL_READS_PER_SESSION KERNEL DEFAULT
DEVELOPER LOGICAL_READS_PER_CALL KERNEL DEFAULT
DEVELOPER IDLE_TIME KERNEL 30
DEVELOPER CONNECT_TIME KERNEL DEFAULT
DEVELOPER PRIVATE_SGA KERNEL DEFAULT
DEVELOPER FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEVELOPER PASSWORD_LIFE_TIME PASSWORD 30
DEVELOPER PASSWORD_REUSE_TIME PASSWORD 30
DEVELOPER PASSWORD_REUSE_MAX PASSWORD DEFAULT
DEVELOPER PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
DEVELOPER PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEVELOPER PASSWORD_GRACE_TIME PASSWORD 5
16 rows selected.
SQL> drop profile developer cascade;
Profile dropped.
