Hot answers tagged

34

Set the AD group as a login. And "login" means server level login not the AD concept of user/login. In SQL Server speak, this is a server level principal Create a mapped user in. You shouldn't really permission a user directly on tables. And "user" means database user not the AD concept of user: in SQL Server speak, this is a "database level principal" Add ...


20

List all users who have been assigned a particular role -- Change 'DBA' to the required role select * from dba_role_privs where granted_role = 'DBA' List all roles given to a user -- Change 'PHIL@ to the required user select * from dba_role_privs where grantee = 'PHIL'; List all privileges given to a user select lpad(' ', 2*level) || granted_role "...


19

From the CREATE ROLE documentation: Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only ...


10

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it. As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of ...


10

These terms are not new to SQL 2016, but they are also not server roles as far as I know. For all i know these are pre-built Parallel Data Warehouse resource classes. There isn't much documentation on that except some blog posts. Have a look at these PDW, Integration Services and Resource Classes A teched presentation from Channel 9 I only heard about ...


9

Apples and Oranges. Roles are roles and schemas are schemas. The fact that there is a role called db_accessadmin and also a schema called db_accessadmin does not mean a role is a schema nor that a schema is a role. Roles are security membership containers, a principal is member of a role. Schemas contain database schema bound objects and are owned by a ...


9

Granting permissions on the schema (e.g. dbo) will cascade to all the objects in that schema. For individual exceptions you can just list those explicitly: GRANT SELECT ON SCHEMA::dbo TO [role]; GO GRANT INSERT, UPDATE --, DELETE ON dbo.table_they_can_write_to TO [role]; DENY SELECT ON dbo.table_they_cannot_read TO [role];


8

GRANTing ALL permissions for public to the database is mostly redundant (as public has connect, temporary by default, so you'd only be adding CREATE which you probably don't want to do). You probably expected a GRANT ALL on the database to result in a recursive GRANT ALL to contained schemas and tables. GRANT is not recursive, so this doesn't happen; a GRANT ...


8

Yes, a role is a collection of system and/or object privileges and/or other roles. It simplifies privilege management by allowing you to manage bundles of privileges.


7

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?


7

Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately. Table GroupMemberRoles GroupID FK UserID FK RoleID FK (PK on all three, with perhaps other constraints) Your queries (say, to find the admins of a certain group) should be of the form:...


6

You can query the system catalog with a recursive query, in particular pg_auth_members: WITH RECURSIVE cte AS ( SELECT oid FROM pg_roles WHERE rolname = 'maxwell' UNION ALL SELECT m.roleid FROM cte JOIN pg_auth_members m ON m.member = cte.oid ) SELECT oid FROM cte; BTW, INHERIT is the default behavior of CREATE ROLE and doesn't have to ...


6

Short version: SELECT a.oid FROM pg_authid a WHERE pg_has_role('maxwell', a.oid, 'member'); Here we use a version of pg_has_role that takes a role name as the subject and role oid to test for membership, passing member mode so we test for inherited memberships. The advantage of using pg_has_role is that it uses PostgreSQL's internal caches of role ...


6

My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224)) You are punching a hole in your security by allowing an unprivileged user run as sysadmin role. If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE e.g. Below enables lock ...


6

EXEC msdb.sys.sp_executesql N'ALTER ROLE ...';


6

You indeed have to add the user to the db_datareader and db_datawriter roles for each of your existing databases. Such a query can be used for existing (and ONLINE) user databases: DECLARE @user sysname = 'userTest1'; DECLARE @login sysname = 'userTest1'; DECLARE @SQL nvarchar(max) = ''; SELECT @SQL = @SQL + ' USE ' + QUOTENAME(NAME) + '; IF NOT ...


5

Try using the DDL trigger FOR DROP_TABLE: CREATE TRIGGER NO_DROP_TABLE ON DATABASE FOR DROP_TABLE AS PRINT 'Dropping tables are not allowed' ROLLBACK


5

From the manual: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510 The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather ...


5

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the ALTER SESSION SET_CURRENT_SCHEMA=vwowner Roles are transient. You can do a SET ROLE NONE to ...


5

It looks like you need something like this: GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE contacts TO "user"; By the way, I hope "user" is not an actual user name you chose. Not only does it fail to convey much about the semantics of the role, but you will need to quote it everywhere, which can be something of a bother and lead to confusion when there is ...


5

It is not a system level role, it is assigned in each database. Assign the login "public" and "db_backupoperator" roles in each database it needs to backup. http://www.sqlbackuprestore.com/backupandrestorerights.htm


5

You want to manage your users, roles and user to role mappings in AD? I think Enterprise User Security is what you want, see http://docs.oracle.com/cd/E11882_01/network.112/e10744/concepts.htm#autoId10 Basically you'll need Oracle Enterprise Edition, and, after you've configured your database to use your directory (see http://docs.oracle.com/cd/E11882_01/...


5

If the same user owns both table and stored procedure (usually dbo), then permissions are not checked. This is known as "ownership chaining". So you could have a DENY on the table and it won't be checked. db_datareader makes no difference: it isn't checked. If the owners are different (and note that it's the owner of the schema that matters) then rights ...


5

It can be done but it's generally considered fairly dangerous. At a very basic level you set the trustworthy flag on the database and then when use you execute as on the sp it can take advantage of it's server level principals security access. Because of how dangerous this is I don't want to go into any detail here. However I've blogged about it here ...


5

Right from the manual: The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is ...


5

At the SQL level you can't, since all those tasks are governed by table ownership. The CREATE on a tablespace is required but not sufficient to create an index on a table and store the index in that tablespace. If you don't have the CREATE right on the tablespace you want to put the index in then you cannot CREATE INDEX that index. However, having that ...


4

I just wrote about this in my answer to Granting rights on postgresql database to another user on ServerFault. Basically, the best solution when you have a single user and you want to grant other users the same rights is to turn that user into a group, create a new user with the same name as the original one that's a member of the group, and grant that ...


4

Granting the permissions within SQL Server to an AD Group is relatively straightforward. It can be done either through T-SQL or Management Studio. For instance, if you have an AD group called MYDOMAIN\APPLICATION SUPPORT, you would create the login at the server level,and then use mappings to individual databases to give slightly more granular permissions ...


4

The simple explanation is that the software was not designed to allow that. As Gary pointed out, since roles were designed to be able to be turned on and off the view could be valid for some sessions while it isn’t for others. However, the system could be designed to allow roles to work. What we need is a persistent role or a device similar to a role ...


4

Never a simple answer... For a direct DELETE, a user in both roles won't be able to DELETEDENY always has precedence when permissions are checked For indirect via a stored procedure, the permissions may not be checked if both table and proc have the same owner. So both GRANT and DENY will be ignored. This is called "ownership chaining" Personally, I don't ...



Only top voted, non community-wiki answers of a minimum length are eligible