How to Grant/Revoke privileges

Data control language (DCL)

A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database (Authorization). In particular, it is a component of Structured Query Language (SQL).

Examples of DCL commands include:

GRANT    to allow specified users to perform specified tasks.
REVOKE  to cancel previously granted or denied permissions.

The operations for which privileges may be granted to or revoked from a user or role apply to both the Data definition language (DDL) and the Data manipulation language (DML), and may include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, and USAGE.

In the Oracle database, executing a DCL command issues an implicit commit. Hence you cannot roll back the command.

Privileges

When multiple users can access database objects, authorization can be controlled to these objects with privileges. Every object has an owner. Privileges control if a user can modify an object owned by another user. Privileges are granted or revoked either by the instance administrator, a user with the ADMIN privilege or, for privileges to a certain object, by the owner of the object.

There are two type of Privileges: System Privileges and Object privileges


System privileges
A system privilege is the right to perform a particular action or to perform an action on any object of a particular type. Objects include tables, views, materialized views, synonyms, indexes, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages. Only the instance administrator or a user with ADMIN privilege can grant or revoke system privileges.
System Privileges are normally granted by a DBA to users.


System privileges are

Privilege	Description
ADMIN	Enables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion.
ALTER ANY CACHE GROUP	Enables a user to alter any cache group in the database.
ALTER ANY INDEX	Enables a user to alter any index in the database.
	Note: There is no ALTER INDEX statement.
ALTER ANY MATERIALIZED VIEW	Enables a user to alter any materialized view in the database.
	Note: There is no ALTER MATERIALIZED VIEW statement.
ALTER ANY PROCEDURE	Enables a user to alter any PL/SQL procedure, function or package in the database.
ALTER ANY SEQUENCE	Enables a user to alter any sequence in the database.
	Note: There is no ALTER SEQUENCE statement.
ALTER ANY TABLE	Enables a user to alter any table in the database.
ALTER ANY VIEW	Enables a user to alter any view in the database.
	Note: There is no ALTER VIEW statement.
CACHE_MANAGER	Enables a user to perform operations related to cache groups.
CREATE ANY CACHE GROUP	Enables a user to create a cache group owned by any user in the database.
CREATE ANY INDEX	Enables a user to create an index on any table or materialized view in the database.
CREATE ANY MATERIALIZED VIEW	Enables a user to create a materialized view owned by any user in the database.
CREATE ANY PROCEDURE	Enables a user to create a PL/SQL procedure, function or package owned by any user in the database.
CREATE ANY SEQUENCE	Enables a user to create a sequence owned by any user in the database.
CREATE ANY SYNONYM	Enables a user to create a private synonym owned by any user in the database.
CREATE ANY TABLE	Enables a user to create a table owned by any user in the database.
CREATE ANY VIEW	Enables a user to create a view owned by any user in the database.
CREATE CACHE GROUP	Enables a user to create a cache group owned by that user.
CREATE MATERIALIZED VIEW	Enables a user to create a materialized view owned by that user.
CREATE PROCEDURE	Enables a user to create a PL/SQL procedure, function or package owned by that user.
CREATE PUBLIC SYNONYM	Enables a user to create a public synonym.
CREATE SEQUENCE	Enables a user to create a sequence owned by that user.
CREATE SESSION	Enables a user to create a connection to the database.
CREATE SYNONYM	Enables a user to create a private synonym.
CREATE TABLE	Enables a user to create a table owned by that user.
CREATE VIEW	Enables a user to create a view owned by that user.
DELETE ANY TABLE	Enables a user to delete from any table in the database.
DROP ANY CACHE GROUP	Enables a user to drop any cache group in the database.
DROP ANY INDEX	Enables a user to drop any index in the database.
DROP ANY MATERIALIZED VIEW	Enables a user to drop any materialized view in the database.
DROP ANY PROCEDURE	Enables a user to drop any PL/SQL procedure, function or package in the database.
DROP ANY SEQUENCE	Enables a user to drop any sequence in the database.
DROP ANY SYNONYM	Enables a user to drop a synonym owned by any user in the database.
DROP ANY TABLE	Enables a user to drop any table in the database.
DROP ANY VIEW	Enables a user to drop any view in the database.
DROP PUBLIC SYNONYM	Enables a user to drop a public synonym.
EXECUTE ANY PROCEDURE	Enables a user to execute any PL/SQL procedure, function or package in the database.
FLUSH ANY CACHE GROUP	Enables a user to flush any cache group in the database.
INSERT ANY TABLE	Enables a user to insert into any table in the database. It also enables the user to insert into any table using the synonym, public or private, to that table.
LOAD ANY CACHE GROUP	Enables a user to load any cache group in the database.
REFRESH ANY CACHE GROUP	Enables a user to flush any cache group in the database.
SELECT ANY SEQUENCE	Enables a user to select from any sequence or synonym on a sequence in the database.
SELECT ANY TABLE	Enables a user to select from any table, view, materialized view, or synonym in the database.
UNLOAD ANY CACHE GROUP	Enables a user to unload any cache group in the database.
UPDATE ANY TABLE	Enables a user to update any table or synonym in the database.
XLA	Enables a user to connect to a database as an XLA reader.

Object Privileges

Object privileges mean privileges on tables, views, materialized views, indexes, synonyms, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages.

An object's owner has all object privileges for that object, and those privileges cannot be revoked. The object's owner can grant object privileges for that object to other database users. A user with ADMIN privilege can grant and revoke object privileges from users who do not own the objects on which the privileges are granted.

Object Privileges are

Privilege	Object type	Description
DELETE	Table	Enables a user to delete from a table.
EXECUTE	PL/SQL package, procedure or function	Enables a user to execute a PL/SQL package, procedure or function directly.
FLUSH	Cache group	Enables a user to flush a cache group.
INDEX	Table or materialized view	Enables a user to create an index on a table or materialized view.
INSERT	Table or synonym	Enables a user to insert into a table or into the table through a synonym.
LOAD	Cache group	Enables a user to load a cache group.
REFERENCES	Table or materialized view	Enables a user to create a foreign key dependency on a table or materialized view. The REFERENCES privilege on a parent table implicitly grants SELECT privilege on the parent table.
REFRESH	Cache group	Enables a user to refresh a cache group.
SELECT	Table, sequence, view, materialized view, or synonym	Enables a user to select from a table, sequence, view, materialized view, or synonym. The SELECT privilege enables a user to perform all operations on a sequence. A user can be granted the SELECT privilege on a synonym or a view without being explicitly granted the SELECT privilege on the originating table.
UNLOAD	Cache group	Enables a user to unload a cache group.
UPDATE	Table	Enables a user to update a table.

Dictionary views which show the granted privileges and roles

DBA_SYS_PRIVS - contains information about System privileges
DBA_TAB_PRIVS  - contains information about Object Privileges
DBA_ROLE_PRIVS - contains information about assigned rows;

* You can use user_sys_priv, user_tab_privs and user_role_privs to list the privileges information to the current user.

Examples

To grant the SELECT privilege on table tab1 to the authorization IDs maria and harry, use the following syntax:
GRANT SELECT ON TABLE tab1  TO maria,harry 


To grant the UPDATE and TRIGGER privileges on table tab1  to the authorization IDs anita and zhi, use the following syntax:
GRANT UPDATE, TRIGGER ON TABLE tab1  TO anita,zhi 


To grant the SELECT privilege on table tab1  to all users, use the following syntax:
GRANT SELECT ON TABLE tab1 to PUBLIC


To grant the EXECUTE privilege on procedure proc_1 to the authorization ID george, use the following syntax:
GRANT EXECUTE ON PROCEDURE proc_1 TO george


To grant the role purchases_reader_role to the authorization IDs george and maria, use the following syntax:
GRANT purchases_reader_role TO george,maria


To grant the SELECT privilege on table tab_1 to the role purchases_reader_role, use the following syntax:
GRANT SELECT ON TABLE tab_1 TO purchases_reader_role


To grant the USAGE privilege on the sequence generator order_id to the role sales_role, use the following syntax:
GRANT USAGE ON SEQUENCE order_id TO sales_role;


To grant the USAGE privilege on the user-defined type price to the role finance_role, use the following syntax:
GRANT USAGE ON TYPE price TO finance_role;


The following statement revokes the DROP ANY TABLE system privilege from the users george and oe:
REVOKE DROP ANY TABLE FROM hr, oe;


The following statement revokes the role dw_manager from the user sh:
REVOKE dw_manager FROM sh;


The following statement revokes the CREATE TABLESPACE system privilege from the dw_manager role:
REVOKE CREATE TABLESPACE  FROM dw_manager;


To revoke the DELETE privilege on orders from hr, issue the following statement:
REVOKE DELETE  ON orders FROM hr;

Using UNIX environment variables in SQL Plus

Passing variables to a SQL script under UNIX enviroment.

Method 1

$ Variable1="Someting value"
sqlplus db_user/db_password@db_sid <<EOF
set serveroutput on
BEGIN
  dbms_output.put_line('${Variable1}');
END;
/
exit;
EOF



Method 2
Create a test file called test_script.sql

$ cat test_script.sql
set serveroutput on
DECLARE
  var1 varchar2(100):='&&1';
  var2 varchar2(100):='&&2';
BEGIN
  dbms_output.put_line(var1);
  dbms_output.put_line(var2);
END;
/
exit;

For the test purpose let’s create a UNIX script file which executes out sql script.
$ cat test_unix_script.sh
var1="Some text"
var2="Some other text”
sqlplus -s db_user/db_password@db_sid @test_script.sql $var1 $var2

Output from execution of  the script
$ test_str.sh
old   3:   l_var1 varchar2(100):='&&1';
new   3:   l_var1 varchar2(100):='Some text';
old   4:   l_var2 varchar2(100):='&&2';
new   4:   l_var2 varchar2(100):='Some other text';

Some text 
Some other text

Fix for Oracle12c - ORA-01792: maximum number of columns in a table or view is 1000

SYMPTOMS

It is possible to get an ORA-01792 "MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000"  error even when the original query does not select that many columns  in Oracle 12.1.0.2.0 version.

SOLUTION

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug.
Oracle suggest to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by hidden parameter "_fix_control"

The workaround is to set "_fix_control"='17376322:OFF'

On Session level
SQL> alter session set "_fix_control"='17376322:OFF';

OR at system level :

SQL> alter system set "_fix_control"='17376322:OFF';

OR

Apply Patch 19509982 if available for your DBVersion and Platform

To download the patch, please follow the steps below:
1) Go To MOS
2) Click on Patches & Updates
3) In the Patch Search Section, enter your patch number <19509982> in the Patch Name or Number field.
4) Click Search
5) Click the patch that meets your DbVersion and platform
6) Read any applicable notes before downloading, then click the download button.

Setting, Resetting/Unsetting Oracle DB Parameters

There are more than 250 documented and more than 900 undocumented parameters for Oracle.
You can query the documented parameters with the query below

select name,value,isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated,
from v$parameter

You can query the list of undocumented parameters with the script  list of hidden parameters in oracle db

Also you can query v$parameter2 as the same way you query the v$parameter view to view the session specific parameter values.

The values from the columns isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated are important.

isdefault = TRUE/FALSE –indicates if the value is the default value

isses_modifiable=TRUE/FALSE –indicates if the value can be modified by sessions

issys_modifiable = IMMEDIATE/DEFERRED/FALSE –indicates if the value can be modified by alter system command. Values represents when the changes takes effect. For immediate, no matter what you use (spfile or init file ) parameter change takes effect immediately.For deferred, parameter can be changed but change takes affect in subsequent sessions not for currently established sessions. For false it means parameter cannot be changed if you dont have a spfile.

isinstance_modifiable= true/false –this option is spesicif for RAC which has multiple instances for one database

If you want to view value of the parameter briefly you can also use show parameter command which searches the entered string with wildcard on both ends

SQL> show parameter pool

NAME TYPE VALUE
————————————
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 7759462
shared_pool_size big integer 0
streams_pool_size big integer 0

As you see the output above all parameters which include “pool” string in it are listed briefly.

Setting Parameter for Instance

You can set the parameter values with the command below

Alter system set parameter=value <comment=’text’> <deferred>
<scope=memory|spfile|both> <sid=’sid|*’>

elements of the command;

scope =BOTH/SPFILE/MEMORY indicates the scope of the parameter setting. both is the default behaviour.

sid = SID/* if you are on RAC environment you can give SID or * for this element which indicate that the setting will effect only the given SID or all nodes in RAC configuration.

comment= You can comment the change for future lookups. This comment can be viewed from UPDATE_COMMENT column of v$parameter view.

deferred = Indicates that the parameter change takes place for subsequent sessions only (not currently established sessions, including the one making the change). This element must be used for the parameters whose ISSYS_MODIFIABLE column in v$parameter view is deferred.

When you want to change the parameter only for the running instance and not for the subsequent instance you can change it only in memory. ;

SQL> alter system set cpu_count=4 scope=MEMORY;
System altered.

When you want to change the parameter for subsequent instances only
SQL> alter system set cpu_count=4 scope=SPFILE;
System altered.

When you want to change the parameter for running and subsequent instances (if issys_modifiable is not false);

SQL> alter system set cpu_count=4 scope=BOTH;
System altered.

!!!!!NB: When using an init.ora parameter file, the default and only valid value is SCOPE=MEMORY. So you must manually edit your init.ora file to change the parameter for subsequent instances.

Setting Parameters for sessions

You can change the values of some parameters whose ISSES_MODIFIABLE column of v$parameter view is TRUE. Scope of the parameter will be the end of the session.

SQL> alter session set timed_statistics=false;
Session altered.

SQL> show parameter timed_statistics

NAME TYPE VALUE
———————————— ———– ——-
timed_statistics boolean FALSE

Resetting/Unsetting parameters

If you want to use the default value of the parameter you have changed. You must reset it.

alter system reset parameter <scope=memory|spfile|both> sid=’sid|*’

Elements are same as setting parameter. The only difference is “you must” give si value (for single instance use ‘*’).

SQL> alter system reset control_file_record_keep_time scope=spfile sid=’*’;
System altered.

!!!!!NB: Do not try to change the value of a parameter with ” or null to reset it to default

List of hidden parameters in Oracle DB

How to view list of hidden parameters?

Hidden parameters sometimes ease the life of  DBA but be carefull while using them. Oracle usually does not support the usage of hidden parameters without their knowledge and you can't even know the side effects of the parameter.
Here is the query you can use to view the values of hidden parameter.

Managing Initialization Parameters Using a Server Parameter File (SPFILE)

Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file(PFILE). For better manageability, you can choose to maintain initialization parameters in a binary server parameter file (SPFILE) that is persistent across database startup and shutdown.

Grants to execute AWR (Automatic Workload Repository) Report

Problem:  How to give access rights to certain user to run AWR reports.

How to give access to run awr reports from normal users.
Generally, we run AWR from some administrator accounts (usually from sys or system users).
In order to generate the AWR reports from normal user we need to grant the follow privileges to him.

Let's follow the following plan:
  1. Create user.
  2. Grant the advisor privilege.
  3. Grant execute permission on dbms_workload_repository package.
  4. Grant select any dictionary

Here here is the complete summary of the activity
===================================================================
SQL> create user awradmin identified by pass123;

User created.

SQL> grant connect,resource to awradmin;

Grant succeeded.

SQL> grant advisor to awradmin;

Grant succeeded.

SQL> grant execute on dbms_workload_repository to awradmin;

Grant succeeded.

SQL> grant select any dictionary to awradmin;

Grant succeeded.

SQL> conn awradmin/awradmin123
Connected.
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>


===================================================================
In some cases it may be not possible to grant all permissions so in that case you can use follow the plan described below.

GRANT SELECT ON SYS.V_$DATABASE TO awradmin;
GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;

SQL> GRANT SELECT ON SYS.V_$DATABASE TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;

Grant succeeded.

SQL> GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;

Grant succeeded.

SQL>

=====================================================================
There is a fast way to do that :) You need to grant role CONNECT, SELECT_CATALOG_ROLE and OEM_MONITOR to user and that is all.

GRANT CONNECT TO awradmin;
GRANT SELECT_CATALOG_ROLE TO awradmin;
GRANT OEM_MONITOR TO awradmin;

The role "SELECT_CATALOG_ROLE" contains the privileges:
   SELECT ON DBA_HIST_SNAPSHOT
   SELECT ON DBA_HIST_DATABASE_INSTANCE
   SELCT ON V_$INSTANCE
   SELECT ON V_$DATABASE

The role "OEM_MONITOR" contains the privilege EXECUTE ON DBMS_WORKLOAD_REPOSITORY.


SQL> GRANT CONNECT TO awradmin;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO awradmin;

Grant succeeded.

SQL> GRANT OEM_MONITOR TO awradmin;

Grant succeeded.

SQL>


Thank you, Enjoy ... :)