Provisioning Database Access

http://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006107


Create a User and Grant the Create Session System Privilege

CREATE USER jward
    IDENTIFIED BY AZ7BC2
    DEFAULT TABLESPACE data_ts
    QUOTA 100M ON TBS_NAME
    PROFILE USERS;

GRANT create session TO jward;

RESETTING USERS PASSWORD

ALTER USER andy IDENTIFIED BY swordfish; 

Dropping Users - When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped.

DROP USER jones CASCADE;

User and Profile Information in Data Dictionary Views

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;
 
USERNAME        PROFILE         ACCOUNT_STATUS  
--------------- --------------- --------------- 

Listing All Tablespace Quotas

SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE    USERNAME    BYTES     MAX_BYTES    BLOCKS    MAX_BLOCKS
----------    ---------  --------   ----------   -------   ----------

Listing All Profiles and Assigned Limits

SELECT * FROM DBA_PROFILES
   ORDER BY PROFILE;

PROFILE             RESOURCE_NAME              RESOURCE   LIMIT             
-----------------   ---------------            ---------- --------------

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';

To see the maximum UGA memory ever allocated to each session since the instance started, replace 'session uga memory' in the preceding query with 'session uga memory max'.

A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object.

Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges
or other roles. They are a means of facilitating the granting of 
multiple privileges or roles to users. 

A profile is a named set of resource limits and password parameters that 

restrict database usage and instance resources for a user.

Create a profile

CREATE PROFILE clerk LIMIT
    SESSIONS_PER_USER 1
    IDLE_TIME 30
    CONNECT_TIME 600;

Drop a profile 

DROP PROFILE clerk CASCADE;

Predefined Roles

Role Name	Created By (Script)	Description
CONNECT	SQL.BSQ	Includes only the following system privilege: CREATE SESSION
RESOURCE	SQL.BSQ	Includes the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
DBA	SQL.BSQ	All system privileges WITH ADMIN OPTION
EXP_FULL_DATABASE	CATEXP.SQL	Provides the privileges required to perform full and incremental database exports and includes: SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and INSERT, DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. Also the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
IMP_FULL_DATABASE	CATEXP.SQL	Provides the privileges required to perform full database imports. Includes an extensive list of system privileges (use view DBA_SYS_PRIVS to view privileges) and the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
DELETE_CATALOG_ROLE	SQL.BSQ	Provides DELETE privilege on the system audit table (AUD$)
EXECUTE_CATALOG_ROLE	SQL.BSQ	Provides EXECUTE privilege on objects in the data dictionary. Also, HS_ADMIN_ROLE.
SELECT_CATALOG_ROLE	SQL.BSQ	Provides SELECT privilege on objects in the data dictionary. Also, HS_ADMIN_ROLE.
RECOVERY_CATALOG_OWNER	CATALOG.SQL	Provides privileges for owner of the recovery catalog. Includes: CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK, CREATE TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, and CREATE PROCEDURE
HS_ADMIN_ROLE	CATHS.SQL	Used to protect access to the Heterogeneous Services (HS) data dictionary tables (grants SELECT) and packages (grants EXECUTE). It is granted to SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE such that users with generic data dictionary access also can access the HS data dictionary.
AQ_USER_ROLE	CATQUEUE.SQL	Obsolete, but kept mainly for release 8.0 compatibility. Provides execute privilege on DBMS_AQ and DBMS_AQIN.
AQ_ADMINISTRATOR_ROLE	CATQUEUE.SQL	Provides privileges to administer Advance Queuing. Includes ENQUEUE ANY QUEUE, DEQUEUE ANY QUEUE, and MANAGE ANY QUEUE, SELECT privileges on AQ tables and EXECUTE privileges on AQ packages.

Creating a Role

CREATE ROLE clerk IDENTIFIED BY bicentennial;

Dropping Roles

DROP ROLE clerk;

Granting System Privileges and Roles

GRANT CREATE SESSION, accts_pay TO jward;

Creating a New User with the GRANT Statement

GRANT CONNECT TO ssmith IDENTIFIED BY p1q2r3;

Oracle enables you to create a new user with the GRANT statement. If you specify a password using the IDENTIFIED BY
 clause, and the user name/password does not exist in the database, then
 a new user with that user name and password is created. The above
example creates ssmith as a new user while granting ssmith the CONNECT system privilege 

 

Granting Object Privileges

The following statement grants the SELECT, INSERT, and DELETE object privileges for all columns of the emp table to the users, jfee and tsmith:

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

Revoking System Privileges and Roles

The following statement revokes the CREATE TABLE system privilege and the accts_rec role from tsmith:

REVOKE CREATE TABLE, accts_rec FROM tsmith;

Revoking Object Privileges

The following statement revokes the SELECT and INSERT privileges on the emp table from the users jfee and tsmith:

REVOKE SELECT, insert ON emp FROM jfee, tsmith; 

The following statement revokes all object privileges for the dept table that you originally granted to the human_resource role

REVOKE ALL ON dept FROM human_resources; 

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.

Some examples of using these views follow. For these examples, assume the following statements have been issued:

CREATE ROLE security_admin IDENTIFIED BY honcho;

GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
    CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
    AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
    TO security_admin WITH ADMIN OPTION;

GRANT SELECT, DELETE ON SYS.AUD$ TO security_admin;

GRANT security_admin, CREATE SESSION TO swilliams;

GRANT security_admin TO system_administrator;

GRANT CREATE SESSION TO jward;

GRANT SELECT, DELETE ON emp TO jward;

GRANT INSERT (ename, job) ON emp TO swilliams, jward; 

Listing All System Privilege Grants

The following query returns all system privilege grants made to roles and users:

SELECT * FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM
--------------     --------------------------------- ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
SWILLIAMS          CREATE SESSION                    NO
JWARD              CREATE SESSION                    NO

 

Listing All Role Grants

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE            GRANTED_ROLE                         ADM
------------------ ------------------------------------ ---

Listing the Current Privilege Domain of Your Session

The following query lists all roles currently enabled for the issuer:

SELECT * FROM SESSION_ROLES; 

The following query lists all system privileges currently available in the security domain of the issuer, both from explicit privilege grants and from enabled roles:

SELECT * FROM SESSION_PRIVS;

Listing Roles of the Database

The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role.

The following query lists all the roles in the database:

SELECT * FROM DBA_ROLES;

Listing Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles. For example, the following query lists all the roles granted to the system_admin role:

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

GRANTED_ROLE              ADM
----------------          ----
SECURITY_ADMIN            NO 

The following query lists all the system privileges granted to the security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

 

The following query lists all the object privileges granted to the security_admin role:

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

 

 

 

 

 

 

 

 

 

 

 

 

 

System DBA views - User Management and Administration

Who are you?

SQL> SELECT *
  2  FROM USER_USERS;

OUTPUT:

USERNAME     USER_ID   DEFAULT_TABLESPACE      TEMPORARY TABLESPACE  CREATED
----------   ------    --------------------    --------------------  --------

USER_USERS view allows you to view how your Oracle ID was set up, when it
was set up, and it also shows other user-specific, vital statistics. 

How to view all users that exist in the database - Use ALL_USERS view 

SQL> SELECT *
  2  FROM ALL_USERS;

USERNAME        USER_ID         CREATED
--------------  -------    ------------

What Are Your Privileges?

Use the following views 

USER_SYS_PRIVS - examine your system privileges

USER_ROLE_PRIVS  - view information about roles you have been granted within the database

What Do You Have Access To?

USER_CATALOG -  is simply a catalog of the tables, views, synonyms, and sequences owned by the current user 

ALL_CATALOG - enables you to see tables owned by other individuals.

USER_OBJECTS - view to select general information about a user's
owned objects, such as the name, type, date created, date modified, and the status
of the object.

USER_TABLES - view the information in detail

ALL_TABLES - allows you to see all the tables to which you have access,
instead of just the tables you own 

As a database user, you can monitor the growth of tables and indexes in your catalog
by querying the USER_SEGMENTS view. As the name suggests.

USER_SEGMENTS gives you information about each segment, such as storage information and extents taken. A
segment may consist of a table, index, cluster rollback, temporary, or cache.

ALL_TAB_PRIVS view lists all privileges that you have as a database user on each
table available to you. 

read-only access = SELECT privileges

When you create objects, you usually need to know where to place them in the database unless you allow your target destination to take the default. An Oracle database is broken up into tablespaces, each of which are capable of storing objects. Each tablespace is allocated a certain amount of disk space, according to what is available on the system. Disk space is usually acquired through the system administrator (SA).

USER_TABLESPACES - will list the tablespaces that you have access to, the default initial and next sizes of objects created within them, and their status.

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,
  2         INITIAL_EXTENT,
  3         NEXT_EXTENT,
  4         PCT_INCREASE,
  5         STATUS
  6  FROM USER_TABLESPACES;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE STATUS
------------------------------ -------------- ----------- ------------ ------

This type of query is very useful when you are creating objects, such as tables and indexes, which will require storage. When a table or index is created, if the initial and next storage parameters are not specified in the DDL, the table or index will take the tablespace's default values. The same concept applies to PCT INCREASE, which is an Oracle parameter specifying the percentage of allocated space an object should take when it grows. If a value for PCT INCREASE is not specified when the table or index is created, the database server will allocate the default value that is specified for the corresponding tablespace. Seeing the default values enables you to determine whether you need to use a storage clause in the CREATE statement. 

 Sometimes, however, you need to know more than which tablespaces you may access, that is, build tables under. For example, you might need to know what your limits are within the tablespaces so that you can better manage the creation and sizing of your objects. The USER_TS_QUOTAS view provides the necessary information. The next query displays a user's space limits for creating objects in the database. 

 

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,
  2         BYTES, MAX_BYTES
  3  FROM USER_TS_QUOTAS;

TABLESPACE_NAME                     BYTES  MAX_BYTES
------------------------------ ---------- ----------

BYTES identifies the total number of bytes in that tablespace that are associated with the user. MAX BYTES identifies the maximum bytes allotted to the user, or the user's quota, on the tablespace. The first two values in this column are self-explanatory. The -1 in the third row means quota unlimited--that is, no limits are placed on the user for that tablespace.  

 

System DBA Views

DBA_USERS - you are able to see the vital information on each user.

DBA_ROLE_PRIVS - provides information about database roles that have been granted to users.

DBA_ROLE_PRIVS - provides information about database roles that have been granted to users. The first column is the grantee, or user. The second column displays the granted role. Notice that every role granted to the user corresponds to a record in the table. ADM identifies whether the role was granted with the Admin option, meaning that the user is able to grant the matching role to other users. The last column is DEFAULT, stating whether the matching role is a default role for the user.

 

SQL> SELECT *
  2  FROM SYS.DBA_SYS_PRIVS
  3  WHERE GRANTEE = 'RJENNINGS';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
AHASTINGS                      CREATE SESSION                           NO
VROY                           UNLIMITED TABLESPACE                     NO

DBA_SYS_PRIVS view lists all system-level privileges that have been granted to the user. This view is similar to DBA_ROLE_PRIVS

DBA_CATALOG is the same thing as the USER_CATALOG, only the owner of the table is included. DBA_CATALOG is a view that the DBA can use to take a quick look at all tables.

DBA_TABLES view gives specific information about database tables, mostly concerning storage

DBA_SYNONYMS view provides a list of all synonyms that exist in the database

DBA_IND - listing all indexes that belong to a schema and matching them up with their corresponding table.

DBA_TABLESPACES to see a list of all tablespaces.

DBA_SEGMENTS provides information about each segment, or object in the database such as storage allocation, space used, and extents

SQL> SELECT SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  2         SUBSTR(SEGMENT_TYPE,1,12) SEGMENT_TYPE,
  3         BYTES,
  4         EXTENTS,
  5  FROM SYS.DBA_SEGMENTS

SEGMENT_NAME                   SEGMENT_TYPE      BYTES    EXTENTS
------------------------------ ------------ ---------- ----------

DBA_EXTENTS provides information about each extent of a segment.

SQL> SELECT SUBSTR(OWNER,1,10) OWNER,
  2         SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  3         EXTENT_ID,
  4         BYTES
  5  FROM SYS.DBA_EXTENTS

OWNER      SEGMENT_NAME                    EXTENT_ID     BYTES
---------- ------------------------------ ---------- --------

Oracle allocates space to the database by using "data files." Space logically exists within a tablespace, but data files are the physical entities of tablespaces    

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,25) TABLESPACE_NAME,
  2         SUBSTR(FILE_NAME,1,40) FILE_NAME,
  3         BYTES
  4  FROM SYS.DBA_DATA_FILES;

TABLESPACE_NAME           FILE_NAME                                     BYTES
------------------------- ---------------------------------------- ----------

 

Open a Editor from SQL*PLUS

SQL>define_editor=vi    // for UNIX
SQL>define_editor=notepad    //for Windows



SQL>edit    // this would open a vi in Unix


afiedt.buf is the file that would be opened. You can then edit the file with a command\query .


Ensure you put as / as a end of the file


Look out for a file called glogin.sql which would ensure setting is permanent in the system line size or page size

Formatting SQL*PLUS command line terminal

SQL>column <column_name> format a20
// format the column_name with 20 alphabets

SQL>set pagesize 100
//set page size view to 100 alphabets, this decides how much length of the putty terminal will be occupied with the same headings. Like how much letter does a page consist of

SQL>set linesize 150
//set the size of the line to 150 alphabets

For more formatting options check out
http://goo.gl/DPXV9

How to check if Oracle DB is started by spfile or pfile

Below is the code that can be used to check

SELECT DECODE (VALUE, NULL, 'PFILE', 'SPFILE')
"Init File Type"
FROM V$PARAMETER
WHERE NAME = 'spfile';

Oracle Database Startup Modes

No Mount Mode

Open and Read the Parameter file

Create\Append the Alert Log file

Memory Structures are allocated

Background process is started

Instance is started

Administrative Tasks in No Mount mode

Ø  To create Database

Ø  To recreate Control file

Mount Mode

Open and Read the Parameter file

Create\Append the Alert Log file

Memory Structures are allocated

Background process is started

Instance is started.

Open the Control file and read, check for physical existence of file.

Mount the Database. Data file\Redo Log files

Administrative Tasks in Mount mode

Ø  To perform system datafile recovery

Ø  To perform full database complete recovery

Ø  To perform incomplete recovery

Ø  To rename system datafile or redo log file

Ø  To place database to archive log mode to noarchive log mode

Ø  Other administrative tasks

Open Mode

Open and Read the Parameter file

Create\Append the Alert Log file

Memory Structures are allocated

Background process is started

Instance is started.

Open the Control file and read, check for physical existence of file.

Mount the Database. Data file\Redo Log files

Sync status of the Database

Database will be open

Administrative Tasks in Mount mode

To make database avaialble for users

To perform transactions