Tuesday, February 27, 2007

Introduction to Oracle9i: SQL (4)

Controlling user access

  • The default parameter set with the NLS_DATA_FORMAT is the DD-MON-YY format. This can be changed with the Alter Session command.
  • Nchr(x) returns the character that has the value equivalent to x in the database national character set.
  • Profiles can be used to restrict CPU time used, Total time connected to the database, Maximum time a session can be inactive.
  • Create a new database is a privilege of the SYSDBA privilege.
  • Start up and shut down a database, Alter a database, Create an SP file is SYSOPER privilege.
  • USER_TABLES : To view all the tables in your schema
  • USER_OBJECTS : To view all the objects owned by the user database.
  • ALL_OBJECTS : if you want to see all the objects you can access in your database, whether it is in your schema or not.
  • An object privilege is the right to perform a particular action on a Sequence. The only privileges that apply to a sequence are SELECT and ALTER.
  • User_Constraints lists all of the constraints on the table. User_Cons_Columns displays the columns associated with a constraint. Both have table_name which lists the table name associated with the constraint.
  • For system privileges even if the grantor looses the privileges the grantee will till retain it.
  • System privilege can only be granted with ADMIN option
  • Grant Any Privilege allows the grantee to grant any system privilege.
  • Oracle supports 3 types of user authentication. The default user authentication is database authentication. In external authentication the password is verified by the operating system or network. In global authentication Oracle checks if the user is a legitimate user. In database authentication Oracle checks for the correct password. In global authentication the password is validated by the Oracle Security Service.
  • ALL_COL_PRIVS_MADE displays all of the grants in which the user is the grantor or the owner whether through a role, public, or direct grant, while the latter.
  • ALL_COL_PRIVS displays all of the grants on columns that have been granted to the user or to public.
  • DBA_CONS_COLUMNS displays all the objects in the database.

Read more!

No comments: