Questions tagged [role]

A set of permissions.

Filter by
Sorted by
Tagged with
41
votes
4answers
119k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
39
votes
1answer
38k views

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role "...
37
votes
2answers
130k views

SQL Server - granting permissions to an entire schema vs. object?

I'm very green when it comes to the world of database permissions management in SQL Sever. Let's keep an example simple. Say account 'admin' is the owner of schemas A, B, and C. There another ...
32
votes
6answers
65k views

How to get all roles that a user is a member of (including inherited roles)?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; ...
31
votes
2answers
286k views

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
25
votes
2answers
29k views

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. ...
25
votes
1answer
741 views

Reporting Service and Application Role

First poster, long time lurker here. What is the best way to activate application role in a report ? I've tried different things and so far the only method that works is to embed the call to the ...
15
votes
2answers
10k views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
14
votes
1answer
24k views

PostgreSQL: permission denied for relation

I'm a bit confused about setting permissions in PostgreSQL. I have these roles: List of roles Role name | Attributes | Member of ---...
13
votes
2answers
12k views

PostgreSQL - Who or what is the “PUBLIC” role?

I was looking at information_schema.role_table_grants when I saw public in grantee column, then I've checked at information_schema.enabled_roles but this role_name does not exist. => Who or what is ...
12
votes
3answers
37k views

Granting Select access to all tables within a specific Database in MS SQL

I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012. I have a special role set up with 3 users that are apart of that role. I ...
10
votes
1answer
9k views

Is there a difference between granting SELECT to a user and adding them to the db_datareader role?

I was asked to grant SELECT, INSERT and UPDATE to a given user for all tables in a database. I ended up adding the user to db_datareader and granting them INSERT and UPDATE at the database level. ...
9
votes
1answer
9k views

PostgreSQL, display role members

Is there a way to visualize which users are mebers of a role in PostgreSQL / pgAdminIII, like role: council_stuff members: Harry Ben Steve Melinda
9
votes
2answers
4k views

postgres: how can I allow index creation but no table mutations or table drops by the same user?

I am setting up a postgres server with what is going to be a (mostly) read-only workload. I would like to allow myself and colleagues to create/drop indices as we see fit, without allowing ...
9
votes
1answer
664 views

Grant deny permission stacking

For the role db_denycustomer, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this: DENY SELECT ON dbo.customer TO db_denycustomer GRANT SELECT ON ...
9
votes
2answers
15k views

Create database role to allow read access to all tables; write access to some

We have a database with over 1000 tables. I need to create a role that allows read only on all tables, as well as write access to two specific tables. I was messing around with database roles, but ...
9
votes
1answer
2k views

Map Oracle roles to Active Directory groups?

Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?
8
votes
1answer
167 views

Giving server-role to login breaks stored procedures

I have a login called user1. user1 can execute stored procedure [Test].[SP1] EXEC [Test].[TestSortation] EXEC [TestSortation] Both of the above commands work. Now if I give the user1 the server ...
8
votes
2answers
2k views

Scripting an application role with a hashed password

I need to script an application role with a hashed password, so I can copy it from one database to another. Consider the following example code, which uses an Application Role to provide elevated ...
8
votes
1answer
133 views

I want to add a user/login to role in another database without switching databases

I have a role / login in a 'new' database mynewdb with a role "emailsender". I have scripted a profile for sp_send_dbmail and am now trying to script adding the the login to the DatabaseMailUserRole ...
8
votes
3answers
9k views

Do roles have an owner in oracle?

Suppose that I have logged in to oracle database using user X, then user X creates some roles using create role command. I want to know that is user X the owner of the roles? Can I select all roles ...
7
votes
3answers
27k views

When creating views, why do users need direct object permissions if they already have the same permissions via a role?

I'm having a problem with permissions in Oracle 10g. I'm hoping someone can help me make sense of this. I have a schema with a table in it. I have granted select on that table to a role. grant ...
7
votes
2answers
10k views

Problem with permissions for user

Hi I'm trying to create a login role (user) with a fairly minimal ability to modify the database. They should be able to select, delete, create triggers, insert and update. I would then like to have ...
7
votes
3answers
10k views

Using the sysadmin role with EXECUTE AS

It is my understanding that I can use the EXECUTE AS OWNER clause as part of a procedure that I create to make the body of that procedure run as a different user. My goal is to execute a command that ...
7
votes
1answer
27k views

Is there a backup operator role in Microsoft SQL Server 2008?

I have a Windows Server 2008 with Microsoft SQL Server 2008 and VERITAS NetBackup installed. Basically, this NetBackup requires a valid logon ID into the SQL Server that is powerful enough to do a ...
7
votes
1answer
2k views

How to GRANT permission to execute stored procedure?

I have an AD group that is setup as a Windows Authenticated SQL Login on my SQL Server. On DatabaseA it has the db_datareader and public roles. So the users of this AD group only have read access to ...
6
votes
2answers
12k views

How to deny drop permission for a table?

Is there a way to deny drop permissions for a specific table from a user or role?
6
votes
2answers
7k views

What is the difference between ROLE and PRIVILEGE for Oracle?

Is role a collection of certain privileges?
6
votes
1answer
677 views

Server roles in SQL Server 2016

There are 3 new server roles in SQL Server 2016: mediumrc largerc xlargerc Any idea what these are for? Google doesn't give any information about these.
6
votes
1answer
4k views

Purpose of SQL Server Application Roles

I've been reading up on application roles in Microsoft SQL server. It seems an application role is like any other role in that permissions on securables can be granted to an application role, however, ...
6
votes
3answers
12k views

How do I detect execute permission granted to a role (when no ON clause was used)

I just spent hours trying to figure out how an app had privilege to execute stored procedures. TIL from StackExchange: Turns out the ON clause is optional for GRANT, allowing a role to be granted ...
6
votes
3answers
2k views

Easily give role in one database access to tables in another database on the same server

I have a database server (Sql Server 2008 R2 Enterprise) supporting two separate but related vendor products, each with it's own database (call them X and Y) In database X I a have a lot of users ...
6
votes
2answers
154 views

Will a SP which writes records fail if accessed by a db_datareader role?

I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that. Recently there ...
6
votes
3answers
356 views

Are there any tables that I can get all Oracle supported system privileges and object privileges?

Are there any tables that I can get all Oracle supported system privileges and object privileges? I know there is a view called all_sys_privs in Oracle. But I can't find more information about it. ...
6
votes
1answer
701 views

How to GRANT object permission to a user defined server role?

I want to create logins that are granted permission to access all user databases view all definitions select all records of all tables To me it seems to be too much overhead to assign the login to ...
6
votes
4answers
1k views

Oracle data dictionary, tell built-in roles apart from custom roles

In Oracle, if you describe the DBA_ROLES view, you only get three columns: SQL> describe dba_roles; Name Null Type ------------------------------------------------ ROLE ...
6
votes
1answer
872 views

Is there a way to give GRANT OPTION recursively for subsets of permissions?

In SQL Server, I would like to create a role that has the ability to manipulate database objects as well as create other roles and grant those roles subsets of its permissions. CREATE ROLE ...
6
votes
0answers
418 views

Roles and policies in Postgresql 9.5 row-level security

Following the the info in posts like these i would like to use the role system and policies, i.e. each user in my system would have a db role. I want to model the following properties but i can't come ...
5
votes
2answers
561 views

Why public is not mentioned as a fixed role in system database in SQL Server?

While running select * from sys.server_principals for public role, in the column is_fixed_role is shown 0 But the documentation states that it is a fixed server role. Documentation Link Although ...
5
votes
1answer
2k views

Postgres 10. Hide users/roles from other users

I have a postgresql 10 installed and I want to hide the name of users/roles to other users. I searched through other posts and somebody suggested this: REVOKE SELECT ON pg_catalog.pg_authid FROM ...
5
votes
1answer
899 views

Cannot add new Server Role SQL Server 2014

It seems that I have the same problem as Yasser in this post I cannot add a new Server Role "SQL Server 2008 R2" but I am running SQL Server 2014. Basically when I right click on Server ...
4
votes
1answer
610 views

How to prevent sysadmin server role from executing a stored procedure

I have a SQL Server default instance using SQL Server Authentication and two users, User1 and User2. User1 has the public server role while User2 has the public server role + sysadmin server role. ...
4
votes
2answers
11k views

SQL Server database level roles for creating tables

Is there a way to create or change a special role for creating tables? Our software developer team has db_datareader and db_datawriter roles but they can not create new tables. I read this article ...
4
votes
2answers
7k views

How to get user-specific settings in PostgreSQL?

I have to collect some information of PostgreSQL roles. One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters (...
4
votes
1answer
10k views

How to grant connect and read/write permission to ALL databases?

I want a SQL Server login that can READ, WRITE and View definition on all databases. After doing: USE master; GRANT VIEW ANY Definition TO [a]; It looks like I have to GRANT CONNECT and read/write ...
4
votes
1answer
4k views

Postgres get function owner

I would like to be able to get a table of function names and function owner names. Currently, this query returns a list of functions for a given schema: SELECT proname FROM pg_catalog....
4
votes
2answers
3k views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 roles....
3
votes
1answer
6k views

Problem granting user privileges via roles in Oracle 12c

I'm starting to learn Oracle 12c so please excuse my bad terminology. I wrote this basic script to set up a user and privileges: CREATE USER wertyq_db_usr IDENTIFIED BY justatest ACCOUNT UNLOCK ...
3
votes
2answers
51 views

How can I determine which permissions needs to be assigned to the newly created login?

We have 4 databases which contain different procedures and functions. Till now the actual application can access the database using only the sa account, now I would like to change the app to use ...
3
votes
2answers
233 views

Can I trust the effective permissions of default database roles?

I've granted membership in the db_datareader and db_datawriter roles for some testing being performed by a specific user. Looking at the names of each role, I know what the roles do. However, I want ...

1
2 3 4 5