среда, 14 сентября 2011 г.

Alternative quoting mechanism

    Oracle Database 10g introduced an alternative quoting syntax for both SQL and PL/SQL - the so-called alternative quoting mechanism (sometimes know as the user-defined quoting mechanism, or the q-quote syntax). It aims to increase usability when the value itself contains single quote characters. Here is an example in PL/SQL:

v1 varchar2(80) := q'{You can't do that}';
v2 varchar2(80) := q'[You can't do that]';

-- Sample of where
...where Last_Name = q'{O'Brien}'

    The opening q' and the closing ' are non-negotiable; The user chooses the inner parentheses, in this example { and }.

Also characters can be any of the following as long as they do not already appear in the string:
  • !
  • [ ]
  • { }
  • ( )
  • < >

GRANT ANY OBJECT PRIVILEGE

    До Oracle 9 Release 9.2 надо было обращаться от имени учетной записи, чтобы задать привилегии и выполнить операторы DDL для объектов, содержащихся в схеме данных. Начиная с Oracle 9 Release 9.2, привилегия GRANT ANY OBJECT PRIVILEGE (GAOP) позволяет ее обладателю создавать объекты и контролировать привилегии в схемах, к которым он не подключен. Поэтому никому, даже администраторам, не надо будет подключаться к схеме для данных.

A user with the GRANT ANY OBJECT system privilege can grant and revoke any object privilege as if he were the actual object owner. When you connect as sysdba(user SYS), you are automatically granted this role with ADMIN OPTION.

Privileges in an Oracle Database

System Privileges

Here are some common system priveleges:
  • ADVISOR
  • ALTER DATABSE
  • ALTER SYSTEM
  • AUDIT SYSTEM
  • CREATE DATABASE LINK
  • CREATE TABLE
  • CREATE ANY INDEX
  • CREATE SESSION
  • CREATE TABLESPACE
  • CREATE USER
  • DROP USER
  • INSERT ANY TABLE

Granting System Privileges

You can grant all system privileges to a user (except SELECT ANY DICTIONARY privilege), by specifying:
 
GRANT ALL PRIVILEGES TO scott;
ALL PRIVILEGES itself isn't a system privilege - it's a convenient way to grant all privileges in one step. You can revoke all system privileges similary, by using statement:
REVOKE ALL PRIVILEGES FROM scott;
The GRANT ANY OBJECT privilege is a special system privilege that lets the grantee grant(and revoke) object privileges for objects in any schema. All users with SYSDBA privilege automatically have the GRANT ANY OBJECT privilege.

System Role Privileges

  •  SELECT_CATALOG_ROLE: This role grants SELECT privileges on the data dictionary views.
  • EXECUTE_CATALOG_ROLE: This role grants EXECUTE privileges on the data dictionary views.
  • DELETE_CATALOG_ROLE: This role enables users to delete records from the audit table, called SYS.AUD$.
You can also use the SELECT ANY DICTIONARY system privilege to grant a user (usually developers) the privilege to select data from any object in the SYS schema.

The SYSDBA and SYSOPER System Privileges

You can't use with ADMIN OPTION when granting these roles; only a user connected as SYSDBA can grant (or revoke) these privileges to other users; and you can't grant this system privilege to role.
The SYSDBA system privilege includes the RESTRICTED SESSION privilege and has all system privileges with ADMIN OPTION, including the SYSOPER system privilege. The SYSDBA privilege lets you do the following:
  • Perform STARTUP and SHUTDOWN operations
  • Use the ALTER DATABASE command to open, mount, back up, or change a character set
  • Use the CREATE DATABASE command
  • Perform ARCHIVELOG and RECOVERY operations
  • Create an SPFILE
The SYSOPER privilege similarly includes the RESTRICTED SESSION privilege, and it lets you do the following:
  • Perform STARTUP and SHUTDOWN operations
  • Use the ALTER DATABASE command to open, mount, or back up
  • Perform ARCHIVELOG and RECOVERY operations
  • Create an SPFILE

  •