Oracle: Grant/Revoke Privileges

Grant Privileges on Tables

You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

Description

1. Select Ability to query the table with a select statement.
2. Insert Ability to add new rows to the table with the insert statement.
3. Update Ability to update rows in the table with the update statement.
4. Delete Ability to delete rows from the table with the delete statement.
5. References Ability to create a constraint that refers to the table.
6. Alter         Ability to change the table definition with the alter table statement.
7. Index Ability to create an index on the table with the create index statement.

The syntax for granting privileges on a table is:

grant privileges on object to user;

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:

grant select, insert, update, delete on suppliers to smithj;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to smithj;

If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:

grant select on suppliers to public;

Revoke Privileges on Tables

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from user;

For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:

revoke delete on suppliers from anderson;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke all on suppliers from public;

Grant Privileges on Functions/Procedures

When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:

Description

Execute

1. Ability to compile the function/procedure.
2. Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to user;

For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:

grant execute on Find_Value to smithj;

If you wanted to grant all users the ability to execute this function, you would execute the following:

grant execute on Find_Value to public;

Revoke Privileges on Functions/Procedures

Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.

The syntax for the revoking privileges on a function or procedure is:

revoke execute on object from user;

If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:

revoke execute on Find_Value from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke execute on Find_Value from public;

Oracle Useful Queries

SQL: UNION Query

The SQL UNION query allows you to combine the result sets of 2 or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements.

Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL UNION query is:

select field1, field2, . field_n from tables

UNION

select field1, field2, . field_n from tables;

SQL UNION Query - Returns single field example

The following is an example of the SQL UNION query that returns one field from multiple SELECT statements (and both fields have the same data type):

select supplier_id from suppliers

UNION

select supplier_id from orders;

In this SQL UNION query example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The SQL UNION query removes duplicates. If you do notwish to remove duplicates, try using the SQL UNION ALL query.

SQL: UNION ALL Query

The SQL UNION ALL query allows you to combine the result sets of 2 or more SELECT statements. It returns all rows from the query (even if the row exists in more than one of the SELECT statements).

Each SQL SELECT statement within the SQL UNION ALL query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL UNION ALL query is:

select field1, field2, ... field_n from tables

UNION ALL

select field1, field2, ... field_n from tables;

SQL UNION ALL Query - Returns single field example

The following is an example of the SQL UNION ALL query that returns one field from multiple SELECT statements (and both fields have the same data type):

select supplier_id from suppliers

UNION ALL

select supplier_id from orders;

This SQL UNION ALL query would return a supplier_id multiple times in your result set if the supplier_id appeared in both the suppliers and orders table. The SQL UNION ALL query does not remove duplicates. If you wish to remove duplicates, try using the SQL UNION query.

SQL: INTERSECT Query

The SQL INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Each SQL statement within the SQL INTERSECT query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL INTERSECT query is:

select field1, field2, . field_n from tables

INTERSECT

select field1, field2, . field_n from tables;

SQL INTERSECT Query - Single field example

The following is an example of an SQL INTERSECT query that has one field with the same data type:

select supplier_id from suppliers

INTERSECT

select supplier_id from orders;

In this SQL INTERSECT query example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.

SQL: MINUS Query

The SQL MINUS query returns all rows in the first SQL SELECT statement that are not returned in the second SQL SELECT statement.

Each SQL SELECT statement within the SQL MINUS query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL MINUS query is:

select field1, field2, ... field_n from tables

MINUS

select field1, field2, ... field_n from tables;

SQL MINUS Query - Single field example

The following is an example of an SQL MINUS query that has one field with the same data type:

select supplier_id from suppliers

MINUS

select supplier_id from orders;

This SQL Minus query example returns all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.

Oracle: DUAL Table

The Oracle DUAL table

dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.

SQL> desc dual

 Name                    Null?    Type

 ----------------------- -------- ----------------

 DUMMY                            VARCHAR2(1)

SQL> select * from dual;

D

-

X

The owner of dual is SYS but dual can be accessed by every user.

As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements. Therefor, dual is the prefered table to select a pseudo column (such as sysdate

select sysdate from dual

Although it is possible to delete the one record, or insert additional records, one really should not do that!.

SOAP UI and Groovy Script

SoapUI is an open source web service testing application for service-oriented architectures (SOA). Its functionality covers web serviceinspection, invoking, development, simulation and mocking, functional testing, load and compliance testing.

Groovy is an object-oriented programming language for the Java platform. It is a dynamic language with features similar to those of Python, Ruby,Perl, and Smalltalk.It can be used as a scripting language for the Java Platform, is dynamically compiled to Java Virtual Machine (JVM) bytecode, and interoperates with other Java code and libraries. 

RightNow WSDL Format

                            Connect Web Services should be used to extend the functionality offered by RightNow CX and the ability to access data stored in the RightNow CX platform.

WSDL Creation:

There are two WSDL files that provide access to Connect Web Services:

• Standard WSDL – This WSDL should be used by application developers building integrations specific to their Oracle RightNow CX instance. The typed WSDL is a strongly typed representation of the Connect Common Object Model. The strongly typed WSDL can be accessed from the following URL:

http://<host_name>/cgi-bin/<interface>.cfg/services/soap?wsdl=typed

• Partner WSDL – This WSDL should be used by application developers building integrations that function across multiple instances of Oracle RightNow CX. It provides the ability to work with Generic Objects that can be used across multiple instances of Oracle RightNow CX. The generic WSDL can be accessed from the following URL:

http://<host_name>/cgi-bin/<interface>.cfg/services/soap?wsdl=generic

Difference between Bio Data, Resume and CV

Oracle: Foreign Key(Error) : ORA-02298: cannot validate (tablespace.FK_table1) - parent keys not found

                     Adding a foreign key to an existing table, some times it show the error like "ORA-02298: cannot validate (tablespace.FK_table1) - parent keys not found"

Way to solve:

Sample Query to know about the parent and child tables.,

select xxxxx from child_table a where not exists (select 'x' from parent_table where primary_key = a.key_values);

Description.,


This will show you the row (or rows) that exists in the child table that
don't exist in the parent table. Once you have either (added rows
to the parent table to match the child rows) or (removed the child
rows that don't have parents), then you can enable your RI constraints