Samples Queries

These are formatted test results of Books application. You can test these queries by executing samples included with aarbac nuget package.

See csv format

Wildcard select, the user can read Author table, cannot read SSN column and can see authors from New York and Charlotte
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from Author

Parsed Query:

SELECT Author.AuthorId , Author.Name , Author.ZipCodeId FROM Author   
inner join [ZipCode] [t1] on [t1].ZipCodeId = [Author].ZipCodeId   
inner join [City] [t2] on [t2].CityId = [t1].CityId 
WHERE t2.Name
 in ('New York','Charlotte')

Specific columns the user can read Author table, cannot read SSN column and can see authors from New York and Charlotte
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select name,ssn from Author

Parsed Query:

SELECT name   FROM Author   
inner join [ZipCode] [t5] on [t5].ZipCodeId = [Author].ZipCodeId   
inner join [City] [t6] on [t6].CityId = [t5].CityId 
WHERE t6.Name
 in ('New York','Charlotte')

- Ambiguous column name 'name'.

Specific columns select, the user cannot read SSN column & can see authors from NY and Charlotte
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from Author a

Parsed Query:

SELECT a.AuthorId , a.Name , a.ZipCodeId FROM Author a   
inner join [ZipCode] [t9] on [t9].ZipCodeId = [a].ZipCodeId   
inner join [City] [t10] on [t10].CityId = [t9].CityId 
WHERE t10.Name
 in ('New York','Charlotte')

One specific column select, the user does not have permission to see the SSN column
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select SSN from Author

- RBAC.Core - The query returned 0(zero) column!

Wildcard select, there is no restriction on Book table, user should see everything
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from Book

Parsed Query:

select Book.BookId,Book.Title,Book.Subject,Book.Price,Book.Isbn13,Book.Isbn10,Book.PublisherId from Book

Wildcard select with alias
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select a.* from Author a

Parsed Query:

SELECT a.AuthorId , a.Name , a.ZipCodeId FROM Author a   
inner join [ZipCode] [t18] on [t18].ZipCodeId = [a].ZipCodeId   
inner join [City] [t19] on [t19].CityId = [t18].CityId 
WHERE t19.Name
 in ('New York','Charlotte')

Wildcard select with only table alias
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from Author a

Parsed Query:

SELECT a.AuthorId , a.Name , a.ZipCodeId FROM Author a   
inner join [ZipCode] [t22] on [t22].ZipCodeId = [a].ZipCodeId   
inner join [City] [t23] on [t23].CityId = [t22].CityId 
WHERE t23.Name
 in ('New York','Charlotte')

Wildcard muliselect with table alias
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select a.*, a.* from Author a

Parsed Query:

SELECT a.AuthorId , a.Name , a.ZipCodeId , a.AuthorId , a.Name , a.ZipCodeId FROM Author a   
inner join [ZipCode] [t26] on [t26].ZipCodeId = [a].ZipCodeId   
inner join [City] [t27] on [t27].CityId = [t26].CityId 
WHERE t27.Name
 in ('New York','Charlotte')

Complex wildcard select
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from Author 
where Author.ZipCodeId
 in (select zc.ZipCodeId from ZipCode zc 
where zc.CityId
 in (select c.CityId from City c 
where c.StateId
 in (select StateId from State 
where ShortName
 in ('NY', 'NC'))))

Parsed Query:

SELECT Author.AuthorId , Author.Name , Author.ZipCodeId FROM Author   
inner join [ZipCode] [t30] on [t30].ZipCodeId = [Author].ZipCodeId   
inner join [City] [t31] on [t31].CityId = [t30].CityId 
WHERE (Author.ZipCodeId
 in (SELECT zc.ZipCodeId  FROM ZipCode zc  
WHERE zc.CityId
 in (SELECT c.CityId  FROM City c  
WHERE c.StateId
 in (SELECT StateId  FROM State  
WHERE ShortName
 in ('NY'  , 'NC' ) ) ) )) AND (t31.Name
 in ('New York','Charlotte'))

select non-permitted table with wildcard, user does not have permission to read from Country table
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from Country

- RBAC.Core - The query returned 0(zero) column!

select non-permitted table with specific columns, user does not have permission to read from Country table
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select countryId,Name from Country

- RBAC.Core - The query returned 0(zero) column!

select permitted & non-permitted tables with specific columns, user does not have permission to read from Country table
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select Country.CountryId, dbo.Country.Code, dbo.Country.Name, dbo.State.ShortName, dbo.State.Name AS [StateName]
from dbo.Country 

inner join dbo.State ON dbo.Country.CountryId = dbo.State.CountryId

Parsed Query:

select dbo.State.ShortName, dbo.State.Name AS [StateName] from dbo.Country 

inner join dbo.State ON dbo.Country.CountryId = dbo.State.CountryId

Select into wildcard
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * into Author2 from Author

Parsed Query:

SELECT Author.AuthorId , Author.Name , Author.ZipCodeId  into Author2  FROM Author   
inner join [ZipCode] [t34] on [t34].ZipCodeId = [Author].ZipCodeId   
inner join [City] [t35] on [t35].CityId = [t34].CityId 
WHERE t35.Name
 in ('New York','Charlotte')

select into specific columns
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select name,ssn into Author2 from Author

Parsed Query:

SELECT name    into Author2  FROM Author   
inner join [ZipCode] [t38] on [t38].ZipCodeId = [Author].ZipCodeId   
inner join [City] [t39] on [t39].CityId = [t38].CityId 
WHERE t39.Name
 in ('New York','Charlotte')

- Ambiguous column name 'name'.

select into specific columns recommended way
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select Author.name, Author.ssn into Author2 from Author

Parsed Query:

SELECT Author.name    into Author2  FROM Author   
inner join [ZipCode] [t42] on [t42].ZipCodeId = [Author].ZipCodeId   
inner join [City] [t43] on [t43].CityId = [t42].CityId 
WHERE t43.Name
 in ('New York','Charlotte')

- There is already an object named 'Author2' in the database.

Select using inner join 1
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select a.AuthorId, a.Name as [AuthorName], a.ZipCodeId, c.Name as City from Author a
inner join Zipcode zc on zc.ZipCodeId = a.ZipCodeId
inner join City c on c.CityId = zc.CityId
where c.Name = 'Charlotte'

Parsed Query:

SELECT a.AuthorId , a.Name as [AuthorName] , a.ZipCodeId , c.Name as City  FROM Author a 
inner join Zipcode zc on zc.ZipCodeId = a.ZipCodeId 
inner join City c on c.CityId = zc.CityId   
WHERE c.Name
 in ('New York','Charlotte')

Select using inner join 2
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select zc.ZipCode, a.Name from ZipCode zc 
inner join Author a on a.ZipCodeId = zc.ZipCodeId 
where zc.ZipCodeId = 12

Parsed Query:

SELECT zc.ZipCode , a.Name  FROM ZipCode zc 
inner join Author a on a.ZipCodeId = zc.ZipCodeId    
inner join [City] [t48] on [t48].CityId = [zc].CityId 
WHERE (zc.ZipCodeId = 12) AND (t48.Name
 in ('New York','Charlotte'))

aarbac recommends to use table or alias prefix, this query will parse good, but will throw error while executing Ambiguous column name 'ZipCodeId'
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from Author 
where ZipCodeId
 in (select zc.ZipCodeId from ZipCode zc 
where zc.CityId
 in (select c.CityId from City c 
where c.StateId
 in (select StateId from State 
where ShortName
 in ('NY', 'NC'))))

Parsed Query:

SELECT Author.AuthorId , Author.Name , Author.ZipCodeId FROM Author   
inner join [ZipCode] [t51] on [t51].ZipCodeId = [Author].ZipCodeId   
inner join [City] [t52] on [t52].CityId = [t51].CityId 
WHERE (ZipCodeId
 in (SELECT zc.ZipCodeId  FROM ZipCode zc  
WHERE zc.CityId
 in (SELECT c.CityId  FROM City c  
WHERE c.StateId
 in (SELECT StateId  FROM State  
WHERE ShortName
 in ('NY'  , 'NC' ) ) ) )) AND (t52.Name
 in ('New York','Charlotte'))

- Ambiguous column name 'ZipCodeId'.

aarbac recommended column usage
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from Author 
where Author.ZipCodeId
 in (select zc.ZipCodeId from ZipCode zc 
where zc.CityId
 in (select c.CityId from City c 
where c.StateId
 in (select StateId from State 
where ShortName
 in ('NY', 'NC'))))

Parsed Query:

SELECT Author.AuthorId , Author.Name , Author.ZipCodeId FROM Author   
inner join [ZipCode] [t55] on [t55].ZipCodeId = [Author].ZipCodeId   
inner join [City] [t56] on [t56].CityId = [t55].CityId 
WHERE (Author.ZipCodeId
 in (SELECT zc.ZipCodeId  FROM ZipCode zc  
WHERE zc.CityId
 in (SELECT c.CityId  FROM City c  
WHERE c.StateId
 in (SELECT StateId  FROM State  
WHERE ShortName
 in ('NY'  , 'NC' ) ) ) )) AND (t56.Name
 in ('New York','Charlotte'))

incorrect query
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select

Parsed Query:

select

- Incorrect syntax near select.Error:Incorrect syntax near select. at line nr:1 column:1

incorrect query
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

abc

- RBAC.Core - Invalid query type!

incorrect query
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select *

Parsed Query:

select *

- Must specify table to select from.

incorrect query
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from

Parsed Query:

select * from

- Unexpected end of file occurred.Error:Unexpected end of file occurred. at line nr:1 column:15

incorrect query
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from book 
where

Parsed Query:

select * from book 
where

- Unexpected end of file occurred.Error:Unexpected end of file occurred. at line nr:1 column:25

incorrect table
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

select * from abc

- RBAC.PRS - The referred table abc was not found in meta data!

Simple insert into permissible table, user can insert record into Book
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

INSERT INTO [dbo].[Book] ([Title],[Subject],[Price],[PublisherId]) VALUES
           ('A new Book'
           ,'aarbac - An Automated Role Based Access Control'
           ,5.50          
           ,11)

Parsed Query:

INSERT INTO [dbo].[Book] ([Title],[Subject],[Price],[PublisherId]) VALUES
           ('A new Book'
           ,'aarbac - An Automated Role Based Access Control'
           ,5.50          
           ,11)

Simple Insert
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

insert into Author values ('','',1)

- RBAC.PRS - User 'Lashawn' does not have permission to insert record into the table 'Author'!

Simple insert with specific column names
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

insert into Author(Name,SSN,ZipCodeId) values ('','',1)

- RBAC.PRS - User 'Lashawn' does not have permission to insert record into the table 'Author'!

Simple update, Lashawn has permission to update Name
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

update Author set Name='abc' 
where AuthorId = 1

Parsed Query:

update Author set Name='abc' 
where AuthorId = 1

Simple update, Lashawn does not have permission to update SSN
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

update Author set SSN='abc' 
where AuthorId = 1

- RBAC.PRS - User 'Lashawn' has permission to update table 'Author', however has no permission to update column 'SSN'!

Update with Join Clause
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

UPDATE a 
SET a.Name = 'abc' 
FROM dbo.Author AS a
INNER JOIN dbo.ZipCode AS zc 
       ON a.ZipCodeId = a.ZipCodeId

WHERE zc.ZipCode = '00000'

Parsed Query:

UPDATE a 
SET a.Name = 'abc' 
FROM dbo.Author AS a
INNER JOIN dbo.ZipCode AS zc 
       ON a.ZipCodeId = a.ZipCodeId

WHERE zc.ZipCode = '00000'

simple delete
Rbac: books
User: Lashawn
Role: role_city_mgr
Query:

delete from author 
where name = 'abc'

- RBAC.PRS - User 'Lashawn' does not have permission to delete record from table 'Author'!

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Samples Queries

See csv format

Clone this wiki locally