·  SQL – Privileges

 

- Info:

  - Privileges are given to users and roles to define which actions are allowed to them.

  - GRANT statement is used to give priveleges.

  - REVOKE statement is used to cancel those priveleges.

  - Additional info can be foound at http://www.adp-gmbh.ch/ora/misc/users_roles_privs.html.

 

- Active Priviliges:

  - You can use following dictionary views to see currently active priviliges:

    SELECT * FROM USER_ROLE_PRIVS     ORDER BY USERNAME, GRANTED_ROLE -- Roles given to each user.

    

    SELECT * FROM ROLE_SYS_PRIVS      ORDER BY ROLE,     PRIVILEGE -- System privileges for all roles.

    SELECT * FROM USER_SYS_PRIVS      ORDER BY USERNAME, PRIVILEGE -- System privileges for all user.

    SELECT * FROM SESSION_PRIVS       ORDER BY PRIVILEGE           -- System privileges for current user

    

    SELECT * FROM ROLE_TAB_PRIVS      ORDER BY ROLE,     PRIVILEGE -- Table privileges  for all roles.

    SELECT * FROM USER_TAB_PRIVS_RECD                              -- Table privileges  for current user

    

    SELECT * FROM USER_COL_PRIVS_MADE                              -- Column privileges current user.

 

- System Privileges:

 

  - All system privileges are listed in a table SYSTEM_PRIVILEGE_MAP which can be searched like this:

    SELECT   NAME

    FROM     SYSTEM_PRIVILEGE_MAP

    WHERE    NAME LIKE '%TABLE%'

    ORDER BY NAME

 

  - Here are some of the most popular system privileges:

    GRANT CREATE PROCEDURE,

          CREATE PUBLIC SYNONYM,

          CREATE ROLE,

          CREATE SESSION,     

          CREATE SYNONYM,

          CREATE SEQUENCE,

          CREATE TABLE,

          CREATE VIEW,

          DROP   PUBLIC SYNONYM

    TO    USER2, ROLE2, PUBLIC;

    WITH  ADMIN OPTION         -- Users and roles can grant these options to others.    

 

- Object Privileges – Table:

 

  - Here are some of the most popular privileges that can be given to specific table:

    GRANT  UPDATE (NAME, AGE),  -- User can UPDATE only listed columns.      

           INSERT (NAME, AGE),  -- When INSERT-ing rows user can only define values of listed columns.

           SELECT,              -- User can SELECT any record.

           DELETE,              -- User can DELETE any record.                

           ALTER,                -- User ca change table structure.

           ALL                  -- ALL table operations are granted to the user.

    ON     PEOPLE               -- Single table to which these privileges were given.

    TO     USER2, ROLE2, PUBLIC -- List of users and roles to which these grants apply.

    WITH   GRANT OPTION         -- Users and roles can grant these options to others.    

    

  - Here are some of the most popular privileges that can be taken fom specific table:

    REVOKE UPDATE,              -- User can't UPDATE any columns.

           INSERT,              -- User can't INSERT rows.

           SELECT,              -- User can't SELECT rows.

           DELETE,              -- User can't DELETE rows.

           ALTER ,              -- User can't change table structure.

           ALL                  -- ALL table operations are revoked from the user.   

    ON     PEOPLE               -- Single table from which these privileges were taken.

    FROM   USER2, ROLE2, PUBLIC -- Users and roles from which these privileges were taken.

    

- Object Privileges – View:

 

  - Here are some of the most popular privileges that can be given to specific view:

    GRANT  UPDATE (NAME, AGE),  -- User can UPDATE only listed columns.      

           INSERT (NAME, AGE),  -- When INSERT-ing rows user can only define values of listed columns.

           SELECT,              -- User can SELECT any record.

           DELETE,              -- User can DELETE any record.                

           ALTER,                -- User ca change table structure.

           ALL                  -- ALL table operations are granted to the user.

    ON     VIEW_PEOPLE          -- Single table to which these privileges were given.

    TO     USER2, ROLE2, PUBLIC -- List of users and roles to which these grants apply.

    WITH   GRANT OPTION         -- Users and roles can grant these options to others.    

 

  - Here are some of the most popular privileges that can be taken fom specific view:    

    REVOKE UPDATE,              -- User can't UPDATE any columns.

           INSERT,              -- User can't INSERT rows.

           SELECT,              -- User can't SELECT rows.

           DELETE,              -- User can't DELETE rows.

           ALTER ,              -- User can't change table structure.

           ALL                  -- ALL table operations are revoked from the user.   

    ON     VIEW_PEOPLE          -- Single table from which these privileges were taken.

    FROM   USER2, ROLE2, PUBLIC -- Users and roles from which these privileges were taken.