Oracle User Management

1. User-Created
sql> CREATE USER rusking
2 identified by rusking
3 Default Tablespace users-specifies that the defaults table space is users. If you use the system default Tablespace, you can actually do so without specifying it.
4 QUOTA 10M on the users---QUOTA specify a tablespace quota for the user. That is, the maximum space that the user object occupies in the table space. Default Unlimited.
5 temporary tablespace TEMP; --Specifies that the default temp table space is temp. Because temp is the default temporary table space, it can also be used without specifying it.
User created
--If you do not specify a default tablespace or temporary tablespace, you will be given a table space by default.
SELECT * from User_ts_quotas;

2. Change user password and account lock and unlock
Sql> ALTER USER rusking identified by RUSKING1;
User Altered

sql> ALTER USER rusking account LOCK;
User Altered

sql> ALTER USER rusking account UNLOCK;
User Altered

3. Viewing user quotas Quota
Max_bytes is 1 of the expression Infinity
Sql> select Tablespace_name,username,max_bytes from Dba_ts_quotas;
Tablespace_name USERNAME Max_bytes
------------------------------ ------------------------------ ----------
Sysaux OLAPSYS-1
Sysaux SYSMAN-1
Sysaux flows_files-1
USERS TEST-1
Sysaux APPQOSSYS-1
USERS rusking 10485760
6 Rows selected

4. Modify user Quotas
sql> ALTER USER TEST QUOTA 10M on USERS;
User Altered
sql> ALTER USER TEST QUOTA UNLIMITED on USERS;
User Altered
Sql> Grant Unlimited tablespace to Anqing; -This approach is global. That is, modify the quota for all tablespaces for that user.
Grant succeeded.

5. User authorization
DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.
RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.
Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.
For normal users: Grant Connect, resource permissions.
For DBA administration users: Grant Connect,resource, dba authority.
Sql> GRANT Connect,resource to rusking;
Grant succeeded
Sql> Grant Select on Scott.emp to Rusking;
Grant succeeded

6. View user Information
Sql> SELECT * from all_users WHERE username= ' rusking ';
USERNAME user_id CREATED
------------------------------ ---------- -----------
Rusking 93 2015/2/15 1

To view user system permissions:
Sql> select * from User_sys_privs;
Sql> SELECT * from Dba_sys_privs WHERE grantee= ' rusking ';
GRANTEE PRIVILEGE admin_option
------------------------------ ---------------------------------------- ------------
Rusking UNLIMITED tablespace NO

Sql> SELECT * from Dba_role_privs WHERE grantee= ' rusking ';
GRANTEE granted_role admin_option Default_role
------------------------------ ------------------------------ ------------ ------------
Rusking CONNECT NO YES
Rusking RESOURCE NO YES
To view the permissions that are included in the granted role:
Sql> SELECT * from Role_sys_privs WHERE role= ' CONNECT ';
ROLE PRIVILEGE admin_option
------------------------------ ---------------------------------------- ------------
CONNECT CREATE SESSION NO

Sql> SELECT * from Role_sys_privs WHERE role= ' RESOURCE ';
ROLE PRIVILEGE admin_option
------------------------------ ---------------------------------------- ------------
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE Indextype NO
8 Rows selected

=========================
To view User object permissions:
SELECT * from Dba_tab_privs;
SELECT * from All_tab_privs;
SELECT * from User_tab_privs;
View all roles:
SELECT * from Dba_roles;
To view the roles owned by the user:
SELECT * from Dba_role_privs;
SELECT * from User_role_privs;

View the default tablespace for the current user
Select Username,default_tablespace from User_users;

From "http://www.cnblogs.com/rootq/archive/2009/09/03/1559576.html"

Oracle User Management