Managing Oracle Users

Creating Users

CREATE USER <UserName>

    IDENTIFIED BY <Password>

    DEFAULT TABLESPACE data_ts

    QUOTA 100M ON test_ts

    QUOTA 500K ON data_ts

    TEMPORARY TABLESPACE temp_ts

    PROFILE clerk;

GRANT create session TO jward;

A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.

Note:

As administrator, you should create your own roles and assign only those privileges that are needed. For example, many users formerly granted the CONNECT privilege did not need the additional privileges CONNECT used to provide. Instead, only CREATE SESSION was actually needed, and in fact that is the only privilege CONNECT presently retains.

Specifying a Name

Within each database, a user name must be unique with respect to other user names and roles. A user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have a unique name.

Assigning a Default Tablespace

Each user should have a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle Database stores the object in the default user tablespace.

Assigning Tablespace Quotas

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.

Assigning a Temporary Tablespace

Each user also should be assigned a temporary tablespace. When a user executes a SQL statement that requires a temporary segment, Oracle stores the segment in the user's temporary tablespace. These temporary segments are created by the system when doing sorts or joins and are owned by SYS, which has resource privileges in all tablespaces.

Note:

If your SYSTEM tablespace is locally managed, then users must be assigned a specific default (locally managed) temporary tablespace. They may not be allowed to default to using the SYSTEM tablespace because temporary objects cannot be placed in permanent locally managed tablespaces.

Specifying a Profile

You also specify a profile when you create a user. A profile is a set of limits on database resources and password access to the database. If no profile is specified, then the user is assigned a default profile.

Setting Default Roles

You cannot set default roles for a user in the CREATE USER statement. When you first create a user, the default role setting for the user is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to change the default roles for the user.

Altering Users

You can alter user security settings with the ALTER USER statement. Changing user security settings affects the future user sessions, not current sessions.

The following statement alters the security settings for the user, avyrros:

ALTER USER avyrros

    IDENTIFIED EXTERNALLY

    DEFAULT TABLESPACE data_ts

    TEMPORARY TABLESPACE temp_ts

    QUOTA 100M ON data_ts

    QUOTA 0 ON test_ts

    PROFILE clerk;

Dropping Users

DROP USER jones CASCADE;

User and Profile Information in Data Dictionary Views

The following data dictionary views contain information about database users and profiles:

View	Description
DBA_USERS	Describes all users of the database
ALL_USERS	Lists users visible to the current user, but does not describe them
USER_USERS	Describes only the current user
DBA_TS_QUOTAS USER_TS_QUOTAS	Describes tablespace quotas for users
USER_PASSWORD_LIMITS	Describes the password profile parameters that are assigned to the user
USER_RESOURCE_LIMITS	Displays the resource limits for the current user
DBA_PROFILES	Displays all profiles and their limits
RESOURCE_COST	Lists the cost for each resource
V$SESSION	Lists session information for each current session, includes user name
V$SESSTAT	Lists user session statistics
V$STATNAME	Displays decoded statistic names for the statistics shown in the V$SESSTAT view
PROXY_USERS	Describes users who can assume the identity of other users

Listing All Users and Associated Information

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

Listing All Tablespace Quotas

SELECT * FROM DBA_TS_QUOTAS;

Listing All Profiles and Assigned Limits

SELECT * FROM DBA_PROFILES ORDER BY PROFILE;

Viewing Memory Use for Each User Session

SELECT USERNAME, VALUE || 'bytes' "Current UGA memory" FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name WHERE sess.SID = stat.SID AND stat.STATISTIC# = name.STATISTIC# AND name.NAME = 'session uga memory';

Dropping Profiles

DROP PROFILE clerk CASCADE;

Managing User Roles

Creating a Role

CREATE ROLE clerk IDENTIFIED BY bicentennial;

Later, you can set or change the authorization method for a role using the ALTER ROLE statement.

ALTER ROLE clerk IDENTIFIED EXTERNALLY;

To alter the authorization method for a role, you must have the ALTER ANY ROLE system privilege or have been granted the role with the ADMIN OPTION.

Dropping Roles

DROP ROLE clerk;

User Privileges and Roles

Granting System Privileges and Roles

GRANT CREATE SESSION, accts_pay TO jward;

Granting the ADMIN OPTION

GRANT new_dba TO michael WITH ADMIN OPTION;

Creating a New User with the GRANT Statement

GRANT CONNECT TO ssmith IDENTIFIED BY p1q2r3;

Granting Object Privileges

GRANT SELECT, INSERT, DELETE ON emp TO jfee, tsmith;

To grant all object privileges on the salary view to the user jfee, use the ALL keyword as shown in the following example:

GRANT ALL ON salary TO jfee;

Note:

A grantee cannot regrant access to objects unless the original grant included the GRANT OPTION. Thus in the example just given, jfee cannot use the GRANT statement to grant object privileges to anyone else.

Granting Object Privileges on Behalf of the Object Owner

GRANT SELECT ON hr.employees TO blake WITH GRANT OPTION;

If you examine the DBA_TAB_PRIVS view, then you will see that hr is shown as the grantor of the privilege:

SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';

GRANT SELECT ON hr.employees TO clark;

Granting Privileges on Columns

GRANT INSERT (acct_no) ON accounts TO scott;

GRANT INSERT(ename, job) ON emp TO jfee, tsmith;

Revoking User Privileges and Roles

Revoking System Privileges and Roles

REVOKE CREATE TABLE, accts_rec FROM tsmith;

Revoking Object Privileges

REVOKE SELECT, insert ON emp FROM jfee, tsmith;

REVOKE ALL ON dept FROM human_resources;

Revoking Object Privileges on Behalf of the Object Owner

REVOKE  SELECT ON hr.employees FROM clark;

Revoking Column-Selective Object Privileges

REVOKE UPDATE ON dept FROM human_resources;

GRANT UPDATE (dname) ON dept TO human_resources;

Revoking the REFERENCES Object Privilege

REVOKE REFERENCES ON dept FROM jward CASCADE CONSTRAINTS;

The SET ROLE Statement

SET ROLE clerk IDENTIFIED BY bicentennial;

You can disable all roles with the following statement:

SET ROLE NONE;

Specifying Default Roles

ALTER USER jane DEFAULT ROLE payclerk, pettycash;

Restricting the Number of Roles that a User Can Enable

A user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES.

Viewing Privilege and Role Information

To access information about grants of privileges and roles, you can query the following data dictionary views:

View	Description
DBA_COL_PRIVS ALL_COL_PRIVS USER_COL_PRIVS	DBA view describes all column object grants in the database. ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. USER view describes column object grants for which the current user is the object owner, grantor, or grantee.
ALL_COL_PRIVS_MADE USER_COL_PRIVS_MADE	ALL view lists column object grants for which the current user is object owner or grantor. USER view describes column object grants for which the current user is the grantor.
ALL_COL_PRIVS_RECD USER_COL_PRIVS_RECD	ALL view describes column object grants for which the current user or PUBLIC is the grantee. USER view describes column object grants for which the current user is the grantee.
DBA_TAB_PRIVS ALL_TAB_PRIVS USER_TAB_PRIVS	DBA view lists all grants on all objects in the database. ALL view lists the grants on objects where the user or PUBLIC is the grantee. USER view lists grants on all objects where the current user is the grantee.
ALL_TAB_PRIVS_MADE USER_TAB_PRIVS_MADE	ALL view lists the all object grants made by the current user or made on the objects owned by the current user. USER view lists grants on all objects owned by the current user.
ALL_TAB_PRIVS_RECD USER_TAB_PRIVS_RECD	ALL view lists object grants for which the user or PUBLIC is the grantee. USER view lists object grants for which the current user is the grantee.
DBA_ROLES	This view lists all roles that exist in the database.
DBA_ROLE_PRIVS USER_ROLE_PRIVS	DBA view lists roles granted to users and roles. USER view lists roles granted to the current user.
DBA_SYS_PRIVS USER_SYS_PRIVS	DBA view lists system privileges granted to users and roles. USER view lists system privileges granted to the current user.
ROLE_ROLE_PRIVS	This view describes roles granted to other roles. Information is provided only about roles to which the user has access.
ROLE_SYS_PRIVS	This view contains information about system privileges granted to roles. Information is provided only about roles to which the user has access.
ROLE_TAB_PRIVS	This view contains information about object privileges granted to roles. Information is provided only about roles to which the user has access.
SESSION_PRIVS	This view lists the privileges that are currently enabled for the user.
SESSION_ROLES	This view lists the roles that are currently enabled to the user.

Listing All System Privilege Grants

SELECT * FROM DBA_SYS_PRIVS;

Listing All Role Grants

SELECT * FROM DBA_ROLE_PRIVS;

Listing Object Privileges Granted to a User

SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'JWARD';

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS;

Listing the Current Privilege Domain of Your Session

SELECT * FROM SESSION_ROLES;

SELECT * FROM SESSION_PRIVS;

Listing Roles of the Database

SELECT * FROM DBA_ROLES;

Listing Information About the Privilege Domains of Roles

SELECT GRANTED_ROLE, ADMIN_OPTION FROM ROLE_ROLE_PRIVS WHERE ROLE = 'SYSTEM_ADMIN';

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS WHERE ROLE = 'SECURITY_ADMIN';