Permission to see one table, view or other object

PROBLEM:
You want to allow a user to read only one database table, view or other object

SOLUTION:

Use the following script:

-- replace 'MY_DOMAIN\MyLogin' with your login name
-- you can skip this create login if you have already a domain user
-- alternatively, you can create login using SQL Server authentication
USE [master]
GO
CREATE LOGIN [MY_DOMAIN\MyLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

-- replace 'MyUser' with your user name
-- replace 'MyDatabase' with your database name that contains the table
-- replace 'MyTable' with your table name
USE [MyDatabase]

--create a role to wrap up the permission
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyObjectAccess' AND type = 'R')
BEGIN
       EXEC SP_DROPROLEMEMBER 'MyObjectAccess', 'MyUser'
       DROP ROLE [MyObjectAccess]
END
GO

CREATE ROLE MyObjectAccess
GRANT SELECT ON [MyDatabase].[DBO].[MyTable] TO MyObjectAccess;
GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyUser')
DROP USER [MyUser]
GO
CREATE USER [MyUser] FOR LOGIN [MyLogin]
GO

EXEC sp_addrolemember N'MyObjectAccess', N'MyUser'
GO

-- Now, login to SQL Server as MyLogin
-- You should see all databases that exist in the instance, but you will not see their objects.
-- You can read MyDatabase.MyTableName

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Transaction log cannot be truncated

PROBLEM:

You cannot truncate SQL Server transaction log

SOLUTION:

If you are wondering why a transaction log cannot be truncated, exeuting the following query can assist

select name, log_reuse_wait_desc from sys.databases

The following briefly describes the values of the log_reuse_wait_desc column:

• NOTHING - Currently there are one or more reusable log files. This is the normal state that means you can do a log truncation
• Wait 15 min, try again. If you still cannot truncate, restart the server
• CHECKPOINT - No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). This is a routine reason for delaying log truncation
• Do a manual checkpoint
• LOG_BACKUP - A log backup is required to move the head of the log forward (full or bulk-logged recovery models only)
• You need to make two backups to actually free the space
• ACTIVE_BACKUP_OR_RESTORE - A data backup or a restore is in progress (all recovery models). A data backup works like an active transaction, and, when running, the backup prevents truncation
• Wait until it ends
• ACTIVE_TRANSACTION - A transaction is active (all recovery models). A long-running transaction might exist at the start of the log backup
• Do a log backup
• DATABASE_MIRRORING - Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only)
• Configure the mirroring
• REPLICATION - During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only)
• Wait until it ends
• DATABASE_SNAPSHOT_CREATION - A database snapshot is being created (all recovery models). This is a routine, and typically brief
• Wait until it ends
• LOG_SCAN - A log scan is occurring (all recovery models). This is a routine, and typically brief
• Wait until it ends
• AVAILABILITY_REPLICA - To identify which secondary database is delaying log truncation, see the truncation_lsn column of the sys.dm_hadr_database_replica_states dynamic management view
• OTHER_TRANSIENT - This value is currently not used

After resolving the reason that is shown, if you are using full recovery model then perform a log backup to truncate the log file, and then use DBSS SHRINKFILE to reduce the filesize of the log file.

USE [db_name]
GO
DBCC SHRINKFILE(N'db_name_log', 1)
BACKUP LOG [db_name]
DBCC SHRINKFILE(N'db_name_log', 1)
GO

If the log file does not reduce in size when using DBCC SHRINKFILE as part of the steps above, the active part of the log file must have been at the end of the log file at that point in time. You can correct that situation by generating enough dummy transactions to cause SQL Server to switch back to the beginning of the log file. You should then attempt the truncation again.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Find why your SQL Server query is stuck

PROBLEM:
What should I do when SQL Server query is stuck?

SOLUTION:
First, open SQL Server Activity Monitor, you should wait some time before data is loaded

The Activity Monitor is available in SQL Server 2008 Management Studio is a great tool which can be used to get a quick overview of SQL Server 2008 system performance. The Activity Monitor tool in the previous version of SQL Server used to display information related to processes, lock by objects and locks by process. There are many enhancements in Activity Monitor in SQL Server 2008 like a graphical display of processor time, waiting tasks, database I/O's, batch requests, processes, resource waits, data file I/O's and also information about the most expensive queries.
To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission

One of the first lines of defense in determining why a query is stuck is to use sp_who2. It will shows all the sessions that are currently established in the database. These are denoted as SPID’s, or server process Id’s.

sp_who2

The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs.

There are four main things to look for when when diagnosing slowdowns:

• Blocking
• High CPU usage
• High IO usage
• Multiple entries for the same SPID (representing parallelism)

When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking another process. In turn, the process that is being blocked, blocks others. This occurs down a chain. It can be a messy situation. In order to rectify, you may have to kill the lead blocker. If it happens often, you will want to research why this particular process is blocking. So, before you kill any process, find out what statement it is running first. To do this, execute DBCC INPUTBUFFER

DBCC INPUTBUFFER(500) -- 500 represent the SPID you see in the result of sp_who2

Once you find the blocker, you may need to kill it. To do so use the kill command (replace the number below with the blocking SPID)
IMPORTANT: before the kill, keep a copy of the SQL statement being run for later investigation

KILL 500

sp_who2 does provide limited information regarding slowdowns.

For more information use the following script that shows the SQL statement being run, only sessions that have a current executing request, reads and writes for the current command, along with the number of reads and writes for the entire SPID and the protocol being used (TCP, NamedPipes, or Shared Memory)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SPID = er.session_id ,
       BlkBy = er.blocking_session_id ,
       ElapsedMS = er.total_elapsed_time ,
       CPU = er.cpu_time ,
       IOReads = er.logical_reads + er.reads ,
       IOWrites = er.writes ,
       Executions = ec.execution_count ,
       CommandType = er.command ,
       ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,
       SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2 ) ,
       Status = ses.status ,
       [Login] = ses.login_name ,
       HOST = ses.host_name ,
       DBName = DB_Name(er.database_id) ,
       LastWaitType = er.last_wait_type ,
       StartTime = er.start_time ,
       Protocol = con.net_transport ,
       transaction_isolation = CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END ,
        ConnectionWrites = con.num_writes ,
        ConnectionReads = con.num_reads ,
        ClientAddress = con.client_net_address ,
        Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt OUTER APPLY
( SELECT execution_count = MAX(cp.usecounts)
 FROM sys.dm_exec_cached_plans cp
 WHERE cp.plan_handle = er.plan_handle) ec
ORDER BY er.blocking_session_id DESC,
         er.logical_reads + er.reads DESC,
         er.session_id

The following script will show the blocking processes (lead blocker)

SELECT spid ,
       sp.status ,
       loginame = SUBSTRING(loginame, 1, 12) ,
       hostname = SUBSTRING(hostname, 1, 12) ,
       blk = CONVERT(char(3), blocked) ,
       open_tran ,
       dbname = SUBSTRING(DB_NAME(sp.dbid),1,10) ,
       cmd ,
       waittype ,
       waittime ,
       last_batch ,
       SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2 )
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN
    (SELECT blocked
     FROM master.dbo.sysprocesses)
  AND blocked = 0

The following script will find most expensive queries (remark the needed ORDER BY)

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1),
              qs.execution_count,
              qs.total_logical_reads,
              qs.last_logical_reads,
              qs.total_logical_writes,
              qs.last_logical_writes,
              qs.total_worker_time,
              qs.last_worker_time,
              qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
              qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
              qs.last_execution_time,
              qp.query_plan
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

The following script will find longest running query

SELECT DISTINCT TOP 10 t.TEXT QueryName,
   s.execution_count AS ExecutionCount,
   s.max_elapsed_time AS MaxElapsedTime,
   ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
   s.creation_time AS LogCreatedOn,
   ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_elapsed_time DESC

LETS CHECK, do the following steps and run the above scripts when needed

-- open new query window (SESSION A) and run the following 3 lines
USE [AdventureWorks]
BEGIN TRANSACTION
UPDATE Sales.SalesOrderDetail
SET [UnitPrice] = 1 -- this update takes ~30sec

-- while the above update in progress, open a new query window (SESSION B) and run sp_who2
-- open another query window (SESSION C) and run the following line, it will be blocked by SESSION A because the transaction there is not committed yet
UPDATE Sales.SalesOrderDetail
SET [UnitPrice] = 2

-- while the above update in progress, go to SESSION B query window and run again sp_who2, you will see the blocking
-- return to SESSION A query window and rollback the transaction. This will free the blocking in SESSION C (after the update finishes, takes ~30sec)
ROLLBACK

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Shrink SQL Server log file

PROBLEM:
My SqlServer database log files are too big

SOLUTION:
If this is a development or testing environment and you don't need the log backup, you can change the recovery model to simple and then use DBCC SHRINKFILE command.

USE [db_name]
GO
ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(N'db_name_log', 1)

The following code shrink the log for databases in FULL recovery mode.
In production environment, this breaks the chain of the logs and in future you will not be able to restore point in time so you must take log backup before

USE [db_name]
GO
DBCC SHRINKFILE(N'db_name_log', 1)
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
DBCC SHRINKFILE(N'db_name_log', 1)
GO

Problems when trying to shrink log

1. Long running transaction - Can prevent transaction log truncation.  These types of transactions can range from transactions being blocked from completing to open transactions waiting for user input. The longer the transaction remains open, the larger the transaction log can grow.  To see the longest running transaction on your SQL Server instance, run the following statement

DBCC OPENTRAN

If there are open transactions, DBCC OPENTRAN will provide a session_id (SPID) of the connection that has the transaction open.  You can pass this session_id to sp_who2 to determine which user has the connection open

sp_who2 SPID

You can determine the SQL statement being executed inside the transactions using the DBCC INPUTBUFFER() statement to return the first part of the SQL statement

DBCC INPUTBUFFER(SPID) --from DBCC OPENTRAN

2.  Backup or restore operation - In SQL Server 2005 and later, you can create a transaction log backup while a full or differential backup is occurring, but the log backup will not truncate the log due to the fact that the entire transaction log needs to remain available to the backup operation.  If a database backup is keeping your log from being truncated you might consider cancelling the backup to relieve the immediate problem.

3. Replication - The inactive portion of the transaction log is not truncated until transactions have been replicated to the distributor.  This may be due to the fact that the distributor is overloaded and having problems accepting these transactions or maybe because the Log Reader agent should be ran more often.  IF DBCC OPENTRAN indicates that your oldest active transaction is a replicated one and it has been open for a significant amount of time, this may be your problem.

4. Mirroring - Similar to transactional replication in that it requires that the transactions remain in the log until the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space will grow. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database and restart mirroring.

5. Disk Space - It is possible that you’re just running out of disk space and it is causing your transaction log to error.  You might be able to free disk space on the disk drive that contains the transaction log file for the database by deleting or moving other files. The freed disk space will allow for the log file to enlarge.  If you cannot free enough disk space on the drive that currently contains the log file then you may need to move the file to a drive with enough space to handle the log.  If your log file is not set to grow automatically, you’ll want to consider changing that or adding additional space to the file.  Another option is to create a new log file for the database on a different disk that has enough space by using the ALTER DATABASE YourDatabaseName ADD LOG FILE syntax.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Generate script that create and drop foreign keys references specific table

PROBLEM:

You want to generate script that create and drop foreign keys that references to specific table

SOLUTION:

To generate script that create foreign keys references specific table (change the name 'MY_REFERENCED_TABLE' to your table name)

WITH TABLESWITHFOREIGNKEY AS
  ( SELECT DISTINCT T.NAME AS TABLEWITHFOREIGNKEYNAME,
                    O.NAME AS FOREIGNKEYNAME,
--sub query with a comma separated resultset that returns the foreign key columns
   SUBSTRING (
     ( SELECT ',' + A.NAME
       FROM SYS.FOREIGN_KEY_COLUMNS
       INNER JOIN SYS.COLUMNS AS A ON PARENT_OBJECT_ID = A.OBJECT_ID
       AND PARENT_COLUMN_ID = A.COLUMN_ID
       WHERE PARENT_OBJECT_ID = T.OBJECT_ID
       AND CONSTRAINT_OBJECT_ID = O.OBJECT_ID
       ORDER BY CONSTRAINT_COLUMN_ID
       FOR XML PATH('') ), 2, 1000000) AS FOREIGNKEYCOLUMNS,
--sub query with a comma separated resultset that returns the referenced columns
   SUBSTRING (
      ( SELECT ',' + A.NAME
        FROM SYS.FOREIGN_KEY_COLUMNS
        INNER JOIN SYS.COLUMNS AS A ON REFERENCED_OBJECT_ID = A.OBJECT_ID
        AND REFERENCED_COLUMN_ID = A.COLUMN_ID
        WHERE PARENT_OBJECT_ID = T.OBJECT_ID
        AND CONSTRAINT_OBJECT_ID = O.OBJECT_ID
        ORDER BY CONSTRAINT_COLUMN_ID
        FOR XML PATH('') ), 2, 1000000) AS REFERENCESCOLUMNS
   FROM SYS.FOREIGN_KEY_COLUMNS AS FK
   INNER JOIN SYS.TABLES AS T ON FK.PARENT_OBJECT_ID = T.OBJECT_ID
   INNER JOIN SYS.OBJECTS AS O ON FK.CONSTRAINT_OBJECT_ID = O.OBJECT_ID
   INNER JOIN SYS.COLUMNS AS C ON FK.PARENT_OBJECT_ID = C.OBJECT_ID
   AND FK.PARENT_COLUMN_ID = C.COLUMN_ID
   WHERE FK.REFERENCED_OBJECT_ID =
       (SELECT OBJECT_ID
        FROM SYS.TABLES
        WHERE NAME = 'MY_REFERENCED_TABLE'))
SELECT ' IF NOT EXISTS
  (SELECT *
   FROM sys.foreign_keys
   WHERE object_id = OBJECT_ID(N''[' + FOREIGNKEYNAME + ']'') AND parent_object_id = OBJECT_ID(N''[' + TABLEWITHFOREIGNKEYNAME + ']''))
ALTER TABLE [' + TABLEWITHFOREIGNKEYNAME + '] WITH CHECK ADD CONSTRAINT [' + FOREIGNKEYNAME + '] FOREIGN KEY([' + FOREIGNKEYCOLUMNS + ']) REFERENCES [MY_REFERENCED_TABLE] ([' + REFERENCESCOLUMNS + ']) '
FROM TABLESWITHFOREIGNKEY
ORDER BY TABLEWITHFOREIGNKEYNAME

To generate script that drop foreign keys references specific table (change the name 'MY_REFERENCED_TABLE' to your table name)

WITH TABLESWITHFOREIGNKEY AS
  ( SELECT T.NAME AS TABLEWITHFOREIGNKEYNAME,
           O.NAME AS FOREIGNKEYNAME
   FROM SYS.FOREIGN_KEY_COLUMNS AS FK
   INNER JOIN SYS.TABLES AS T ON FK.PARENT_OBJECT_ID = T.OBJECT_ID
   INNER JOIN SYS.OBJECTS AS O ON FK.CONSTRAINT_OBJECT_ID = O.OBJECT_ID
   WHERE FK.REFERENCED_OBJECT_ID =
       (SELECT OBJECT_ID
        FROM SYS.TABLES
        WHERE NAME = 'MY_REFERENCED_TABLE'))
SELECT ' IF EXISTS
  (SELECT *
   FROM sys.foreign_keys
   WHERE object_id = OBJECT_ID(N''[' + FOREIGNKEYNAME + ']'') AND parent_object_id = OBJECT_ID(N''[' + TABLEWITHFOREIGNKEYNAME + ']''))
ALTER TABLE [' + TABLEWITHFOREIGNKEYNAME + '] DROP CONSTRAINT [' + FOREIGNKEYNAME + '] '
FROM TABLESWITHFOREIGNKEY
ORDER BY TABLEWITHFOREIGNKEYNAME

Check the scripts: create 2 tables with FK and run the scripts above. In this case, change MY_REFERENCED_TABLE to AAA

CONSTRAINT PK_1 PRIMARY KEY CLUSTERED (ID_AAA1,ID_AAA2))

CREATE TABLE BBB ( ID_BBB1 INT, ID_BBB2 INT, NAME NVARCHAR(50), CONSTRAINT PK_2 PRIMARY KEY CLUSTERED (ID_BBB1,ID_BBB2))

ALTER TABLE [dbo].[BBB]  WITH CHECK ADD  CONSTRAINT [FK_1] FOREIGN KEY([ID_BBB1],[ID_BBB2]) REFERENCES [dbo].[AAA] ([ID_AAA1],[ID_AAA2])

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

ALTER TABLE CHECK CONSTRAINT after ALTER TABLE WITH CHECK ADD CONSTRAINT

PROBLEM:

Do you need to add ALTER TABLE CHECK CONSTRAINT statement after ALTER TABLE WITH CHECK ADD CONSTRAINT statement?

SOLUTION:

Let's check:

CREATE TABLE AAA
(
       ID_AAA1 INT,
       ID_AAA2 INT,
       NAME NVARCHAR(50),
       CONSTRAINT PK_1 PRIMARY KEY CLUSTERED (ID_AAA1,ID_AAA2)
)

CREATE TABLE BBB
(
       ID_BBB1 INT,
       ID_BBB2 INT,
       NAME NVARCHAR(50),
       CONSTRAINT PK_2 PRIMARY KEY CLUSTERED (ID_BBB1,ID_BBB2)
)

ALTER TABLE [dbo].[BBB]  WITH CHECK ADD  CONSTRAINT [FK_1] FOREIGN KEY([ID_BBB1],[ID_BBB2])
REFERENCES [dbo].[AAA] ([ID_AAA1],[ID_AAA2])
GO

INSERT AAA(ID_AAA1, ID_AAA2) VALUES (1,1);
INSERT AAA(ID_AAA1, ID_AAA2) VALUES (2,2);

-- The following INSERT statement is OK
INSERT BBB(ID_BBB1, ID_BBB2) VALUES (1,1);
-- The following INSERT statement conflicted with the FOREIGN KEY constraint "FK_1"
INSERT BBB(ID_BBB1, ID_BBB2) VALUES (3,3);

ALTER TABLE {CHECK | NOCHECK} CONSTRAINT 

Specifies that constraint_name is enabled or disabled. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions.

ALTER TABLE {WITH CHECK | WITH NOCHECK} ADD CONSTRAINT

If you want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH CHECK.

It is not recommend using WITH NOCHECK, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

By default, when you add a constraint, its enable, so you don't need to add ALTER TABLE CHECK CONSTRAINT statement after it.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

GO statement inside a transaction

PROBLEM:
Can I use GO statement inside a transaction?

SOLUTION:
GO is just a command to the Client SQL program (Query Analyzer, SSMS, etc.) to terminate the current batch and execute it. GO does not terminate the current session or process and transactions are session-scoped entities. So, transactions are not commited or rollbacked when a GO is encountered

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

TFS Build failed - Access to the path is denied

PROBLEM:
TFS Build failed when trying to build project because of the following error:

Copying file
C:\Builds\Agent\MyPrj\Binaries\Abc.Client.Silverlight.xap failed.
Access to the path 'C:\Builds\Agent\MyPrj\Sources\Dev\Abc.Client.Silverlight.Web\ClientBin\Abc.Client.Silverlight.xap'
is denied

SOLUTION:
Open your TFS source control in your Silverlight project ClientBin directory and check you didn't checked-in your xap files (in this case: Abc.Client.Silverlight.xap). If checked-in, delete it from the source control.
This error can occur in other projects types (not only Silverlight) when you checked-in files in a build deployment folder. Those files are copied into the build server as Read Only and cannot be overwritten later when the build server trying to deploy them

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Grant SQL Server user the needed permissions to backup and restore his database

PROBLEM:
You want to grant SQL Server user the needed permissions to backup and restore his database. Not other databases

SOLUTION:
This script gives 'UserName' the ability to backup and restore only 'TheDatabase' or other databases he created.
When restoring, the user should give direct path to the BAK file. SSMS will not open the default location and will show the following error:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists..

USE [TheDatabase]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
EXEC sp_addrolemember N'db_backupoperator', N'UserName'
GO
USE [master]
EXEC master..sp_addsrvrolemember @loginame = N'UserName', @rolename = N'dbcreator'
GO

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

What is Oracle ASM - Automatic Storage Management?

PROBLEM:
What is Oracle Automatic Storage Management (ASM)?

1. ASM Advantages
2. ASM Basic Concepts
• Disk Group (DG)
• Allocation Unit (AU)
• ASM Instance
• ASM Templates 
• ASM Striping
• Disk Group Dynamic Rebalancing
• ASM Mirroring & Failure Groups
3. ASM Instance Management
4. ASM initialization parameters
5. ASM Shutdown / Startup
6. ASM Instance Architecture
7. Common ASM V$ Views
8. ASM Fast Mirror Resync
9. Bad block repair (11g)
10. ASM Intallation Guidelines
11. DEMO!

SOLUTION:

-------------------------------------
-- Oracle Automatic Storage Management (ASM)
-------------------------------------
-- * An Oracle file system, built specifically for database related files.
-- * Can be used for single or multiple instance architecture (RAC).
--     (Eliminates the need for third party Clustered File System)
-- * Enables storage solutions such as Striping & Mirroring (for performance & Survivability)
-- * Stripes and Mirrors at file level (as opposed to volume level)
--     - Files are striped more efficiently.
--     - Mirroring can be enabled for specific files only, at different levels.
-- * Each DB can have both ASM and regular files. (Can also migrate some files to ASM)
-- * ASM can store all Oracle file types. (Not just Data Files).

-------------------------------------
-- ASM Advantages
-------------------------------------
-- 1. it is aware of the nature of Oracle database files. This means it can make
--    more intelligent decisions about how to manage the files than a third-party product.
-- 2. ASM can handle files individually, whereas all other LVMs work at the
--    volume level: they are not aware of the files within the volume. So with a third-party
--    LVM, you have to specify RAID attributes per volume.
--    ASM can specify the attributes per file, so you can for instance have three-way
--    mirroring for your SYSTEM tablespace datafiles but no mirroring at all for your
--    temporary tablespaces’ tempfiles, all within the same logical volume.

-------------------------------------
-- ASM Basic Concepts
-------------------------------------

-----------------------
-- * Disk Group (DG)
-----------------------
--   * A group of disks which are managed as a single logical unit:
--     * If I need to add more space to a DG, I'll add another disk to it.
--     * Although we may be constantly adding ASM Files and Disks to a growing system, the number of Disk Groups to manage remains constant.
--   * ASM files are assigned to a Disk Group, instead of to a specific disk.
--   * All data will be striped and mirrored between the disks of this DG.
--   * DG >-< DB:
--     * Each DG can contain files from multiple DBs.
--     * Each DB can store his files on different DGs.
--   * Each DG is self describing, and contains all information about his Disks and Files.

CREATE DISKGROUP DG3
    '/devices/diskb3' NAME diskb3,
    '/devices/diskb4' NAME diskb4

-----------------------
-- * Allocation Unit (AU)
-----------------------
--   *Each datafile is striped to Allocation Units (AU).
--   * AUs are spread evenly across the disks of the Disk Group.
--   * AUs are rebalanced automatically across the disks whenever disks are added or removed from the DG.
--   * AU size:
--     * 10g: 1MB
--     * 11g: Determined at DG creation. (1MB, 2, 4, 8, 16, 32, 64MB)

CREATE DISKGROUP DG3
    '/devices/diskb3' NAME diskb3,
    '/devices/diskb4' NAME diskb4
ATTRIBUTE 'au_size'='4M';

-----------------------
-- * ASM Instance
-----------------------
--   * ASM metadata is managed by a single ASM instance, which can serve many DBs and Instances.
--   * The instance mounts his DGs instead of a specific database.
--   * Created and configured via the DBCA. ("Configure ASM")
--   * The instances, however, work directly with the ASM files.

----------------------------------
-- Creating a Disk Group
-- (Performed in the ASM instance)
----------------------------------

conn /@ASM AS SYSASM

-- See current Disk Groups:
SELECT GROUP_NUMBER, NAME, TOTAL_MB
FROM v$asm_diskgroup;

col path for a50
-- See available disks:
SELECT GROUP_NUMBER "DG#", PATH
FROM v$asm_disk
ORDER BY path;

-- Join:
SELECT GROUP_NUMBER, dg.name, SUM(d.TOTAL_MB) AS "Size", COUNT(*) AS "Disks"
FROM v$asm_diskgroup dg JOIN v$asm_disk d
USING (GROUP_NUMBER)
GROUP BY GROUP_NUMBER, dg.name ;


-- Create a new disk group DG2:
CREATE DISKGROUP DG2
DISK
'\\.\ORCLDISK_LABEL_G' NAME DG2_0,
'\\.\ORCLDISK_LABEL_H' NAME DG2_1 ;


-- See available disks:
SELECT GROUP_NUMBER "DG#", PATH
FROM v$asm_disk
ORDER BY path;

-- DROP DISKGROUP DG2;

-------------------------------------
-- Create a tablespace with ASM file
-- (Performed in the DB instance)
-------------------------------------
conn sys/oracle@orcl as sysdba

CREATE TABLESPACE ASM_TBS
  DATAFILE '+DG2/asm_tbs01.dbf' SIZE 100M;

SELECT name FROM v$datafile;

-- DROP TABLESPACE asm_tbs INCLUDING CONTENTS AND DATAFILES;


-- * Stamp candidate disks / change name and creating disk groups can be done by using ASMCA
--   $oracle_grid_home\BIN\asmca.bat
-- * for stamping and name changing :
--   SHUTDOWN ASM => stamp/change names => STARTUP ASM

-------------------------------------
-- ASM Templates
-------------------------------------

-- * A template is a collection of file attributes that are applied to the ASM files.
--   It is associated with a Disk Group.
-- * Whenever a file is created the file attributes specific to the template are applied to 
--   the files.
-- * For eg., if we want to create a users tablespace that contains data file we use the
--   DATAFILE template.
-- * Default Template: When you create a disk group, Oracle creates system default template
--   for the disk group.

Select * From V$ASM_TEMPLATE

-------------------------------------
-- ASM Striping
-------------------------------------
-- * ASM stripes the file to AUs, and devides them between the DG disks.
-- * Two types of striping:
--      1. Coarse-based striping:
--          - Each stripe size is one AU.
--          - Classic striping. Suitable for most file types.
--          - Used for load balancing.
--      2. Fine-Grained striping
--            - Each stripe size is 128KB. (Wider distribution of data)
--            - Used for decreasing IO Latency (Waiting for IO).
--            - Suitable for small IO operations such as Redo Log Files.

/*
ALTER DISKGROUP DG2
      ATTRIBUTE 'template.tname.stripe' = 'COARSE',
            'template.tname.redundancy' = 'MIRROR';

ALTER DISKGROUP dg2 MODIFY TEMPLATE ONLINELOG
ATTRIBUTES (FINE);

ALTER DISKGROUP dg2 MODIFY TEMPLATE DATAFILE
ATTRIBUTES (COARSE);

Select * From V$ASM_TEMPLATE
WHERE name = ('DATAFILE', 'ONLINELOG')
AND group_number = 2

*/

-------------------------------------
-- Disk Group Dynamic Rebalancing
-------------------------------------
-- * ASM performs automatic rebalancing online whenever we add / remove disks from a DG.
-- * ASM_POWER_LIMIT:
--   - Controls IO overhead during rebalancing.
--   - Values are between 1 and 11. (Minimum overhead (1) .. Minumum time (11))
--   - The higher the limit, the faster rebalancing will complete.
--   - Lower values will take longer, but consume fewer processing and I/O resources.

show parameter ASM_POWER_LIMIT

-------------------------------------
-- ASM Mirroring & Failure Groups
-------------------------------------
-- * ASM can protect our files from media failures (disk / disk-controller crash)
--
-- * Mirroring:
--   - Applied at Extent (AU) level (Not at file or disk level).
--   - When an AU is allocated to a file on a specific disk,
--       a mirrored AU is also allocated on another disk in the SAME Disk Group.
--   - Therefore, if a disk is lost, we can restore its contents
--      from other disks on its Disk Group.

-- * Failure Group:
--   - A failure group is a subset of the disks in a disk group,
--     which could fail at the same time because they share hardware.
--     (For example, disks that share the same disk controller)
--   - By default, each ASM disk belongs to his own Failure Group (FG).
--   - However, we can group a few disks in a DG to a single Failure Group.
--   - When an AU is allocated on a FG disk, a mirrored AU is also allocated
--      on ANOTHER Failure Group in the SAME Disk Group.
--   - Therefore, if a Failure Group is lost, we can restore its contents
--      from other disks on its Disk Group.

-- *   types or mirroring:
--     1. External Redundancy - No mirroring or FGs. (When using an external tool for --        protection).
--     2. Normal Redundancy   - Two-way mirroring.   (Two copies)
--     3. High Redundancy     - Triple-mirroring.    (Three copies)

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-- A protected disk group:
CREATE DISKGROUP DG2
  NORMAL REDUNDANCY 
  DISK
     '//./ORCLDISKDATA3' NAME DG2_0,
     '//./ORCLDISKDATA4' NAME DG2_1,
     '//./ORCLDISKDATA5' NAME DG2_2,
     '//./ORCLDISKDATA6';

col name for a10
col failgroup for a11
col path for a20
col redundancy for a10

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-- A Disk Group with 2 Failure Groups (one for each controller):
CREATE DISKGROUP DG2
  NORMAL REDUNDANCY 
  FAILGROUP controller1 DISK
     '//./ORCLDISKDATA3' NAME DG2_0,
     '//./ORCLDISKDATA4' NAME DG2_1
  FAILGROUP controller2 DISK
     '//./ORCLDISKDATA5' NAME DG2_2,
     '//./ORCLDISKDATA6' NAME DG2_3;


col name for a10
col failgroup for a11
col path for a20
col redundancy for a10

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;

-------------------------------------
-- ASM Instance Management
-------------------------------------

-------------------------------------
-- ASM initialization parameters
-------------------------------------
-- * Important ASM related Parameters:
--    - INSTANCE_TYPE  : should be set to ASM. This is the only parameter that MUST be --      defined.
--    - ASM_DISKGROUPS : List of disk groups to be mounted at startup.
--                        (Or during ALTER DISKGROUP ALL MOUNT)
--    - ASM_POWER_LIMIT: The speed for a rebalance operation (1-11. default is 1).
-- * Additional parameters can be set...

-------------------------------------
-- ASM Shutdown / Startup
-------------------------------------

-------------
-- STARTUP
-------------
-- * The options for the STARTUP command are:
--   - NOMOUNT - Starts the ASM instance without mounting any disk groups.
--   - MOUNT   - Starts the ASM instance and mounts the disk groups specified
--               by the ASM_DISKGROUPS parameter.
--   - FORCE   - Performs a SHUTDOWN ABORT before restarting the ASM instance.
--   - OPEN    - Not a valid option.
--   - RESTRICT- DB instances cannot use the DGs. (Used for maintenance)

-- * If the ASM_DISKGROUPS parameter is empty, no DGs are mounted.
--    (Can be mounted manually using ALTER DISKGROUP...MOUNT)

-------------
-- SHUTDOWN
-------------
-- * ASM instance shutdown is possible only after shutting down all instances connected to it
--     (Otherwise: ORA-15097: cannot SHUTDOWN ASM instance with connected RDBMS instance)

-- * The options for the SHUTDOWN command are:
--   - NORMAL    - Waits for all connections to finish (DB instances & SQL sessions)
--   - TRANSACTIONAL - Waits for any active transactions to complete. (Doesn't wait for sessions)
--   - IMMEDIATE - Same as TRANSACTIONAL.
--   - ABORT     - The ASM instance shuts down instantly (Crash).

-- * Shutdown ABORT will require crash recovery upon next startup:
--   - All connected instances will also shutdown.
--       (ORA-15064: communication failure with ASM instance)
--   - Each DG has log files for recovery of ASM metadata.
--   - Actual data is recovered using the DB Redo Logs.

------------------------------
-- ASM Instance Architecture
------------------------------
-----------------
-- MEMORY
-----------------
-- * SGA:
--    - Shared Pool: Used for metadata information
--    - Large Pool : Used for parallel operations
--    - ASM Cache  : Used for data blocks during rebalance operations
--    - Free Memory: Unallocated memory available
-- * Minimum recommended size is 256 MB. (Depends on the used disk space size)
-- * AMM (Automatic memory management) is enabled by default.

------------------------------
-- Background Processes
------------------------------
-----------------------
-- In the ASM instance
-----------------------
-- * The ASM instance contains some special ASM background processes:
--    - RBAL - Manages the Rebalancing process across the DG.
--    - ARBn - Perform the actual relocation of the AU during rebalancing.
--    - GMON - (Group Monitor) - Disk-level activities (Offline, Drop etc).
--    - MARK - Marks ASM allocation units as stale when needed
--    - Onnn - pool of connections to the ASM instance for exchanging messages

-----------------------
-- In the DB instance
-----------------------
-- * In addition, each regular instance that uses ASM has some special background processes:
--    - ASMB - Connects to the ASM instance at startup, and works with it (Data file management).

--------------------------------------------
-- Common ASM V$ Views
--------------------------------------------
-- V$ASM_FILE - Files of disk groups mounted by the ASM instance.

SELECT file_number, name, ROUND(bytes/1024/1024) SIZE_MB
FROM v$asm_alias JOIN v$asm_file
USING (file_number)
ORDER BY file_number, name;


-- V$ASM_CLIENT - DB instances using the ASM instance.
--   (In the DB - ASM instance data if the database has open ASM files)


-- V$ASM_DISK   - List of discovered Disks. (even if not part of any disk group).
--   (In the DB - Disks of DGs used by the database instance).
col name for a10
col failgroup for a11
col path for a20
col redundancy for a10
SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH
FROM v$asm_disk
ORDER BY path;

-- V$ASM_DISKGROUP - List of discovered disk groups.
--   (In the DB - DGs mounted by the local ASM instance)

-- V$ASM_OPERATION - Files with executing long running operations.

-- V$ASM_TEMPLATE - Templates of DGs mounted by the ASM instance.

-- V$ASM_ALIAS  - Aliases that belong to mounted disk groups.
--              - Files with aliases will have 2 rows. (Original & alias)

SELECT file_number, name, CASE ALIAS_DIRECTORY WHEN 'Y' THEN 'Directory' ELSE 'File' END Type
FROM v$asm_alias
ORDER BY type, file_number;

-------------------------------------
-- SYSASM
-------------------------------------
-- * Meant for ASM management. (Separate from SYSDBA for security)
-- * As ASM has no Data Dictionary, users are authenticated via OS or password-file.

-- * SYSOPER, enables basic administration, but not CREATE/ALTER/DROP DGs.
--     (Startup/Shutdown, Mount/Dismount, Rebalance, Online/Offline disk, v$ASM_*)
-- * SYSDBA enables all SYSASM privs, but will be limited in the future.

-- * Via OEM (Only if connected via SYSASM):
--    ASM => Users => Create/Edit/Delete
-- (Connecting as SYSASM: Preferences => Preferred Credentials => ASM:Set Credentials => Specify...

-- In CMD:
SET ORACLE_SID=+ASM
sqlplus /nolog
conn / as sysasm

SELECT GROUP_NUMBER, NAME, TOTAL_MB
FROM v$asm_diskgroup;

-- Or:
CREATE USER asm_mgr IDENTIFIED BY asm_mgr;
GRANT SYSASM TO asm_mgr;
SELECT * FROM v$pwfile_users;
conn asm_mgr/asm_mgr AS SYSASM

-------------------------------------
-- Interaction Between Database Instances and ASM
-------------------------------------

-- * The Database Instance and ASM Instance work together in a coordinated fashion.
--   A Database instance must interact with ASM to map database files to ASM extents.
-- * A Database instance also receives a constant stream of messages relating to ASM operations
--   (such as disk group rebalancing) that may lock or move ASM extents.
-- * Database I/O is not channeled through the ASM instance. In fact, the database conducts I/O
--   operations directly against ASM files.

-------------------------------------
-- Disk Group Maintenance - SYNTAX...
-------------------------------------
-- * Should be performed from the ASM instance by a user with a SYSASM privilege.

set oracle_sid=+ASM
sqlplus / as sysasm

col name for a10
col path for a30

SELECT GROUP_NUMBER, NAME, STATE, TYPE, TOTAL_MB
FROM V$ASM_DISKGROUP
ORDER BY 1;

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk;

-----------------------
-- CREATE DG
-----------------------
-- * We can optionally name and size each disk.
--    (Otherwise a system generated name is given, and ASM determines its size automatically)
-- * FORCE - Move the disk from another DG to this one.

CREATE DISKGROUP DG2
  NORMAL REDUNDANCY
  DISK
   '//./ORCLDISKDATA3' NAME DG2_0,
   '//./ORCLDISKDATA4' NAME DG2_1,
   '//./ORCLDISKDATA5' NAME DG2_2,
   '//./ORCLDISKDATA6' NAME DG2_3;

-----------------------
-- DROP DG
-----------------------
-- * INCLUDING CONTENTS - Must be specified if it contains files.
-- * The DG must be mounted, and its files inactive.
-- * The header of each disk is formatted, so it will be available
--     to other DGs or to the OS.

DROP DISKGROUP DG2 INCLUDING CONTENTS;

-----------------------------
-- Adding disks to a DG
-----------------------------
-- * Disks are added and Rebalaced online while data is fully accessible.
-- * The rebalancing load is determined according to ASM_POWER_LIMIT.

SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP, PATH, OS_MB, TOTAL_MB, FREE_MB
FROM v$asm_disk
WHERE group_number=0
ORDER BY path;

-- Add two disks to a "DG1":
ALTER DISKGROUP DG1 ADD DISK
   '//./ORCLDISKDATA3' NAME DG1_3,
   '//./ORCLDISKDATA4' NAME DG2_4;


-- Special discovery characters can be used to add a group of disks.
ALTER DISKGROUP DG1 ADD DISK '//./ORCLDISKDATA*';

------------------------------
-- ASM DG Compatability
------------------------------
-- * Determines which Oracle version can run the ASM or work with him.
-- * Determined at Disk Group level.
-- * Two types:
--    1. RDBMS Compatibility (Compatible.RDBMS)
--       - Refers to the regular instances which work with the ASM.
--       - The compatability version of the instance must be equal or higher.
--       - Determines the format of the massages between the ASM and the regular instance.
--       - ASM can operate with instances of different compatability versions.
--    2. ASM Compatability (Compatible.ASM)
--       - The compatability version of the ASM instance (ASM Metadata).
--       - Must be greater or equal to the RDBMS comp. of the FG.

------------------------------
-- ASM DG Attributes
------------------------------
-- * We can specify DG attributes when creating and altering the DG in the ATTRIBUTE clause.
--   - AU_SIZE (C)           - Allocation Unit size (1MB ,2, 4, 8, 16, 32, 64MB)
--   - COMPATIBLE.RDBMS (AC) - Minimal Oracle version for the regular instances using the DG.
--   - COMPATIBLE.ASM   (AC) - Minimal Oracle version for the ASM instance mounting the DG.
--   - DISK_REPAIR_TIME (AC) - Length of time before removing a disk once OFFLINE
--   - TEMPLATE.t-name.REDUNDANCY (A) - Redundancy level of a specifid template.
--                                      (UNPROTECTED / MIRROR / HIGH)
--   - TEMPLATE.t-name.STRIPE (A) - Striping attribute of specified template. (COARSE / FINE)

ALTER DISKGROUP DG2
ATTRIBUTE 'compatible.asm'='11.1';

-- * We can view all attributes of the DGs in the V$ASM_ATTRIBUTE view.
-- * Via the OEM: Create/Edit DG => Advanced Attributes
col name for a15
col value for a10
SELECT group_number DG#, name, value
FROM v$asm_attribute;

--------------------------------
-- ASM Fast Mirror Resync
--------------------------------
-- * Reduces the time required to resynchronize failed disk data.
-- * When a disk goes offline due to a failure, his mirrored extents are still available.
-- * ASM tracks the extents that were modified during the outage using a bitmap
--   (Bit for each extent), and resynchronizes only these extents when the disk is back online.
--
-- * When a disk becomes unavailable due to a failure, ASM places him OFFLINE automatically.
-- * DISK_REPAIR_TIME - Determines the length of time before removing a disk once OFFLINE.
--      (Length of outage which still enables auto-recovery of the disk).
-- * To manually place a DG or FG Offline/Online via the OEM (For maintenance):
--     - ASM Management => Click the DG usage pie-chart => View by disk/FG => Offline
--     - We can override the default value of DISK_REPAIR_TIME here.

--------------------------------------------
-- Bad block repair (11g)
--------------------------------------------
-- * Performed automatically when in Normal/High Redundancy.
-- * When a block access fails, ASM recovers it automatically from its mirror.
-- * If recovery to the original location fails, its recovered to an alternative location.
-- * Can perform manually from ASMCMD using repair/remap.

--------------------------------------------
-- Miscellaneous ALTER Commands
--------------------------------------------
-- Remove a disk from a DG:
ALTER DISKGROUP dgroupA DROP DISK A5;

-- Removing and adding disks in a single statement (Efficient for rebalancing):
ALTER DISKGROUP dgroupA
 DROP DISK A6
 ADD FAILGROUP fred
         DISK '/dev/rdsk/c0t8d0s2' NAME A9;

-- Cancel a disk drop operation (Only if the drop operationis not yet completed)
ALTER DISKGROUP dgroupA UNDROP DISKS;

-- Manually rebalance a DG:
--   - Useful to change the default Power level.
--       (Also for a rebalace that is already active in the background)
--   - Setting the Power level to 0, stop the rebalancing.
ALTER DISKGROUP dgroupB REBALANCE POWER 5;

-- Mount/Dismount a DG:
--   - Makes it unavailable to the DB.
ALTER DISKGROUP DG1 DISMOUNT;
ALTER DISKGROUP DG1 MOUNT;
ALTER DISKGROUP ALL DISMOUNT;

--------------------------------------------
-- ASMCMD
--------------------------------------------
-- * Manage ASM from the command prompt.
-- * Works with ASM Files, Directories & Aliases.
-- * Like other file systems, ASM also has directories which contain the ASM Files.
-- * Each file has a "Fully Qualified File Name", which is the path to it:
--     - The + sign represents the Root directory of the Disk Group:
ASMCMD> ls -l +DGROUP1/ORCL/DATAFILE

-- * mkdir - Add new directories:
ASMCMD> mkdir +dgroup1/sample/mydir

-- * md_backup / md_restore - backup and restore ASM Metadata:
--    - This enables us to recreate a DG with the same directories and templates (attributes):
ASMCMD> md_backup –b /tmp/dgbackup070222 –g admdsk1 –g asmdsk2
ASMCMD> md_restore –t full –g asmdsk1 –i backup_file

-- * lsdsk - Displays ASM disk information.
--     - When connected - uses V$/GV$ tables.
--     - When not-connected - scans the disk headers to get the information.
--         (can use a disk string to limit the discovery set)

--------------------------------------------
-- ASM Scalability and Performance
--------------------------------------------

--------------------------------------------
-- ASM Directories
--------------------------------------------
-- * A directory heirarchy can be defined using the ALTER DISKGROUP statement
--     to support ASM file aliasing:

-- Create a directory
ALTER DISKGROUP DG1 ADD DIRECTORY '+DG1/my_dir';
ALTER DISKGROUP DG1 ADD DIRECTORY '+DG1/emps';

-- Rename a directory.
ALTER DISKGROUP DG1 RENAME DIRECTORY '+DG1/emps' TO '+DG1/employees';

-- Delete a directory and all its contents.
ALTER DISKGROUP DG1 DROP DIRECTORY '+DG1/employees';

--------------------------------------------
-- Aliases
--------------------------------------------

-- * Aliases allow you to reference ASM files using user-friendly names,
--     rather than the fully qualified ASM filenames.
-- *
SELECT name FROM v$datafile;

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP DG1 ADD ALIAS '+DG1/my_dir/my_file.dbf'
  FOR '+DG1/mydb/datafile/asm_tbs.256.718138495';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP DG1 ADD ALIAS '+DG1/my_dir/my_file.dbf'
  FOR '+DG1.256.718138495';

-- Rename an alias.
ALTER DISKGROUP DG1 RENAME ALIAS '+DG1/my_dir/my_file.dbf'
  TO '+DG1/my_dir/my_file2.dbf';


-- Delete an alias.
ALTER DISKGROUP DG1 DROP ALIAS '+DG1/my_dir/my_file2.dbf';

Attempting to drop a system alias results in an error

--------------------------------------------
-- Create an ASM-based tablespace
--------------------------------------------

conn /@orcl as sysdba
CREATE TABLESPACE ASM_TBS DATAFILE '+DG1/asm_tbs01.dbf' size 10m;
ALTER TABLESPACE ASM_TBS ADD DATAFILE '+DG1' size 10m;

SELECT name FROM v$datafile;

--------------------------------------------
-- ASM Intallation Guidelines
--------------------------------------------

-- 1. Using Third-Party tools such as EASEUS Partition Master 8.0.1 Home Edition
--    Create Unallocated space on your hard drive
-- 2. Create free space out of the anallocated space
-- 3. Create at least 2 raw devices (without any file-system)
-- 4. download Oracle Grid Infrastructure from oracle.com
-- 5. Install Oracle Grid Infrastructure must be installed before Oracle Database
--    NOTE : Oracle Grid Infrastructure must be installed before Oracle Database
-- 6. Install Oracle Database


-- * Networking (tnsnames / listener / sqlnet) should be configured in grid_home
-- TNSNAMES.ORA Should look something like this:
/*
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )
*/

-- Listener should look something like this :
/*

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = +ASM)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\grid)
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = OWNER-3WSG9BKMD)(PORT = 1521))
    )
  )

*/

------------------------------------------------
DEMO!
------------------------------------------------

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 21 08:04:08 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

@> conn /@ASM AS SYSASM
Connected.
SYS@ASM>
SYS@ASM> -- See current Disk Groups:
SYS@ASM> SELECT GROUP_NUMBER, NAME, TOTAL_MB
  2  FROM v$asm_diskgroup;

GROUP_NUMBER NAME                             TOTAL_MB
------------ ------------------------------ ----------
           1 DG1                                  9998

SYS@ASM> col path for a50
SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         0 \\.\ORCLDISK_LABEL_G
         0 \\.\ORCLDISK_LABEL_H

SYS@ASM>
SYS@ASM> -- DG#=0 means that specific path does not have any Disk Group
SYS@ASM>
SYS@ASM> -- Join:
SYS@ASM> SELECT GROUP_NUMBER, dg.name, SUM(d.TOTAL_MB) AS "Size", COUNT(*) AS "Disks"
  2  FROM v$asm_diskgroup dg JOIN v$asm_disk d
  3  USING (GROUP_NUMBER)
  4  GROUP BY GROUP_NUMBER, dg.name ;

GROUP_NUMBER NAME                                 Size      Disks
------------ ------------------------------ ---------- ----------
           1 DG1                                  9998          2

SYS@ASM>
SYS@ASM> -- Create a new disk group DG2:
SYS@ASM> CREATE DISKGROUP DG2
  2  DISK
  3  '\\.\ORCLDISK_LABEL_G' NAME DG2_0,
  4  '\\.\ORCLDISK_LABEL_H' NAME DG2_1 ;

Diskgroup created.

SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         2 \\.\ORCLDISK_LABEL_G
         2 \\.\ORCLDISK_LABEL_H

SYS@ASM>
SYS@ASM> -- DROP DISKGROUP DG2;
SYS@ASM>
SYS@ASM> -------------------------------------
SYS@ASM> -- Create a tablespace with ASM file
SYS@ASM> -- (Performed in the DB instance)
SYS@ASM> -------------------------------------
SYS@ASM>
SYS@ASM> conn sys/oracle@orcl as sysdba
Connected.
SYS@orcl>
SYS@orcl> CREATE TABLESPACE ASM_TBS
  2    DATAFILE '+DG2/asm_tbs01.dbf' SIZE 100M;

Tablespace created.

SYS@orcl> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
+DG2/asm_tbs01.dbf

6 rows selected.

SYS@orcl>
SYS@orcl> --   DROP TABLESPACE asm_tbs INCLUDING CONTENTS AND DATAFILES;
SYS@orcl>
SYS@orcl>
SYS@orcl> -- * Stamp candidate disks / change name and creating disk groups can be done by using ASMCA
SYS@orcl> --   $oracle_grid_home\BIN\asmca.bat
SYS@orcl> -- * for stamping and name changing :
SYS@orcl> --   SHUTDOWN DB => SHUTDOWN ASM => stamp/change names => STARTUP ASM => STARTUP DB
SYS@orcl> -- * SHUTDOWN ASM => stamp/change names => STARTUP ASM
SYS@orcl>

SYS@orcl> -------------------------
SYS@orcl> -- ASM Templates
SYS@orcl> -------------------------
SYS@orcl>
SYS@orcl> -- * A template is a collection of file attributes that are applied to the ASM files.
SYS@orcl> --   It is associated with a Disk Group.
SYS@orcl> -- * Whenever a file is created the file attributes specific to the template are applied to
SYS@orcl> --   the files.
SYS@orcl> -- * For eg., if we want to create a users tablespace that contains data file we use the
SYS@orcl> --   DATAFILE
SYS@orcl> --   template.
SYS@orcl> -- * Default Template: When you create a disk group, Oracle creates system default template
SYS@orcl> --   for the disk group.
SYS@orcl>

SYS@orcl> SELECT GROUP_NUMBER , NAME , STRIPE, REDUNDANCY
  2  FROM V$ASM_TEMPLATE ;

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           1 PARAMETERFILE                  COARSE MIRROR
           1 ASMPARAMETERFILE               COARSE MIRROR
           1 ASMPARAMETERBAKFILE            COARSE MIRROR
           1 DUMPSET                        COARSE MIRROR
           1 CONTROLFILE                    FINE   HIGH
           1 FLASHFILE                      COARSE MIRROR
           1 ARCHIVELOG                     COARSE MIRROR
           1 ONLINELOG                      COARSE MIRROR
           1 DATAFILE                       COARSE MIRROR
           1 TEMPFILE                       COARSE MIRROR
           1 BACKUPSET                      COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           1 AUTOBACKUP                     COARSE MIRROR
           1 XTRANSPORT                     COARSE MIRROR
           1 CHANGETRACKING                 COARSE MIRROR
           1 FLASHBACK                      COARSE MIRROR
           1 DATAGUARDCONFIG                COARSE MIRROR
           1 OCRFILE                        COARSE MIRROR
           1 OCRBACKUP                      COARSE MIRROR
           1 ASM_STALE                      COARSE HIGH
           2 PARAMETERFILE                  COARSE MIRROR
           2 ASMPARAMETERFILE               COARSE MIRROR
           2 ASMPARAMETERBAKFILE            COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 DUMPSET                        COARSE MIRROR
           2 CONTROLFILE                    FINE   HIGH
           2 FLASHFILE                      COARSE MIRROR
           2 ARCHIVELOG                     COARSE MIRROR
           2 ONLINELOG                      COARSE MIRROR
           2 DATAFILE                       COARSE MIRROR
           2 TEMPFILE                       COARSE MIRROR
           2 BACKUPSET                      COARSE MIRROR
           2 AUTOBACKUP                     COARSE MIRROR
           2 XTRANSPORT                     COARSE MIRROR
           2 CHANGETRACKING                 COARSE MIRROR

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 FLASHBACK                      COARSE MIRROR
           2 DATAGUARDCONFIG                COARSE MIRROR
           2 OCRFILE                        COARSE MIRROR
           2 OCRBACKUP                      COARSE MIRROR
           2 ASM_STALE                      COARSE HIGH

38 rows selected.

SYS@orcl>

SYS@orcl> ------------------------
SYS@orcl> -- ASM Striping
SYS@orcl> ------------------------
SYS@orcl>
SYS@orcl> -- * ASM stripes the file to AUs, and devides them between the DG disks.
SYS@orcl> -- * Two types of striping:
SYS@orcl> --      1. Coarse-based striping:
SYS@orcl> --          - Each stripe size is one AU.
SYS@orcl> --          - Classic striping. Suitable for most file types.
SYS@orcl> --          - Used for load balancing.
SYS@orcl> --      2. Fine-Grained striping
SYS@orcl> --            - Each stripe size is 128KB. (Wider distribution of data)
SYS@orcl> --            - Used for decreasing IO Latency (Waiting for IO).
SYS@orcl> --            - Suitable for small IO operations such as Redo Log Files.
SYS@orcl>
SYS@orcl> -- ONLINELOG
SYS@orcl> ALTER DISKGROUP dg2 MODIFY TEMPLATE ONLINELOG
  2  ATTRIBUTES (FINE);

Diskgroup altered.

SYS@orcl> ALTER DISKGROUP dg2 MODIFY TEMPLATE DATAFILE
  2  ATTRIBUTES (COARSE);

Diskgroup altered.

  1  Select * From V$ASM_TEMPLATE
  2  WHERE name IN ('DATAFILE', 'ONLINELOG')
  3* AND group_number = 2
SYS@orcl> /

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME                           PRIM MIRR
------------ ------------ ------ ------ - ------------------------------ ---- ----
           2            7 MIRROR FINE   Y ONLINELOG                      COLD COLD
           2            8 MIRROR COARSE Y DATAFILE                       COLD COLD

SYS@orcl> SELECT GROUP_NUMBER , NAME , STRIPE, REDUNDANCY
  2  FROM V$ASM_TEMPLATE
  3  WHERE name IN ('DATAFILE', 'ONLINELOG')
  4  AND group_number = 2 ;

GROUP_NUMBER NAME                           STRIPE REDUND
------------ ------------------------------ ------ ------
           2 ONLINELOG                      FINE   MIRROR
           2 DATAFILE                       COARSE MIRROR

SYS@orcl>
SYS@orcl> CONN /@ASM AS SYSASM
SYS@ASM> DROP DISKGROUP DG2 INCLUDING CONTENTS;

---------------------------------------------------------------------------------------------
-- ASM Mirroring & Failure Groups
---------------------------------------------------------------------------------------------
-- * ASM can protect our files from media failures (disk / disk-controller crash)
--
-- * Mirroring:
--   - Applied at Extent (AU) level (Not at file or disk level).
--   - When an AU is allocated to a file on a specific disk, a mirrored AU is also allocated on another disk in the SAME Disk Group.
--   - Therefore, if a disk is lost, we can restore its contents from other disks on its Disk Group.

-- * Failure Group:
--   - A failure group is a subset of the disks in a disk group, which could fail at the same time because they share hardware. (For example, disks that share the same disk controller)
--   - By default, each ASM disk belongs to his own Failure Group (FG).
--   - However, we can group a few disks in a DG to a single Failure Group.
--   - When an AU is allocated on a FG disk, a mirrored AU is also allocated on ANOTHER Failure Group in the SAME Disk Group.
--   - Therefore, if a Failure Group is lost, we can restore its contents from other disks on its Disk Group.

-- *   types or mirroring:
--     1. External Redundancy - No mirroring or FGs. (When using an external tool for protection).
--     2. Normal Redundancy   - Two-way mirroring.   (Two copies)
--     3. High Redundancy     - Triple-mirroring.    (Three copies)

SYS@ASM> col path for a50
SYS@ASM> -- See available disks:
SYS@ASM> SELECT GROUP_NUMBER "DG#", PATH
  2  FROM v$asm_disk
  3  ORDER BY path;

       DG# PATH
---------- --------------------------------------------------
         1 \\.\ORCLDISK_LABEL_E
         1 \\.\ORCLDISK_LABEL_F
         0 \\.\ORCLDISK_LABEL_G
         0 \\.\ORCLDISK_LABEL_H

-- A protected disk group:

SYS@ASM>   CREATE DISKGROUP DG2
  1        NORMAL REDUNDANCY
  2        DISK
  3        '\\.\ORCLDISK_LABEL_G',
  4        '\\.\ORCLDISK_LABEL_H' ;

Diskgroup created.

SYS@ASM> SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP
  2  FROM v$asm_disk;

       DG#       DSK# NAME       FAILGROUP
---------- ---------- ---------- -----------
         2          0 DG2_0000   DG2_0000
         2          1 DG2_0001   DG2_0001
         1          0 DG1_0000   DG1_0000
         1          1 DG1_0001   DG1_0001


SYS@ASM> DROP DISKGROUP DG2 INCLUDING CONTENTS;

Diskgroup dropped.


SYS@ASM> CREATE DISKGROUP DG2
  2    NORMAL REDUNDANCY
  3    FAILGROUP controller1 DISK
  4   '\\.\ORCLDISK_LABEL_G'
  5    FAILGROUP controller2 DISK
  6    '\\.\ORCLDISK_LABEL_H' ;

Diskgroup created.

SYS@ASM> SELECT GROUP_NUMBER "DG#", DISK_NUMBER "DSK#", NAME, FAILGROUP
  2  FROM v$asm_disk;

       DG#       DSK# NAME       FAILGROUP
---------- ---------- ---------- -----------
         2          0 DG2_0000   CONTROLLER1
         2          1 DG2_0001   CONTROLLER2
         1          0 DG1_0000   DG1_0000
         1          1 DG1_0001   DG1_0001

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Oracle Users Management

PROBLEM:
What are the basics of Oracle users management?

1. Users 
• username 
• identified by 
• default tablespace 
• temporary tablespace 
• password expire option 
• profile 
• account 
2. Priviliges 
• system privileges
• object privileges
3. Roles 
4. Quotas  
5. Profiles 

SOLUTION:

----------------------------------------------------------
-- USERS
----------------------------------------------------------

-- View all users / schemas

SELECT username FROM dba_users;

-- Create a new user
CREATE USER kipi IDENTIFIED BY sumsum ;

--   * EXTERNALLY Clause - External user will not require any login password as local user does.
--     Such a user will be authenticated by operating System.
--     Means: If you can log in to operating system, you can use oracle too.

show parameter os_authent_prefix

select UPPER(sys_context('userenv','os_user')) from dual;

CREATE USER "OPS$BASDBINS\ADMINISTRATOR" IDENTIFIED EXTERNALLY;

GRANT CREATE SESSION TO "OPS$BASDBINS\ADMINISTRATOR";

conn /

-- * GLOBALLY Clause - GLobal user will also not require any login password as local user does.
--   Such a user will be authenticated by a directory service.
--   like OID (Oracle Internet Directory).
-- * GLOBALLY AS extname, where the username and password will be passed to
--   the extname service for logon validation

-- * DEFAULT TABLESPACE
-----------------------
-- * Specify the default tablespace for objects that the user creates.
--   If you omit this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are
--   stored in the SYSTEM tablespace 
-- * DEFAULT TABLESPACE option does not grant any quota to the user.

-- * QUOTA [size] ON [tablespace]
---------------------------------
--   specify the maximum amount of space the user can allocate in the tablespace
--   UNLIMITED lets the user allocate space in the tablespace without bound.

-- * TEMPORARY TABLESPACE
-------------------------
--   Specify the tablespace or tablespace group for the user's temporary segments.
--   If you omit this clause, then the user's temporary segments are stored in the database default temporary tablespace or, if none has been specified, in the SYSTEM tablespace.

-- * PASSWORD EXPIRE
--------------------
--   Specify PASSWORD EXPIRE if you want the user's password to expire.
--   This setting forces the user or the DBA to change the password before the user
--   can log in to the database

-- * PROFILE [profile name]
---------------------------
--   Specify the profile you want to assign to the user.
--   The profile limits the amount of database resources the user can use.
--   If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.

-- * ACCOUNT Clause
-------------------
--   Specify ACCOUNT LOCK to lock the user's account and disable access.
--   Specify ACCOUNT UNLOCK to unlock the user's account and enable access to the account.

CREATE USER debra;

CREATE USER debra
IDENTIFIED BY pass;

DROP USER debra;

CREATE USER debra
IDENTIFIED BY debra
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10m ON users
QUOTA UNLIMITED ON example
PROFILE DEFAULT
ACCOUNT LOCK
PASSWORD EXPIRE;

SELECT username, DEFAULT_TABLESPACE, profile
FROM dba_users
ORDER BY username;

-- Quotas
SELECT * FROM dba_ts_quotas
WHERE USERNAME = 'debra';

-- * UNLIMITED TABLESPACE privilege - which is a result of assigning the RESOURCE role.
--   In that case user will not appear in dba_ts_quotas.

-- Via OEM:
-- Server => (Security) => Users

-------------------------------------------------------
-- Passwords
-------------------------------------------------------

-- Password column is null

SELECT username, password FROM dba_users WHERE username = 'HR';

SELECT NAME, PASSWORD FROM SYS.USER$ WHERE name = 'HR';

ALTER USER hr IDENTIFIED BY my_pass

conn hr/my_pass

-- Change whatever i want...

conn / as sysdba

ALTER USER hr IDENTIFIED BY VALUES ' '

-- Hashing vs Encription
------------------------
-- * There is a huge difference between encryption and hashing.
-- * Encrypton is a reversible proces, while hashing is not.
-- * With encryption, you can allways get the original value from the encrypted value,
--   provided that you have propper encryption key and an alghorytm.
-- * But with hashing process it is different. You can (generally speaking) never get
--   the original value from the hashed value, even if you know the hashing algorythm.
--   It is mathematicaly one-way process.

-------------------------------------------------------
-- PRIVILEGES
-------------------------------------------------------

-- * System privileges
--   Allow a user to perform certain database actions, such as create a table,
--   or create an index, or even connect to the instance
-- * Object privileges
--   allow a user to manipulate objects, as by reading data through a view,
--   executing a stored procedure, or changing data in a table

-- Connect first attempt
Conn debra/debra

conn / as sysdba
GRANT CREATE SESSION TO debra;

-- Connect second attempt :
Conn debra/debra

SELECT * FROM session_privs;

-- Try to create a table:
create table test(y number);

conn / as sysdba
grant create table to debra;

conn debra/debra

SELECT * FROM session_privs;

create table test(y number);

-- GRANT ANY
------------

-- Try to create a table on another schema:
[debra] create table sh.newtab(y number);

conn / as sysdba

grant create any table to debra;

conn debra/debra

create table sh.newtab(y number);

create table sys.newtab(y number);

conn / as sysdba

show parameter O7_DICTIONARY_ACCESSIBILITY

------------------------------------------------
-- WITH ADMIN OPTION - (System - No cascade)
------------------------------------------------

-- GRANT system privilege  [ANY] TO username [WITH ADMIN OPTION]
-- REVOKE system privilege [ANY] FROM username

conn / as sysdba

grant create any table to debra with admin option ;

conn debra/debra

grant create any table to hr ;

grant create any table to hr with admin option ;

-- is it possible to grant user hr CREATE TABLE privilege ?

grant create table to hr ;

conn hr/hr

create table sh.test2(y number) ;

conn / as sysdba

revoke create any table from debra;

conn debra/debra

create table sh.test3(y number) ;

conn hr/hr

create table sh.test3(y number) ;

-- we can monitor different privileges using dba_sys_privs
-- we cannot monitor the hierarchy

SELECT *
FROM dba_sys_privs
WHERE grantee in ('debra','HR','SH')
ORDER BY grantee ;

-------------------------------------------------------
-- OBJECT PRIVILEGES (CASCADE)
-------------------------------------------------------

-- GRANT | REVOKE object privilege ON object TO username [WITH GRANT OPTION]

conn debra/debra

update hr.employees set salary=200;

conn hr/hr

GRANT UPDATE ON employees TO debra WITH GRANT OPTION;

GRANT UPDATE (salary) ON employees TO debra WITH GRANT OPTION;

conn debra/debra

update hr.employees set salary=200;

roll

GRANT UPDATE ON hr.employees to SH;
-- or
GRANT UPDATE ON hr.employees to SH WITH GRANT OPTION ;

Grant succeeded.

conn sh/sh

update hr.employees set salary=400;

roll

conn / as sysdba

col owner for a10
col table_name for a15
col grantor for a15
col privilege for a15

SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE,GRANTABLE
FROM  dba_tab_privs
WHERE table_name='EMPLOYEES'
AND GRANTEE <> 'OE'

conn hr/hr (or conn / as sysdba)

REVOKE UPDATE ON hr.employees FROM debra;

SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE,GRANTABLE
FROM  dba_tab_privs
WHERE table_name='EMPLOYEES'
AND GRANTEE <> 'OE'

-- Granting all object privileges to a user using a quick script

conn hr/hr

SELECT 'GRANT ALL ON ' || table_name || ' TO ' || '&username' FROM user_tables

-------------------------------------------------------
-- ROLES
-------------------------------------------------------

-- recreate debra

drop user debra ;

create user debra identified by debra;

grant create session to debra ;

grant CREATE VIEW to debra ;

grant UPDATE on HR.EMPLOYEES to debra ;

-- create role basic;

drop role basic ;

create role basic;

grant CREATE TABLE to basic;

grant RESOURCE to basic ;

grant DROP ANY TABLE to basic ;

grant UPDATE on HR.DEPARTMENTS to basic ;

-- Grant role to debra

GRANT basic TO debra;

conn debra/debra

select * from session_privs ;

conn / as sysdba

Helpful Views
-------------------------------

-- list of object privs for every user
SELECT GRANTEE, TABLE_NAME, PRIVILEGE
FROM dba_tab_privs
WHERE GRANTEE = 'debra';

-- list of system privs for every user
SELECT * FROM dba_sys_privs
WHERE grantee = 'debra'; 

-- list of roles for every user
SELECT GRANTEE, GRANTED_ROLE
FROM dba_role_privs
WHERE GRANTEE = 'debra'

-- list of roles for every role
SELECT ROLE, GRANTED_ROLE
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'BASIC';

-- list of sys privs for every role
SELECT *
FROM dba_sys_privs
WHERE grantee = 'RESOURCE';

-- list of object privs for every role
SELECT GRANTEE, TABLE_NAME, PRIVILEGE
FROM dba_tab_privs
WHERE GRANTEE = 'BASIC'

-- or we could just use OEM or external scripts such as
-- user_privs.sql
-- source : http://www.petefinnigan.com/tools.htm

-- DEFAULT / NON-DEFAULT ROLES
------------------------------

ALTER USER debra DEFAULT ROLE ALL;

ALTER USER debra DEFAULT ROLE NONE;

ALTER USER debra DEFAULT ROLE ALL EXCEPT basic;

conn debra/debra

SELECT username, granted_role, default_role
FROM   user_role_privs ;

select * from session_privs ;

SET ROLE basic;

select * from session_privs ;
                               
-- PASSWORD PROTECTED ROLES:
----------------------------

conn / as sysdba

ALTER ROLE basic IDENTIFIED BY mypass;

conn debra/debra

select * from session_privs ;

SET ROLE basic IDENTIFIED BY mypass;

select * from session_privs ;

ALTER ROLE basic NOT IDENTIFIED ;
                                                                  

---------------------------------------------------------------------------------
-- PROFILES
---------------------------------------------------------------------------------

-- Via OEM:
-- Server => Security => Profiles

PROFILES
--------
-- * A profile is an Oracle object that allows you to set both password management and
--   resource limits.
-- * In every Oracle database a single profile is created when you create the
--   database. This profile, called DEFAULT, places no limits on password and account
--   lockout, or on resource utilization.
-- * You can change the settings of the DEFAULT profile to conform to your requirements,
--   and they will then be applied to all users in thedatabase assigned the DEFAULT profile.
--   A database administrator may create additional profiles dealing with password or
--   account lockout issues, resource management settings, or both.
-- * Once created, a profile can be assigned to a user account as it is created, or it can be
--   assigned to the user with the ALTER USER command.
-- * Any settings in the profile will then apply to the user the next time he/she connects to the
--   database.
-- * A user may have only one profile active at one time, so you need to ensure that the
--   settings within the profile match the requirements of each user.

-- * PERFORMANCE
----------------
-- * CPU_PER_SESSION -
--   The total CPU time, measured in hundredths of a second, that a user
--   is allowed to consume during a session.
--   Once the limit is reached, the user’s session is terminated with
--   an Oracle server error message.
--   To reset this limit, the user needs to disconnect from the instance
--   and connect again.

-- * CPU_PER_CALL -
--   The total CPU time, measured in hundredths of a second, that a user
--   is allowed to consume for a single SQL statement.
--   Once the limit is reached, the SQL statement is aborted and the transaction it is
--   a part of is rolled back.
--   The user’s session remains connected.
--   The limit is reset on every call to the database.

-- * CONNECT_TIME -
--   The maximum amount of time, specified in minutes, that a user may remain
--   connected to the instance.

-- * IDLE_TIME -
--   The maximum amount of time, specified in minutes,
--   that a user’ssession may remain connected to the instance while not performing
--   any database activity.

-- * SESSIONS_PER_USER -
--   The maximum number of concurrent sessions that a user may have at one time.

-- * LOGICAL_READS_PER_SESSION -
--   The number of blocks (both physical—from disk—and logical—
--   from the database buffer cache) that the user is allowed to read
--   during their session.
--   Once the number of blocks specified by this parameter are read, the user will need to
--   start another session in order to access data in the database.

-- * LOGICAL_READS_PER_CALL -
--   The number of blocks (both physical—from disk—and logical—from
--   the database buffer cache) that the user is allowed to read when
--   executing a SQL statement.
--   Once the number of blocks specified by this parameter are read,
--   the SQL statement will be terminated and any transaction that it is a part
--   of will be rolled back.

-- * PRIVATE_SGA -
--   In a Multi-Threaded Server (MTS) environment, this parameter
--   specifies the maximum number of bytes that a user’s session can
--   occupy in the SGA.
--   If you are not connected to the database instance with an MTS connection,
--   this parameter is ignored.

-- Change the costs
-------------------

-- * Example

ALTER RESOURCE COST
CPU_PER_SESSION 100
CONNECT_TIME      1;

-- * The weights establish this cost formula for a session:
--   cost = (100 * CPU_PER_SESSION) + (1 * CONNECT_TIME)
-- * If you do not assign a weight to a resource, then the weight defaults to 0, and use of the
--   resource subsequently does not contribute to the cost.
--   The weights you assign apply to all subsequent sessions in the database.

-- The composite limit
-----------------------

--   Specifies a numerical value that is the weighed average of four resource limits:
--   1.CPU_PER_SESSION
--   2.CONNECT_TIME
--   3.LOGICAL_READS_PER_SESSION
--   4.PRIVATE_SGA
--   Setting COMPOSITE_LIMIT will allow Oracle to monitor all four
--   of these parameter values; when the combination of all exceeds the
--   value specified by COMPOSITE_LIMIT, the user’s session will be
--   terminated.

-- * Example :

ALTER RESOURCE COST
CPU_PER_SESSION 10
LOGICAL_READS_PER_SESSION 2
PRIVATE_SGA 6
CONNECT_TIME 1;

-- * When the session hits the composite limit, the session is terminated.

ALTER PROFILE app_developr
COMPOSITE_LIMIT 500;

-- * PASSWORD
-------------
-- * PASSWORD_LIFE_TIME
--   The number of days that will pass before the user is prompted to change his password.

-- * PASSWORD_GRACE_TIME
--   The number of days following a successful login after the expiration of PASSWORD_LIFE_TIME.
--   The grace time starts after the login, even if the password has already expired.

-- * PASSWORD_REUSE_MAX
--   Specify the number of password changes required before the current password can be reused.
--   If you set PASSWORD_REUSE_MAX to an integer value, then you must set
--   PASSWORD_REUSE_TIME to UNLIMITED.

-- * PASSWORD_REUSE_TIME
--   The time in days that must elapse before apassword can be reused.
--   If you set PASSWORD_REUSE_TIME to an integer value, then you must set
--   PASSWORD_REUSE_MAX to UNLIMITED.

-- * These two parameters must be set in conjunction with each other.
-- * For these parameter to have any effect, you must specify an integer for both of them.
--   Possible via sql*plus only.

ALTER PROFILE "DEFAULT" LIMIT
PASSWORD_REUSE_MAX 4
PASSWORD_REUSE_TIME 1/1440

-- * PASSWORD_VERIFY_FUNCTION
--   A PL/SQL function that will perform complexity checks (or any other checks
--   that may be needed) whenever a password is changed.

-- * FAILED_LOGIN_ATTEMPTS
--   After this number of consecutive connect requests with an incorrect password,
--   the account will be locked.
--   This will prevent hackers from trying numerous common passwords in an attempt
--   to guess the correct one.
--   The counter is reset after a successful connection.

-- * PASSWORD_LOCK_TIME
--   The number of days for which the account will be locked if the FAILED_LOGIN_ATTEMPTS
--   limit is reached. If days are unsuitable as units, use appropriate arithmetic.
--   For instance, one day has one thousand four hundred and forty minutes,
--   so setting the PASSWORD_LOCK_TIME to (30/1440) is equivalent to thirty minutes
--   enough to foil a hacker who is throwing a dictionary at your database, without inconveniencing --   forgetful users too much.

-- Current profiles - by default - all is unlimited:
col limit for a15
set lines 200
SELECT *
FROM dba_profiles
ORDER BY profile, RESOURCE_TYPE;

-- Users profiles
SELECT username , profile
FROM dba_users ;

-- Create a new profile:
CREATE PROFILE dev_prof
LIMIT
SESSIONS_PER_USER 2
FAILED_LOGIN_ATTEMPTS 5;

-- All the rest - depend on the Default values (Unlimited):
SELECT *
FROM dba_profiles
WHERE profile= 'DEV_PROF'
ORDER BY profile, RESOURCE_TYPE;

ALTER USER hr PROFILE DEFAULT;

ALTER PROFILE "DEFAULT"
LIMIT FAILED_LOGIN_ATTEMPTS 3 ;

conn hr/hr

conn hr/badpass

conn hr/badpass

conn hr/badpass

conn / as sysdba

ALTER USER hr ACCOUNT UNLOCK;

ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;

ALTER USER sh ACCOUNT UNLOCK IDENTIFIED BY sh PASSWORD EXPIRE;

-- Complexity Function:

get ?\rdbms\admin\utlpwdmg.sql

ALTER PROFILE dev_prof
LIMIT
PASSWORD_VERIFY_FUNCTION my_func

ALTER USER hr PROFILE dev_prof;

-- Enabling resource limits
show parameter resource_limit

-- ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;                                                            
-- Change a default profile for all users
SELECT 'ALTER USER '||username||' PROFILE dev_prof;' FROM dba_users

-- for more complex management
-- Via OEM => Server => Resource Manager

-------------------------------------------------------
-- PROXY USERS
-------------------------------------------------------
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:21575905259251

-- Enable HR to connect to debra without a password:
ALTER USER debra GRANT CONNECT THROUGH hr;

conn hr[debra]/hr

show user

-- Can access HR?
SELECT * FROM hr.departments;

-- Try to connect to HR from debra:
conn debra[hr]/debra

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!